Revisiting the Use of ADO in .NET Applications

 

Dino Esposito
Wintellect

August 9, 2001

In a previous installment of this column, I talked about the tools and the techniques available within the .NET Framework to import ADO code in .NET applications. In that article, I ended up creating DataTable-derived classes based on an ADO Recordset schema and contents.

Starting with Beta 2, using such a handcrafted class is no longer needed to import a recordset in a .NET application. The data adapter class now natively provides the ability to import the information held in ADO recordsets into .NET data structures.

Beta 2 sheds some light on how you should approach the common problem of re-using or revitalizing existing ADO code. Any middle-tier made of data access components that internally use ADO and return recordsets to ASP pages can be ported to .NET more easily and progressively. For example, you can start updating the server front-end, moving from ASP to ASP.NET pages, but preserve all of your business and data access components.

The data adapter class is the bridge between the Web server and anything that resides behind it—a COM+ component that returns a recordset, a Web Service that outputs XML streams, a simple stored procedure.

Let's see how the data adapter class works when it comes to importing an ADO Recordset into ASP .NET pages.

Setting Up for ADO

A page that wants to use ADO always ends up having a similar heading:

<%@ Page Language="VB" %>
<%@ Assembly Name="ADODB" %>
<%@ Import Namespace="ADODB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

The @Assembly directive is used to link an assembly to the current page. The association takes place at compile time. A page that declares an assembly imports all of its classes and interfaces for use within the page. Notice, though, that you don't need the @Assembly directive to link to all the assemblies found in the application's Bin directory. They are automatically linked to pages within that application unless the line

<add assembly="*"/>

is removed or modified in the <assembly> section of the application's web.config file.

You normally identify a compiled assembly by name (without extension). However, you can also link to source file that will be dynamically compiled and linked to the page. In this case, the @Assembly syntax changes accordingly:

<%@ Assembly Src="path_to_file" %>

The @Import directive is not strictly necessary to use the classes in the assembly. However, consider that:

<%@ Import Namespace="ADODB" %>

lets you call any class in the ADODB assembly (that is, Recordset) without prefixing it with namespace information. So you can create a new recordset with:

Dim rs As Recordset rs = New Recordset

rather than with:

Dim rs As ADODB.Recordset rs = New ADODB.Recordset

In no case is the sole use of the @Import directive sufficient to successfully access ADODB and any other assembly. Any ADODB.dll you may have created with Beta 1 tools (either Visual Studio® .NET or tlbimp.exe) will not work with the Beta 2 platform. So you should recreate it using the same tools. However, Beta 2 gently provides you with a ready-to-use copy of ADODB.dll that you find available in the folder:

C:\Program Files\Microsoft.NET\Primary Interop Assemblies

Importing ADODB.dll is the only task you need to accomplish if your goal is merely activating the ADO library in the folds of a .NET application. However, this is an approach that won't take you too far.

With Beta 2, you have a much more effective alternative. If you limit to import the ADO assembly, you have the problem of effectively using the data you fetch in the context of a Web or Windows Form. Such applications, in fact, use .NET data-bound controls that don't know how to work with ADO recordsets. For example, once you hold a recordset, you must employ the following code to generate a list of data-bound check boxes:

String buf = "";
while (!adoRS.EOF)
{
   buf += "<span>";
   buf += "<input type=checkbox name=theEmployee value=" + 
        adoRS.Fields["employeeid"].Value.ToString() + 
        ">";
   buf += "&nbsp;&nbsp;";
   buf += adoRS.Fields["lastname"].Value.ToString();
   buf += "</span><br>";
   adoRS.MoveNext();         
}
adoRS.Close();

The same results could have been obtained more easily through a CheckBoxList Web control. But this control can be bound only by a .NET data source like a DataTable class.

The OleDbDataAdapter class, defined in the System.Data.OleDb namespace, provides for a Fill method that converts an ADO recordset into a stand-alone DataTable class or inserts it into an existing DataSet.

Note OleDbDataAdapter is the Beta 2 class that replaces ADODataSetCommand. The Fill method, instead, is the counterpart of the FillDataSet method.

Another subtle problem that you will face when trying to port ADO code as-is to .NET is the language compatibility. In most cases, you have VBScript or Visual Basic code that uses ADO making an extensive use of default properties and optional arguments. Unfortunately, neither VBScript nor Visual Basic is really identical to Visual Basic .NET or C#, and optional arguments are not supported. So your existing ADO code is destined for extensive editing because you need to bind to the .NET data-aware controls and the language limited compatibility.

