| An application that uses ADO.NET to access data commonly relies on some .NET Framework data provider, software able to access data in a particular way. Figure 6-1 illustrates how clients, .NET Framework data providers, and DBMSs fit together. The client, written as managed code running on the CLR, can use various .NET Framework data providers to access stored data. Each .NET Framework data provider is also written as managed code, but a data provider might also rely on software that doesn't use the .NET Framework to accomplish its task. Figure 6-1. Applications using ADO.NET rely on .NET Framework data providers to access information. 
| A .NET Framework data provider allows access to stored data in a specific way |
Perspective: Oh, No, Not Another Data Access Interface! ODBC, OLE DB, ADO, and more: The list goes on and on. If you've been working in the Microsoft world for a while, you've surely encountered some of these data access technologies. Each of them was at one time presented as the optimal way to access data, especially data in a relational database system. Yet today, each of them is a legacy technology, which means that while new applications shouldn't in general use them, somebody still has to support the old applications that do. Why does Microsoft do this? Why can't the people in Redmond make up their minds in this terrifically important area? After all, the dominant database model hasn't changed in 20 yearsa relation today is pretty much the same as it was thenso why should the way we access relational data be so volatile? There are many answers. For one thing, the interface between object-oriented software and relational databases is inherently a messy place. With few exceptions, the database people never bought into objects as a core abstraction. There are good reasons for thisobjects work much better for creating software than they do for storing databut the result is a mismatch of models and a plethora of pain. In its attempts to make this difficult interface as useful as possible, Microsoft has taken a few wrong turns. A more obvious reason for creating new data access interfaces is that the fundamental development technologies on which they depend change over time. For example, ADO.NET's predecessor, ADO, was based on the Component Object Model (COM), while the .NET Framework is not. While .NET Framework applications can use ADO through the Framework's COM interoperability features, this is a suboptimal approach. It makes more sense to offer a data access mechanism built solely with managed code, which is exactly what ADO.NET provides. Another reason for the instability in data access interfaces is that the problem being addressed changes regularly. A primary goal of ODBC, for instance, was to let applications written in C and C++ issue SQL queries against a database system. These applications were either running on just one system or were spread across two machines in a two-tier client/server configuration. Yet today a very common scenario is a browser or Windows Forms client talking to business logic on a middle-tier server. In this kind of application, it's this middle-tier logic that accesses data and then passes it across a network to the browser. ODBC didn't address this situation at all, since it provided no way to serialize its results for transmission across a network. ADO addressed it in a way that made some sense inside the firewall but didn't work well on the Internet, especially with browsers other than Internet Explorer. ADO.NET, however, was designed with exactly this style of application in mind. And because the older models are still the right approach for some applications, ADO.NET also allows writing code in a more traditional style. Finally, new people join development teams at Microsoft all the time, and the existing people move on to other groups or other companies. New people bring new ideas, and as ownership of a technology changes, that technology will also change. I don't believe Microsoft is intentionally making developers' lives difficult, but that has sometimes been the result of the technology churn in data access interfaces. Still, ADO.NET is a clean, attractive, and relatively simple technology for today's applications. All historical confusion aside, it's a good fit for the world of the .NET Framework. |
As Figure 6-1 shows, ADO.NET version 2.0 includes four .NET Framework data providers: .NET Framework Data Provider for SQL Server: This data provider allows access to Microsoft's SQL Server 7.0 or later. Access to earlier versions of SQL Server requires using another provider option. .NET Framework Data Provider for Oracle: This data provider allows access to an Oracle database. .NET Framework Data Provider for OLE DB: Many pre-.NET applications, such as those built using ActiveX Data Objects (ADO), rely on Object Linking and Embedding for Databases (OLE DB) providers for access to data. This data provider implements a wrapper around any OLE DB provider that lets it be used by .NET Framework applications. .NET Framework Data Provider for ODBC: Open Database Connectivity (ODBC) is another of the many data access interfaces Microsoft has provided for Windows developers, and applications that use it rely on ODBC drivers to get at stored data. Much like the .NET Framework Data Provider for OLE DB, this data provider implements a wrapper around an ODBC driver, allowing managed code to use that driver for accessing data.
Each .NET Framework data provider is made visible to a developer as a group of types, each in its own namespace. The primary types that implement the SQL provider are located in the System.Data.SqlClient namespace, for instance, while those that implement the OLE DB provider are in System.Data.OleDb. Regardless of which provider a developer chooses, each one offers an analogous set of classes. Figure 6-2 shows the fundamental kinds of objects supported by any .NET Framework data provider. They are as follows: Connection: allows establishing and releasing connections. This class can also be used to begin transactions. Command: allows storing and executing a command, such as a SQL query or stored procedure, and specifying parameters for that command. DataReader: allows direct, sequential, read-only access to data in a database. DataAdapter: creates and populates instances of the class DataSet. As described later in this chapter, DataSets allow more flexible access to data than is possible using just a DataReader.
Figure 6-2. A .NET Framework data provider allows clients to access data either directly as rows or through a DataSet. 
| Each .NET Framework data provider exposes the same core set of classes |
With the exception of DataSet, these names are descriptivethey're not actual class names. There is no DataReader class, for example. Instead, each .NET Framework data provider implements its own version of these classes. The DataReader for the .NET Framework data provider for SQL Server, for example, is implemented by the SqlDataReader class, while that for the .NET Framework data provider for OLE DB is implemented by the OleDbDataReader class. Using these four kinds of objects, a .NET Framework data provider gives clients two options for accessing data. Both use Connections and Commands to interact with a DBMS, but they differ primarily in how a client can work with the result of a query. As Figure 6-2 shows, a client that needs read-only, one-row-at-a-time access to a query's results can use a DataReader object to read those results. Clients with more complex requirements, such as accessing a query's result in an arbitrary order, filtering that result, sending that result across a network, or modifying data, can use a DataAdapter object to retrieve data wrapped in a DataSet. In both cases, clients use Connection and Command objects in similar ways, so how these objects are used is described next. | Clients can access data through a DataReader or by using a DataSet |
Writing Provider-Independent Code: System.Data.Common Doesn't it seem odd that ADO.NET has different .NET Framework data providers for different DBMSs? After all, one of the reasons for using a common interface such as ADO.NET rather than one provided by a DBMS vendor is to access different vendors' products in a common way. Yet each .NET Framework data provider uses its own specific names for most of the fundamental classes. The result is that clients become wedded to just one .NET Framework data provider. To address this oddity, ADO.NET version 2.0 includes an extended version of the System.Data.Common namespace. This namespace provides generic versions of the fundamental ADO.NET types, including DbConnection, DbCommand, DbDataReader, and DbDataAdapter. Rather than using the provider-specific incarnations of these types, a developer who needs to create provider-independent code can use these more general options. (In fact, the provider-specific classes for Connection, Command, DataReader, and DataAdapter all now inherit from their generic equivalents in System.Data.Common.) Taking this more general approach constrains an application's ability to exploit DBMS-specific features, but the independence it brings can sometimes outweigh this limitation. |
| A client uses a Connection object's Open method to open a connection to a DBMS |
|
