ADO.NET for the ADO Programmer

ADO.NET for the ADO Programmer

It is obvious from the get-go that ADO.NET isn t the traditional ADO. The data object model provided by ADO.NET is substantially different from that of ADO and requires a different approach when designing and implementing solutions. While some of the concepts remain familiar to ADO developers (such as using Connection and Command objects to manipulate a data source), others will require some adjustment. This discussion gives only a short overview, so we can quickly move on to the more interesting stuff.

Overview of ADO.NET

While ADO.NET presents a new data object model, parts of it are quite similar to the ADO we all know and love. Think of ADO.NET as the result of the integration of both classic ADO and the Microsoft XML technologies in a single, coherent framework. Unlike ADO, ADO.NET was designed from the ground up to support a more general data architecture that is abstracted from an underlying database. In other words, ADO.NET was designed to work with a myriad of data sources and can function as a data store in and of itself. This framework provides a series of data container objects that support functions similar to those used in databases (indexing, sorting, and views) without requiring an actual physical database behind the scenes.

ADO.NET also provides an interface that is consistent with other .NET stream-based data classes. This is a huge step forward and a wonderful design win. In Visual Basic 6, ADO uses a totally different object model compared to the FileSystemObject or the Microsoft XML libraries. ADO.NET, on the other hand, is designed to be consistent with other .NET Framework classes. This new compatibility enables developers to move between vastly different kinds of classes with relative ease. Figure 20-1 provides an overview of the ADO.NET class structure.

Figure 20-1

Overview of ADO.NET classes.

While the Connection and Command objects are still required for most data access, the distinction between the two objects has been made literal, as the Connection object now solely represents a database connection, while the Command object handles all queries.

The classic ADO Recordset functionality has been split into smaller and more specialized objects: the DataSet and DataReader classes. Forward-only firehose Recordsets map directly to DataReaders, while disconnected Recordsets map best to DataSets. DataSet is a powerful new class designed solely to address the disconnected data model problem. It was designed specifically to fill the gaps in the way ADO handles disconnected data, which was never very satisfactory. Given the new kinds of applications that Visual Basic .NET targets, improving and enhancing data access was a primary goal. ADO.NET goes still further by providing strong integration with XML, for which ADO had only minimal support.

DataSet s

DataSets are probably the most foreign of the new objects in the ADO.NET arsenal and thus require a bit more of an introduction than the rest. A DataSet is an in-memory, named cache of data retrieved from a generic data container. What this means is that the original data source is not important and does not have to be a database. DataSets are extremely flexible for that reason. They can be populated from various sources, including data adapters, local data, or XML.

In essence, a DataSet contains collections of DataTables and DataRelations. The DataTable class conceptually corresponds closely to a database table, while the DataRelation class is used to specify relations between the DataTables. DataSets can also contain user-specific information with the ExtendedProperties collection. Contained data and the current schema (or both) can be directly extracted as XML streams, making the information highly mobile. Figure 20-2 illustrates the structure of the DataSet class and its relationship to the data-specific ADO.NET classes.

	Any relationships between DataTables need to be specified by the programmer. The current version of ADO.NET does not support propagating preexisting relationships from the data source to the DataSet. Therefore, any relationships that are defined in your SQL database (or other relational databases) need to be redefined in your DataSet.

Figure 20-2

DataSet class hierarchy.

Using a DataAdapter in conjunction with a DataSet enables you to explicitly manage changes made to all of the tables contained within the DataSet and propagate those changes back to the originating data source. The DataAdapter is a new component in ADO.NET. It is designed to operate as an intermediary between disconnected forms of data (DataSets, DataTables) and the underlying data store. The simplest use of a DataAdapter is to fill a DataSet or DataTable with the results of a database query, but it is able to go much further than that. The DataAdapter is an intermediary in the truest sense of the word, in that it supports bidirectional data transfers. Using the DataAdapter, you can define how changes to a DataSet or DataTable are merged back into the source database and vice versa. In its most advanced use, the DataAdapter can specify how to handle conflict resolution when merging, including insertions and updates.

The .NET Framework ships with two types of DataAdapters: the OleDbDataAdapter and the SqlDataAdapter. Although these DataAdapters target different data sources, they can be used interchangeably, allowing you to move between data sources with minimal disruption. As you will see later in this chapter, the OleDbDataAdapter can also be used as a bridge between ADO and ADO.NET.

A Quick Note on Providers

You will see managed providers mentioned throughout this chapter. Two kinds of providers ship with Visual Basic .NET: the OleDb providers and the SqlClient providers, which live in the System.Data.OleDb and System.Data.SqlClient namespaces, respectively. The majority of the examples in this chapter use the OleDb managed provider because it maps the most directly to ADO. The OleDbConnection object can use the same connection string that ADO uses. The SqlClient managed provider is designed to talk directly to a Microsoft SQL server and uses a slightly different connection string. (There is no need to specify a provider in a connection string for the SqlConnection object.)