How can I read multiple tables into a dataset?

I have a stored procedure that returns multiple tables. How can I 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 it...whats the best way to handle this type of query, I am guessing I should read the data into a DataSet? How is the best way to do this?

Thanks.

From stackoverflow

• If you want to read the results into a DataSet, you'd be better using a DataAdapter.

  But with a DataReader, first iterate through the first result set, then call NextResult to advance to the second result set.

• the reader will deal with the result sets in the order returned; when done processing the first result set, call rdr.NextResult() to set for the next one

  note also that a table adapter will automatically read all result sets into tables in a dataset on fill, but the datatables will be untyped and named Table1, Table2, etc.

• Adapted from MSDN:

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

•         connection.Open()
          oledbAdapter = New OleDbDataAdapter(firstSql, connection)
          oledbAdapter.Fill(ds, "First Table")
          oledbAdapter.SelectCommand.CommandText = secondSql
          oledbAdapter.Fill(ds, "Second Table")
          oledbAdapter.Dispose()
          connection.Close()
  

  http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

  bolt.

• * Reading All Excel sheet names and adding multiple sheets into single dataset with table names as sheet names.*

  'Global variables

  Dim excelSheetNames As String()

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

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

  Dim MyConnection As OleDbConnection

  Dim da As System.Data.OleDb.OleDbDataAdapter

  Dim i As Integer

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

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

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

  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