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 initial approach one may take is just to try and get the results into a
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.
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
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 _
Dim myCommand As New SqlCommand("MySP", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int))
myCommand.Parameters("@MyParam").Value = intMyParam
Dim dr As SqlDataReader = _
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
Dim objTable As New DataTable("MyTable")
Dim objDataRow As DataRow
Dim decAmount As Decimal
Dim strAccount As String
Dim strDesc As String
drData = GetSPResults()
Do Until Not flgNextResult
Do While drData.Read()
If Not drData.IsDBNull(0) Then
decAmount = drData("Amount")
strAccount = drData("Number")
strDesc = drData("Description")
objDataRow = objTable.NewRow()
objDataRow("Number") = strAccount
objDataRow("AmountPY") = decAmountPY
objDataRow("Description") = strDesc
flgNextResult = drData.NextResult()
Finally, we bind the object to a
DataGrid in a
Private Sub BindGrid()
Dim dtData As New DataTable
dtData = CreateTable()
DataGrid1.DataSource = dtData.DefaultView
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.