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.
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.
|
Related Reading 
ADO.NET in a Nutshell |
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).
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.
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 CommandBehavior.CloseConnection attribute.
|
The SqlDataSet solution is also very similar to the earlier SqlDataReader solution. The only difference is that we’re going to fill the DataSet with the lookup data and bind the DataTable inside the DataSet with the dropdown server controls. Let’s look at the code.
What we’ve done here is that, after opening the database connection, we’re
creating a new SqlDataAdopter object with the open database connection object
and the batch SQL statement. Then we’re calling the Fill method of the
SqlDataAdopter object and we’re 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 we’re 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
...
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 can’t 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.
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, we’ve identified the function name with its parameters. As you can see, we’ve 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, we’ve declared three ref cursor objects and we’ve opened the cursors in the body of the package. Then we’ve 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, I’ll 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, it’s going to be in the same bundle.
If you’ve never used the .NET Managed Provider for Oracle, then I’d 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 I’m 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 you’d do for any provider.
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, we’ve created a new OracleCommand object. Then we’ve
assigned the Oracle package name and the parameter information to the
OracleCommand object. Then we’re 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()
If we compare the Oracle DataSet solution with the SQL Server solution, it is not much different at all. All we’ve done is add the Oracle package information to the OracleCommand object, and we’ve 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.
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, I’ve 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.
I’ve also tried to do the same solution with Access and MySql databases. Unfortunately, both MS Access and MySql drivers didn’t understand the batch dynamic SQL statements and both of them don’t support stored procedures (MS Access does support stored queries, but can’t 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.