Multiple ResultSets in ADO.NETby Srinivasa Sivakumar
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, we’ll 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.
On the other hand, if you use the DataSet, you will be disconnected and can increase scalability of your application through smart caching. We’ll 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 can’t 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 can’t be done with the DataReader object.
Well, we’ve 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 we’re 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
Let’s 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. We’ll talk about that a little later in the article.
The server control layout is going to be very simple. We’ll 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>
Let’s look at the
Page_Load event that fills the combobox controls. We’ve
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 we’ve included three
select statements in the
strSQL string variable.
After that, we’re 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 we’re calling the
ExecuteReader() method of the SqlCommand object. This
will fetch the first set of results from the command object. Then we’re 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, we’re calling the
NextResult() method of the SqlDataReader object.
This will fill the next result set from into the DataReader object. Then we’re
binding the SqlDataReader object into the second dropdown server control. We’re
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, we’re closing the DataReader as well as the database connection objects.
Tip: one thing that we have to watch here is that we can’t 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
Pages: 1, 2