Abstract

This article reviews the ADO.NET object model found in Microsoft's .NET Framework Beta2. The information presented here will be useful both to programmers who worked with Beta1 and also, to those just starting with the framework.

The article describes the overall ADO.NET model and then provides an example in Visual Studio.NET to make these concepts more concrete. This article will not present how to do everything that can be done with ADO.NET; however it will provide a solid grounding in how the object model works so that the programmer can go forward comfortably and with confidence.

The ADO.NET object model has been streamlined significantly in Beta2, and, as a result, is much easier to work with and understand. ADO.NET is represented in the .NET namespace as System.Data and all of its child namespaces.

The ADO.NET object model is an all-purpose data access model. It is designed to support the developer in accessing and writing to a wide variety of data sources. This can be anything from a product list in a database used on an e-commerce web site to a collection of sales figures collected in Excel and saved to an XML file and used as the basis for a table being displayed on an internal Intranet site.

The example provided makes extensive use of Visual Studio.NET's visual designer capabilities to streamline the writing of code. Writing applications has never been like this - it is so easy! In fact, to produce a fully working application, only three lines of code have to be typed in!

The ADO.NET Object Model

I always find it easier to understand things when presented visually. Therefore, I will begin with a diagram of the ADO.NET object model as it is presented in Beta2:

How it Works

Here is how it works:

The Data Provider at the bottom of the diagram speaks to the database. The DataSet at the top right of the diagram speaks to the Data Provider and represents a disconnected recordset that caches data locally on the client. To the left, the DataView speaks to the DataSet and is a special class designed for UI objects to bind to and can provide customized views of the DataSet.

In cases where forward-cursor read-only access is required, the DataSet and DataView can be bypassed and the DataReader, part of the Data Provider can be used for a significant performance gain.

So let's review each of these three areas; 1) Data Provider 2) DataSet and 3) DataView in a bit more depth. This will provide the basis to feel comfortable with ADO.NET in Beta2.

Data Provider

The Data Provider connects to the database on behalf of ADO.NET. It encapsulates all connections to a database. The blue section at the bottom of the diagram describes a Data Provider with several key objects in it:

• Connection
• Command
• DataAdaptor
• DataReader

.NET Beta2 provides two distinct Data Providers and only one is used at a time, based on the type of database being connected to.

The first Data Provider, SQL Server .NET Data Provider uses a special protocol called TDS (Tabular Data Stream) to communicate directly with SQL Server without adding the overhead of OLE DB or ODBC. This Data Provider is represented in the .NET namespace as System.Data.SqlClient.

The other Data Provider is the OLE DB .NET Provider. Using .NET's COM interoperability features provides native OLE DB to enable data access. According to the .NET documentation, one important difference between the two is that the OLE DB .NET Provider supports both manual and automatic transactions. For automatic transactions, the OLE DB .NET Data Provider automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. Component Services will be covered in a future article. This Data Provider is represented in the .NET namespace as System.Data.OleDb.

Connections

Connections are part of a Data Provider and are used to connect to databases. They are represented by the provider-specific classes SqlConnection and OleDbConnection.

Connections can be opened in two ways:

1. Explicitly by calling the Open method on the connection;
2. Implicitly when using a DataAdapter.

Commands

Commands contain information that is submitted to a database as a query, and, like connections, are represented by the provider-specific classes SqlCommand and OleDbCommand. A command can be any valid SQL statement against the database referred to in the connection. Input and output parameters are supported along with return values as part of the command syntax.

Commands can be set explicitly in order to control the statements used at runtime to resolve changes, including the use of stored procedures. According to Microsoft, using commands in this way is this is a very important part of building an application that is high-performance and secure.

From a functional point of view, Commands travel over Connections and query results are returned in the form of streams which can be accessed by a DataReader object, or passed into a DataSet object via a DataAdapter, which is the next topic.

DataAdapters

The DataAdapter provides a set of methods and properties to retrieve and save data between a DataSet and its source data store. It does the actual work of putting returned data from a database into a DataSet. It also manages reconciling how data should be updated against a database.

Connections and Commands whose properties are set early on in code are often passed into DataAdapters for use when their action methods are invoked.

The DataAdapter object encapsulates a set of data commands and a database connection which are used to fill the DataSet and update the data source. The Fill method of the DataAdapter calls the SELECT command while Update method calls INSERT, UPDATE or DELETE command for each changed row. Simple and straightforward, huh?

One of the great features about the DataAdapter object is that these commands can be set explicitly in order to control the statements used at runtime to resolve changes, including the use of stored procedures.

The .NET documentation notes that the CommandBuilder object can be used to generate these commands at run-time based upon a select statement for "ad-hoc queries". However, this requires an extra round-trip to the server in order to gather required metadata, so explicitly providing the INSERT, UPDATE, and DELETE commands at design time will always result in better run-time performance.

The DataAdapter is the object that connects to the database to fill the DataSet. It also connects to the database in order to update the data, and this is based on the operations that take place while the DataSet holds the data.

However, there are cases where the DataAdapter and DataSet objects are bypassed. In these cases, the DataReader object is used.

DataReaders

A DataReader object provides methods and properties that deliver a forward-only stream of data rows from a data source.

The DataReader is an interesting optimization of the ADO.NET model. When a DataReader is used, parts of the ADO.NET model are cut out, providing faster and more efficient data access.

Here is a diagram that shows how it works:

In this scenario, the ExecuteReader method of the Command object sends the Command.CommandText to the Connection object, which in turn builds the DataReader with and sends the result set to is through a stream. Use a DataReader when there is no need to update the data in anyway.

DataSet

The DataSet object is the most talked about object within ADO.NET. It represents a cache of data that contains tables, columns, relationships, and constraints. A DataSet behaves much like a database, however, DataSet objects do not interact directly with databases - that is what the Data Provider is for.

This programming model allows the developer to work in a way that is consistent, regardless of where the source data comes from. In ADO.NET, data from a database, an XML file, generated in code, or from user input can all be placed into DataSet objects.

Another feature of the DataSet is that it tracks changes that are made to the data it holds before updating the source data. For example, the GetChanges method of the DataSet object creates a second DataSet that contains only the changes to the data. This DataSet is then used by a DataAdapter to update the original data source.

DataSet are also fully XML-featured. They contain methods such as GetXml and WriteXml that respectively produce and consume XML data easily. In am XML scenario where there is no database, these methods enable use of ADO.NET without the Data Provider being involved.

The DataSet object provides a consistent programming model that works with all current models of data storage: flat, relational, and hierarchical. It represents the data that it holds as collections and data types. The data within a DataSet is manipulated via the set of standard APIs exposed through the DataSet and its child objects regardless of its data source.

DataView

And finally, the DataView object. The DataView is really very simple. It provides methods and properties that enable UI objects such as a DataGrid to bind to a DataSet and contains properties such as AllowEdit and Count that allow the UI object to work with the data in meaningful ways.

A DataView is only used in conjunction with a DataSet and never with a DataReader.

In Part 2 of this article I will show you a code example that goes through creating a web application that connects to the Northwind SQL database, executes a stored procedure and binds the results to a DataGrid on an ASP.NET web page.