RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Writing Provider-Independent Data Access Code with ADO.NET 2.0

When you need to write an application that allows users to select the database provider, the application code itself needs to be completely provider-independent. ADO.NET 2.0 helps you create and deliver database applications even when you don't know what database your clients are using.


he data access features in .NET changed the way developers write code by segregating the data providers into separate classes and namespaces, depending on the type of database that was being targeted. For example, to access SQL Server, Microsoft encourages you to use the classes from the System.Data.SqlClient namespace, such as SqlConnection and SqlDataReader, which take advantage of the better performance available through SQL Server's native Tabular Data Stream (TDS) interfaces.

However, if you do that, your code won't work if you switch to a different database system, and you may need to change your code to use classes in the OleDb or Odbc namespaces instead. Of course, you can do a global search and replace to rewrite the code, but that's of no value if you want to be able to select the provider type through a simple configuration setting, or even dynamically at runtime.

In ADO.NET 2.0, Microsoft has both exposed and enhanced the base classes in the System.Data.Common namespace, and added some new classes that directly enable the implementation of provider-independent data access techniques. This article looks at these new features and demonstrates how you can create tools, development environments, and applications that let you change the provider type as required, without having to change your code.

The topics you'll see discussed in this article are:

  • The factory class hierarchy
  • Enumerating the installed providers
  • Selecting a provider factory
  • Checking whether a provider factory is installed
  • Specifying connection strings and parameter names
  • Creating connections and commands
  • Creating a DataAdapter and retrieving rows
  • Creating and using a DataReader
  • When should I use provider-independent code?
The Factory Class Hierarchy
Figure 1. The Factory Class Hierarchy in ADO.NET 2.0: The figure shows the hierarchical relationship of the base classes and interfaces in ADO.NET 2.0 that let you write either provider-specific or provider-independent code implementations.
Figure 1 shows a schematic representation of the class hierarchy in ADO.NET 2.0 for the classes in System.Data and its descendant namespaces. At the root are the base interfaces for the classes, which define the functionality that is common across the different provider-dependent classes. For example the IDbConnection interface is implemented by SqlConnection, OleDbConnection, OracleConnection, etc.

Below this are the abstract base classes, such as DbConnection, which implement this common functionality. In ADO.NET 1.x, you could not write code to use these classes directly, but instead had to inherit from them and add your own required features. However, in ADO.NET 2.0, you can use the new provider factory model to create provider-specific instances of these classes using code, rather than having to write code that accesses specific provider-dependent subclasses.

In the remainder of this article, you'll see how to use the new provider-independent model to access and create instances of the base classes on demand, how to enumerate the available providers, and when and where you should consider this approach over the more usual technique of specifying the concrete provider-dependent classes in your code.

Author's Note: The code described in this article is based on the Beta 1 release of the .NET Framework v 2.0. In addition to the downloadable code on this site, you can download the code or run the examples online from http://www.daveandal.net/articles/provider-independent/.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date