CNative.DbUtils 2.0.6

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" } ] }

//测试用例 //测试用例 [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();
    }