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