In this article I'll provide you with a look at how ADO.NET has changed from Beta 1 to Beta 2 to make migrating existing code an easier process. Specifically, I'll focus on the following Beta 1 classes and show how they have changed in Beta2:

• Command Class
• Data Reader Class

If you didn't get a chance to work with ADO.NET in Beta 1 but have "classic" ADO experience, you'll be happy to know that many classes found in ADO are also found in ADO.NET. However, ADO.NET introduces several new classes that allow shortcomings found in "classic" ADO to be overcome such as referencing multiple data sources, managing hierarchical data, and working with XML. Before seeing some of the differences between Beta 1 and Beta 2, let's review the key differences between ADO and ADO.NET.

Comparing ADO to ADO.NET

While the "classic" ADO model provides a robust set of functionality it can certainly be improved in several key areas. Although these features were discussed in my previous article, it's worth touching upon them briefly especially if you consider yourself new to ADO.NET.

Connection Model Versus a Disconnected Model

ADO relies on a connection-based model. The following section of "classic" ASP code shows how the ADO connection is left open until the looping logic performed on the RecordSet (rstobj) is completed.

Dim 

dataconn,rstobj,sql

'Create 

Connection

set dataconn = 

Server.CreateObject("ADODB.Connection")

dataconn.open 

"connectionString"

'Create 

Recordset

set rstobj = 

Server.CreateObject("ADODB.Connection")

sql = "SELECT * FROM 

authors"

rstobj.Open 

sql,dataconn,adOpenStatic,adLockReadOnly,adCmdText

'Walk through 

Recordset

Do Until 

rstobj.EOF

For each field in 

rstobj.Fields

  Response.Write(field.Name & "<br>")

Next

rstobj.MoveNext

Loop

'Close 

Objects

rstobj.Close

dataconn.Close

set rstobj = 

nothing

set dataconn = 

nothing

As you retrieve data into an ADO RecordSet, the connection established is maintained while looping or other logic is performed. The problem with leaving the connection open (even if it is for a very short time period) is that it ties up valuable resources that may be needed elsewhere. While disconnected RecordSets are available in ADO, connected RecordSets are typically used in many programs resulting in an unnecessary reduction in available resources. ADO.NET handles this by introducing a new class called the DataSet.

RecordSet Versus DataSet

With ADO.NET, a major shift is made from the connection-based model to a truly disconnected and more message-like model. ADO.NET creates a disconnected version of a RecordSet without having to explicitly tell it that you want things to be disconnected. One benefit of this disconnected model is that data can be retrieved from the data source and the connection can be closed immediately, even if you need to loop through the data within your program.

Wait you cry; I can already do that with "classic" ADO and a disconnected RecordSet! While this is true, even the disconnected RecordSet has been improved upon in ADO.NET. To see a few of these improvements, the next section lists a few features associated with ADO.NET's DataSet class.

The DataSet class functions by storing data in-memory in a disconnected manner. The following list shows some of features/advantages of the DataSet class:

• A DataSet does not interact with the data source directly (i.e. it is disconnected). As such, a DataSet has no knowledge of the data source.
• A DataSet allows for relationships to be created between multiple tables.
• Since a DataSet is capable of holding an in-memory cache containing table relationships, you can walk between tables based upon their relationships.
• A DataSet does not use COM marshaling to transmit data between components. This is much more efficient than the marshalling process found in "classic" ADO and allows a greater number of types to be supported which is important when data is transferred between distributed data sources.
• DataSets have a native serialization format of XML which is useful when transferring data between distributed systems. By using XML schemas, a richer set of data types is exposed.
• Because an XML representation of the data within a DataSet can be accessed directly, DataSets can be passed between any objects (not just COM objects) without being forced to open ports in firewalls (as with ADO Recordsets).

Now that you've seen some of the higher-level differences between ADO and ADO.NET and seen the improvements ADO.NET offers in how it handles connections, data type marshalling, and data structures, let's take a look at the Command and DataReader classes.

