如何以XML格式返回存储过程的结果？

I coded a web service connecting to MS-Sql server and getting the results from a stored procedure. My below code is getting only the first result of procedure, but I need all results in the response xml file. I think that I need to use data adapter, data reader, data fill, etc.. something like this. But I could not succeed. I would appreciate any help:

我编写了一个连接到MS-Sql服务器的Web服务，并从存储过程中获取结果。我的下面的代码只获得过程的第一个结果，但我需要响应xml文件中的所有结果。我认为我需要使用数据适配器，数据读取器，数据填充等。这样的事情。但我不能成功。我将不胜感激任何帮助：

PS: Info.cs class already created.

PS：Info.cs类已经创建。

[WebMethod]
public Info NumberSearch2(string no)
{
    Info ourInfo = new Info();
    string cs = ConfigurationManager.ConnectionStrings["DBBaglan"].ConnectionString;

    using (SqlConnection Con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand(cmdText: "NumberSearch", connection: Con)
        {
            CommandType = CommandType.StoredProcedure
        };

        SqlParameter parameter = new SqlParameter
        {
            ParameterName = "@no",
            Value = no
        };

        cmd.Parameters.Add(parameter);
        Con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        DataSet ds = new DataSet();

        while (dr.Read())
        {
            ourInfo.PartNo = dr["PartNo"].ToString();
            ourInfo.BrandNo = dr["BrandNo"].ToString();
            ourInfo.Manufacturer = dr["Manufacturer"].ToString();
            ourInfo.Country = dr["Country"].ToString();
            ourInfo.ReferenceNo = dr["ReferenceNo"].ToString();
        }
    }

    return ourInfo;
}

After @David 's recommendation:

在@David的推荐之后：

    [WebMethod]
    //public Info NumberSearch2(string no)
    public List<Info> NumberSearch2(string no)
    {
        Info ourInfo = new Info();
        var ourInfos = new List<Info>();

        string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBBaglan"].ConnectionString;
        using (System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(cs))
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(cmdText: "NumberSearch", connection: Con)
            {
                CommandType = System.Data.CommandType.StoredProcedure
            };

            System.Data.SqlClient.SqlParameter parameter = new System.Data.SqlClient.SqlParameter
            {
                ParameterName = "@no",
                Value = no
            };

            cmd.Parameters.Add(parameter);
            Con.Open();
            System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
            DataSet ds = new DataSet();
            while (dr.Read())

            {
                var ourInfos = new Info();
                ourInfo.PartNo = dr["PartNo"].ToString();
                ourInfo.BrandNo = dr["BrandNo"].ToString();
                ourInfo.Manufacturer = dr["Manufacturer"].ToString();
                ourInfo.Country = dr["Country"].ToString();
                ourInfo.ReferenceNo = dr["ReferenceNo"].ToString();
                ourInfos.Add(ourInfo);
            }
        }

        return ourInfos;

    }

1 个解决方案

#1

You're returning a single Info object. If you want a collection of them, return a List<Info> instead. Change the method signature:

您将返回单个Info对象。如果您想要它们的集合，请返回List 。更改方法签名：

public List<Info> NumberSearch2(string no)

Declare the object to return:

声明要返回的对象：

var ourInfos = new List<Info>();

Within your loop, add each record to the list:

在循环中，将每条记录添加到列表中：

while (dr.Read())
{
    var ourInfo = new Info();
    ourInfo.PartNo = dr["PartNo"].ToString();
    ourInfo.BrandNo = dr["BrandNo"].ToString();
    ourInfo.Manufacturer = dr["Manufacturer"].ToString();
    ourInfo.Country = dr["Country"].ToString();
    ourInfo.ReferenceNo = dr["ReferenceNo"].ToString();
    ourInfos.Add(ourInfo);
}

And return the list:

并返回列表：

return ourInfos;

#1