How can I read multiple tables in a dataset?

Question

I have a stored procedure that returns multiple tables. How to execute and read both tables?

I have something like this:

SqlConnection conn = new SqlConnection(CONNECTION_STRING); SqlCommand cmd = new SqlCommand("sp_mult_tables",conn); cmd.CommandType = CommandType.StoredProcedure); IDataReader rdr = cmd.ExecuteReader(); 

I'm not sure how to read this ... How can I handle this type of request, I assume that I should read the data in a DataSet? What is the best way to do this?

Thanks.

Answer 1

Adapted from MSDN :

 using (SqlConnection conn = new SqlConnection(connection)) { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(query, conn); adapter.Fill(dataset); return dataset; } 

Answer 2

If you want to read the results in a DataSet, you better use the DataAdapter.

But with DataReader, we first iterate over the first result set, and then call NextResult to go to the second result set.

Answer 3

the reader will deal with result sets in return order; when processing the first result set is completed, call rdr.NextResult () to set the following

also note that the table adapter will automatically read all result sets into tables in the data set when populating, but the data data will be displayed without a name called Table1, Table2, etc.

Answer 4

* Read all Excel sheet names and add multiple sheets to a single data set with table names in the form of sheet names. *

'' Global variables

Dim excelSheetNames As String ()

Dim DtSet As System.Data.DataSet = New DataSet ()

Private Sub btnLoadData_Click (sender ByVal As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click

Dim MyConnection As OleDbConnection

Dim da As System.Data.OleDb.OleDbDataAdapter

Dim I'm As Integer

MyConnection = New System.Data.OleDb.OleDbConnection ("provider = Microsoft.Jet.OLEDB.4.0;

data source = SStatus.xls; Advanced Properties = "Excel 8.0; HDR = NO; IMEX = 1" "")

', the following method gets all the Excel sheet names in the globabal excelSheetNames array

GetExcelSheetNames ("SStatus.xls")

  For Each str As String In excelSheetNames da = New OleDbDataAdapter("select * from [" & str & "]", MyConnection) da.Fill(DtSet, excelSheetNames(i)) i += 1 Next DataGridView1.DataSource = DtSet.Tables(0) End Sub 

Public Function GetExcelSheetNames (ByVal excelFileName As String)

  Dim con As OleDbConnection = Nothing Dim dt As DataTable = Nothing Dim conStr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + excelFileName & ";Extended Properties=Excel 8.0;" con = New OleDbConnection(conStr) con.Open() dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) excelSheetNames = New String(dt.Rows.Count - 1) {} Dim i As Integer = 0 For Each row As DataRow In dt.Rows excelSheetNames(i) = row("TABLE_NAME").ToString() i += 1 Next End Function