Мы разрабатываем продукт, который может поддерживать несколько баз данных. В настоящее время мы делаем что-то подобное, чтобы наш код поддерживал 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;
}
}
}
}
Можете ли вы предложить, если есть лучший способ сделать это? Каковы же плюсы и минусы подхода?
