Reading Data Reader Secrets

Reading Data Reader Secrets-by Dino Esposito-April 12, 2001- To be archived April 26, 2001

When talking about ADO.NET, you can hardly ignore ADO. If not for other reasons, you end up mentioning ADO just because its name is hard-coded in some of the .NET exclusive classes for data access.

In its simplest form, ADO.NET is just what the name suggests: ADO adapted to .NET. While this definition is true, it does require some further explanation.

ADO and ADO.NET have different design centers, but both attempt to provide the same logical set of functionality. It's only that in doing so that each looks after its own design pattern. As a result, you have two rather disjointed APIs with consequent issues in terms of backward compatibility.

However, looking beyond the immediate differences of syntax and semantics, you cannot help but recognize a common substrate that is the foundation of the most recent Microsoft data access strategy.

In my previous column, I explained that .NET Managed providers are the next step in the Darwinistic evolution of OLE DB according to UDA natural laws. Likewise, ADO.NET is the next step in the natural evolution of ADO-evolution always intended in the Darwinistic sense of the word.

Evolution is about adaptation in a new environment. The new environment is .NET, so ADO requires modified behavior and attitude to fit in the new computing scenarios. ADO is not the only software technology that is undergoing such an evolutionary change. It is in good company with OLE DB, COM(+), MSMQ, and ASP.

Reading Records with ADO

ADO was introduced and thrived in a time-frame when connected and (mostly) two-tier applications were moving to a more layered and Web-based architecture. The overall ADO object model design reflects this transition.

The Web forced the adoption of a disconnected model where more and more information is downloaded and cached on the client. Within ADO, you find two souls hidden just under the skin of the Recordset object.

The Recordset can leverage server cursors and do what it is supposed to do being connected to the data source all the time. At the same time, another facet of the same object can work disconnected and build up a correct representation of records in two ways. Either it can fetch rows from the data source and then drop the connection, or it can read all the needed information from a client disk file, including an XML file with a fixed schema.

The disconnected facilities of ADO have been bolted on the Recordset object interface, paying careful attention to make connected and disconnected functionalities available through equivalent APIs. As a result, the Recordset became hard to wield, rather overwhelming and with significant housekeeping.

Anyway, in ADO you can get data in three ways:

1. Through full-time connection using server cursors.

2. By the means of a fast, read-only, forward-only mechanism specifically thought to read through and process a set of records.

3. Getting a static snapshot of data, processing them disconnected from the data source and submitting changes at a later time.

Whatever way you work, you always use the Recordset object and choose the operating mode by selecting the proper cursor type and location. (See the ADO documentation for more details.)

An obvious drawback is that no matter how optimized and well-designed the Recordset object is, you load in memory more stuff than you actually need. In addition, it adds an extra layer of code to process input parameters and possibly fixes their values. Given the overall programming interface of ADO, this is an absolute necessity as well as a performance hit.

ADO.NET simplifies the data reading infrastructure by applying the old "divide et impera" motto. It introduces two new objects that don't have particularly familiar names but expose certainly well-known functionalities. They are the DataReader and the DataSet.

The DataReader object is the ADO.NET counterpart of the read-only, forward-only default ADO cursor. The DataSet is a container that can be programmatically filled with static snapshot of data. In this sense, it can be seen as a repository of disconnected recordsets. There's no Recordset component in the ADO.NET object model, but the DataTable object is the .NET double of a disconnected recordset.

In ADO.NET there's no explicit support for server cursors even though, in a future version, you can expect to see ADO.NET to support server cursors when the data provider is SQL Server 7.0 or later.

If you need server cursors tody, just import the ADO type library in your .NET application and code through it. To learn more on this good, bad and ugly, see the previous column ADO Rocks and Rolls in .NET Applications.

ADO.NET is designed around XML to work seamlessly in highly interoperable and disconnected scenarios. From this point of view, it is certainly more appropriate than ADO for the breed of applications that the majority of people are writing and planning today. It simplifies the coding and optimizes the working of data access components that have Web-based clients.

It behaves well also when you have a Web-enabled database server such as SQL Server 2000 with the XML/HTTP support. By the way, don't forget to check the newest Web Release 1 of XML for SQL Server 2000 with improved support for inserts, updates, and deletes and the loading of large XML files into SQL Server tables.

Despite the actual syntax differences, the programming philosophy behind ADO and ADO.NET is aligned as much as possible to reduce the learning curve for data access developers and the quantity of new concepts they have to familiarize with.

Signs of an ADO Presence

More often than not, you can see signs of a supernatural ADO presence in several ADO.NET code snippets. Want an example? Let's consider a piece of ADO code that, although in slightly different flavors, runs buried in the body of thousands of ASP pages and middle-tier components.

SAMPLE ONE: (below)

In this fragment, you explicitly create a Connection and a Command object, and when this executes, you are returned a new Recordset object. The Connection object contains information about the desired cursor type and location. If you choose the adOpenStatic type of cursor, and necessarily the client-side location, you can safely close the connection and walk through the records. Otherwise, you keep the connection open until you finish navigating through the fetched rows.

Once you have the recordset, you scroll it using a loop as follows:

SAMPLE TWO:(below)

This type of code won't work as-is in ADO.NET. However, the ADO.NET DataReader object lets you write code that, at least functionally speaking, is nearly identical.

First off, you create a special object to govern the execution of the command. The base .NET class is DBCommand. You normally don't use this class; you'd use one of the more specialized .NET classes like ADOCommand and SQLCommand or any user-defined derived class. DBCommand represents a command that the data source can understand.

The SQLCommand class embeds the functionality of SQL Server commands while ADOCommand wraps any access made the COM way against any OLE DB provider. These objects have the same programming interface and a number of constructors. One of the most frequently used is the following:

SAMPLE THREE: (below)

TO SEE COMPLETE ARTICLE:
http://msdn.microsoft.com/voices/data04122001.asp