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?

Solution 1

A DataSet contains Tables. For your above example, if you had two SqlDataAdapters, each calling a stored procedure and stored them like you did above.

adapter1.Fill(DS, "Table1");
adapter2.Fill(DS, "Table2");

This will take the table results from your first query and store it in the DataSet DS as Table1. It will then store another Table (Table2) in the same DataSet. To access these tables you use the following code:

DS.Tables["Table1"]  //Or Table2, or whatever you name it during your Fill.

You already have the right process, you just need to look up how a DataSet works and decide how you want to call your information.

IF you want to combine your results into one DataTable however, you will need to iterate through the tables and combine information.

ex:
DataTable combinedTable = new DataTable();
//Create columns

foreach (DataRow row in DS.Tables["Table1"].Rows)
{
    //Create rows?  Copy information over?  Whatever you want to do.
}

Solution 2

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

Solution 3

try using this:

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

this works here so...