When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, March 27, 2002

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

By Scott Mitchell


Introduction


If you've developed any data-driven classic ASP applications, you've likely written code similar to the following far too many times:

- continued -

'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


As with classic ADO, when you want to retrieve the results of a database query from a data store, the first task is to create a connection to the data store. In classic ADO, developers used the Connection object (as shown in the example above). With ADO.NET, developers still use a connection object, but should use one appropriate to the data store they are wanting to connect to.

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


The next step is to issue a database query. In classic ADO, one could create a Command object with the proper SQL statement and then populate a Recordset object from the Command object. Alternatively, developers could just populate a Recordset object directly (as in the first example in this article).

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!

  • Read Part 2!



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article