Database Independent Programming Using ADO.NET
- .NET Architecture
- Sample Database
- Connected Data Access
- Disconnected Data Sets
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
ADO.NET is a great new data access technology, but if you're not careful, you can tie your code to classes associated with a particular database. This article shows you how to write database-independent code using ADO.NET interfaces.
This article is based on the book Introduction to Visual Basic Using .NET, by Dana Wyatt Ph.D. and Robert J. Oberg. (Prentice Hall PTR, 2002, ISBN: 0130418048), which is part of The Integrated .NET Series from Object Innovations and Prentice Hall PTR.
You can download a full-length version of this article (Chapter 20 from the book) and sample code here.
ADO.NET Architecture
ADO.NET is a set of classes that provides consistent access to multiple data sources, which may be either relational data from a database or hierarchical data expressed in XML. A driving factor in ADO.NET is a provision for disconnected access to data, which is much more scalable and flexible than the connection-oriented database access that is traditional in client/server systems.
The DataSet class is the central component of the disconnected architecture. A data set can be populated from either a database or from an XML stream. From the perspective of the user of the data set, the original source of the data is immaterial. A consistent programming model is used for all application interaction with the DataSet.
The second key component of ADO.NET architecture is the .NET Data Provider, which provides access to a database, and can be used to populate a data set. A data provider can also be used directly by an application to support a connected mode of database access.
Figure 1 illustrates the overall architecture of ADO.NET.
){kind=link}
Figure 1 ADO.NET architecture block diagram.
.NET Data Providers
A .NET data provider is used for connecting to a database. It provides classes that can be used to execute commands and to retrieve results. The results are either used directly by the application, or else they are placed in a data set. A .NET data provider implements four key interfaces:
IDbConnection is used to establish a connection to a specific data source.
IDbCommand is used to execute a command at a data source.
IDataReader provides an efficient way to read a stream of data from a data source. The data access provided by a data reader is forward-only and read-only.
IDbDataAdapter is used to populate a data set from a data source.
The ADO.NET architecture specifies these interfaces, and different implementations can be created to facilitate working with different data sources. A .NET data provider is analogous to an OLE DB provider, but the two should not be confused. An OLE DB provider implements COM interfaces, and a .NET data provider implements .NET interfaces.
When OLE DB first came out, it immediately supplied a provider for ODBC. This single provider offered access to a wide array of data sources to any data source with an ODBC driver. A native OLE DB provider was offered for SQL Server. As time passed, more OLE DB providers became available.
The situation today is similar for .NET data providers. Currently, there are two .NET data providers. The OleDb data provider goes through the COM interop layer to talk to OLE DB. Thus, any data source with an OLE DB provider can be accessed through ADO.NET. The SqlServer data provider uses the native SQL Server wire protocol. As time passes, we can anticipate that additional native .NET data providers will be offered by different database vendors.
In order to make your programs more portable, you should endeavor to program with the interfaces rather than using specific classes directly. In our example programs, we illustrate using interfaces to talk to an Access database (using the OleDb data provider) and a SQL Server database (using the SqlServer data provider).
Classes of the OleDb provider have a prefix of OleDb, and classes of the SqlServer provider have a prefix of Sql. Table 1 shows a number of parallel classes between the two data providers and the corresponding interfaces.
Table 1—Comparison of Parallel Classes in the OleDb and SqlServer Data Providers
|
Interface |
OleDb |
SQL Server |
|---|---|---|
|
IDbConnection |
OleDbConnection |
SqlConnection |
|
IDbCommand |
OleDbCommand |
SqlCommand |
|
IDataReader |
OleDbDataReader |
SqlDataReader |
|
IDbDataAdatpter |
OleDbDataAdapter |
SqlDataAdapter |
|
IDbTransaction |
OleDbTransaction |
SqlTransaction |
|
IDataParameter |
OleDbDataParameter |
SqlDataParameter |
Classes such as DataSet, which are independent of any data provider, do not have any prefix.
.NET Namespaces
ADO.NET classes are found in the following namespaces:
System.Data consists of classes that constitute most of the ADO.NET architecture.
System.Data.OLEDB contains classes that provide database access using the OLE DB data provider.
System.Data.SQLClient contains classes that provide database access using the SQL Server data provider.
System.Data.SQLTypes contains classes that represent data types used by SQL Server.
System.Data.Common contains classes that are shared by data providers.