Returning Multiple Resultsets with the DataReader...
By: John Kilgo Date: February 9, 2003 Download the code.

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 download the code here.