title: What is the best way to support multiple databases for a .NET product?
body: <p>We are designing a product which could support multiple databases. We are doing something like this currently so that our code supports MS SQL as well as MySQL:</p>
  
  <pre><code>namespace Handlers
  {
      public class BaseHandler
      {
          protected string connectionString;
          protected string providerName;
  
          protected BaseHandler()
          {
              connectionString = ApplicationConstants.DatabaseVariables.GetConnectionString();
              providerName = ApplicationConstants.DatabaseVariables.GetProviderName();
          }
      }
  }
  
  namespace Constants
  {
      internal class ApplicationConstants
      {
          public class DatabaseVariables
          {
              public static readonly string SqlServerProvider = "System.Data.SqlClient";
              public static readonly string MySqlProvider = "MySql.Data.MySqlClient";
  
              public static string GetConnectionString()
              {
                  return ConfigurationManager.ConnectionStrings["CONNECTION_STRING"].ConnectionString; 
              }
  
              public static string GetProviderName()
              {
                  return ConfigurationManager.ConnectionStrings["CONNECTION_STRING"].ProviderName;
              }
          }
      }
  }
  
  namespace Handlers
  {
      internal class InfoHandler : BaseHandler
      {
          public InfoHandler() : base()
          {
          }
  
          public void Insert(InfoModel infoModel)
          {
              CommonUtilities commonUtilities = new CommonUtilities();
              string cmdInsert = InfoQueryHelper.InsertQuery(providerName);
              DbCommand cmd = null;
              try
              {
                  DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);
                  DbConnection con = LicDbConnectionScope.Current.GetOpenConnection(provider, connectionString);
                  cmd = commonUtilities.GetCommand(provider, con, cmdInsert);
                  commonUtilities.PrepareCommand(cmd, infoModel.AccessKey, "paramAccessKey", DbType.String, false, provider, providerName);
                  commonUtilities.PrepareCommand(cmd, infoModel.AccessValue, "paramAccessValue", DbType.String, false, provider, providerName);
                  cmd.ExecuteNonQuery();
              }
              catch (SqlException dbException)
              {
                  //-2146232060 for MS SQL Server
                  //-2147467259 for MY SQL Server
                  /*Check if Sql server instance is running or not*/
                  if (dbException.ErrorCode == -2146232060 || dbException.ErrorCode == -2147467259)
                  {
                      throw new BusinessException("ER0008");
                  }
                  else
                  {
                      throw new BusinessException("GENERIC_EXCEPTION_ERROR");
                  }
              }
              catch (Exception generalException)
              {
                  throw generalException;
              }
              finally
              {
                  cmd.Dispose();
              }
          }
      }
  }
  
  namespace QueryHelpers
  {
      internal class InfoQueryHelper
      {
          public static string InsertQuery(string providerName)
          {
              if (providerName == ApplicationConstants.DatabaseVariables.SqlServerProvider)
              {
                  return @"INSERT INTO table1
             (ACCESS_KEY
             ,ACCESS_VALUE)
       VALUES
             (@paramAccessKey
             ,@paramAccessValue) ";
              }
              else if (providerName == ApplicationConstants.DatabaseVariables.MySqlProvider)
              {
                  return @"INSERT INTO table1
             (ACCESS_KEY
             ,ACCESS_VALUE)
       VALUES
             (?paramAccessKey
             ,?paramAccessValue) ";
              }
              else
              {
                  return string.Empty;
              }
          }
      }
  }
  </code></pre>
  
  <p>Can you please suggest if there is any better way of doing it? Also what are the pros and cons of the approach?</p>