我们正在设计一个可以支持多个数据库的产品。我们目前正在做这样的事情，以便我们的代码支持 MS SQL 和 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;
            }
        }
    }
}

请问有没有更好的方法？这种方法的优缺点是什么？

最佳答案

无论您做什么，都不要编写您自己的映射代码。它以前已经有人做过，而且可能比您手写的任何东西都要好一百万倍。

毫无疑问，您应该使用 NHibernate .它是一个使数据库访问透明的对象关系映射器:您定义一组 DAL 类来表示数据库中的每个表，然后使用 NHibernate 提供程序对数据库执行查询。 NHibernate 将动态生成查询数据库和填充 DAL 对象所需的 SQL。

NHibernate 的好处在于它会根据您在配置文件中指定的任何内容生成 SQL。开箱即用，它支持 SQL Server、Oracle、MySQL、Firebird、PostGres 和 a few other databases .

关于c# - 为 .NET 产品支持多个数据库的最佳方式是什么？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1311579/