How to save multiple results from a stored procedure to a dataset?

Question

How can I combine with result sets from 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 data set?

Answer 1

The DataSet contains tables. For your example above, if you have two SqlDataAdapters, each of which calls a stored procedure and stores them, as you did above.

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

This will result in the table results from your first query and save it in the DataSet DS in table 1. Then it will save another table (Table 2) 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 see how the DataSet works, and decide how you want to name your information.

IF you want to combine the results into one DataTable, you will need to iterate over the tables and combine the 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. } 

Answer 2

In MS SQL we create a procedure such as:

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

In C# , we write the 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]); } } 

Answer 3

try using this:

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

it works here like this ...