后台代码的SQL语句根据不同数据库转换
⼀般情况下，我们的SQL语句都写在代码⾥，虽然耦合度⾼了，但是⽅便。然⽽，如果突然有⼀天，需求变了，换数据库，然后我们⽤新的数据库，⼀调试，发现有些SQL语句不执⾏，傻眼了，只能⼀条⼀条的改，⽽且，如果有⼀天数据库⼜要换回来，⼜要改回去，⾮常不⽅便。这⾥我提供⼀个⽅法。
根据配置更换数据库
<connectionStrings>
<!--<add name="dbPath" connectionString="..\..\App_Data\data.db" providerName="System.Data.SQLite"/>-->
<add name="dbPath" providerName="System.Data.OleDb" connectionString="..\..\App_Data\access.accdb"/>
</connectionStrings>
关键词ProviderName
mysql语句转oracleAceess数据库 System.Data.OleDb
SQLite数据库 System.Data.SQLite
Oracle 数据库 System.Data.OracleClient 或 Oracle.DataAccess.Client
MySql数据库 MySql.Data.MySqlClient
原本代码
我原本使⽤Sqlite数据库的代码
public static IDbConnection GetDBConnection()
{
System.Data.SQLite.SQLiteConnectionStringBuilder SQLiteSB = new System.Data.SQLite.SQLiteConnectionStringBuilder();
SQLiteSB.DataSource = System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ConnectionString;
return new System.Data.SQLite.SQLiteConnection(SQLiteSB.ToString());
}
使⽤Access数据库的代码
public static IDbConnection GetDBConnection()
{
System.Data.OleDb.OleDbConnectionStringBuilder AccessSB = new System.Data.OleDb.OleDbConnectionStringBuilder();
AccessSB.Provider = "Microsoft.ACE.OLEDB.12.0";
AccessSB.DataSource = System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ConnectionString;
return new System.Data.OleDb.OleDbConnection(AccessSB.ToString());
}
更改之后的代码
修改为⼀个抽象类MyDb
private static MyDb _myDb;
public abstract IDbConnection GetDbConnection();
public abstract string InsertTest();
public string SelectMyProject_All()
{
return "select * from MyProject where Name = @Name";
}
public string InsertMyProject()
{
return "insert into MyProject(ProjectID,Name,SharpType) Values(@ProjectID,@Name,@SharpType)";
}
public string InsertMySharp()
{
return "insert into MySharp(ProjectID,Name,SharpID) Values(@ProjectID,@Name,@SharpID)";
}
public string InsertProperty()
{
return "insert into Property(Standard,PositiveTolerance,NegativeTolerance,Compensation,PropertyID,Name,SharpID,IsSelected) Values(@Standard, @PositiveTolerance,@NegativeTolerance,@Compensation,@PropertyID,@Name,@SharpID,@IsSelected)";
}
public string SelectMyProject_ProjectForm()
功勋人物素材摘抄{
return "select t1.projectID as ProjectIDString,t1.Name,t1.Selection,t2.Name as SharpName from MyProject t1 left join MySharp t2 on t1.ProjectID=t2.P rojectID"; ;
}
public string SelectProperty()
{
return "select t1.Standard,t1.NegativeTolerance,t1.PositiveTolerance,t1.Compensation,t1.PropertyID as PropertyIDString,t1.IsSelected,t1.Name,t1.Sh arpID as SharpIDString from Property t1 left join MySharp t2 on t1.SharpID=t2.SharpID where t2.ProjectID=@ProjectID";
}
public string DelectMyProject()
{
return "delete from MyProject where Name =@Name";
}
public static MyDb CreateMyDbFactory()
{
switch (System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ProviderName)
{
//Aceess数据库
case "System.Data.OleDb":
_myDb = new AccessDb();
return _myDb;
/
/SQLite数据库
case "System.Data.SQLite":
default:
_myDb = new SqliteDb();
return _myDb;
Oracle 数据库
//case "System.Data.OracleClient":
//case "Oracle.DataAccess.Client":
// break;
MySql数据库
//case "MySql.Data.MySqlClient":
/
/ break;
}
}
}
⼀个Access的类继承MyDb
public override IDbConnection GetDbConnection()
{
System.Data.OleDb.OleDbConnectionStringBuilder AccessSB = new System.Data.OleDb.OleDbConnectionStringBuilder();
AccessSB.Provider = "Microsoft.ACE.OLEDB.12.0";
AccessSB.DataSource = System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ConnectionString;
return new System.Data.OleDb.OleDbConnection(AccessSB.ToString());
}
public override string InsertTest()
{
return "insert into Test([ID],[TestID],[PropertyID],[Name],[Value],[IsMore],[IsLess]) Values(@ID,@TestID,@PropertyID,@Name,@Value,@IsMore,@Is Less)";
}
}
⼀个Sqlite的类继承MyDb
public class SqliteDb : MyDb
{
public override IDbConnection GetDbConnection()
{
System.Data.SQLite.SQLiteConnectionStringBuilder SQLiteSB = new System.Data.SQLite.SQLiteConnectionStringBuilder();
SQLiteSB.DataSource = System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ConnectionString;
return new System.Data.SQLite.SQLiteConnection(SQLiteSB.ToString());
}
public override string InsertTest()
{
return "insert into Test(ID,TestID,PropertyID,Name,Value,IsMore,IsLess) Values(@ID,@TestID,@PropertyID,@Name,@Value,@IsMore,@IsLess)"; }
}
后台代码调⽤
之前的SQL语句代码
using (System.Data.IDbConnection con = db.GetDbConnection())
{
string sqlStr = "insert into Test([ID],[TestID],[PropertyID],[Name],[Value],[IsMore],[IsLess]) Values(@ID,@TestID,@PropertyID,@Name,@Value,@IsMore ,@IsLess)";
con.Execute(sqlStr, new
{
ID = test.ID.ToString("N"),
TestID = test.TestID.ToString("N"),
PropertyID = test.PropertyID.ToString("N"),
Name = test.Name,
Value = values,
IsMore = test.IsMore,
IsLess = test.IsLess,
Time = test.Time
});
}
改了之后
var db = MyDb.CreateMyDbFactory();
using (System.Data.IDbConnection con = db.GetDbConnection())
{
string sqlStr = db.InsertTest();
con.Execute(sqlStr, new
{
ID = test.ID.ToString("N"),
TestID = test.TestID.ToString("N"),
PropertyID = test.PropertyID.ToString("N"),
Name = test.Name,
Value = values,
IsMore = test.IsMore,
IsLess = test.IsLess,
Time = test.Time
});
}
改成MyDb抽象类之后便于测试，把SQL语句统⼀放在⼀个类⾥⽅便管理，⽽且耦合度低，还符合单⼀职责。
移植出业务逻辑层中的数据访问层的东西
后来，我看了三层架构，发现业务代码⾥，虽然SQL语句被隐藏了，但是还是有对数据进⾏操作，应该隐藏起来，如果要改，还是在数据访问层操作⽐较好。于是把MyDb类改成如下所⽰，将之前的得到SQL语句的⽅法变为protected。
public abstract class MyDb
{
名词加weight的短语private static MyDb _myDb;
protected abstract IDbConnection GetDbConnection();
protected abstract string CreateTest();
protected abstract string RetrieveTest();
protected string RetrieveMyProject_All()
{
return "select * from MyProject where Name = @Name";
}
protected string CreateMyProject()
{
return "insert into MyProject(ProjectID,Name,SharpType) Values(@ProjectID,@Name,@SharpType)";
}
protected string CreateMySharp()
{
return "insert into MySharp(ProjectID,Name,SharpID) Values(@ProjectID,@Name,@SharpID)";
}
protected string CreateProperty()
{
return "insert into Property(Standard,PositiveTolerance,NegativeTolerance,Compensation,PropertyID,Name,SharpID,IsSelected) Values(@Standar d,@PositiveTolerance,@NegativeTolerance,@Compensation,@PropertyID,@Name,@SharpID,@IsSelected)";
}
protected string RetrieveMyProject_ProjectForm()
{
return "select t1.projectID as ProjectIDString,t1.Name,t1.Selection,t2.Name as SharpName from MyProject t1 left join MySharp t2 on t1.ProjectID=t2 .ProjectID"; ;
}
protected string RetrieveProperty()
{
return "select t1.Standard,t1.NegativeTolerance,t1.PositiveTolerance,t1.Compensation,t1.PropertyID as PropertyIDString,t1.IsSelected,t1.Name,t1. SharpID as SharpIDString from Property t1 left join MySharp t2 on t1.SharpID=t2.SharpID where t2.ProjectID=@ProjectID";
}
protected string DelectMyProject()
{
return "delete from MyProject where Name =@Name";
}
#region 公有⽅法
public virtual int CreateTest(ITest test)
{
string values = string.Empty;
string values = string.Empty;
foreach (double dd in test.Value)
{
values += dd + ",";
}
values = values.Remove(values.Length - 1);
using (System.Data.IDbConnection con = GetDbConnection())
{
/
/string sqlStr = "insert into Test([ID],[TestID],[PropertyID],[Name],[Value],[IsMore],[IsLess]) Values(@ID,@TestID,@PropertyID,@Name,@Value, @IsMore,@IsLess)";
string sqlStr = CreateTest();
return con.Execute(sqlStr, new
{
ID = test.ID.ToString("N"),
TestID = test.TestID.ToString("N"),
PropertyID = test.PropertyID.ToString("N"),
Name = test.Name,
Value = values,
IsMore = test.IsMore,
IsLess = test.IsLess,
Time = test.Time
});
}
}
public int CreateMyProject(MyProject myProject)
{
using (IDbConnection con = GetDbConnection())
{
//string sqlStr = "insert into MyProject(ProjectID,Name,SharpType) Values(@ProjectID,@Name,@SharpType)";
string sqlStr = CreateMyProject();
详细的三角函数公式表
return con.Execute(sqlStr, new { ProjectID = myProject.ProjectID.ToString("N"), Name = myProject.Name, SharpType = myProject.SharpType }); }
}
public int RetrieveMyProject_All(string name)
{
using (IDbConnection con = GetDbConnection())
{
//string sqlStr = "select * from MyProject where Name = @Name";
string sqlStr = RetrieveMyProject_All();
return con.Query<int>(sqlStr, new { Name = name }).Count();
}
}
public int CreateMySharp(MyProject myProject)
{
常用傅里叶变换公式表>avalonedit 不被修改using (IDbConnection con = GetDbConnection())
{
//sqlStr = "insert into MySharp(ProjectID,Name,SharpID) Values(@ProjectID,@Name,@SharpID)";
string sqlStr = CreateMySharp();
return con.Execute(sqlStr, new { ProjectID = myProject.MySharp.ProjectID.ToString("N"), Name = myProject.MySharp.Name, SharpID = myProje ct.MySharp.SharpID.ToString("N") });
}
}
public int CreateProperty(Property property)
{
using (IDbConnection con = GetDbConnection())
{
//sqlStr = "insert into Property(Standard,PositiveTolerance,NegativeTolerance,Compensation,PropertyID,Name,SharpID,IsSelected) Values(@St andard,@PositiveTolerance,@NegativeTolerance,@Compensation,@PropertyID,@Name,@SharpID,@IsSelected)";
string sqlStr = CreateProperty();
return con.Execute(sqlStr, new
{
Standard = property.Standard,
PositiveTolerance = property.PositiveTolerance,
NegativeTolerance = property.NegativeTolerance,
Compensation = property.Compensation,
PropertyID = property.PropertyID.ToString("N"),
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论