How do I store multiple results from a stored procedure into a dataset?

Question

How do I combine to result sets from a StoredProcedure into one dataset in ASP.NET?

Below is my code in asp.net

SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter("sp_Home_MainBanner_TopStory",con);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@rows", 9);

DataSet DS = new DataSet();

adap.Fill(DS, "Table1");
adap.Fill(DS, "Table2");

GridView1.DataSource = DS.Tables["Table2"];
GridView1.DataBind();

Even if there were two adapters, how could I combine the results into one dataset?

Answer 1

In MS SQL we create a procedure like:

[ create proc procedureName
    as
    begin
        select * from student
        select * from test
        select * from admin
        select * from result
    end
]

In C#, we write following code to retrieve these values in a DataSet

{
    SqlConnection sqlConn = new SqlConnection("data source=(local);initial catalog=bj001;user id=SA;password=bj");
    SqlCommand sqlCmd = new SqlCommand("procedureName", sqlConn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlConn.Open();
    SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    sqlconn.Close();

    // Retrieving total stored tables from a common DataSet.              
    DataTable dt1 = ds.Tables[0];
    DataTable dt2 = ds.Tables[1];  
    DataTable dt3 = ds.Tables[2];
    DataTable dt4 = ds.Tables[3];  

    // To display all rows of a table, we use foreach loop for each DataTable.
    foreach (DataRow dr in dt1.Rows)
    {
        Console.WriteLine("Student Name: "+dr[sName]);
    }
}