What is the best way to support multiple databases for a .NET product?

Question

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:

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

Can you please suggest if there is any better way of doing it? Also what are the pros and cons of the approach?

Answer 1

Whatever you do, don't write your own mapping code. Its already been done before, and its probably been done a million times better than whatever you could write by hand.

Without a doubt, you should use NHibernate. Its an object-relational mapper which makes database access transparent: you define a set of DAL classes which represent each table in your database, and you use the NHibernate providers to perform queries against your database. NHibernate will dynamically generate the SQL required to query the database and populate your DAL objects.

The nice thing about NHibernate is that it generates SQL based on whatever you've specified in the config file. Out of the box, it supports SQL Server, Oracle, MySQL, Firebird, PostGres and a few other databases.