Asp.Net 高性能框架 SqlSugar.ORM 2.3

前言

sqlsugar从去年到现在已经一年了，版本从1.0升到了现在的2.3 ，这是一个稳定版本 ，有数家公司已经项目上线，在这里我将sqlsugar的功能重新整理成一篇新的贴子，希望大家喜欢。

公司团队项目、产品已经完全抛弃ef，sqlsugar定位不是orm,而是为了方面的让你去写sql。

支持json 、dynamic、 list、 list、 valuetype和 dictionary 等多种类型的返回值也是一大亮点。[]>

执行十万次【将拉姆达】转成【sql】只用了1秒多（机型：surface pro4 i5 8g）

更多测试（机型：过时机器）

二、功能介绍

查询

1、sqlquery 用于执行原生sql和存储过程

           //转成list
                list list1 = db.sqlquery("select * from student");
                //转成list带参
                list list2 = db.sqlquery("select * from student where id=@id", new { id = 1 });
                //转成dynamic
                dynamic list3 = db.sqlquerydynamic("select * from student");
                //转成json
                string list4 = db.sqlqueryjson("select * from student");
                //返回int
                var list5 = db.sqlquery("select top 1 id from student").single();
                //反回键值
                dictionary list6 = db.sqlquery>("select id,name from student").todictionary(it => it.key, it => it.value);
                //反回list
                var list7 = db.sqlquery("select top 1 id,name from student").single();
                //存储过程
                var spresult = db.sqlquery("exec sp_school @p1,@p2", new { p1=1,p2=2 });

sql查询

2、queryable 用于单表或者视图查询

  //---------queryable,扩展函数查询---------//

                //针对单表或者视图查询

                //查询所有
                var student = db.queryable().tolist();
                var studentdynamic = db.queryable().todynamic();
                var studentjson = db.queryable().tojson();

                //查询单条
                var single = db.queryable().single(c => c.id == 1);
                //查询单条没有记录返回空对象
                var single2 = db.queryable().where(c => c.id == 1).singleordefault();

                //查询第一条
                var first = db.queryable().where(c => c.id == 1).first();
                var first2 = db.queryable().where(c => c.id == 1).firstordefault();

                //取10-20条
                var page1 = db.queryable().where(c => c.id > 10).orderby("id").skip(10).take(20).tolist();

                //上一句的简化写法，同样取10-20条
                var page2 = db.queryable().where(c => c.id > 10).orderby("id").topagelist(2, 10);

                //查询条数
                var count = db.queryable().where(c => c.id > 10).count();

                //从第2条开始以后取所有
                var skip = db.queryable().where(c => c.id > 10).orderby("id").skip(2).tolist();

                //取前2条
                var take = db.queryable().where(c => c.id > 10).orderby("id").take(2).tolist();

                // not like 
                string conval = "a";
                var notlike = db.queryable().where(c => !c.name.contains(conval.tostring())).tolist();

                // 可以在拉姆达使用 tostring和 convert,比ef出色的地方
                var convert1 = db.queryable().where(c => c.name == "a".tostring()).tolist();
                var convert2 = db.queryable().where(c => c.id == convert.toint32("1")).tolist();// 
                var convert3 = db.queryable().where(c => datetime.now > convert.todatetime("2015-1-1")).tolist();
                var convert4 = db.queryable().where(c => datetime.now > datetime.now).tolist();

                //支持字符串where 让你解决，更复杂的查询
                var student12 = db.queryable().where(c => "a" == "a").where("id>100").tolist();
                var student13 = db.queryable().where(c => "a" == "a").where("id>100 and id in( select 1)").tolist();


                //存在记录反回true，则否返回false
                bool isany100 = db.queryable().any(c => c.id == 100);
                bool isany1 = db.queryable().any(c => c.id == 1);

                int maxid = db.queryable().max("id");
                int minid = db.queryable().where(c => c.id > 0).min("id");



                //in
                var list1= db.queryable().in("id", "1", "2", "3").tolist();
                var list2=db.queryable().in("id", new string[] { "1", "2", "3" }).tolist();
                var list3=db.queryable().in("id", new list { "1", "2", "3" }).tolist();
                var list4 = db.queryable().where(it=>it.id<10).in("id", new list { "1", "2", "3" }).tolist();

                //分组查询
                var list5= db.queryable().where(c => c.id < 20).groupby("sex").select("sex,count=count(*)").tolist();
                //select sex,count=count(*)  from student  where 1=1  and  (id < 20)    group by sex --生成结果

