The .NET managed provider factory model makes it easy to switch between database providers without having to change or re-compile any code. How does it achieve this vendor independent behaviour? Through a software pattern or model known as the database provider model.

Database Providers

Typically, when writing applications that make use of ADO.NET you make use of a database provider; a namespace that essentially defines a collection of types for connecting to, and operating on, a specific database implementation. These include creating the initial connection, running SQL commands, reading or operating on the results etc. Each database provider, defines these types in its namespace. One such example, is the System.Data.SqlClient namespace which defines all the types need to connect and operate on a SQL Server database. The System.Data.SqlClient namesapce defines types such as SqlConneciton, SqlCommand and SqlDataReader and SqlDataAdapter.

Benefits to using data provider specific implementations

Using data provider specific types creates two benefits for the developer; these types can come with optimsations for the underlying database whilst offering extra provider specific functionality specific to the database implementation which is not common across all databases.

Disadvantages to using data provider specific implementations

Whilst using provider specifc types yields the aforementioned benefits it also has the unwanted side effect of binding an application strongly to a specific provider. This makes portability difficult since switching database providers would require a lot of code re-write.

In steps .NET 2.0 Data Provider Factory

.NET 2.0 brings with it a new data provider factory which is essentially a common namespace for database types; connection, command, data reader, data adapter, exception etc. This namespace; System.Data.Common provides data provider independent implementations of these types.

Any data provider implementations that inherit from the abstract System.Data.Common types can now be interchanged without instantiating specific types. This latter part, is provided by the System.Data.Common.DbProviderFactories, which is responsible for returning the correct types according to the information the factory is instantiated with. So if you provide the factory with an Sql provider, it will take care of returning you the correct Sql* implementations of the types you request. Similarly, providing the factory with a MySQL provider will will result in MySQL* types being returned. Note that you are always working with the generic abstract type and not the provider specific type.

 

Using application settings, you can define the provider and connection information outside your code which means you can change your database provider without having to re-compile or touch a single line of code!

public class DataProviderSpike
{
    private static DbProviderFactory _DbProviderFactory;
 
    public static void Main(String[] args)
    {
        Console.WriteLine("===== Data Provider Factory Pattern ======\n");
 
        // Get connection string infromation from *.config
        string dbProvider = ConfigurationManager.AppSettings["provider"];
        string conn= ConfigurationManager.AppSettings["connectionString"];
 
        // Get a factory for the provider information in our
        // application settings.
        _DbProviderFactory = DbProviderFactories.GetFactory(dbProvider);
 
        // Create generic connection object using helper utility.
        DbConnection dbConn = ADOUtility.OpenConnection(_DbProviderFactory,
                                         conn);
 
        string commandText = "SELECT * FROM Person";
 
        // Create generic command object.
        DbCommand dbCommand = DbProviderFactory .CreateCommand();
        dbCommand.Connection = dbConnection;
        dbCommand.CommandText = commandText;
 
        // Create generic data reader.
        DbDataReader reader = dbCommand.ExecuteReader();
 
        // Helper utlity for printing out provider info.
        ADOUtility.PrintProviderInfo(dbConn, dbCommand, reader);
 
        Console.WriteLine("\n\nRunning command: {0}\nResults:", commandText);
 
        while (reader.Read())
        {
            Console.WriteLine("\t\t {0} {1}", reader["FirstName"],
                                    reader["Surname"]);
        }
        Console.WriteLine("\nPress any key to exit...");
        // Pause execution.
        Console.ReadLine();
    }
}

Summary

The portability and flexability of the data provider factory pattern comes at the cost of losing the extra “goodies” and optimised types that would have otherwise been provided by the data provider specific implementation. Depending on the project, it is a case of deciding which is more important. Database specific functionality with potential provider optimisations, or ease of migration to different database vendors.