The other day I saw some code from a vendor (who will remain unnamed) that prompted me to write this post…

The aforementioned vendor has a product that works on many relational databases, including Microsoft SQL Server, Oracle, etc.  The product is written in C#, but as it turns out, they are using OleDb classes for all database I/O (OleDbConnection, OleDbCommand, etc.)

 

In my opinion, a factory object-oriented design pattern would be a better choice…  While you could create a factory class that has the if-then type of logic for figuring out and instantiating the proper implementation (e.g. SqlConnection, OleDbConnection, OracleConnection, etc.) based on configuration settings, .NET framework has provided such class – System.Data.Common.DbProviderFactory. 

 

So, if you have your connection strings declared in connectionStrings config file element (i.e. utilizing the System.Configuration.ConfigurationStringSettings class), your job is as simple as this:

 

<connectionStrings>

      <add name="YourDbName"

        connectionString="Persist Security Info=False;Integrated Security=SSPI;database=YourDB;server=YourServer;"

            providerName="System.Data.SqlClient" />  

</connectionStrings>

 

 System.Configuration.ConnectionStringSettings setting =

    System.Configuration.ConfigurationManager.ConnectionStrings["YourDbName"];

 

System.Data.Common.DbProviderFactory providerFactory =

    System.Data.Common.DbProviderFactories.GetFactory(setting.ProviderName);

 

System.Data.Common.DbConnection cn = providerFactory.CreateConnection();

cn.ConnectionString = setting.ConnectionString;

 

 

cn.Open();

. . .

 

 

Note:  DbProviderFactory classes are registered in machine.config in system.data element; e.g.:

<system.data>

    <DbProviderFactories>

      <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" />

      <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" />

      <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" />

      <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" />

      <add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

    </DbProviderFactories>

  </system.data>

 

 

 

We can use this pattern for other similar scenarios, e.g. different implementations of excepting logging – DbExLogger, FileExLogger, WindowsEvenLogExLogger, ConsoleExLogger, etc.

  1. Define an interface (similar to IDbConnection interface)
  2. Optionally, create an abstract class that derives from this interface plus implements other common properties, methods, events (similar to DbConnection class)
  3. Create implementation classes – DbExLogger, FileExLogger, etc. that inherit from the abstract class created in step 2 and implement the interface defined in step 1 (similar to SqlConnection, OleDbConnection, etc.).
  4. Create a factory class that creates the proper instance of the exception logging implementation based on run-time known data (e.g. based on configuration file settings)

Now, that, in my opinion, is a clean, maintainable, and extensible solution!