单表查询

3、sqlable 用于多表查询

//---------sqlable,创建多表查询---------//

                //多表查询
                list datalist = db.sqlable()
                   .form("school", "s")
                   .join("student", "st", "st.id", "s.id", jointype.inner)
                   .join("student", "st2", "st2.id", "st.id", jointype.left)
                   .where("s.id>100 and s.id<@id")
                   .where("1=1")//可以多个where
                   .selecttolist("st.*", new { id = 1 });

                //多表分页
                list datapagelist = db.sqlable()
                    .form("school", "s")
                    .join("student", "st", "st.id", "s.id", jointype.inner)
                    .join("student", "st2", "st2.id", "st.id", jointype.left)
                    .where("s.id>100 and s.id<100")
                    .selecttopagelist("st.*", "s.id", 1, 10);

                //多表分页where加子查询
                list datapagelist2 = db.sqlable()
                    .form("school", "s")
                    .join("student", "st", "st.id", "s.id", jointype.inner)
                    .join("student", "st2", "st2.id", "st.id", jointype.left)
                    .where("s.id>100 and s.id<100 and s.id in (select 1 )" /*这里面写子查询都可以*/)
                    .selecttopagelist("st.*", "s.id", 1, 10);



                //--------转成list dynamic 或者 json-----//

                //不分页
                var list1 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttodynamic("*", new { id = 1 });
                var list2 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttojson("*", new { id = 1 });
                var list3 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttodatatable("*", new { id = 1 });

                //分页
                var list4 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagedynamic("s.*", "l.id", 1, 10, new { id = 1 });
                var list5 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagetable("s.*", "l.id", 1, 10, new { id = 1 });
                var list6 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagedynamic("s.*", "l.id", 1, 10, new { id = 1 });


                //--------拼接-----//
                sqlable sable = db.sqlable().form("s").join("l", "s.sch_id", "l.id", jointype.inner);
                string name = "a";
                int id = 1;
                if (!string.isnullorempty(name))
                {
                    sable = sable.where("s.name=@name");
                }
                if (!string.isnullorempty(name))
                {
                    sable = sable.where("s.id=@id or s.id=100");
                }
                if (id > 0)
                {
                    sable = sable.where("l.id in (select top 10 id from school)");//where加子查询
                }
                var pars = new { id = id, name = name };
                int pagecount = sable.count(pars);
                var list7 = sable.selecttopagelist("s.*","l.id desc",1,20, pars);

多表查询

4、新容器转换

 using (sqlsugarclient db = sugardao.getinstance())
            {
                var list2= db.queryable().where(c => c.id < 10).select(c => new classnew { newid = c.id, newname = c.name, xx_name = c.name }).tolist();//不支持匿名类转换,也不建议使用

                var list3 = db.queryable().where(c => c.id < 10).select(c => new   { newid = c.id, newname = c.name, xx_name = c.name }).todynamic();//匿名类转换
            }

新容器转换

添加

 using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
            {
                student s = new student()
                {
                    name = "张" + new random().next(1, int.maxvalue)
                };

                db.insert(s); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)


                list list = new list()
                {
                     new student()
                {
                     name="张"+new random().next(1,int.maxvalue)
                },
                 new student()
                {
                     name="张"+new random().next(1,int.maxvalue)
                }
                };

                db.insertrange(list); //批量插入
            }
        }

添加

