Return multiple recordsets from stored proc in C#

Question

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

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

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.

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.

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

Answer 1

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

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

Answer 2

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();
    }
}