The Command and DataReader Classes

ADO.NET has a Command class that functions in a similar manner to the one found in "classic" ADO. It also has a DataReader class than provides a forward-only stream of data records. The Command class can be used to perform updates, inserts, deletes, and select queries against data sources.

Tables 4 and 5 provide a look at the classes used with each managed provider in Beta 1 and Beta 2 to create Command and DataReader objects and execute a query against a data source. Following each table you'll find a code snippet that shows how to instantiate the appropriate Command and DataReader classes.

Table 4. SQL Server Managed Provider Command and DataReader Class differences between Beta 1 and Beta 2.

Beta 1 

Code:

using 

System.Data;

using 

System.Data.SQL;

string cmdString = "SELECT 

ContactName from Customers";

SQLDataReader reader = 

null;

SQLConnection dataConn = new 

SQLConnection();

dataConn.ConnectionString= 

@"server=localhost;uid=sa;pwd=;

                             database=northwind;";

SQLCommand sqlCmd = new 

SQLCommand(cmdString,dataConn);

dataConn.Open();

sqlCmd.Execute(out 

reader);

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

Beta 2 

Code:

using 

System.Data;

using 

System.Data.SqlClient;

string cmdString = "SELECT 

ContactName from Customers";

SqlConnection dataConn = new 

SqlConnection();

dataConn.ConnectionString= 

@"server=localhost;uid=sa;pwd=;

                             database=northwind;";

SqlCommand sqlCmd = new 

SqlCommand(cmdString,dataConn);

dataConn.Open();

SqlDataReader reader = 

sqlCmd.ExecuteReader();

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

Table 5. Ole DB Managed Provider Command and DataReader Class differences between Beta 1 and Beta 2.

Beta 1 

Code:

using 

System.Data;

using 

System.Data.ADO;

string cmdString = "SELECT 

ContactName from Customers";

ADODataReader reader = 

null;

ADOConnection dataConn = new 

ADOConnection();

ADOConnection.ConnectionString= 

@"Provider=SQLOLEDB.1;Data Source=(local);

  uid=sa;pwd=;Initial Catalog=northwind";

ADOCommand cmd = new 

ADOCommand(cmdString,dataConn);

ADOConnection.Open();

cmd.Execute(out 

reader);

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

Beta 2 

Code:

using 

System.Data;

using 

System.Data.OleDb;

string cmdString = "SELECT 

ContactName from Customers";

OleDbConnection dataConn = new 

OleDbConnection();

dataConn.ConnectionString= 

@"Provider=SQLOLEDB.1;Data Source=(local);

  uid=sa;pwd=;Initial Catalog=northwind";

OleDbCommand cmd = new 

OleDbCommand(cmdString,dataConn);

dataConn.Open();

OleDbReader reader = 

cmd.ExecuteReader();

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

Notice that a new method named ExecuteReader() that returns a DataReader class has been added to the different Beta 2 managed provider Command classes. This mechanism for reading through the data in a forward-only manner is much more intuitive and natural than it was in Beta 1. To instantiate the different managed provider Command and DataReader classes and use them in an ASP.NET Beta 2 page, the following code can be used:

<%@Import 

Namespace="System.Data"%>

<%@Import 

Namespace="System.Data.SqlClient"%>

<%@Import 

Namespace="System.Data.OleDb"%>

<script 

language="C#" runat="server">