更新

 using (var db = sugardao.getinstance())
            {
                //指定列更新
                db.update(new { name = "蓝翔2" }, it => it.id == id);
                db.update(new { name = "蓝翔2" }, 1, 3, 12);
                db.update(new { name = "蓝翔2" }, new string[] { "1", "2" });

                //整个实体更新
                db.update(new school { id = id, name = "蓝翔2" });
                db.update(new school { id = id, name = "蓝翔2" }, it => it.id == id);


                //设置不更新列
                db.disableupdatecolumns = new string[] { "createtime" };//设置createtime不更新

                testupdatecolumns updobj = new testupdatecolumns()
                {
                    vguid = guid.parse("542b5a27-6984-47c7-a8ee-359e483c8470"),
                    name = "xx",
                    name2 = "xx2",
                    identityfield = 0,
                    createtime = null
                };

                //createtime将不会被更新
                db.update(updobj);
                //以前实现这种更新需要用指定列的方式实现，现在就简单多了。
               
            }

更新

删除和假删除

using (var db = sugardao.getinstance())
            {
                //真删除
                db.delete(10);//注意主键必需为实体类的第一个属性
                db.delete(it => it.id > 100);
                db.delete(new string[] { "100", "101", "102" });

                //假删除
                //db.falsedelete("is_del", 100);
                //等同于 update school set is_del=0 where id in(100)
                //db.falsedelete("is_del", it=>it.id==100);
            }

删除

全局过滤器

这功能主要用于通用权限设计等功能， 当有很多where重复，并且动态 这时候就需要在全局设置好过滤器接口集合，程序中使用只需要设key便可

    /// 
    /// 过滤器
    /// 
    public partial class filter : system.web.ui.page
    {
        protected void page_load(object sender, eventargs e)
        {
            using (sqlsugarclient db = sugardaofilter.getinstance())//开启数据库连接
            {
                //设置走哪个过滤器
                db.currentfilterkey = "role";

                //queryable
                var list = db.queryable().tolist(); //通过全局过滤器对需要权限验证的数据进行过滤
                //相当于db.queryable().where("id=@id",new{id=1})


                //sqlable
                var list2 = db.sqlable().form("s").selecttolist("*");
                //同上

                //sqlquery
                var list3 = db.sqlquery("select * from student");
                //同上
            }
        }
    }
    /// 
    /// 扩展sqlsugarclient
    /// 
    public class sugardaofilter
    {
        //禁止实例化
        private sugardaofilter()
        {

        }
        /// 
        /// 页面所需要的过滤函数
        /// 
        private static dictionary> _filterparas = new dictionary>()
        {
          { "role",()=>{
                    return new keyvalueobj(){ key=" id=@id" , value=new{ id=1}};
               }
          },
          { "org",()=>{ 
                    return new keyvalueobj(){ key=" orgid=@orgid" , value=new{ orgid=1}};
              }
          },
        };
        public static sqlsugarclient getinstance()
        {
            string connection = system.configuration.configurationmanager.connectionstrings[@"sqlconn"].tostring(); //这里可以动态根据cookies或session实现多库切换
            var reval = new sqlsugarclient(connection);
            reval.setfilterfilterparas(_filterparas);
            return reval;
        }
    }

过滤器

无参模式

将isgetpageparas设为true之后页面的参数都不需要使用 new{}来赋值

         using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
                {

                    db.isgetpageparas = true;//使用无参模式直接将requst中的id传给@id无需在代码中写出来

                    var list = db.queryable().where("id=@id").tolist();

                    //sqlable queryable sqlquery通用

                    //获取页面所有参数到键值对
                    //var kvs= sqlsugartool.getparameterdictionary();

                    //以前写法
                    //var xx = db.queryable().where("id=@id", new { id=request["id"] }).tolist();
                }

无参

事务

 using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
            {
                db.isnolock = true;//启用无锁查询
                db.commandtimeout = 30000;//设置超时时间
                try
                {
                    db.begintran();//开启事务

                    db.committran();//提交事务
                }
                catch (exception)
                {
                    db.rollbacktran();//回滚事务
                    throw;
                }
            }

事务

