title: C# Design Pattern for Database Helper classes
body: <p>I'm designing a WCF Service that will called by several hundred clients, and I have a question about the best architecture for the classes that will run by database queries. Today I only access SQL Server, so I have a static class that I call internally that does all the dirty work of creating connections and datareaders. Below is a simple example:</p>
  
  <pre><code>namespace DBHelper.Utility
  {
    public static class SqlDBManager
    {
      public static void RunSql(String pSql, DBParamsHelper pDBParams, String pConnStringConfigName)
      {
        String sConnectionString = GetConnectionStringFromConfig(pConnStringConfigName);
        SqlConnection oConn = new SqlConnectionsConnectionString 
        oConn.Open();
        try
        {
          SqlCommand oCommand = new SqlCommand(pSql, oConn);
          oCommand.CommandTimeout = 0;
          if (pDBManagerParams != null)
          {
            foreach (SqlParameter sqlParam in pDBManagerParams)
            {
              oCommand.Parameters.Add(sqlParam);
            }
          }
          oCommand.ExecuteNonQuery();
        }
        finally
        {
          oConn.Close();
        }
      }
    }
  }
  </code></pre>
  
  <p>Now, I need to add support for running both Sql Server and Oracle. My initial idea was to declare an interface, and have my existing <code>SqlDBManager</code> implement it, and then develop an <code>OracleDBManager</code> implementing the same interface. The problem is that my class is static, and static classes cannot implement an interface. I would like my helper class to remain as static, with it's a lot more practical, and I don't have to create a new object every time I need to run a query. I also thought of using class inheritance, but I can't have statis virtual methods, so not much use there. I considered some singleton implementations so I wouldn't have to create classes, but then I would have trouble on the multi-threaded access.<br>
  What would be the best design pattern so I can have great performance on multiple threaded scenario (very important), not too much work coding for productivity (not have to create a lot of classes), and have a standard methods for both <code>OracleDBManager</code> and <code>SqlDBManager</code> classes? The standard method is very important, because I don't want to the code that uses these helper classes to know if they are connected to Oracle or Sql Server.<br>
  I did consider using ORM solution, such as Entity Framework 4 and nHibernate, but the performance impact was too much. Since I will run simple queries, the query syntax difference between PL-SQL and TSQL won't matter.<br>
  Any input and idea will be greatly appreciated. Tks</p>