How to bind multiple resultsets into a DataGrid control

Introduction

There are sometimes when you find yourself in a situation where you obtain many resultsets from a given stored procedure, and then you want to bind those results into a DataGrid control. This article shows a very simple example of how to do this.

The Problem

The initial approach one may take is just to try and get the results into a DataSet or DataReader. The problem is that if you get the results in any of these objects, and try to bind it to a DataGrid control, you would only get the results of the first table (resultset) that your stored procedure returns.

After trying to find some help in newsgroups all over the web, I couldn’t find a solution to do this. So I began thinking in an alternative way of how to do it, and I found myself with a very simple answer.

The Solution

I needed an object that could be bound to a DataGrid, so I thought, why not use a DataTable object. So the only thing left to do was to create the DataTable with the results of the different tables that I get from my stored procedure. This can be done very easily with the help of the DataReader object.

The Code

First we write the code for the DataReader that holds the results of the SP.

Public Function GetSPResults(intMyParam As Integer) As SqlDataReader
 Dim myConnection As New _ 
    SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
 Dim myCommand As New SqlCommand("MySP", myConnection)
 myCommand.CommandType = CommandType.StoredProcedure
 myCommand.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int))
 myCommand.Parameters("@MyParam").Value = intMyParam 
 myConnection.Open()
 Dim dr As SqlDataReader = _ 
     myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 Return dr
End Function

Now we create the table object with the fields we need and we add the data from the results in the SP above.

Private Function CreateTable() As DataTable
    Dim drData As SqlDataReader
    Dim flgNextResult As Boolean = True
   
    'create a new empty Table object 
    Dim objTable As New DataTable("MyTable")

    'Add fields to the table
    objTable.Columns.Add("Number", System.Type.GetType("System.String"))
    objTable.Columns.Add("Description", System.Type.GetType("System.String"))
    objTable.Columns.Add("Amount", System.Type.GetType("System.Decimal"))

    Dim objDataRow As DataRow
    Dim decAmount As Decimal
    Dim strAccount As String
    Dim strDesc As String

    'Get the results from the Function above
    drData = GetSPResults()

    'Loop through each resultsets
    Do Until Not flgNextResult
        'Loop through each row of a resultset
        Do While drData.Read()
            If Not drData.IsDBNull(0) Then
                decAmount = drData("Amount")
                strAccount = drData("Number")
                strDesc = drData("Description")
                
                'Generate new row in table
                objDataRow = objTable.NewRow()

                'Add values to fields in the row
                objDataRow("Number") = strAccount
                objDataRow("AmountPY") = decAmountPY
                objDataRow("Description") = strDesc

                'Add the row to the table
                objTable.Rows.Add(objDataRow)
            End If
        Loop
    flgNextResult = drData.NextResult()
    Loop
    drData.Close()

    'Return table object
    Return objTable
End Function

Finally, we bind the object to a DataGrid in a BindGrid function.

Private Sub BindGrid()
    Dim dtData As New DataTable
    dtData = CreateTable()
    DataGrid1.DataSource = dtData.DefaultView
    DataGrid1.DataBind()
End Sub

Conclusion

We have seen one very simple approach of being able to achieve this goal. Now, we can even page, sort or whatever we want to do with our DataGrid. I hope you find this approach useful.