public void Page_Load(Object 

Src, EventArgs E) { 

string OleDbConnStr = 

@"Provider=SQLOLEDB.1;Data Source=(local);

uid=sa;pwd=;Initial 

Catalog=northwind";

string SqlConnStr = 

"server=localhost;uid=sa;pwd=;database=northwind";

string cmdString = "SELECT 

ContactName from Customers";

//Open SQL Server Managed 

Provider Connection

Response.Write("<p><b>Writing out records from 

" +

                      "SqlDataReader!</b></p><br 

/>");

SqlConnection sqlConn = new 

SqlConnection(SqlConnStr);

SqlCommand sqlCmd = new 

SqlCommand(cmdString,sqlConn);

sqlConn.Open();

SqlDataReader sqlReader = 

sqlCmd.ExecuteReader();

while (sqlReader.Read()) 

{

  Response.Write(sqlReader.GetValue(0) + "<br />");

}

sqlReader.Close();

sqlConn.Close();

//Open ADO.NET Managed Provider 

Connection

Response.Write("<p><b>Writing out records from 

" +

" 

OleDbDataReader!</b></p><br />");

OleDbConnection oleDbConn = new 

OleDbConnection(OleDbConnStr);

OleDbCommand oleCmd = new 

OleDbCommand(cmdString,oleDbConn);

oleDbConn.Open();

OleDbDataReader oleReader = 

oleCmd.ExecuteReader();

//Read through oleReader 

records

while (oleReader.Read()) 

{

  //Write out value of first field (ContactName)

  Response.Write(oleReader.GetValue(0) + "<br />");

}

oleReader.Close();

oleDbConn.Close();

}

</script>

It's important to note that when the Command object is instantiated, several default properties are set. Once of the most important is the type of command being issued? Unless specified, the CommandType property defaults to CommandType.Text. However, you can change the code shown above to call a stored procedure by simply changing the command type as shown below:

SQL Managed Provider (Beta 2):

using 

System.Data;

using 

System.Data.SqlClient;

string cmdString = "SELECT 

ContactName from Customers";

SqlConnection dataConn = new 

SqlConnection();

dataConn.ConnectionString= 

@"server=localhost;uid=sa;pwd=;

                             database=northwind;";

SqlCommand sqlCmd = new 

SqlCommand();

sqlCmd.Connection = 

dataConn;

sqlCmd.CommandText = 

"sp_MyStoredProc";

sqlCmd.CommandType = 

CommandType.StoredProcedure;

dataConn.Open();

SqlDataReader reader = 

sqlCmd.ExecuteReader();

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

Ole DB Managed Provider (Beta 

2):

using 

System.Data;

using 

System.Data.OleDb;

string cmdString = "SELECT 

ContactName from Customers";

OleDbConnection dataConn = new 

OleDbConnection();

dataConn.ConnectionString= 

@"Provider=SQLOLEDB.1;Data Source=(local);

  uid=sa;pwd=;Initial Catalog=northwind";

OleDbCommand cmd = new 

OleDbCommand();

cmd.Connection = 

dataConn;

cmd.CommandText = 

"sp_MyStoredProc";

cmd.CommandType = 

CommandType.StoredProcedure;

dataConn.Open();

OleDbReader reader = 

cmd.ExecuteReader();

while (reader.Read()) 

{

    //Read through records

}

reader.Close();

dataConn.Close();

The Command object in ADO.NET allows you to set parameters that will be passed to a stored procedure just like you could in ADO. Creating parameters for the specific managed providers in Beta 1 involved using the SQLParameter for the SQL Server provider and the parameters collection of the ADOCommand class for the ADO.NET provider.

In Beta 2, the SQL Server provider uses the SqlParameter class while the Ole DB provider uses the OleDbParameter class. The Parameter class data type enumeration names also change in Beta 2. In Beta 1, the SQL Server managed provider used the SQLDataType enumeration to set the data type while the ADO.NET managed provider used the ADODBType enumeration. In Beta 2 this changes to the SqlDbType and OleDbType enumerations respectively.

Summary

In this article I've provided you with an overview of a couple of the "major" changes in ADO.NET from Beta 1 to Beta 2. There are several other changes that have occurred as well with other classes and ASP.NET server controls. The .NET documentation provides excellent coverage of these different classes.

Copyright and Authorship Notice