ADO code ceases to be a severe problem if you hide it in a COM or COM+ business object that returns a recordset and then use the OleDbDataAdapter class to transform that content in a .NET native data management class like DataTable or DataSet.

The OleDbDataAdapter Class

In general, a data adapter represents one or more commands that are issued over an existing database connection. Through this object, you can read data into an in-memory container like the DataSet or you can update the data source through a batch update mechanism. The data adapter class is one of the key components of the architecture of a .NET managed provider.

OleDbDataAdapter lets you fetch disconnected data out of an OLE DB provider, while SqlDataAdapter does the same for SQL Server 7.0+ databases. Fill is the data adapter's method responsible for populating ADO.NET data stores such as DataTable and DataSet.

Fill is a polymorphic method as it counts a number of different prototypes. Essentially, Fill is supposed to add or refresh rows in a DataSet class. It can do that using a range of different combinations of arguments. For example, Fill can be used to populate a stand-alone DataTable or to append a new DataTable element in an existing DataSet class.

public override int Fill(DataSet);
public override int Fill(DataSet, String);
public int Fill(DataTable);

The method returns the number of rows that have been successfully added to the data structure or refreshed upon termination. The Fill method knows how to handle multiple tables of data that the command execution may generate. If Fill is run against a DataSet, multiple DataTable objects will be created using a conventional naming schema for extra tables. If Fill runs against a stand-alone DataTable, only the first resultset is considered.

The Fill method can also work with ADO Recordset and Record objects. When used with ADO elements, the data adapter command does not strictly act to retrieve data but limits to "adapt" existing data to a .NET data structure.

Ways to Fill

The versions of Fill that work with ADO objects are prototyped as follows:

public int Fill(DataTable, Object);
public int Fill(DataSet, Object, String);

The arguments of type Object can represent either a Recordset or a Record that you are responsible to provide. Typically, you get instances of any of these objects prior to calling the Fill method using either a data access component or plain ADO code. For example,

Recordset adoRS = new Recordset();
adoRS.Open(strCommandText, strConnString, 
CursorTypeEnum.adOpenForwardOnly,
   LockTypeEnum.adLockReadOnly, 0);

You can choose to merge the recordset (or the single record) with a stand-alone DataTable object or a particular table within a DataSet class. The container object—be it the DataTable or the DataSet—must exist at the time that Fill is called. In other words, Fill can add a new DataTable to an existing DataSet, but cannot create from the ground up a new DataSet or a new DataTable.

The following code adds a new Employees table to new and empty DataSet. The contents of the table reflects contents and schema of the specified ADO recordset:

OleDbDataAdapter oda = new OleDbDataAdapter();
DataSet ds = new DataSet();
oda.Fill(ds, adoRS, "Employees");

If the DataSet already exists and contains an Employees table, then the method will try to merge the given recordset with the existing, table taking into account any primary key information that may be present.

Normally, when the Fill method creates a new DataTable it replicates the Recordset structure, limiting to column names. However, it also adds appropriate primary key and constraint information if the MissingSchemaAction property of the data adapter is set to AddWithKey.

OleDbDataAdapter oda = new OleDbDataAdapter();
oda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
oda.Fill(ds, adoRS, "Employees");

Before you call the Fill method, the recordset should be open. However, if it is closed, no error occurs but the data adapter jumps to the next resultset if any is found.

The Fill method automatically handles multiple resultsets that might be generated by the query command. The data adapter creates as many tables as needed. The first table is given the specified name; the others are named after the first with a progressive index. For example, Employees1, Employees2, and so on.

When the Fill method terminates, the recordset is closed. So if you call:

adoRS.Close();

a runtime error results.

Filling a Stand-alone DataTable

When you put the Recordset content in a DataSet, the Recordset is automatically closed and becomes unusable unless you recreate it. This behavior is by design.

If this feature does not meet your requirements, you can use Fill to copy the Recordset to a stand-alone DataTable object, namely an object not (yet) associated with a DataSet.

OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable("Employees");
oda.Fill(dt, adoRS);

In this case, the Recordset is not closed at the end of the method. So, if the recordset is disconnected or relies on server cursors, you can continue using it to refresh the in-memory data or update the data source.

The behavior of the Fill method when it works on a stand-alone DataTable class looks nearly identical to the AdoDataTable class I described in my previous Voices article on ADO in .NET.

Assorted Annotations

