How can I read multiple tables into a dataset?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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 4

        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.

Answer 5

* 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