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
Dim objTable As New DataTable("MyTable")
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
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
objTable.Rows.Add(objDataRow)
End If
Loop
flgNextResult = drData.NextResult()
Loop
drData.Close()
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.