Overview of ADO.NET

ADO.NET divides its world into two hemispheres: providers and the DataSet. Imagine your kitchen as the world of ADO.NET, with your refrigerator representing the provider, and the oven/stove as the DataSet. The provider "provides" access to some content, such as food, or an Oracle database (which normally appears in the meat-and-cheese drawer). It's a long-term storage facility, and content that goes in there usually stays in there for quite a while. If something is removed, it's because it is no longer valid, or has become corrupted.

To use your oven, you select content from the long-term storage of the refrigerator, update it by mixing, heating, and rearranging the content, and eventually return it to the refrigerator where it will again sit in storage. This analogy isn't perfect; in fact, something just doesn't smell right about it. But it conveys the basic idea: Providers give you access to stored data, some of which can be moved into and processed through an application and its DataSet on a short-term basis.

Providers

Large database systems, such as SQL Server and Oracle, are stand-alone "servers" (hence the "SQL Server" name) that interact with client tools and applications only indirectly. These systems generally accept network connections from clients through a TCP/IP port or similar connection. Once authenticated, the client makes all of its requests through this connection before disconnecting from the system.

Back in the early 1990s, Microsoft implemented ODBC (based on other existing standards) as a common system through which clients would connect to database servers, as well as other simpler data sources. Clients no longer had to worry about all of the networking protocols needed to talk with a database; all of that code was included in the ODBC driver.

Microsoft later released a similar data connection system called OLE DB, based on ActiveX technology. OLE DB drivers for common systems soon appeared, although you could still get to ODBC resources through a generic ODBC driver built in OLE DB.

In .NET, both ODBC and OLE DB are replaced by providers, libraries of code that provide all of the communication between the database and your application. Providers are an integral part of ADO.NET, and you will have to use them to get to your databases. Fortunately, providers exist for the main database systems, and an "OleDb provider" exists for systems without their own providers.

Four primary objects make up the programmer's view of the provider.

• The Connection object. This object directs communication between your program and the data source. It includes properties and methods that let you indicate the location or connection parameters for the data source. Multi-command transactions are managed at this object level.

• The Command object. This object takes the SQL statement you provide, and prepares it for transport through the Connection object. You can include parameters in your command for stored procedure and complex statement support.

• The DataReader object. The DataReader provides a simple and efficient way to retrieve results from a data query. It is used by other objects in ADO.NET to receive and redirect data for use within your program, but your code can use it directly to process the results of a SELECT statement or other data retrieval action.

• The DataAdapter object. This object is what makes communication between a DataSet and the rest of a provider possible. One of its primary jobs is to modify data manipulation statements (the SELECT, INSERT, UPDATE, and DELETE statements) generated by a DataSet into a format that can be used by the related data source.

Using these objects is a little involved, but not hard to understand. To connect to a typical relational database, such as SQL Server, and process data, follow these steps:

1.	Establish a connection to your data source using a Connection object.
2.	Wrap a SQL statement in a Command object.
3.	Execute that Command object in the context of the established Connection.
4.	If any results are to be returned, use either a DataReader to scan through the records, or use a combination of a DataAdaptor and DataSet (or DataTable) to retrieve or store the results.
5.	Close all objects that you opened to process the data.

Although the .NET Framework includes data providers for a few different data systems, the remainder of this chapter's discussion focuses only on the SQL Server provider, exposed through the System.Data.SqlClient namespace.

Note

SQL Server 2005 includes support for a new feature called "User Instances," for use with SQL Server 2005 Express Edition databases. This feature allows a low-privilege user to access a specific SQL Server Express database file without the need for an administrator to establish SQL Server security settings for that user. This feature is useful in environments where the related software was installed through the new ClickOnce deployment method (discussed in Chapter 24, "Deployment,") without administrator involvement. It also requires specific reconfiguration of the SQL Server Express installation before use. For more information on this feature, reference the "Working with User Instances" article in the ADO.NET portion of the MSDN documentation supplied with your Visual Studio installation.

DataSets

If you are going to do more than just quickly scan the data that comes back from a DataReader query, you will probably use a DataSet to store, manage, and optionally update your data. Each DataSet provides a generic disconnected view of data, whether it is data from a provider, or data that you build through code. While each provider is tied to a specific database platform (such as Oracle) or communication standard (such as OLE DB), the objects in the DataSet realm are generic, and can interact with any of the platform-specific providers.

Three main objects make up the world of DataSets.

• The DataSet object. Each DataSet object acts like a mini-database. You can add as many tables to a DataSet as you like, and establish foreign-key relationships between the fields of these tables. The internals of each DataSet are an unfathomable mystery, but you can export an entire DataSet to XML, and load it back in again later if you must.

• The DataTable object. Each table in your DataSet uses a separate DataTable object, accessible through the DataSet's Tables collection. DataTables are also useful as stand-alone objects. If you plan on only adding a single table to your DataSet, you might opt to just use a DataTable object alone without a DataSet. Within each DataTable object, separate DataColumn and DataRow objects establish the field definitions and the actual data values respectively.

• The DataRelation object. Use the DataRelation objects, stored within a DataSet's Relations collection, to establish field-level relationships and constraints between columns in your DataTable objects.

Although data sets are most often used with providers, you can use them independently to build your own in-memory collection of tables and relationships. This is similar to the "client-side record sets" that you could build with pre-.NET ADO objects, although the features included with ADO.NET make the DataSet much more powerful than the older Recordset.

Note

Visual Basic 2005 includes "Typed DataSets," a feature used to integrate a DataSet with a specific data or record format. You may find them useful in your applications, but I won't be discussing them in this book.