ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


Multiple ResultSets in ADO.NET

by Srinivasa Sivakumar
12/16/2002

Introduction

Most Web Forms applications need to load multiple pieces of data, usually to load up comboboxes or listboxes. Many times we end up querying the database again and again for different lookup table data, and this reduces scalability and adds more stress to the backend database.

With my current ASP.NET application, I faced this challenge. I had to fill about three comboboxes from a SQL Server 2000 database. Initially, I created SqlConnection and SqlCommand objects and I reused them with different SqlDataReader objects to fill the dropdown boxes. But it was a tedious task, since I had to re-execute different queries against the same data source again and again to get the lookup data. Then I figured out that I could get all the results from multiple queries in a single roundtrip to the database. By using batch queries and multiple ResultSets in ADO.NET, I could increase the scalability of my application and improve my user's experience by simply being faster.

DataReaders and DataSets

We can use both the DataReader as well as DataSet to get the functionality that we want. The difference is, we use the DataReader, well be always connected to the database until we fetch all of the records. Using the DataReader object is a faster approach, since it is always connected to the database. But this approach can affect the scalability of the application.

Related Reading

ADO.NET in a Nutshell
By Bill Hamilton, Matthew MacDonald

On the other hand, if you use the DataSet, you will be disconnected and can increase scalability of your application through smart caching. Well fill the DataSet with any number of DataTable objects (optionally, we can also create relationships between the DataTable objects).

The Best Solution

So which is the best solution? Well, it depends on the volatility of the application. The DataReader is faster, but cannot be easily cached. The DataSet is a bit slower, but caches nicely, so that subsequent hits on the page do not require any database access at all.

If your application follows the N-Tier architecture and you are passing the lookup data from the Data Access Layer (DAL), then you may want to adopt the DataSet approach. This is because we cant keep the database connection open between different tiers by using the DataReader object. The other advantage of using them ASP.NET applications is that we can cache the DataSet object in the Application or session, or using the Cache APIs. This cant be done with the DataReader object.

The Solutions

Well, weve had enough introduction to the solution. Let move to the interesting part of the article, the code section. I hear your voice saying, "Come on baby, show me the code!"

The sample ASP.NET page that were going to use is very simple. It is going to have a textbox, textarea, and three dropdown comboboxes. We have to fill the comboboxes with the lookup data.

The SqlDataReader Solution

Lets look at the DataReader solution for SQL Server. Why SQL Server, rather than Oracle? Well, Oracle handler the batch SQL statements differently than SQL Server. Well talk about that a little later in the article.

The server control layout is going to be very simple. Well arrange all the server controls inside a table, as shown below.


<table cellspacing="0" cellpadding="5" align="left" border="0">
  <tbody>
  <tr>
    <td class="ColumnHeading">
    <b>Issue Name</b>:</td>
    <td class="Data">
      <asp:TextBox id="txtIssueName" 
                    runat="server" 
                    MaxLength="100" > 
      </asp:TextBox>
    <font color="red">*</font>
      <asp:RequiredFieldValidator id="reqIssueName" 
            runat="server"
            display="Dynamic" 
            ControlToValidate="txtIssueName"
            ErrorMessage="The Issue Name is required.">
      </asp:RequiredFieldValidator>
    </td>
  </tr>
  <tr>
    <td class="ColumnHeading" valign="top">
    <b>Issue Desc:</b></td>
    <td class="Data">
    <asp:TextBox id="txtIssueDesc" runat="server"  Rows="12"
    TextMode="MultiLine"></asp:TextBox>
    </td>
  </tr>
  <tr>
    <td class="ColumnHeading">
    <b>Submitted Date:</b></td>
    <td class="Data">
    <asp:Label id="lblSubmittedDate" 
                runat="server">
    </asp:Label>
    </td>
  </tr>
  <tr>
    <td class="ColumnHeading">
    <b>Priority:</b></td>
    <td class="Data">
    <asp:Dropdownlist id="ID1" 
                       Runat="server" 
                       EnableViewState="False" >
