Return multiple recordsets from stored proc in C#

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]

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

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:

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

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