Easing Transition From ASP and ADO to ASP.NET and ADO.Net Part 1/2 - Making the connection

(Page 2 of 5 )

Our first step will be to utilize ADO.NET's DataReader object in a fashion that's very similar to how we've been using the ADO RecordSet object with ASP in the past.

To create an ASP.NET page that uses C# and references common ADO.NET objects, we're going to need to add the following lines to the top of our aspx page (the code below is similar to how we used to put <% LANGUAGE="VBSCRIPT" %> at the top of our classic ASP pages):

<%@ Page language="c#"%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

With these lines in place at the top of our aspx file, we're ready to go. If you're a classic ASP programmer then you're probably already used to writing ADO code that looks like this:

Set cxnPubs = Server.CreateObject("ADODB.Connection")

strCxn = "Provider=SQLOLEDB.1; Persist Security Info=False; User ID=dbmsUser; pwd=secretPwd; Initial Catalog=pubs; Data Source=myPC"

cxnPubs.Open strCxn

Set cmdPubs = Server.CreateObject("ADODB.Command")

Set rsPubs = Server.CreateObject("ADODB.Recordset")

cmdPubs.CommandType = adCmdText ‘ or 1

Set cmdPubs.ActiveConnection = cxnPubs

cmdPubs.CommandText = "SELECT * FROM Authors”

rsPubs.Open cmdPubs, ,adOpenForwardOnly ,adLockReadOnly

As a brief review, we've created a connection to the pubs database on the SQL Server myPC as user dbmsUser with password secretPwd. We've created ADODB command and recordset objects to use with this connection. We set the command's CommandType property to adCmdText, which means that we'll be sending plain-text SQL statements to the database. We then assign a simple SQL select statement to the command's CommandText property and finally used this command object to open the Recordset.

We open the recordset with the adOpenForwardOnly option. We'll rarely have an ASP page that needs to move backwards in our recordset, as it's usually dumping the data directly to the user. We also use adLockReadOnly, since we're not using this recordset to update or otherwise change the information in the Authors table, at least for now anyway.

We can almost exactly duplicate this same code in ASP.NET with very similar objects. To dim and set our connection object with ADO.NET, we use the following lines:

SqlConnection cxnPubs = new SqlConnection("SERVER=myPC; UID=dbmsUser; pwd=secretPwd; Database=pubs");

All the important information's still there –- the user (dbmsUser), the server (myPC), the password (secretPwd), and the database (pubs). ADO.NET has different connection types for a number of different data providers. I'm using SQL Server 7, so my most efficient connection object is the SqlConnection object. The "Sql" in SqlConnection, sure enough, stands for SQL Server, and works with versions 7.0 and 2000. OLEDB connections aren't much different, but they do require different objects.

Next, we move on to our command object:

SqlCommand cmdPubs = new SqlCommand("SELECT * FROM Authors", cxnPubs);

cxnPubs.Open();

I've created an SqlCommand object that accepts a text-based SQL command as its first parameter. C# recognizes this and automatically sets our command type to text, which we can accomplish by setting the CommandType to adCmdText with classic ASP.

Next: Where did my RecordSet go? >>
 

More ADO.NET Articles
More By Ruffin Bailey

Comments On: Easing Transition From ASP and ADO to ASP.NET and ADO.Net Part 1/2

>>> Be the FIRST to comment on this article!