At its essence, ADO.NET is data access middleware that enables the development of database applications. ADO.NET builds on the platform provided by the .NET Framework. ADO.NET is built using managed code from the Microsoft .NET Framework, which means that it enjoys the benefits of the robust .NET execution time environment. Designed primarily to address the issues of Web and distributed applications, ADO.NET consists of a set of classes or namespaces within the .NET Framework that provide data access and management capabilities to .NET applications.

As a data access framework, ADO.NET has been primarily designed to allow it to work in the disconnected data access model that is required by n-tiered Web-based applications. ADO, the direct predecessor of ADO.NET, was primarily designed to accommodate a two-tiered client/server style of applications, which typically open a database connection when the application first starts and then hold that connection open until the application ends. This technique works fine for most intranet-style applications where the total number of client connections is a known quantity, and where the state of the application is typically controlled by the application and therefore is also a known quantity. Although this approach worked well for singletier desktop applications and two-tiered client/server-style applications, it ran into serious limitations for n-tiered Web-style applications. Because the Web is a public environment, the total number of open connections required by Web applications isn’t a known quantity. It could vary greatly and quickly: At one minute, an application may need only a handful of connections, but the need can jump to thousands of connections just a few minutes later. Keeping open connections in this type of environment hurts scalability because each connection must go through the overhead of initializing the connection with the back-end database, plus each open connection requires system resources to be held open—reducing the resources available for other database operations. As ADO evolved, Microsoft added mechanisms such as disconnected recordsets to help deal with Web-style applications, but these were never part of ADO’s original design.

Microsoft designed ADO.NET to be able to handle the disconnected computing scenario required by Web-based applications. This disconnected design enables ADO.NET to be readily scalable for enterprise applications because an open connection isn’t maintained between each client system and the database. Instead, when a client connection is initiated, a connection to the database is briefly opened, the requested data is retrieved from the database server, and the connection is closed. The client application then uses the data completely independently from the data store maintained by the database server. The client application can navigate through its subset of the data, as well as make changes to the data, and the data remains cached at the client until the application indicates that it needs to post any changes back to the database server. At that point, a new connection is briefly opened to the server and all of the changes made by the client application are posted to the database in an update batch and the connection is closed.

The core ADO.NET component that enables this disconnected scenario is the DataSet. The DataSet is essentially a miniature in-memory database that is maintained independently of the back-end database. Connections to the data source are opened only to populate the DataSet or to post changes made to the data in the DataSet back to the database. This disconnected computing scenario minimizes the system overhead and improves application throughput and scalability. The in-memory database provided by the ADO.NET DataSet provides many of the functions that you’ll find in a full-blown database, including support for data relations, the capability to create views, and support for data constraints, as well as support for foreign key constraints. However, being an in-memory structure, it doesn’t provide support for many of the more advanced database features that you would find in enterprise-level database products like SQL Server. For example, the DataSet doesn’t support triggers, stored procedures, or user-defined functions. Support for disconnected Web-based applications was one of Microsoft’s priorities in the design of ADO.NET; however, that isn’t all that ADO.NET is capable of. The disconnected model may be appropriate for Web applications, but it really isn’t the best model for client/server and desktop applications. These types of applications can perform better and more efficiently when they run in a connected fashion. To support this connected style of computing, ADO.NET also provides a DataReader object. The DataReader essentially provides fast forward–only cursor style of data access that operates in a connected fashion. While the DataSet provides the basis for disconnected Web applications, the DataReader enables the fast connected style of data access needed by desktop and client/server applications. In this section, you got a high-level overview of the ADO.NET data access middleware. Here you saw that ADO.NET provides the tools to build applications that support both disconnected Web applications as well as connected client/server style applications. In the next section, you’ll get a close look at the different namespaces that make up the ADO.NET architecture.

ADO.NET Namespaces

ADO.NET is implemented as a set of classes that exist within the .NET Framework. These ADO.NET classes are grouped together beneath the .NET Framework’s System.Data namespace. Several important namespaces make up the ADO.NET data access technology. First, the .NET Data Providers are implemented in the System. Data.SqlClient, System.Data.OracleClient, System.Data.OleDbClient, and System.Data.Odbc namespaces. The classes in these four namespaces provide the underlying database connectivity that’s required by all of the other ADO.NET objects. The System.Data.SqlClient namespace provides connectivity to SQL Server 7, SQL Server 2000, and SQL Server 2005 databases. The System.Data. OracleClient namespace provides connectivity to Oracle 8 and 9 databases. The System.Data.OleDbClient namespace provides connectivity to SQL Server 6.5 and earlier databases, as well as Access and Oracle databases. And the System.Data. Odbc namespace provides connectivity to legacy databases using ODBC drivers. These classes also provide support for executing commands, retrieving data in a fast forward-only style of access, and loading ADO.NET DataSets. Next, there are the classes contained in the System.Data namespace itself. These classes can be considered the core of the ADO.NET technology, and they provide support for the new ADO.NET DataSet class and its supporting classes. The DataSet is an in-memory database cache that’s designed to be used in a disconnected fashion. The DataSet consists of a complete collection of tables, columns, constraints, rows, and relationships, plus appropriately named DataTables, DataColumns, DataConstraints, DataRows, and DataRelations.