Returning Multiple Resultsets with the DataReader...
By: John Kilgo
Date: February 9, 2003
Download the code.
Printer Friendly Version
Recently I was asked to create a data entry page for a table with 35 columns, 10 of which needed
to be DropDownLists (combo boxes). It seemed very inefficient, and required too many coding key strokes, to
go to the database 10 times to get the values for the DropDownLists. I started looking for a better
way. I did some poking around and came across the NextResult method of the datareader. It turns out
that you can do batch queries (multiple SELECT statements in one string), requiring only one trip to
the database, and then use NextResult to step through each result set to obtain the data.
This requires only one trip to the database and means you do not have to keep resetting the command object
and the datareader for each resultset. I'm rather anal about how my code looks, and I believe using this
technique makes the code "look better".
To illustrate the technique we will select columns from four different tables of the Northwind database.
These columns don't necessarily make any sense application wise, but they will illustrate the technique.
First, the aspx file where we set up our DropDownLists.
<%@ Page Language="vb" Src="ManyResults.aspx.vb" Inherits="DotNetJohn.ManyResults"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>ManyResults</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body>
<form id="Form1" method="post" runat="server">
<table>
<tr>
<td bgcolor="#EEEEEE" align="right">Category Name:</td>
<td><asp:DropDownList ID="ddlCategoryName" Runat="server" /></td>
</tr>
<tr>
<td bgcolor="#EEEEEE" align="right">Company Name:</td>
<td><asp:DropDownList ID="ddlCompanyName" Runat="server" /></td>
</tr>
<tr>
<td bgcolor="#EEEEEE" align="right">Last Name:</td>
<td><asp:DropDownList ID="ddlLastName" Runat="server" /></td>
</tr>
<tr>
<td bgcolor="#EEEEEE" align="right">Product Name:</td>
<td><asp:DropDownList ID="ddlProductName" Runat="server" /></td>
</tr>
</table>
</form>
</body>
</html>
Next is the code-behind file where the technique is illustrated. I have colored blue the pertinent parts
of the technique in the code below. Notice that strSql contains the SELECT statements for all four of
our needed columns. The command object is set only once, and the datareader is executed only once. We
then start stepping through the four resultsets to populate the DropDownLists. Since by default the
datareader positions to the first result set automatically, we only have to call the NextResult method
after the first resultset.
Imports System
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
NameSpace DotNetJohn
Public Class ManyResults : Inherits System.Web.UI.Page
Protected ddlCategoryName As DropDownList
Protected ddlCompanyName As DropDownList
Protected ddlLastName As DropDownList
Protected ddlProductName As DropDownList
Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim dataReader As SqlDataReader
Dim strSql As String
objConn = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
strSql = _
"SELECT CategoryName FROM Categories ORDER BY CategoryName;" _
& "SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName;" _
& "SELECT LastName FROM Employees ORDER BY LastName;" _
& "SELECT Top 10 ProductName FROM Products ORDER BY ProductName;"
objCmd = New SqlCommand(strSql, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader()
'CategoryName
With ddlCategoryName
.DataSource = dataReader
.DataTextField = "CategoryName"
.DataValueField = "CategoryName"
.DataBind()
End With
'CompanyName
dataReader.NextResult()
With ddlCompanyName
.DataSource = dataReader
.DataTextField = "CompanyName"
.DataValueField = "CompanyName"
.DataBind()
End With
'LastName
dataReader.NextResult()
With ddlLastName
.DataSource = dataReader
.DataTextField = "LastName"
.DataValueField = "LastName"
.DataBind()
End With
'ProductName
dataReader.NextResult()
With ddlProductName
.DataSource = dataReader
.DataTextField = "ProductName"
.DataValueField = "ProductName"
.DataBind()
End With
Catch exc As Exception
Response.Write(exc)
Finally
If Not dataReader Is Nothing Then
dataReader.Close()
End If
objCmd = Nothing
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
objConn.Dispose()
End Try
End Sub
End Class
End NameSpace
In conclusion, I hope you can see how easy (and clean) it is to obtain multiple resultsets with one
trip to the database server and then step through the resultsets one at a time to obtain the data you
need.
You may run the program here.
You may download the code here.
|