C# Design Pattern for Database Helper classes

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:

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();
      }
    }
  }
}

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 SqlDBManager implement it, and then develop an OracleDBManager implementing the same interface. The problem is that my class is static, and static classes cannot implement an interface. I would like my helper clas开发者_JAVA百科s 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.

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 OracleDBManager and SqlDBManager 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.

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.

Any input and idea will be greatly appreciated. Tks

Why not make your static method private, wrap the classes in an interface to support MS-SQL / Oracle, and call the private static methods in the respective interfaces?

E.g:

public interface ISqlDbManager
{
   void SaveOrder(Order o);
   void FindOrderById(int orderId);
}

public class SqlServerDbManager : ISqlDbManager
{
    private static void RunSql(String pSql, DBParamsHelper pDBParams, String   pConnStringConfigName)
    {
       // implement as you did above
    }

   public void FindOrderById(int orderId)
   {
      // create SQL, call private "RunSql" method.
   }
}

Do the same thing for the other implementation (OracleDbManager).

It makes sense to have it private, since the consumer shouldn't care how the underlying persistence mechanism works.

And this will also make unit testing easier - create a "MockDbManager" class, where the private static method does basic LINQ operations on an in-memory list.

On a side note, i would strongly recommend the use of stored procedures, instead of constructing sql commands manually. Better for query plan caching / optimization.

The interface is the right direction to go in, but as you've pointed out, you can't have a static class implement an interface. I understand wanting to minimize the fuss of object creation, but that will likely be necessary in some way in order to have two different database classes.

The solution I suggest is multi-faceted. First is an interface with a signature similar to what you listed above:

public Interface IDbManager {
     void RunSql(String pSql, DBParamsHelper pDBParams, String pConnStringConfigName)
}

That can be implemented in SQL- and Oracle-specific versions, you already have the SQL version, just make it non-static and implement the interface.

Now try a database factory, perhaps like the following:

public static class DbFactory {
    public static IDbManager CreateDb(DbType type) {
        select (type) {
            case DbType.Sql:
                return new SqlDbManager();
                break;
            case DbType.Sql:
                return new OracleDbManager();
                break;
        }
    }
}

Then you should be able to do something like:

var db = DbFactory.CreateDb(DbType.Sql);
db.RunQuery(...);

This code is untested, but hopefully you get the idea. I use a similar solution for one of my projects where I need to get data from different data stores. The strategy and factory patterns ease this process.

Hope that helps!