Using Provider Factories

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

using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.Text; namespace EnumFactories { class Program { static void Main(string[] args) {     DataTable tbl = DbProviderFactories.GetFactoryClasses();     Console.WriteLine("The following data provider factories are available:");     foreach (DataRow row in tbl.Rows)     {         Console.WriteLine("{0} ({1})", row["Name"], row["InvariantName"]);     }     Console.ReadLine(); } } } 

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:

• CreateConnection Creates a new connection instance. Under the hood, this connection will be specific to the provider you chose at the factory level, but you will receive a basic DbConnection instance in return.

• CreateCommand Creates an instance of a new command.

• CreateCommandBuilder Creates a new command builder.

• CreateConnectionStringBuilder Creates an instance of a connection string builder (covered in the next section).

• CreateDataAdapter Creates a new data adapter.

• CreateDataSourceEnumerator If the underlying provider supports it, this will create a new data source enumerator.

• CreateParameter Creates a new DbParameter instance.

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

using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.Text; namespace FactoryConnect { class Program { static void Main(string[] args) {     DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");     using (DbConnection conn = factory.CreateConnection())     {         conn.ConnectionString = "data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;";         conn.Open();         DbCommand cmd = factory.CreateCommand();         cmd.CommandText = "SELECT * FROM Customers";         cmd.Connection = conn;         DbDataReader rdr = cmd.ExecuteReader();         while (rdr.Read())         {             Console.WriteLine("{0}, {1} {2}", rdr["LastName"], rdr["FirstName"], rdr["MiddleInitial"]);         }     }     Console.ReadLine(); } } } 

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.