Reading multiple tables returned by SQLStored Procedure

[nixdaemon000]

Hello all,

Can any one provide me the link or give me an idea how to read all the tables
returned by SQL Stored Procedure?

My Stored procedure returns two tables:

ID Name
-----------
1 Jeff
2 Joe

ID Pages
----------
1 2

i want to read data from both of these tables. I tried using SQLDataReader
and then populating into datatables but then i can only read the values of 1st table. I want to read both the table values. How do i do it? please let me know if you guys have any ideas how to deal with this.

Thanks
Buff

 

[Tom]

Sql Server can return multiple resultsets from a stored procedure that can be poplulated into a dataset with a single call to the SP. You will need to use the DataAdapter's TableMappings to map each of the returned results to each of the DataTables in your DataSet. The first resultset returned from your SP will be given a default name of "Table", the next resultset a name of Table1 etc incrementing by one for each additional resultset being returned.

[COLOR="Blue"]Using [/COLOR]con As [COLOR="blue"]New [/COLOR]SqlConnection(g_strDbConnection)
    [COLOR="blue"]Dim [/COLOR]cmdSelect [COLOR="blue"]As New[/COLOR] SqlCommand
    [COLOR="blue"]Dim [/COLOR]da [COLOR="blue"]As New [/COLOR]SqlDataAdapter

    cmdSelect.Connection = con
    cmdSelect.CommandType = CommandType.StoredProcedure
    cmdSelect.CommandText = [COLOR="darkred"]"spCustomersSelect"[/COLOR]

    da.TableMappings.Add[COLOR="darkred"]("Table"[/COLOR], [COLOR="DarkRed"]"tblCustomers"[/COLOR])
    da.TableMappings.Add([COLOR="darkred"]"Table1"[/COLOR], [COLOR="darkred"]"tblAddresses"[/COLOR])
   
    da.SelectCommand = cmdSelect
    da.Fill(m_dsMyDataSet)

    da.Dispose()
    cmdSelect.Dispose()
[COLOR="blue"]End Using[/COLOR]

 

[nixdaemon000]

Thanks a lot Tom. It worked.