In summary, importing a recordset through a DataSet or a DataTable creates some differences:

  • The recordset is automatically closed if you copy it to a DataSet. It is left open if you copy it to a stand-alone DataTable.
  • If the target is a DataSet, the adapter loops through all the resultsets that may have been generated. Only the first table is taken into account when the target is a DataTable.
  • A stand-alone table can be added to a DataSet at a later time.

Whatever implementation of Fill you use, primary key information is not added by default, but only if you properly set the MissingSchemaAction property of the adapter or if you add a preliminary call to the adapter's FillSchema method. This call will populate the given DataTable or DataSet object with schema information, including keys and constraints. If a primary key is defined on the table, any new row whose primary key value matches an existing row is treated as an update, otherwise it is processed like an insertion.

When used in the context of a .NET application, a Recordset or a Record object is perceived as a native .NET object. In fact, its programming interface includes some basic properties and methods that are common to all .NET objects. In addition, the type of any column is converted to a valid .NET type to be safely stored in a DataTable or DataSet object.

Importing Hierarchical Recordsets

If you try to import a shaped Recordset—one that has been created through the MSDataShape OLE DB provider—the child recordset field is simply ignored when building the DataTable. For example, consider the following shape command.

SHAPE {select * from authors where au_id<6} 
APPEND ({select * from [title author]} AS chapter RELATE au_id TO au_id)

In ADO, for each record taken from the Authors table, you have an extra Chapter field being a Recordset that you can process at leisure. This Chapter field does not appear in the DataTable that the data adapter works on.

Curiously, if you try to directly access the Chapter field and transform it into a DataTable,

DataTable tmp = new DataTable("Tmp");
oda.Fill(tmp, adoRS.Fields["chapter"].Value);

no automatic filter is applied on the related field, and all the records of the child table are returned. If you want to obtain the "right" child DataTable that is specific of the current recordset's row, do the following:

DataTable tmp = new DataTable("Tmp");
oda.Fill(tmp, adoRS.Fields["chapter"].Value);
detailsGrid.DataSource = tmp.DefaultView;
tmp.DefaultView.RowFilter = "au_id=" + adoRS.Fields["au_id"].Value;
detailsGrid.DataBind();

The child recordset populates a DataTable and the DataTable, properly filtered, is then displayed through a datagrid.

Summary

With Beta 2, importing ADO recordsets is a beneficial ADO.NET feature that can really save your investments. However, this doesn't mean that ADO and ADO.NET are interchangeable and fully compatible models. In terms of syntax and object model, although similar, they are radically different, but any ADO skills you have are still useful.

Dialog Box: ODBC .NET Is Coming!

Beta 2 documentation explicitly states that the OLE DB provider for ODBC is not supported. How can I access my data sources through ODBC drivers?

No worries! ODBC .NET is almost here.

Recently, in fact, Microsoft posted the Beta 1 of the newest born in the happy family of .NET managed providers. You can download it following the links available in the .NET Developer's Center.

For being functional, the ODBC .NET Data Provider requires the installation of Microsoft Data Access Component 2.6 or later. However, this is a common requirement for .NET managed providers and MDAC 2.7 is installed with the Framework.

Once installed, you can exploit a brand new namespace called System.Data.Odbc. It is added to the Global Assembly Cache and available as a native part of the framework immediately after setup.

While the ODBC .NET provider is expected to work with any ODBC-compliant driver as of today (Beta 1), it is guaranteed to work only with the SQL Server ODBC Driver, Microsoft's ODBC Driver for Oracle and the Jet driver.

The set of namespace classes comprise: OdbcConnection, OdbcDataAdapter, OdbcCommand, OdbcDataReader, OdbcParameter, OdbcTransaction and OdbcCommandBuilder.

The OdbcType enumeration is used to ensure that the type of command parameters is compliant with the ODBC specification.

With the advent of this .NET provider, accessing data through ODBC is comparable in terms of performance to the OLE DB .NET provider. A minimal extra layer—the driver—is required between the data source and the calling code.

While the structure of the namespace seem to be well established, Microsoft guarantees no compatibility between Beta 1 and future beta or production releases of the ODBC .NET provider. Expect to read more on ODBC .NET in this column very soon!

 

Dino Esposito works for Wintellect,Non-MS link where he takes on ADO.NET and ASP.NET training and consulting. He is the co-founder of VB-2-The-Max,Non-MS link and also contributes the Cutting Edge column to MSDN Magazine. You can reach Dino at dinoe@wintellect.com.