'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!