公开函数

 //tojoinsqlinval
            var par = new string[] { "a", "c", "3" };
            var ids = par.tojoinsqlinval();//将数组转成 'a','c','3'  有防sql注入处理


            //tosqlvalue
            try
            {
                var par2 = "a'";
                var newpar2 = par2.tosqlvalue();//对字符串防注入处理
            }
            catch (exception ex)
            {

                response.write(ex.message);
            }

            //sqllikewordencode 处理like特殊字符
            var likestr = sqlsugartool.sqllikewordencode("[%%%");


            //getparameterarray 获取页面参数所有参数的键和值
            var pars = sqlsugartool.getparameterarray();


            //将匿名对象转成sqlparameter
            var par3= sqlsugartool.getparameters(new { id=1});

函数

实体生成

     //根据当前数据库生成所有表的实体类文件 （参数：sqlsugarclient ，文件目录，命名空间）
                //db.classgenerating.createclassfiles(db,server.mappath("~/models"),"models");
                //根据表名生成实体类文件
                //db.classgenerating.createclassfilesbytablenames(db, server.mappath("~/models"), "models" , "student","school");

                //根据表名生成class字符串
                var str = db.classgenerating.tablenametoclass(db, "student");

                //根据sql语句生成class字符串
                var str2 = db.classgenerating.sqltoclass(db, "select top 1 * from student", "student");

实体生成

t4模版

请查看demos文件下的t4

别名表

                //-----单个-------//
                var list = db.queryable("student").tolist();//查询的是 select * from student 而我的实体名称为v_student




                //-----全局多个设置-------//

                //设置 mapping table 如果没这方面需求可以传null
                list mappingtablelist = new list(){
                new keyvalue(){ key="formattr", value="flow_formattr"},
                new keyvalue(){ key="student3", value="student"}
                };
                db.setmappingtables(mappingtablelist);

别名表

多库并行计算功能

 using (cloudclient db = clouddao.getinstance())
            {
                db.pagemaxhandlenumber = 1500;
                using (committabletransaction trans = new committabletransaction())//启用分布式事务
                {


                    db.tran = trans;

                    /*** 增、删、改 ***/

                    //根据配置的百分比，随机插入到某个节点库
                    var s = new student()
                    {
                        createtime = datetime.now,
                        id = guid.newguid(),
                        name = guid.newguid() + ""
                    };
                    //db.insert(s, false/*false表示不是自增列*/); //数据库设计主键为guid为佳多库计算不会冲突



                    ////并发请求所有节点找出这条数据更新
                    //s.name = "改11";
                    //db.update(s, it => it.id == s.id);//根据表达示更新


                    //db.update(s, s.id);//根据主键更新

                    //db.update(s, new guid[] { s.id });//根据主键数组更新



                    ////并发请求所有节点找出这条数据删除
                    //db.delete(it => it.id == s.id);


                    trans.commit();
                }

                db.trandispose();


                /*** 使用taskable实现云计算 ***/


                //多线程请求所有数据库节点，获取汇总结果
                var taskdatatable = db.taskable("select max(num) from student").tasks;
                foreach (var dr in taskdatatable)//遍历所有节点数据
                {
                    var dt = dr.result.datatable;//每个节点的数据 类型为datatable
                    var connectionname = dr.result.connectionstring;
                }

                //多线程请求所有数据库节点，获取汇总结果
                var taskint = db.taskable("select max(num) from student").tasks;
                foreach (var dr in taskint)//遍历所有节点数据
                {
                    var dt = dr.result.value;//获取单个值
                    var connectionname = dr.result.connectionstring;
                }

                //多线程请求所有数据库节点，获取汇总结果
                var taskentity = db.taskable("select top 100 * from student").tasks;
                foreach (var dr in taskentity)//遍历所有节点数据
                {
                    var dt = dr.result.entities;//获取实体集合
                    var connectionname = dr.result.connectionstring;
                }

                // //taskable实现分组查询
                // var grouplist = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null  group by name ")
                //     .tasks
                //     .selectmany(it => it.result.datatable.asenumerable())
                //     .select(dr => new { num = convert.toint32(dr["num"]), name = dr["name"].tostring(), count = convert.toint32(dr["count"]) })
                //     .groupby(dr => dr.name).select(dt => new { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();


                // //输出结果
                // foreach (var it in grouplist)
                // {
                //     var num = it.num;
                //     var name = it.name;
                //     var count = it.count;
                // }


                // //简化
                // var grouplist2 = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null  group by name ")
                //.mergetable()//将结果集合并到一个集合
                //.select(dr => new { num = convert.toint32(dr["num"]), name = dr["name"].tostring(), count = convert.toint32(dr["count"]) })
                //.groupby(dr => dr.name).select(dt => new { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();

                // //再简化
                // list grouplist3 = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null  group by name ")
                //.mergeentities()//将结果集合并到一个集合
                //.groupby(dr => dr.name).select(dt => new v_student { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();



                //更多简化函数
                int maxvalue = db.taskable("select max(num) from student").max();//求出所有节点的最大值
                int minvalue = db.taskable("select min(num) from student").min();//求出所有节点的最小值
                var datacount = db.taskable("select count(1) from student").count();//求出所有节点数据
                // var avg = db.taskablewithcount("select avg(num)", " from student").avg();
                var all = db.taskable("select * from student where contains(name,@name)"/*使用contains需要建全文索引*/, new { name = "张三" }).tolist();//获取所有节点name为张三的数据,转为list

                var dnsingle = datetime.now;
                var data = db.taskable("select * from student where id=@id", new { id = id }).tosingle();//从所有节点中查询一条记录
                var timesingle = (datetime.now - dnsingle).totalseconds;

                var dn = datetime.now;
                //单列排序
                //  var list = db.taskablewithpage("id", "select * from student    "/*使用contains需要建全文索引*/, 2160, 25, ref pagecount, "num", orderbytype.desc, new { name = "\"*李*\"" });
                var time = (datetime.now - dn).totalseconds;

                //多组排序 order by num,time
                var dn2 = datetime.now;
                var listmultipleorderby = db.taskablewithpage("id", "select * from student   "/*使用contains需要建全文索引*/, 5000, 25, ref pagecount, new list() { 
                     new   orderbydictionary(){ orderbyfield="num", orderbytype=orderbytype.asc},
                     //       new   orderbydictionary(){ orderbyfield="createtime", orderbytype=orderbytype.desc},
                   //  new orderbydictionary(){ orderbyfield="createtime", orderbytype=orderbytype.asc}
                }, new { name = "\"*小*\"" });
                var time2 = (datetime.now - dn2).totalseconds;


            }

