Efficiently Iterating Through Results from a Database Query using ADO.NET

Introduction

'Create a connection to the database Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open connectionString 'Specify a SQL query const strSQL = "SELECT * FROM TableName" 'Create a Recordset object, populate it with the query results Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open strSQL, objConn 'Loop through the Recordset Do While Not objRS.EOF do something objRS.MoveNext Loop

Here, do something may be any number of things, but most commonly involved displaying all, or parts, of the record (perhaps in an HTML table). To display database results through an ASP.NET page, developers no longer need to loop through the results of a database query; instead, they can use databinding and one of the many ASP.NET data Web controls. There are, however, times when a .NET developer does need to loop through database results one at a time. This article examines how to loop through database results using ADO.NET in an ASP.NET Web page.

First Things First - Connnecting to the Data Store

With the .NET Framework, there are two data providers: an OleDb data provider and a SqlClient data provider. The OleDb data provider is for connecting to any OLE-DB compliant data store (Access, Excel, Microsoft SQL Server, Oracle, etc., essentially any modern data store). The SqlClient data provider is specifically for Microsoft's SQL Server, versions 7.0 and up. The SqlClient data provider provides much faster access to SQL Server than the OleDb equivalent; therefore, if you are using Microsoft SQL Server 7.0 or up, you should consider using the SqlClient data provider. (For those who wish to connect via ODBC, there is an ODBC data provider for .NET, but it needs to be downloaded from Microsoft's Web site (here).

Each data provider contains its own connection object class. The OleDb data provider has an OleDbConnection class and the SqlClient data provider has a SqlConnection class. These classes are found in the System.Data.OleDb and System.Data.SqlClient namespaces, respectively, so, depending on what data provider you choose to use, you'll need to Import the appropriate namespace into your ASP.NET Web page (we'll see an example of this shortly).

For this article, we will be connecting to an Access database, so we'll use the OleDbConnection class to connect to the database. To make the connection we need an applicable connection string, which, for Access, is fairly simple: we just need to include the provider name (Microsoft.Jet.OLEDB.4.0 for Access 2000) and the physical path to the .mdb file. The example below illustrates how to connect to an Access database:

<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> sub Page_Load(sender as Object, e as EventArgs) Dim connString as String connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & _ "C:\Inetpub\wwwroot\Projects.mdb;" Dim objConnection as OleDbConnection objConnection = New OleDbConnection(connString) objConnection.Open() 'open the connection ...

Note the Import directive at the top of the code. This is needed so that we can use the OleDbConnection class without having to fully qualify the classname as System.Data.OleDb.OleDbConnection. (We also include the System.Data namespace because we'll need to use classes in it later on in our example.) In our Page_Load event handler (which is fired every time the ASP.NET Web page is viewed) we first create a String variable named connString that holds the connection string information to the database. (Note that we could have used Server.MapPath, like in classic ASP, instead of hard coding the directory information (C:\Inetpub\wwwroot\).)

Next, an instance of the OleDbConnection object is created and the connection string is passed into the constructor. Lastly, the Open method is called to actually open the connection to the database. At this point, we have an open connection to our Access database, Projects.mdb.

Issuing a Database Query

With ADO.NET, you need to create an appropriate Command object (as with the connection objects, this depends on the data provider you are using: you can use the OleDbCommand class or the SqlCommand class). The Command object has two important properties: CommandType, which we use to specify if we are issuing a stored procedure, a specific table name, or a SQL query; and CommandText, where we actually specify the text for the Command (such as SELECT * FROM Tablename if we are doing a SQL query; or TableName if we are doing a query by table name only).

The following example illustrates how to create an OleDbCommand object to perform a query against our Access database. The code below is a continuation from the previous code example, and should follow the connection-opening code in the Page_Load event handler:

... 'Specify our SQL statement Dim strSQL as String = "SELECT * FROM Department" 'Create the Command object Dim objCommand as OleDbCommand objCommand = New OleDbCommand(strSQL, objConnection) ...

In the above example we first specify our SQL statement, placing it in a String variable named strSQL. Next, we create an OleDbCommand object, passing in the SQL string and connection object in the constructor. (Note that by specifying the CommandText in the constructor (the strSQL variable), you are implicitly setting the CommandType to Text.)

Now that we now how to connect to a database and issue a SQL command, we only need to get the query results back in some useful object to be able to iterate through the results. In Part 2 we'll examine how to do just this!