CNative.DbUtils 2.1.12

使用说明 appsettings.json

{ "AppSettings": { "Title": "Test", "Version": "1.2.1", "AccessToken": "xxxxxx@abc.com" }, "connectionStrings": [ { "name": "BaseDb", "connectionString": "Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=xxxxxx", "providerName": "System.Data.SqlClient" }, { "name": "Oracle", "connectionString": "Data Source=xxxxxx;Persist Security Info=True;User ID=hispro;Password=xxxxxx", "providerName": "System.Data.OracleClient" }, { "name": "MySql", "connectionString": "server=xxxxxx;uid=root;pwd=xxxxxx;database=test", "providerName": "System.Data.MySql" }, { "name": "Sqlite", "connectionString": "DataSource=xxxxxx.db;Version=3;Pooling=False;Max Pool Size=100;", "providerName": "System.Data.Sqlite" }, { "name": "PostgreSql", "connectionString": "User id=postgres;Password=xxxxxx;Host=xxxxxx;Port=55433;Database=test;Pooling=true;", "providerName": "System.Data.Npgsql" }{ "name": "MsAccess", "connectionString": "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Documents\DevExpress 2010.1 Demos\Components\ASPxEditors\CS\ASPxEditorsDemos\App_Data\nwind.mdb;Persist Security Info=False ", "providerName": "System.Data.Access" }, { "name": "MsAccess2", "IsUseMasterSlaveSeparation": true,//是否启用主从分离模式 "SlaveConnectStrings": [ "MsAccess", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Documents\DevExpress 2010.1 Demos\Components\ASPxEditors\CS\ASPxEditorsDemos\App_Data\nwind.mdb;Persist Security Info=False " ],//从数据库连接字符串集合, "connectionString": "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Documents\DevExpress 2010.1 Demos\Components\ASPxEditors\CS\ASPxEditorsDemos\App_Data\nwind.mdb;Persist Security Info=False", "providerName": "CNative.DbUtils.MsAccessProvider" } ] } //测试用例

[TestClass] public class UnitTest461DbUtils { private ISqlBuilder DRY2 = null; private ISqlBuilder sqlBuilder = null;

//在运行每个测试之前，使用 TestInitialize 来运行代码 [TestInitialize()] public void MyTestInitialize() { DRY2 = new SqlBuilder("BaseDb"); sqlBuilder = new SqlBuilder("BaseDb");

} [TestMethod]

//单表操作测试 [TestMethod] public void TestSelectSqlBuilder1() { var lst = sqlBuilder.doSelect<Entity_persons>()//查询集合+排序+TOP .Fields(s ⇒ new { s.id, s.name, s.adress })//添加查询多个字段 .Top(8) // .Where(w ⇒ w.id.Between(10, 20)) .OrderByDescending(d ⇒ d.createTime)//按列倒向排序 .Query(); //返回结果 var lst3 = sqlBuilder.doSelect<Entity_persons>()//取单行+排序 .Fields(s ⇒ new { s.id, s.name, s.adress })//添加查询多个字段 .Where(w ⇒ w.id.Between(11, 20)) .OrderBy(d ⇒ d.id) //按列排序 .GetSingleRow(); //返回结果

var lst4 = sqlBuilder.doSelect<Entity_persons>().Count(w ⇒ w.id.Between(11, 20)); //返回结果 }

[TestMethod] public void TestInsertSqlBuilder1() { var jg = new Entity_DMJGXXB() { OrgId = 1002, jgdm = "56783", jgmc = "方舱医院" };

var ret = sqlBuilder.doDelete<Entity_DMJGXXB>()//实体删除 .Delete(jg) .Exec; ret = sqlBuilder.doInsert<Entity_DMJGXXB>()//插入实体 .Insert(jg) .Execute();

ret = sqlBuilder.doDelete<Entity_DMJGXXB>()//表达式删除 .Where(s ⇒ s.OrgId == 1003) .Execute(); ret = sqlBuilder.doInsert<Entity_DMJGXXB>()//表达式插入 .Insert(new Entity_DMJGXXB() { OrgId = 1003, jgdm = "532236783", jgmc = "方舱医院3", CreateId = 0, UpdateId = 0, CreateTime = DateTime.Now, UpdateTime = DateTime.Now }) .Exec;

//表复制 var ret1 = sqlBuilder.doDelete<Entity_DMCZYJGDYB2>() .Where(s ⇒ s.ksid == 1100453) .Execute();

var ret22 = sqlBuilder.doInsert<Entity_DMCZYJGDYB2>()//表复制 .InsertSelect<Entity_DMCZYJGDYB>(s ⇒ new Entity_DMCZYJGDYB2() { OrgId = s.OrgId, czyid = s.czyid, czyjgdyid = s.czyjgdyid, czyxm = s.czyxm, jgmc = "fasdfafdsaf", ksid = s.ksid, ksmc = s.ksmc }, s ⇒ s.ksid == 1100453) .Exec; } [TestMethod] public void TestUpdateSqlBuilder1() { var ret = sqlBuilder.doUpdate<Entity_DMJGXXB>() .Set(s ⇒ new Entity_DMJGXXB() { jgdm = "5322336783", jgmc = "fff232aaa", CreateId = 0, UpdateId = 0, jgjpm = "ffff", UpdateTime = DateTime.Now }) .Where(s ⇒ s.OrgId == jg.OrgId) .Exec;

jg.jgjpm = "345454"; ret = sqlBuilder.DoUpdate .Update(jg, "jgjpm");

var ret1 = sqlBuilder.doDelete<Entity_DMJGXXB>() .Where(s ⇒ s.OrgId == 1005) .Execute();

//更新或插入 var ret23 = sqlBuilder.DoUpdate .UpdateOrInsert(jg, w ⇒ w.OrgId == jg.OrgId);

ret = sqlBuilder.doUpdate<Entity_DMJGXXB>() .UpdateOrInsert(s ⇒ new Entity_DMJGXXB() { OrgId = 1006, jgdm = "5322336783", jgmc = "方舱医院232", CreateId = 0, UpdateId = 0, jgjpm = "wsafdasfd", CreateTime = DateTime.Now, UpdateTime = DateTime.Now }, w ⇒ w.OrgId == 1006); }

//多表操作测试 [TestMethod] public void TestSelectFieldExprSqlBuilder1() { //查寻时加集合函数 var ret1 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>() .Fields(f ⇒ new { czyjgdyid = f.czyjgdyid.SQL_MAX() }) .Where(s ⇒ s.ksid == 1100453) .GetSingle<object>();

var ret2 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>() .Fields(f ⇒ new { f.czyid, f.czyxm, jgmc = f.jgmc.SQL_UCASE(), ksmc = f.jgmc.SQL_SUBSTR(2, 3) }) //.Where(s ⇒ s.ksid == 1100453) .Where(s ⇒ s.ksid == 1100453 && s.jgmc.SQL_UCASE() == "FASDFAFDSAF") .Query();

var ret3 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>() .Fields(f ⇒ new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_MAX() }) .Where(s ⇒ s.OrgId == 1001) .GroupBy(g ⇒ new { g.ksid, g.ksmc }) .OrderBy(g ⇒ g.ksid) .Query();

var ret4 = sqlBuilder.doSelect<Entity_DMCZYJGDYB2>() .Fields(f ⇒ new { f.ksid, f.ksmc, czyjgdyid = f.czyjgdyid.SQL_COUNT(), czyid = f.czyjgdyid.SQL_MAX() }) .Where(s ⇒ s.OrgId == 1001) .GroupBy(g ⇒ new { g.ksid, g.ksmc }) .Having(h ⇒ h.czyjgdyid.SQL_COUNT() > 3 && h.czyjgdyid.SQL_MAX() > 0) .OrderBy(g ⇒ g.ksid) .Query(); }