公司的项目需要兼容多个数据库，需要根据不懂客户提供的环境进行切换数据库配置。
公司现阶段所使用的方法类似于这样。
string sqlStr = "select COUNT(0) as rcount from DS_Compilation where OrgCode like @OrgCode+‘%‘"; string sql2 = "SELECT COUNT(0) AS rcount FROM dbo.DS_Compilation a JOIN dbo.t_SPJKSB b ON a.DevID=b.RecordID AND a.GBID=b.GBID AND a.SBAZSZDW_GAJGJGDM LIKE @OrgCode+‘%‘"; if (Common.Common.isMySQL()) { sqlStr = "select COUNT(0) as rcount from DS_Compilation where OrgCode like CONCAT(@OrgCode,‘%‘)"; sql2 = "SELECT COUNT(0) AS rcount FROM dbo.DS_Compilation a JOIN dbo.t_SPJKSB b ON a.DevID=b.RecordID AND a.GBID=b.GBID AND a.SBAZSZDW_GAJGJGDM LIKE CONCAT(@OrgCode,‘%‘)"; } var rowData = DalContextEx.GetSqlTextList< SPJKSB>(sqlStr, new { OrgCode = OrgCode }, ConnectionKind.Reader); var rowData2 = DalContextEx.GetSqlTextList<SPJKSB>(sql2, new { OrgCode = OrgCode }, ConnectionKind.Reader);
通过if判断是使用sql还是mysql，然后在该处写多段sql语句来实现，当然实现方法有很多种，公司也曾讨论过使用EF来解决该问题，但是项目体量大，重新改用EF时间成本太大。
我个人不太喜欢通过if来判断，所以使用IOC控制反转-把对象的依赖换成对抽象的依赖。直接上代码
大致遇到用到以下几个文件：
1.先创建个接口文件,interface，简单的先上两个方法，我自己文件里面肯定不止两个。
public interface SqlInterface {
void Show(); public int SavePlatformSetting(string PlatformName, string Manufactor, string ip, string port, string uname, string pwd, string sdk, string keyID, string key, string gbid) { return 0; } public int AddNvrDev(string NvrName, string OrgNo, string Place, string IPDZ, string DevPort, string DevUser, string DevPwd, string NvrUrl, int IPC, string gbid) { return 0; } }
2.之后我们就是需要去实现接口文件里面的那些接口，可一个控制器对应一个sql方法文件。sql和mysql各都需要一份。先上sql部分
using Biz; using Dal; using Entity.DeviceSyn; using Entity.Video; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using Web.DeviceMg.Args; using Web.DeviceMg.Models; namespace Web.DeviceMg.SqlFile { //注意这里继承SqlInterface public class DeviceSynSql: SqlInterface { public void Show() { Console.WriteLine("这是使用sql"); } public int SavePlatformSetting(string PlatformName, string Manufactor, string ip, string port, string uname, string pwd, string sdk, string keyID, string key, string gbid) { var sqlStr = @"INSERT INTO [dbo].[DS_PlatSet] ([PSID],[PlatformName],[ManufactorName],[IP],[Port],[UserName],[Password],[SDKKind],[KeyID],[Key],CreateDate) VALUES(@psid,@PlatformName,@Manufactor ,@ip,@port,@uname,@pwd,@sdk,@keyID,@key,getdate())"; var rowLine = DalContextEx.RunSqlText(sqlStr, new { psid = gbid, PlatformName, Manufactor, ip, port, uname, pwd, sdk, keyID, key }, ConnectionKind.Writer); return rowLine; } public int AddNvrDev(string NvrName, string OrgNo, string Place, string IPDZ, string DevPort, string DevUser, string DevPwd, string NvrUrl, int IPC, string gbid) { var sqlStr = @"insert into SI_nvrtab (ID,Name,OrgNo,Scence,IPDZ,Port,IpcCount,UserName,Pass,IsDel,NvrUrl,CreateDate) values(@ID,@Name,@OrgNo,@Scence,@IPDZ,@Port,@IpcCount,@UserName,@Pass,0,@NvrUrl,getdate())"; var rowLine = DalContextEx.RunSqlText(sqlStr, new { ID = gbid, Name = NvrName, OrgNo = OrgNo, Scence = Place, IPDZ = IPDZ, Port = DevPort, IpcCount = IPC, UserName = DevUser, Pass = DevPwd, NvrUrl = NvrUrl }, ConnectionKind.Writer); return rowLine; } } }
3.mysql的文件。
using Dal; using Entity.DeviceSyn; using Entity.Video; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using Web.DeviceMg.Args; using Web.DeviceMg.Models; namespace Web.DeviceMg.MySqlFile { public class DeviceSynMysql: SqlInterface { public void Show() { Console.WriteLine("这是使用Mysql"); } public int SavePlatformSetting(string PlatformName,string Manufactor, string ip, string port, string uname, string pwd, string sdk,string keyID, string key,string gbid) { var sqlStr = @"INSERT INTO DS_PlatSet (PSID,PlatformName,ManufactorName,IP,Port,UserName,Password,SDKKind,KeyID,`Key`,CreateDate) VALUES(@psid,@PlatformName,@Manufactor ,@ip,@port,@uname,@pwd,@sdk,@keyID,@key,now())"; var rowLine = DalContextEx.RunSqlText(sqlStr, new { psid = gbid, PlatformName, Manufactor, ip, port, uname, pwd, sdk, keyID, key }, ConnectionKind.Writer); return rowLine; } public int AddNvrDev(string NvrName, string OrgNo,string Place,string IPDZ, string DevPort, string DevUser, string DevPwd, string NvrUrl,int IPC,string gbid) { var sqlStr = @"insert into SI_nvrtab (ID,Name,OrgNo,Scence,IPDZ,Port,IpcCount,UserName,Pass,IsDel,NvrUrl,CreateDate) values(@ID,@Name,@OrgNo,@Scence,@IPDZ,@Port,@IpcCount,@UserName,@Pass,0,@NvrUrl,now())"; var rowLine = DalContextEx.RunSqlText(sqlStr, new { ID = gbid, Name = NvrName, OrgNo = OrgNo, Scence = Place, IPDZ = IPDZ, Port = DevPort, IpcCount = IPC, UserName = DevUser, Pass = DevPwd, NvrUrl = NvrUrl }, ConnectionKind.Writer); return rowLine; } } }
抽象发放与实现都写完了，接下来就是哪里去调用sql或者mysql的方法，首先我们在控制器里面申明引用下那个抽象方法类
namespace Web.DeviceMg.Controllers { [Route("DeviceSyn")] [ApiController] public class DeviceSynController : ControllerBase { private readonly SqlInterface _SqlService;//声明变量 public DeviceSynController(SqlInterface DeviceSynSql) { _SqlService = DeviceSynSql;//将这个类里面的东西交给 _SqlService； } /// <summary> /// 保存视频平台配置 /// </summary> /// <param name="PlatformName">平台名称</param> /// <param name="Manufactor">厂家</param> /// <param name="ip">ip</param> /// <param name="port">端口</param> /// <param name="uname">用户名</param> /// <param name="pwd">密码</param> /// <param name="sdk">SDK</param> /// <param name="keyID">keyid</param> /// <param name="key">key</param> /// <returns></returns> [Route("SavePlatformSetting")] [HttpPost] public object SavePlatformSetting([FromForm] string PlatformName, [FromForm] string Manufactor, [FromForm] string ip, [FromForm] string port, [FromForm] string uname, [FromForm] string pwd, [FromForm] string sdk, [FromForm] string keyID, [FromForm] string key) { try { string queryStr = "select PSID from DS_PlatSet where PlatformName=@pname"; var queryLine = DalContextEx.GetSqlTextList<DS_PlatSet>(queryStr, new { pname = PlatformName }, ConnectionKind.Reader); if (queryLine.Count() > 0) { //1001--平台名称已存在 return new { State = false, Msg = "1001" }; } string sqlStr = ""; string gbid = Guid.NewGuid().ToString();
//此处去访问sql或者mysql方法 var rowLine = _SqlService.SavePlatformSetting(PlatformName, Manufactor, ip, port, uname, pwd, sdk, keyID, key, gbid); if (rowLine > 0) { return new { State = true }; } else { return new { State = false, Msg = "录入数据失败" }; } } catch (Exception ex) { return new { State = false, Msg = ex.Message }; } } /// <summary> /// 添加NVR /// </summary> /// <param name="NvrName">NVR名称</param> /// <param name="OrgNo">组织机构代码</param> /// <param name="Place">场所</param> /// <param name="IPDZ">IP地址</param> /// <param name="DevPort">设备端口号</param> /// <param name="DevUser">设备用户名</param> /// <param name="DevPwd">设备密码</param> /// <param name="NvrUrl">设备rtspURL</param> /// <param name="IPC">NVR中设备IPC路数</param> /// <returns></returns> [Route("AddNvrDev")] [HttpPost] public object AddNvrDev([FromForm] string NvrName, [FromForm] string OrgNo, [FromForm] string Place, [FromForm] string IPDZ, [FromForm] string DevPort, [FromForm] string DevUser, [FromForm] string DevPwd, [FromForm] string NvrUrl, [FromForm] int IPC) { try { bool isMySql = isMySQL(); string sqlStr = ""; string gbid = Guid.NewGuid().ToString();
//此处访问sql或mysql方法 var rowLine = _SqlService.AddNvrDev(NvrName, OrgNo, Place, IPDZ, DevPort, DevUser, DevPwd, NvrUrl, IPC, gbid); if (rowLine > 0) { StringBuilder sb = new StringBuilder(); if (IPC == 0) { return new { State = true }; } var orgName = ""; var orgList = DalContext.GetSqlTextList<Organization>("select * from t_Organization where orgno=@OrgNo", new { OrgNo = OrgNo }, ConnectionKind.Reader); if (orgList.Count > 0) { orgName = orgList[0].OrgName; } if (isMySql) { } else {
//根据设定的路数，自动生成相应设备数量，通道号不同，设备名称不同。 for (int i = 0; i < IPC; i++) { string thisUrl = NvrUrl; if (NvrUrl.IndexOf("{0}") >= 0) { thisUrl = string.Format(NvrUrl, (i + 1).ToString()); } var gadmSub = OrgNo.Substring(0, 8); var NowTimeStr = System.DateTime.Now.ToString("yyyyMMddHHmmss"); var LastOne = NowTimeStr.Substring(2);//拿到年月日时分秒 var NumberLast = LastOne.Substring(8);// var SumNumber = int.Parse(NumberLast) + i; var SumNumberStr = SumNumber.ToString(); while (SumNumberStr.Length < 4) { SumNumberStr = "0" + SumNumberStr; }
//生成国标编码，组织机构加上时间加上序号 var inserNumStr = gadmSub+LastOne.Substring(0,8)+SumNumberStr; sb.AppendFormat(_SqlService.AddNvrDevRonLin1((NvrName + (i + 1)).ToString(), OrgNo, Place, IPDZ, DevPort, DevUser, DevPwd, orgName, gbid, thisUrl, inserNumStr)); } } var rowlin1 = DalContextEx.RunSqlText(sb.ToString(), null, ConnectionKind.Writer); return new { State = true }; } else { return new { State = false, Msg = "录入数据失败" }; } } catch (Exception ex) { return new { State = false, Msg = ex.Message }; } } } }
接下来就需要决定如何通过配置让它访问sql的方法还是mysql的方法，这个只需要在Startup文件里面判断下就可以了
namespace Web.DeviceMg { public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; } public IConfiguration Configuration { get; } // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddMvc(options => options.EnableEndpointRouting = false); services.Configure<CookiePolicyOptions>(options => { options.MinimumSameSitePolicy = SameSiteMode.None; }); //判断配置文件是使用sql还是mysql if (Dal.CmdContext.isMySQL()) { services.AddTransient<SqlInterface, DeviceSynSql>(); } else { services.AddTransient<SqlInterface,DeviceSynMysql>(); }; }
下面的 public void Configure就没有继续复制出来了。 } }
并没有说推荐使用这种方法，纯属个人兴趣做个试验，无论哪种判断写法各有优劣。