One of the problems of working with data providers is that they are not all the same. Prior to version 2.0 of ADO.NET, developers often had to do a lot of work on their own to get their applications to work with an OLE DB data source as well as a SQL Server data source without having to create two different data layers. In these situations, developers often created their own provider-agnostic wrappers that abstracted the work of allowing code to access both data sources seamlessly.
With ADO.NET 2.0, such wrappers are no longer necessary. The .NET Framework provides several classes that allow you to use a factory pattern to access data providers without specifically tying your code to a single implementation such as the DbProviderFactories class and the DbProviderFactory class. Using the factory pattern, you can create code that will execute commands on multiple providers without having to create complex conditional logic or multiple layers of abstraction.
Data providers are a lot more powerful under ADO.NET 2.0 than they were in previous versions. For example, when a data provider is installed, it can modify a computer's machine.config file to indicate that it has installed a provider factory. This allows developers to query the list of installed provider factories and handle different situations accordingly. In addition, new ADO.NET providers have the ability to provide multiple types of metadata to further assist developers in coding generically against multiple types of data without excessive or redundant code.
Obtaining the List of Installed Provider Factories
You can use the ProviderFactories class to obtain a list of installed factories. The list of installed factories comes in the form of a DataTable that contains the provider name, its invariant name (fully qualified namespace), a long description, and the fully qualified assembly strong name.
The code in Listing 19.1 iterates through the list of installed provider factories and displays their names and invariant names. You will need a provider's invariant name to create an instance of that provider's factory.
Listing 19.1. Enumerating the List of Installed Provider Factories
The preceding code produces the following output on the author's machine:
The following data provider factories are available: Odbc Data Provider (System.Data.Odbc) OleDb Data Provider (System.Data.OleDb) OracleClient Data Provider (System.Data.OracleClient) SqlClient Data Provider (System.Data.SqlClient) SQL Server CE Data Provider (Microsoft.SqlServerCe.Client)
Using a Provider Factory to Establish a Connection
Using a data provider factory to establish a connection is actually a pretty simple task. First, you create an instance of a specific factory using the DbDataFactories.GetFactory() method. When you have an instance of the factory, you can use any of the following DbProviderFactory methods to create a provider-agnostic instance of an object:
Take a look at the code in Listing 19.2, as it provides an illustration of how to establish a connection to a data source and execute a command without ever hard-coding a reference to a SqlConnection instance or an OleDbConnection instance.
Listing 19.2. Establishing a Provider-Agnostic Connection
The important thing to note about the preceding code is that the only indications of the underlying provider are the connection string, and the invariant name of the provider. Both of those pieces of information could have come from a configuration file, from user input, or from another data source. The preceding code will work just as well on any underlying data provider that has a Customers table with the "LastName", "FirstName", and "MiddleInitial" columns.