ADO.NET is Microsoft's platform for data access in the .NET Framework. Out of the box, ADO.NET is scalable, interoperable, and familiar enough to ADO developers to be immediately usable. By design, the ADO.NET object model and many of the ADO.NET code constructs will look very familiar to ADO developers.

At the most basic level, ADO.NET consists of the following set of framework namespaces:

  • System.Data

  • System.Data.Common

  • System.Data.SqlClient

  • System.Data.OleDbClient

  • System.Data.SqlTypes

The System.Data namespace contains many of the objects on which ADO.NET is built. This is where you'll find the DataTable, DataSet, DataRelation, and DataView objects. Additionally, this is where ADO.NET constants are stored. For instance, the System.Data.SqlDbType class shown in Figure 46.2 contains all the Microsoft SQL data types.

Figure 46.2. The System.Data.SqlDbType class contains constant values for all the data types in Microsoft SQL versions 7.0 and 2000.


The System.Data.SqlClient namespace contains objects designed to work with a Microsoft SQL database. These are objects such as SqlCommand, SqlConnection, and SqlParameter as well as new faces such as SqlDataAdapter and SqlDataReader. If you're a SQL developer, this namespace will be your sandbox. The namespace uses a managed SQL provider to work with the database. By working directly with the SQL database APIs, SqlClient bypasses ODBC (Open Database Connectivity) and OLE DB (Object Linking and Embedding for Databases) entirely, offering a very robust and efficient interface.

The System.Data.OleDbClient namespace is designed to work with any valid OLE DB source. This includes data sources as varied as Oracle databases, Microsoft Excel files, standard ASCII comma-delimited text files, Microsoft Access, and versions of Microsoft SQL Server prior to version 7.0. As mentioned previously, if you're working with Microsoft SQL Server directly, the System.Data.SqlClient is the optimal way to go.

The System.Data.OleDbClient namespace almost mirrors the System.Data.SqlClient namespace. In fact, if you scan the classes in both, you'll notice that the classnames differ only by their preface (for instance, SqlCommand versus OleDbCommand). Fortunately, once you've worked with one namespace, you'll understand how to use both.


The relationship between ADO and ADO.NET is analogous to the one between ASP and ASP.NET. Many of the object and method names are similar, but behind the curtains everything has been redesigned and improved.

For instance, the ADO data model is based around the recordset object. In essence, the recordset is a spreadsheet of data in memory. You are very limited in what you can do with a recordset of data. It is difficult to do advanced data filtering or combine two recordsets. And, though it is possible to transmit an ADO recordset to a remote server, you have to configure all firewalls between the two servers to enable the proprietary ports required for COM marshaling.

Additionally, the remote server must know what an ADO recordset is. For all intents and purposes, this means that the remote server must be running a Microsoft operating system. Because of the limitations of the ADO recordset object, solutions based on ADO are likewise limited.

Microsoft has fixed these problems with ADO.NET. The centerpiece object of ADO.NET is the DataSet (not the old recordset). The DataSet, as shown in Figure 46.3, is an in-memory (cached) representation of data that provides a consistent relational programming model regardless of the data source.

Figure 46.3. The ADO.NET DataSet object model.



The DataSet contains a collection of DataTables, which are very much like recordsets in that each DataTable is a set of data. However, rather than just serving as a container for various DataTables, the DataSet can store relations and constraints pertaining to the DataTables! Not only can a DataSet mirror the relations and constraints in your data source, but you can add new ones as the logic of your application dictates. This gives you complete control over filtering and combining DataTables.

Additionally, DataSets (and the DataTables within them) are represented internally by strongly typed XML. Thus, at any point, it is possible to save a DataSet to XML. This might not seem like a major point at first glance. However, this means that any platform that can parse XML?and I don't know of any platform that cannot?can retrieve data from an ADO.NET DataSet.

DataSets are easily transmitted to remote machines, as well. Web services are designed to transmit XML data via SOAP to remote machines. Because the DataSet is represented internally as XML, sending a DataSet to a remote server requires no special handling. The remote server could be running any platform that understands XML, including Java-based solutions such as IBM WebSphere. A WebSphere developer would only need to parse the XML.

The DataSet object is the parent object of most of the other objects in the System.Data namespace. Its primary role is to store a collection of DataTables, the relations, and constraints between those DataTables. The DataSet also contains several methods for reading and writing XML, as well as merging other DataSets, DataTables, and DataRows.

The DataTable

The DataTable stores a table of information, typically retrieved from a data source. In addition to simply containing the various DataColumns and DataRows, however, the DataTable also stores metatable information such as the primary key and constraints.

The DataRow and DataColumn

The DataRow and DataColumn objects are at the bottom of the ADO.NET "food chain," so to speak. These instances are where you can drill down to the actual columns and rows in a DataTable.

The System.Data.SqlClient and System.Data.OleDb Namespaces

As previously mentioned, the System.Data.SqlClient and System.Data.OleDb namespaces work with data sources. System.Data.SqlClient uses a managed provider to interact directly with Microsoft SQL Server Version 7.0 and 2000. System.Data.OleDb interacts with any valid OLE DB source. Though the namespaces are separate, the base objects function very similarly. Both namespaces contain connection, command, DataAdapter, and DataReader objects.

The connection Object

As you might have guessed, the connection object opens a connection to your data source. All the configurable aspects of a database connection are represented in the connection object, including ConnectionString and ConnectionTimeout. Also, database transactions are still dependent on the connection object.

The command Object

The command object performs actions on the data source. You can use the command object to execute stored procedures, or any valid T-SQL command understood by your datasource. This is the object that performs the standard SELECT, INSERT, UPDATE, and DELETE T-SQL operations.

The DataAdapter Object

The DataAdapter object is brand-new in ADO.NET. The DataAdapter takes the results of a database query from a command object and pushes them into a DataSet using the DataAdapter.Fill() method. Additionally, the DataAdapter.Update() method will communicate any changes to a DataSet back to the original data source. Unlike with ADO, updating the original data source with modified data works reliably well.

The DataReader Object

The DataReader object is also brand-new in ADO.NET. The DataReader provides a very fast, forward-only view of the data returned from a data source. In most instances, to display a set of data in a Web or Windows form, this is the object you'll use, because there is very little overhead. No DataSet is created; in fact, no more than one row of information from the data source is in memory at a time. This makes the DataReader quite efficient at returning large amounts of data. You can think of the DataReader as a direct route from the data source to the final destination. However, if you need to manipulate schema or use some advanced display features such as automatic data paging, you must use a DataAdapter and DataSet.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features