Click here to Skip to main content
Click here to Skip to main content

How to bind multiple resultsets into a DataGrid control

, 18 Nov 2003
Rate this:
Please Sign up or sign in to vote.
This article describes 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Hugo Flores
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey7-Feb-12 19:28 
GeneralFollow-Up Question Pinmembersueolo12-Sep-05 6:03 
GeneralWhy use a DataReader and a DataTable PinsussAndrew Wiseman12-May-05 2:09 
GeneralRe: Why use a DataReader and a DataTable PinmemberHugo Flores12-May-05 14:33 
QuestionWouldn't this be easier with a SQL UNION clause ? PinmemberErhan Hosca26-Nov-03 4:22 
AnswerRe: Wouldn't this be easier with a SQL UNION clause ? PinmemberHugo Flores1-Dec-03 4:49 
QuestionHow about a DataView PineditorMarc Clifton19-Nov-03 13:40 
AnswerRe: How about a DataView PinmemberHugo Flores1-Dec-03 4:45 
GeneralRe: How about a DataView PineditorMarc Clifton1-Dec-03 10:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 19 Nov 2003
Article Copyright 2003 by Hugo Flores
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid