One Way to Support for the Independent Database Providers
Excerpt from "The Definitive Guide To The Microsoft Enterprise Library 2007".
In .NET Framework 1.0 and 1.1, ADO.NET provided the following interfaces to help facilitate creating database provider-independent application code: IDbConnection, IDbCommand, IDbDataAdapter, IDbDataParameter, IDbDataParameterCollection, IDataReader, IDataRecord, IDbTransaction, etc.
In most organizations requiring database provider-independent code, the application developer would create some sort of static method that would implement a factory pattern. This method would typically look into some configuration file to determine which database provider implementation to instantiate and return to the application.
2 {
3 string prov = ConfigurationManager.AppSettings["dbProvider"];
4 IDbConnection conn = null;
5 switch (prov)
6 {
7 case "sqlserver":
8 conn = new SqlConnection();
9 break;
10 case "oracle":
11 conn = new OracleConnection();
12 break;
13 default:
14 throw new ConfigurationException("Database provider not found in Configuration File");
15 break;
16 }
17 return conn;
18 }
While the technique in Listing 1 would work, it would also require code changes if new database providers were to be added. Another concern with the use of the database provider independent interfaces is that they are immutable. This creates a major problem when a new base feature needs to be added to a database provider via the base interface.
In ADO.NET 2.0, a series of new base classes were created to essentially replace the existing IDb* interfaces: DbConnection, DbCommand, DbDataReader, DbParameter, DbParameterCollection, DbDataAdapter, DbTransaction, DbCommandBuilder, DbConnectionStringBuilder, etc.
While these base classes help resolve some of the issues involved in using interfaces, using them by themselves would not resolve the issue found in Listing 1, where the switch statement was required to determine which connection object to instantiate. This is where the new ADO.NET 2.0 DbProviderFactory factory class comes in to fill that gap.
The DbProviderFactory class can create all the concrete classes necessary for a specific provider with methods: CreateConnection, CreateCommand, CreateCommandBuilder, CreateConnectionStringBuilder, CreateDataAdapter, CreateParameter, etc.
Each database provider has its own specific implementation of the DbProviderFactory class to support its functionality.
The DbProviderFactories class is responsible for creating the correct concrete instance of the DBProviderFactory class with methods: GetFactoryClasses(), GetFactory(DataRow), GetFactory(string).
In order for the DbProviderFactories class to function properly, it is necessary for the proper configuration data to be inserted into the machine.config or application file.
2 <DbProviderFactories>
3 <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
4 <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
5 <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
6 <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
7 <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/></DbProviderFactories>
8 </system.data>
Now with the use of the connection string settings, database provider base classes, and provider factory classes, it is possible to create a connection object without specifying its type within the code of an application.
2 {
3 ConnectionStringSettings connSetting = ConfigurationManager.ConnectionStrings["MyDataBase"];
4 DbProviderFactory factory = DbProviderFactories.GetFactory(connSetting.ProviderName)
5 DbConnection connection = null;
6 connection = factory.CreateConnection();
7 connection.ConnectionString = connSetting.ConnectionString;
8 return connection;
9 }
2 <connectionStrings>
3 <add name="MyDatabase"
4 connectionString="(local)\SQLEXPRESS;Initial Catalog=MyDatabase;
5 integrated security=SSPI;Asynchronous Processing=True"
6 providerName="System.Data.SqlClient" />
7 </connectionStrings>
8 </configuration>
Now it is possible to write code that is generic and be able to use any provider that implements the database provider base classes. Typically, developers would then use the DbProviderFactory class to create the necessary DbCommand objects, DbDataAdapter objects, DbDataReader objects, and so on to complete their data access layer implementation.