多库模式 

 /// 
        /// 测试类
        /// 
        public class student
        {
            public guid id { get; set; }
            public string name { get; set; }
            public datetime createtime { get; set; }
            public int num { get; set; }
        }
        /// 
        /// 获取数据库连接对象
        /// 
        public class clouddao
        {
            private clouddao() { }
            public static cloudclient getinstance()
            {
                return new cloudclient(new list() { 
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c1"].tostring(), rate=1},
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c2"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c3"].tostring(), rate=1},
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c4"].tostring(), rate=1},
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c5"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c6"].tostring(), rate=1},
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c7"].tostring(), rate=1},
                 new cloudconnectionconfig(){  connectionstring=configurationmanager.connectionstrings["c8"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c9"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c10"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c15"].tostring(), rate=1},
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c16"].tostring(), rate=1},              
                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c17"].tostring(), rate=1},
                //new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy1"].tostring(), rate=1},
                //   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy2"].tostring(), rate=1},
                //    new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy3"].tostring(), rate=1},
                //                      new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq1"].tostring(), rate=1},
                //   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq2"].tostring(), rate=1},
                //    new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq3"].tostring(), rate=1},
                //            new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp1"].tostring(), rate=1},
                //   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp2"].tostring(), rate=1},
                //    new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp3"].tostring(), rate=1},
                //                 new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy1"].tostring(), rate=1},
                //   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy2"].tostring(), rate=1},
                //    new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy3"].tostring(), rate=1},
                //                                   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf1"].tostring(), rate=1},
                //   new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf2"].tostring(), rate=1},
                //    new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf3"].tostring(), rate=1},


            });
            }
        }

多库配置