SYSK 262: Lowest common denominator? No – interfaces offer a superior solution!


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!


 


 

Comments (2)

  1. Peter Ritchie says:

    Ahmen.  And, in fact, FxCop alludes to this with CA1011 "Consider passing base types as parameters", in certain circumstances.  Hopefully support for interface-driven design will be added in future versions.

  2. ChrisRomp says:

    Now that’s pretty cool!  I’ll have to try that one out.