从 C# 中的存储过程返回多个记录集

Return multiple recordsets from stored proc in C#

提问by user2334626

I am having to convert an ASP classic system to C#

我必须将 ASP 经典系统转换为 C#

I have a stored procedure that can return up to 7 recordsets (depending on the parameters passed in).

我有一个存储过程，最多可以返回 7 个记录集（取决于传入的参数）。

I need to know how I can simply return all the recordsets as individual DataTables so that I can loop through whatever is there, skipping to the next DataTable when I get to the end of it without having to run multiple SQL statements and use multiple adapter.Fill statements to add each table into a DataSet.

我需要知道如何简单地将所有记录集作为单独的 DataTable 返回，以便我可以遍历那里的任何内容，当我到达它的末尾时跳到下一个 DataTable，而不必运行多个 SQL 语句并使用多个适配器。填充语句以将每个表添加到数据集中。

In classic it was a simple Do While not objRS.EOF loop with a objRS.NextRecordset() when I got to the end of the loop to move to the next statement.

在经典中，当我到达循环末尾以移动到下一个语句时，它是一个简单的 Do While not objRS.EOF 循环和 objRS.NextRecordset()。

Is there anything I can use that doesn't require a total rewrite of the current back end code?

有什么我可以使用的不需要完全重写当前后端代码的东西吗？

Each recordset has a different number of columns and rows. They are unrelated to each other. We return multiple recordsets from Stored Proc's to reduce traffic.

每个记录集都有不同数量的列和行。它们彼此无关。我们从存储过程返回多个记录集以减少流量。

Examples would be nice.

例子会很好。

Thanks

谢谢

采纳答案by Sasidharan

SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("name of your Stored Procedure", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@SuperID", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

After this you can take advantage of different (7) recordsets using

在此之后，您可以使用不同的 (7) 记录集

ds.Tables[0]
ds.Tables[1]
ds.Tables[2]
ds.Tables[3]
ds.Tables[4]
ds.Tables[5]
ds.Tables[6]

回答by Ehsan

this will return you all you need

这将返回您所需的一切

using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandText = "yoursp";
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;

        conn.Open();

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        conn.Close();
    }
}

回答by GarethD

If you fill a DataSet using the SqlDataAdapter.Fill()Method then each of your recordsets returned from the stored procedure will be returned as a DataTable within your dataset

如果使用SqlDataAdapter.Fill()Method填充 DataSet ，则从存储过程返回的每个记录集都将作为数据集中的 DataTable 返回

DataSet dataset = new DataSet();
using (var adapter = new SqlDataAdapter("yourStoredProcedure", yourConnectionString))
{
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapter.Fill(dataset);
}
for (int i = 0; i < dataset.Tables.Count; i++)
{
    // Do something for each recordset
}

If you use a SqlDataReader then use can use the SqlDataReader.NextResult()method to advance to the next recordset:

如果您使用 SqlDataReader 则使用 can 使用该SqlDataReader.NextResult()方法前进到下一个记录集：

using (var connection = new SqlConnection(yourConnectionString))
using (var command = new SqlCommand("yourStoredProcedure"))
{
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // do something with first result set;
        }
        if (reader.NextResult())
        {
            while (reader.Read())
            {
                // do something with second result set;
            }
        }
        else
        {
            return;
        }
        if (reader.NextResult())
        {
            while (reader.Read())
            {
                // do something with third result set;
            }
        }
        else
        {
            return;
        }
    }
}

回答by Dhaval

you can check if dr has any more recordset ot no by using if (dr.NextResult())

您可以使用以下命令检查 dr 是否还有更多记录集 if (dr.NextResult())

and then loop again with dr.read

然后用 dr.read 再次循环

try this

尝试这个

string connStr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SqlConnection Con = new SqlConnection(connStr);

try
{
    string str1 = "select productid,productname from products;select VendorFName from vendor";
    SqlCommand com = new SqlCommand(str1, Con);

    com.Connection.Open();

    SqlDataReader dr = com.ExecuteReader();

    DropDownList1.Items.Add("Select Product Id");
    DropDownList2.Items.Add("Select Vendor Name");

    while(dr.Read())
    {
        DropDownList1.Items.Add(dr.GetValue(0).ToString());
    }

    if (dr.NextResult())
    {
        while (dr.Read())
        {
            DropDownList2.Items.Add(dr.GetValue(0).ToString());
        }
    }
}
catch (Exception ex)
{
}
finally
{
    if (Con.State == ConnectionState.Open)
    {
        Con.Close();
    }
}