Click here to Skip to main content
15,867,851 members
Articles / Web Development / ASP.NET
Article

How to bind multiple resultsets into a DataGrid control

Rate me:
Please Sign up or sign in to vote.
3.06/5 (17 votes)
18 Nov 20031 min read 122.8K   31   9
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.

VB
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.

VB.NET
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.

VB
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


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey7-Feb-12 19:28
professionalManoj Kumar Choubey7-Feb-12 19:28 
GeneralFollow-Up Question Pin
sueolo12-Sep-05 6:03
sueolo12-Sep-05 6:03 
GeneralWhy use a DataReader and a DataTable Pin
Andrew Wiseman12-May-05 2:09
sussAndrew Wiseman12-May-05 2:09 
GeneralRe: Why use a DataReader and a DataTable Pin
Hugo Flores12-May-05 14:33
Hugo Flores12-May-05 14:33 
QuestionWouldn't this be easier with a SQL UNION clause ? Pin
Erhan Hosca26-Nov-03 4:22
professionalErhan Hosca26-Nov-03 4:22 
AnswerRe: Wouldn't this be easier with a SQL UNION clause ? Pin
Hugo Flores1-Dec-03 4:49
Hugo Flores1-Dec-03 4:49 
QuestionHow about a DataView Pin
Marc Clifton19-Nov-03 13:40
mvaMarc Clifton19-Nov-03 13:40 
AnswerRe: How about a DataView Pin
Hugo Flores1-Dec-03 4:45
Hugo Flores1-Dec-03 4:45 
GeneralRe: How about a DataView Pin
Marc Clifton1-Dec-03 10:56
mvaMarc Clifton1-Dec-03 10:56 

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

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