What Is ADO.NET?

What Is ADO.NET?

ADO.NET is a set of classes, included with the .NET Framework, that represents the primary method by which .NET applications interact with relational databases and other open and proprietary data management systems. But it's not just for interaction; ADO.NET is, in reality, a partial in-memory relational database all by itself. You can create tables and relationships (joins) with ADO.NET objects, add and remove records, query tables based on selection criteria, and do other simple tasks that are typical of stand-alone relational database systems.

All classes included with ADO.NET appear in the System.Data namespace; other subordinate namespaces provide derived classes geared toward specific database platforms. For instance, the System.Data.SqlClient namespace targets SQL Server databases, while System.Data.OracleClient focuses on Oracle RDBMS systems. Other database providers can develop streamlined implementations of the various ADO.NET classes for use with their own systems, and supply them as a separate namespace.

ADO.NET implements a disconnected data experience. In traditional database programming, especially in desktop applications, the connection between an application and its database was fixed and long- term . When the program started up, the connection started up. When the program exited many hours later, the connection finally ended. But in a world of massively scalable web sites, keeping a database connected for hours on end is wasteful and often impossible .

ADO.NET encourages you to open data connections just long enough to get the data that fulfills your immediate needs. Once you have the data, you drop the connection until the next time you need to retrieve, insert, or update database content. If you issue the following SQL statement:

SELECT * FROM Customer WHERE BalanceDue > 0

you have a choice of (1) scanning through all the records once in a quick and simple manner; and (2) loading the data into an in-memory table-like object, closing the connection, and working with the loaded records as if they were the original. If you use the first method, you can take your sweet time waltzing through the records, taking many minutes to process each one. But this type of selfish behavior is frowned upon by ADO.NET. The goal is to get in and get out as quickly as you can.

Because of the disconnected nature of ADO.NET, some techniques common in database applications need to change. For instance, the long-term locking of database records during a user modification ("pessimistic concurrency") is difficult to accomplish in ADO.NET's disconnected environment. You will have to use other methods , such as transactions or atomic stored procedure features, to accomplish the same goals.

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:

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.