How can I return a stored procedure's result as XML?

Question

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:

PS: Info.cs class already created.

[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:

    [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;

    }

Answer 1

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

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;