In "Three Cool New Features in ADO.NET 2.0" (http://www.windowsitpro.com/articles/articleid/45752/45752.html), I talked about some of ADO.NET 2.0's new features. Today, I'll continue to focus on ADO.NET 2.0, but instead of discussing features that are specific to SQL Server 2005, I'll focus on the objects used by ADO.NET 2.0.
At the top level, the System.Data namespace represents ADO.NET, no matter which version you use. The classes in this namespace apply across all the various data-access methods. For example, the Dataset and DataTable classes are generic across the various implementations of ADO.NET. Regardless of the data source used to retrieve your data, you can rely on the behavior of these objects because they've abstracted the underlying data source. But ADO.NET 1.0 has exceptions to this standard abstraction. One such exception is the SQLDataReader class defined under the System.Data.SQLClient namespace. This forward-only cursor is available only for SQL Server clients under ADO.NET 1.0.
Before I get to how ADO.NET 2.0 helps to better abstract the various classes defined in some of the access method namespaces, I want to discuss a new class named DataTableReader. This new object is defined in the System.Data namespace, which means that it's available for any of the database providers supported under ADO.NET 2.0. This object is an excellent addition to the list of available classes; however, I want to warn you not to jump to conclusions based on this new object's name.
The conclusion you might jump to is that, like the DataTable object, the DataTableReader object represents a single table of data. It doesn't. The DataTableReader is actually used to carry out read-only forward-only operations on one or more tables. Thus, if you want to cycle through an entire data set, you can use DataTableReader to cycle through all the tables in that data set.
Unlike SqlDataReader, which uses a server-side cursor to cycle through a table of data and retrieve rows, DataTableReader uses a cached copy of the data table being traversed. Thus, there are several differences from this generic reader and SQLDataReader. The main difference is that the constructor for DataTableReader requires either a DataTable or DataSet object as a parameter. In many ways, the best way to think of DataTableReader is as an extension of the DataTable object. The reader version of this object is simply providing a way for you to efficiently emulate the same forward-only behavior you would expect from a reader.
A change bigger than DataTableReader is coming in ADO.NET 2.0's class hierarchy. In ADO.NET 1.0, the System.Data.Common namespace comes with a generic version of the DataAdapter object, which you can use to populate the DataTable and DataReader objects. Using these three objects, you can work toward abstracting your data source from your application. However, ADO.NET 1.0 doesn't support other database classes such as Command and Connection the same way. Thus, your application can often wind up bound to a specific data source. When you then need to migrate, say, from Oracle to SQL Server 2005, you must make code changes to account for the new data provider. The need to write business-logic code that references a specific type of connection limits the application's ability to move between different databases.
ADO.NET 2.0 changes this. In ADO.NET 2.0, the System.Data.Common namespace has been extended to contain generic Connection, Command, and related objects. With these new base classes, you can use a generic Command object in your business logic; the only code that will need to be aware of which data-access provider is being used is the code that reads the configuration to create that command or connection. The new base objects provide a layer of abstraction from the various data sources. Thus, you can change the underlying data source without impacting your application code.
To implement the new base classes in ADO.NET 2.0, Microsoft completely changed the inheritance model used by the data-provider classes. Instead of having most of the data-provider classes inherit from System.Components, they now inherit from a set of classes in the System.Data.Common namespace.
Thus, instead of writing ADO.NET code that references an instance of a SqlConnection object, you should instead create a SqlConnection object, then pass that object to your business logic as a DbConnection object. That way, the application-specific code becomes unaware of the type of data connection it's using. You still gain all the benefits of using the SQL Server-specific provider, but the data-source specifics are handled behind the scenes.
For more information about the System.Data.Common classes, I recommend that you check out the Web page for the System.Data.Common namespace in Microsoft Developer Network's (MSDN's) Visual Studio 2005 Library. That page is currently at http://msdn2.microsoft.com/library/9tahwysy.aspx. Another source of information is the MSDN article "Generic Coding with the ADO.NET 2.0 Base Classes and Factories" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp.