How to 'inherit' from IDbConnection - just add one property (DatabaseType)

Question

How do you inherit from IDbConnection? I would like to add one property (DatabaseType like MS Access, SQL server, Paradox...). From what I can tell IDbConnection is an interface and thus I believe they want me to implement all members in the entire interface inheritance chain. That seems like a lot of work. What is the best way to add one property to IDbConnection?

Update What I am trying to do is this. I have three methods that I use heavily: ExecuteReader, ExecuteNonQuery, ExecuteNonQueryGetIdentity. They are heavily used and have parameters (IDbConnection Conn, string SQLString, object[] SQLParams). I thought the best way to add DatabaseType to these three methods and all my project methods was to 'override' IDbConnection so that I only have to update one line of code in my project.

I want to know DatabaseType because there are a few things I do differently when adding parameters and building the IDbCommand. Specifically for DateTime.

E.g.:

public static System.Data.IDataReader ExecuteReader(System.Data.IDbConnection Conn, string SQL, object[] SQLParams)
// return IDataReader from connection, SQL string and Params. Params can be null. 
{
  using (var cmd = Conn.CreateCommand())
  {
    cmd.CommandText = SQL;
    AddParametersToCommand(cmd, SQLParams);   // add parameters to IDbCommand object if params not null
    return cmd.ExecuteReader();  
  }
}

private static readonly Regex regParameters = new Regex(@"@\w+", RegexOptions.Compiled);
public static void AddParametersToCommand(IDbCommand Cmd, object[] Parameters)
/* Creates and ads unique parameters to an IDbCommand object to the CommandText SQL string.
  * Tested types with SQL Update statement in MS Access/SQL Server: boolean, int, DateTime, text
  * Parameter names in CommandText must start with the '@' char and can be any unique word (letter or number).
  * E.g. calling code: cmd.CommandText = "Select * From SiteUser Where Site > @1 And User > @NewParam"
  * 
  * http://www.codeproject.com/Articles/15542/IDbDataParameter-error-in-NET  re: some DateTime issues
*/
{
  if (Parameters != null && Parameters.Length > 0)
  {
    MatchCollection cmdParams = regParameters.Matches(Cmd.CommandText);
    var paramNames = new List<String>();
    foreach (var el in cmdParams)
    {
      if (!paramNames.Contains(el.ToString()))   // only add unique parameter names
        paramNames.Add(el.ToString());
    }
    IDbDataParameter dp;
    var dbType = GetDatabaseType(Cmd.Connection);
    for (int n = 0; n < Parameters.Length; n++)
    {
      var param = Parameters[n];
      dp = Cmd.CreateParameter();
      dp.ParameterName = paramNames[n];
      TypeCode myTypeCode = Type.GetTypeCode(param.GetType());
      if (myTypeCode == TypeCode.DateTime)     // this workaround is needed for MS Access and SQL Server
      {
        if (dbType == DatabaseType.Access)
        {
          dp.DbType = DbType.DateTime;         // set dates as DbType.DateTime for MS Access and Paradox
          dp.Value = param.ToString();         // Convert.ToDateTime(param).ToString("yyyy-MM-dd hh:mm:ss"));         
          //Note: MS access cannot store years before December 30, 1899. They will be stored for some other year.
          // for example. Jan 1 0001 will be stored as 2001.
        }
        else if (dbType == DatabaseType.MSSql)
        {
          dp.DbType = DbType.DateTime2;        // set dates as DbType.DateTime2 for SQL Server
          dp.Value = param.ToString();
        }
      }
      else
        dp.Value = param;
      Cmd.Parameters.Add(dp);
    } // for n
  } // if
}

Answer 1

You can create new interface MyDbConnection with DatabaseType property, but none of existing IDbConnection implementations will implement your interface.

I think you should not care which implementation of IDbConnection you are using (MS Access, SQL server, Paradox etc). That's why we have this abstraction and DbProviderFactory classes, which totally abstract type of database we are working with.

BTW you can always check type of IDbConnection instance to see which implementation you are using (if you really need that). Another option for you (if you really need that property) - create decorator over IDbConnection instance:

public class MyDbConnection : IDbConnection
{
    private IDbConnection _connection;

    public MyDbConnection(IDbConnection connection)
    {
       _connection = connection;
    }

    // here goes your property
    public string DatabaseType { get; set; }

    public void Close()
    {
        _connection.Close();
    }

    public IDbTransaction BeginTransaction(IsolationLevel il)
    {
        return _connection.BeginTransaction(il);
    }

    // implement other IDbConnection members by delegating work to _connection
}

You can use this decorator everywhere as IDbConnection, but also your property will be available.

Answer 2

DbConnection implements the interface IDbConnection. You could just extend that (It must implement all of the interfaces methods)