</asp:Dropdownlist>
     <font color="red">*</font></td>
  </tr>
  <tr>
    <td class="ColumnHeading">
    <b>Status:</b></td>
    <td class="Data">
    <asp:Dropdownlist id="ID2" 
                       Runat="server" 
                       EnableViewState="False" >
</asp:Dropdownlist>
     <font color="red">*</font></td>
  </tr>
  <tr>
    <td class="ColumnHeading">
    <b>User:</b></td>
    <td class="Data">
    <asp:Dropdownlist id="ID3" 
                       Runat="server" 
                       EnableViewState="False" >
</asp:Dropdownlist>
     <font color="red">*</font></td>
  </tr>
  <tr>
    <td>
     </td>
    <td>
    <asp:Button id="btnAddIssue" 
                 runat="server" 
                 Text="Add Issue">
    </asp:Button>
    </td>
  </tr>
  </tbody>
</table>
  

Lets look at the Page_Load event that fills the combobox controls. Weve created a SqlConnction object and opened the connection.


Protected Sub Page_Load(Sender As Object, E As EventArgs)
    Dim cnConnection as SqlConnection = _
         New SqlConnection("server=(local);
                            database=IssuesDB;
                            User ID=sa;
                            Password=!Idontknow2002")
    cnConnection.Open()
  

Then weve included three select statements in the strSQL string variable. After that, were creating a new SqlCommand object using the SqlConnection object and the batch SQL statements.


  Dim strSQL as String = _
      "Select * from Tbl_Lu_Priorities Order By PriorityTitle;
      Select * from Tbl_Lu_Statuses Order By Status;
      Select * from Tbl_Ma_Users Order By Username;"
  Dim cnCommand1 As SqlCommand = _
                        New SqlCommand(strSQL, cnConnection)
  

Tip: You can also execute a T-SQL stored procedure with batch SQL statements to get the dynamic SQL effect.

Then were calling the ExecuteReader() method of the SqlCommand object. This will fetch the first set of results from the command object. Then were binding the SqlDataReader object into the dropdown server control.


  'Can't use the CommandBehavior.CloseConnection attribute
  'since the NextResult() method will connect to the database
  'to get the next set of result set.

  Dim Rdr as SqlDataReader = cnCommand1.ExecuteReader()

  With ID1
    .DataSource = Rdr
    .DataValueField = "PK_Priority_id"
    .DataTextField = "PriorityTitle"
    .DataBind()
  End With
  

After that, were calling the NextResult() method of the SqlDataReader object. This will fill the next result set from into the DataReader object. Then were binding the SqlDataReader object into the second dropdown server control. Were following the process once more to fill the third dropdown server control.


  Rdr.NextResult()

  With ID2
    .DataSource = Rdr
    .DataValueField = "Pk_Status_ID"
    .DataTextField = "Status"
    .DataBind()
  End With

  Rdr.NextResult()

  With ID3
    .DataSource = Rdr
    .DataValueField = "PK_User_ID"
    .DataTextField = "Username"
    .DataBind()
  End With

  'close the reader
  Rdr.Close()

  'Close the DB Connection
  CnConnection.Close()

  'Display todays date
  lblSubmittedDate.Text = Now()
End sub
  

Finally, were closing the DataReader as well as the database connection objects.

Tip: one thing that we have to watch here is that we cant use the CommandBehavior.CloseConnection attribute with the ExecuteReader() method of the SqlCommand object. This is because when we call the NextResult() method to fetch the next set of results from the database, the DataReader object will connect back to the database to fetch the next result set. This will cause a problem if use the CommandBehavior.CloseConnection attribute.

The SQL Server DataSet Solution

The SqlDataSet solution is also very similar to the earlier SqlDataReader solution. The only difference is that were going to fill the DataSet with the lookup data and bind the DataTable inside the DataSet with the dropdown server controls. Lets look at the code.

What weve done here is that, after opening the database connection, were creating a new SqlDataAdopter object with the open database connection object and the batch SQL statement. Then were calling the Fill method of the SqlDataAdopter object and were passing the DataSet objects name.


...
  Dim strSQL as String = _
  "Select * from Tbl_Lu_Priorities Order By PriorityTitle;
  Select * from Tbl_Lu_Statuses Order By Status;
  Select * from Tbl_Ma_Users Order By Username;"

  Dim DS as New DataSet()
  Dim cnAdapter as new SqlDataAdapter(strSQL, cnConnection)
  cnAdapter.Fill(DS)
  

This will create three DataTable objects inside the DataSet object with names such as Table, Table1, and Table2.

Then were binding individual DataTable objects with each dropdown server control object:


  With ID1
    .DataSource = DS.Tables("Table")
    .DataValueField = "PK_Priority_id"
    .DataTextField = "PriorityTitle"
    .DataBind()
  End With

  With ID2
    .DataSource = DS.Tables("Table1")
    .DataValueField = "Pk_Status_ID"
    .DataTextField = "Status"
    .DataBind()
  End With

  With ID3
    .DataSource = DS.Tables("Table2")
    .DataValueField = "PK_User_ID"
    .DataTextField = "Username"
    .DataBind()
  End With
  ...
  

The Oracle Solution

So far, so good. Every things works fine and smoothly. Here comes the Oracle surprise: if we want to execute a batch query with Oracle database, you cant give bunch of SQL statements in a string; the Oracle database will not accept it as a valid SQL statement. The only way the Oracle database can handle the batch select problem is using an Oracle package with a Ref Cursor.

Tip: the Oracle package is a special kind of stored procedure that can group several stored functions, PL/SQL Types, and Items. A ref cursor is a PL/SQL data type that you can use in a query to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly-typed ref cursor.

The Oracle Package

Therefore, to make Oracle work with the batch select method, we have to write an Oracle Package first. Every Oracle Package has two parts; the first is the Package header and the second is the package body. Here is the code.


CREATE OR REPLACE PACKAGE IssuesDBPKG AS
  TYPE IssCur IS REF CURSOR;
  PROCEDURE GetLookupValues (PriCur OUT IssCur, 
                             StaCur OUT IssCur, 
                             UsrCur OUT IssCur);
END IssuesDBPKG;
/
  

In the package header, weve identified the function name with its parameters. As you can see, weve specified that all three parameters are output Ref cursors.


CREATE OR REPLACE PACKAGE BODY IssuesDBPKG AS
  PROCEDURE GetLookupValues (PriCur OUT IssCur, 
                             StaCur OUT IssCur, 
                             UsrCur OUT IssCur)
  IS
    LocalPriCur IssCur;
    LocalStaCur IssCur;
    LocalUsrCur IssCur;
  BEGIN
    OPEN LocalPriCur FOR 
        Select * from Tbl_Lu_Priorities Order By PriorityTitle;
    OPEN LocalStaCur FOR 
        Select * from Tbl_Lu_Statuses Order By Status;
    OPEN LocalUsrCur FOR 
        Select * from Tbl_Ma_Users Order By Username;

    PriCur := LocalPriCur;
    StaCur := LocalStaCur;
    UsrCur := LocalUsrCur;
  END GetLookupValues;
END IssuesDBPKG;
/
  

In the body of the package, weve declared three ref cursor objects and weve opened the cursors in the body of the package. Then weve assigned the open cursors into the return variable declared at the procedure signature.

Well, let's look at how to implement the Oracle package with Oracle drivers. If you look at the Oracle Drivers today, there are plenty of ways you can access an Oracle database, such as ODBC, OLE DB, .NET Managed Provider for Oracle from Microsoft, and ODP.NET (a .NET Oracle native driver from Oracle which is still in Beta 2). Since the availability of the Microsoft driver, Ill use the .NET Managed Provider for Oracle to demonstrate the example.

Tip: the MS Oracle driver is a separate download for .NET Version 1.0, and you can download it here. But for .NET version 1.1, its going to be in the same bundle.

.NET Managed Provider for Oracle

If youve never used the .NET Managed Provider for Oracle, then Id like to give you a brief introduction. The .NET Managed Provider for Oracle is bundled in the System.Data.OracleClient.Dll assembly that is very similar to System.Data.SqlClient.Dll for SQL Server. All of the Oracle ADO.NET Objects start with the keyword Oracle, such as OracleDataReader, OracleCommand, OracleConnection, etc.

If you use ODP.NET all the ADO.NET classes start with the keyword "Ora." Only Oracle can say why this is. I guess it is their trademark, since their OLE DB Driver starts with Ora, and if you look at Oracle Objects of OLE, they all start with Ora. I wish Oracle would follow the same naming conversion as Microsoft, so that if Im not satisfied with the Microsoft .NET driver then I could just replace the Microsoft driver with Oracle's driver, or another third-party driver, and my code will work. Anyway, it is just a thought.

To use the .NET Managed provider, you have to import the namespace System.Data.OracleClient as youd do for any provider.

The OracleDataReader Solution

Well, lets look at how to implement the Oracle package with OracleDataReader object. If you compare the Oracle DataReader solution with SQL Server, there are no huge changes at all. After connecting to the Oracle database using the OracleConnection objects, weve created a new OracleCommand object. Then weve assigned the Oracle package name and the parameter information to the OracleCommand object. Then were calling the ExecuteReader() method of the OracleCommand object to get the DataReader object. The remaining code stays the same.


Protected Sub Page_Load(Sender As Object, E As EventArgs)
  Dim cnConnection as OracleConnection = _
           New OracleConnection("User ID=Ora9i;
                                 Password=!Idontknow2002;
                                 Data Source=ORCL")
  cnConnection.Open()

  Dim cnCommand As new OracleCommand()

  With cnCommand
    .Connection = cnConnection
    .CommandText = "IssuesDBPKG.GetLookupValues"
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add(New OracleParameter("PriCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("StaCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("UsrCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
  End With

  'Can't use the CommandBehavior.CloseConnection attribute
  'since the NextResult() method will connect to the database
  'to get the next set of result set.

  Dim Rdr as OracleDataReader = cnCommand.ExecuteReader()
  

The Oracle DataSet Solution

If we compare the Oracle DataSet solution with the SQL Server solution, it is not much different at all. All weve done is add the Oracle package information to the OracleCommand object, and weve executed the Oracle package instead of the dynamic SQL that we did in the SQL Server solution.


Protected Sub Page_Load(Sender As Object, E As EventArgs)
  Dim cnConnection as OracleConnection = _
        New OracleConnection("User ID=Ora9i;
                              Password=!Idontknow2002;
                              Data Source=ORCL")
  cnConnection.Open()

  Dim cnCommand As new OracleCommand()

  With cnCommand
    .Connection = cnConnection
    .CommandText = "IssuesDBPKG.GetLookupValues"
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add(New OracleParameter("PriCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("StaCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("UsrCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
  End With

  Dim DS as New DataSet()
  Dim cnAdapter as new OracleDataAdapter(cnCommand)
  cnAdapter.Fill(DS)
  

Other than that, both of the solutions are very compatible.

Conclusion

Every database project involves with lookup data and most of the time we end up querying the database more than once to get the lookup data. In this article, Ive shown a very cool way to get all of the lookup data in one roundtrip from the database server. Based on your applications requirement, you can either implement a DataReader or a DataSet solution.

Ive also tried to do the same solution with Access and MySql databases. Unfortunately, both MS Access and MySql drivers didnt understand the batch dynamic SQL statements and both of them dont support stored procedures (MS Access does support stored queries, but cant store multiple separate queries in the same object).

Srinivasa Sivakumar is a software consultant, developer, and writer. He specializes in web and mobile technologies using Microsoft solutions.


Return to ONDotnet.com

Copyright © 2009 O'Reilly Media, Inc.