Different database types in .net app

Question

I'm writing an 'off-the shelf' desktop app in C# that needs to connect to one of three different types of database (SQL Server, MySQL or SQL Server Compact) depending on the version and customer requirements.

I have been using ADO code as follows:

using (SqlConnection conn = MSSQLHelpers.GetConnection())
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT * FROM settings WHERE ID=@ID";
        cmd.Parameters.AddWithValue("@ID", 1);

        using (SqlDataReader rdr = MSSQLHelpers.GetDataReader(cmd))
        {
            if (rdr.Read())
            {
                Config.AdvancedSecurity = rdr.GetBoolean(rdr.GetOrdinal("advancedsecurity"));
                Config.BookSampleInOnCreate = rdr.GetBoolean(rdr.GetOrdinal("newsamplein"));
                etc....
            }
            rdr.Close();
        }
    }
    conn.Close();
}

This is obviously specific to SQL Server. My question is what would be the best way to avoid repeating all the above for each of the three database types using MySqlConnection, SqlCeConnection etc?

Many thanks in advance for any help.

Jon

Answer 1

I have actually done similar, and I did with a hierarchy class structure using the INTERFACES. For example, if you look at SqlConnection, you be declared as inclusive of the "IDbConnection" interface. Likewise SqlCommand would include "IDbCommand", SqlDataAdapter uses "IDbDataAdapter" and similar for parameters and the like.

So, I had a parent class that was like a template that all would have (almost an abstract class), but since I had common stuff regardless of which connection type, it was actually a function class too.

public class MyDBHandler
{
   public virtual IDbConnection GetConnection()
   {  throw new Exception( "Please define specific GetConnection method"}; }

   public virtual IDbCommand GetCommand()
   {  throw new Exception( "Please define specific GetCommand method"}; }

   public virtual IDbDataAdapter GetDataAdapter()
   {  throw new Exception( "Please define specific DataAdapter method"}; }

   public virtual string GetConnectionString()
   {  throw new Exception( "Please define specific ConnectionString method"}; }


   etc...

   // Then some common properties you might want for connection path, server, user, pwd
   protected string whatServer;
   protected string whatPath;
   protected string whatUser;
   protected string whatPwd;


   protected connectionHandle;

   public MyDBHandler()
   {
      // always start with HAVING a connection object, regardless of actual connection or not.
      connectionHandle = GetConnection();
   }


   // common function to try opening corresponding connection regardless of which server type
   public bool TryConnect()
   {
      if( connectionHandle.State != System.Data.ConnectionState.Open )
         try
         {
            connectionHandle.ConnectionString = GetConnectionString();
            connectionHandle.Open();
         }
         catch( Exception ex )
         {
            // notify user or other handling 
         }

      if( connectionHandle.State != System.Data.ConnectionState.Open )
         MessageBox.Show( "Some message to user." );

      // return true only if state is open
      return connectionHandle.State == System.Data.ConnectionState.Open;
   }

   // Now, similar to try executing a command as long as it is of IDbCommand interface to work with
   public bool TryExec( IDbCommand whatCmd, DataTable putResultsHere )
   {
      // if can't connect, get out
      if( ! TryConnect() )
         return false;

      bool sqlCallOk = false;

      if( putResultsHere == null ) 
         putResultsHere = new DataTable();

      try
      {
         da.Fill(oTblResults, putResultsHere);

         // we got this far without problem, it was ok, regardless of actually returning valid data
         sqlCallOk = true;
      }
      catch( Exception ex )
      {
         // Notify user of error
      }

      return sqlCallOk;
   }


}

public class SQLHandler : MyDbHandler
{
   public override IDbConnection GetConnection()
   {  return (IDbConnection) new SqlConnection(); }

   public override IDbCommand GetCommand()
   {  return (IDbCommand) new SqlCommand("", connectionHandle ); }

   public override IDbDataAdapter GetDataAdapter()
   {  return (IDbDataAdapter) new SqlDataAdapter(); }

   public override string GetConnectionString()
   {  return "Driver={SQL Server}; blah, blah of properties"; }

}

public class MySQLHandler : MyDbHandler
{
   public override IDbConnection GetConnection()
   {  return (IDbConnection) new MySqlConnection(); }

   public override IDbCommand GetCommand()
   {  return (IDbCommand) new MySqlCommand("", connectionHandle ); }

   public override IDbDataAdapter GetDataAdapter()
   {  return (IDbDataAdapter) new MySqlDataAdapter(); }

   public override string GetConnectionString()
   {  return "Driver={MySql}; blah, blah of properties"; }
}

Then, in your code, depending on how you want to handle, you could do

MyDbHandler whichDatabase = new SQLHandler();
// set any settings for such connection and such...

IDbCommand cmd = whichDatabase.GetCommand();
cmd.CommandText = "select * from whereEver";
DataTable oTmp = new DataTable()
whichDatabase.TryExec( cmd, oTmp );

Now you have a table returned, and populated with rows/columns from the query as needed, close connection in the TryExec() call, or however you find fit.

Just an stripped-down version of something I did in the past.

If you want to swap between MySql, Sql-Server, Access, Visual FoxPro, etc create corresponding handlers.