欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlSugar ORM 入门篇2 【查询】 让我们实现零SQL

程序员文章站 2022-04-30 20:01:18
SqlSugar在查询的功能是非常强大的,多表查询、分页查询 、 一对一查询、二级缓存、一对多查、WhenCase等复杂函数、Mapper功能、和拉姆达自定义扩展等,用好了是可以做到真正零SQL的一款ORM。 首先将SqlSugar更新到4.8版本,下面我就来一一讲解每种查询的写法 创建DbCont ......

sqlsugar在查询的功能是非常强大的,多表查询、分页查询 、 一对一查询、二级缓存、一对多查、whencase等复杂函数、mapper功能、和拉姆达自定义扩展等,用好了是可以做到真正零sql的一款orm。

 

首先将sqlsugar更新到4.8版本,下面我就来一一讲解每种查询的写法

 

创建dbcontext

public class dbcontext
{
        public dbcontext()
        {
            db = new sqlsugarclient(new connectionconfig()
            {
                connectionstring = "server=.;uid=sa;pwd=sasa;database=sqlsugar4xtest",
                dbtype = dbtype.sqlserver,
                isautocloseconnection = true,//开启自动释放模式和ef原理一样我就不多解释了
                //initkey默认systemtable
            });
        }
        public sqlsugarclient db;//用来处理事务多表查询和复杂的操作
        public simpleclient<student> studentdb { get { return new simpleclient<student>(db); } }//用来处理student表的常用操作
        public simpleclient<school> schooldb { get { return new simpleclient<school>(db); } }//用来处理school表的常用操作
}

 

单表的简单查询

我们使用的simpleclient实现了简单的单表查询,如何扩展simpleclient可以看我的上一篇文章

            //调式代码 用来打印sql 
            db.aop.onlogexecuting = (sql, pars) =>
            {
                console.writeline(sql + "\r\n" + db.utilities.serializeobject(pars.todictionary(it => it.parametername, it => it.value)));
                console.writeline();
            };


            var data1 = studentdb.getbyid(1);//根据id查询
            var data2 = studentdb.getlist();//查询所有
            var data3 = studentdb.getlist(it => it.id == 1);  //根据条件查询      

            var p = new pagemodel() { pageindex=1,pagesize=2};// 分页查询
            var data4 = studentdb.getpagelist(it => it.name == "xx", p);
            console.write(p.pagecount);//返回总数


            // 分页查询加排序
            var data5 = studentdb.getpagelist(it => it.name == "xx", p,it=>it.name,orderbytype.asc);
            console.write(p.pagecount);//返回总数

 对于grid控件来说我一般用这个表单封装好了全部通用

  list<iconditionalmodel> conmodels = new list<iconditionalmodel>();
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.equal, fieldvalue = "1" });//id=1
            conmodels.add(new conditionalmodel() { fieldname = "student.id", conditionaltype = conditionaltype.equal, fieldvalue = "1" });//id=1
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.like, fieldvalue = "1" });// id like '%1%'
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.isnullorempty });
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.in, fieldvalue = "1,2,3" });
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.notin, fieldvalue = "1,2,3" });
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.noequal, fieldvalue = "1,2,3" });
            conmodels.add(new conditionalmodel() { fieldname = "id", conditionaltype = conditionaltype.isnot, fieldvalue = null });// id is not null
            var data6 = studentdb.getpagelist(conmodels,p,it=>it.name,orderbytype.asc); //组装条件当查询条件的 分页查询加排序

 

简单查询中拉姆达的使用技巧

基本上和ef差不太多

  var data3 = studentdb.getlist(it => it.name.contains("a"));  // like %a%  模糊查询

var p2 = new int[] { 1, 2, 3 };
var data31 = studentdb.getlist(it => p2.contains(it.id));  // id in (1,2,3)

我们还支持了sqlfunc.xxx一串方法来给我们使用,如下用法

var data311 = studentdb.getlist(it => sqlfunc.between(it.id,1,2));  // id between 1 and 2

 

动态拼表达式查询

            var exp = expressionable.create<student>()
                          .orif(1 == 1, it => it.id == 11)
                          .and(it => it.id == 1)
                          .andif(2 == 2, it => it.id == 1)
                          .or(it => it.name == "a1").toexpression();//拼接表达式

            var data311 = studentdb.getlist(exp);  // 动态表达式查询

 

 

扩展拉姆达方法

例如我们有自定义的sql函数或者sqlsugar不支持的我们可以自定扩展

具体看这个连接 http://www.codeisbug.com/doc/8/1162

 

复杂查询

我们上面看到的简单查询底层都是用复杂查询实现的

var data1 = studentdb.getbyid(1);
//等同于
var data2 = db.queryable<student>().single(it => it.id == 1);

 

多表查询

var list = db.queryable<student, school>((st, sc) => new object[] {
        jointype.left,st.schoolid==sc.id})
      .select((st,sc)=>new{id=st.id,name=st.name,schoolname=sc.name}).tolist();

生成的sql如下

select  [st].[id] as [id] , 
          [st].[name] as [name] , 
          [sc].[name] as [schoolname]  from [student] st 
          left join school sc on ( [st].[schoolid] =[sc].[id])

 

多表查询自支持自动填充到viewmodel

var s11 = db.queryable<student, school>((st, sc) => st.schoolid == sc.id)
                        .select<viewmodelstudent3>().tolist();
 public class viewmodelstudent3: student
{
         public string schoolname { get; set; }
 }

 

生成的sql如下

select
           sc.[name] as [schoolname],--这一列神奇的自动出现了
           sc.[id] as [scid],
           st.[id] as [id],
           st.[schoolid] as [schoolid],
           st.[name] as [name],
           st.[createtime] as [createtime]
           
           
            from [student] st  ,[school]  sc  where ( [st].[schoolid] = [sc].[id])

 

多表分页查询

 var list3 = db.queryable<student, school>((st, sc) => new object[] {
              jointype.left,st.schoolid==sc.id
            }).select<viewmodel>()
            .topagelist(pageindex,pagesize)

 

子查询

var getall = db.queryable<student, school>((st, sc) => new object[] {
jointype.left,st.id==sc.id})
.where(st => st.id == sqlfunc.subqueryable<school>().where(s => s.id == st.id).select(s => s.id))
.tolist();
      
//生成的mysql语句,如果是sqlserver就是top 1
select `st`.`id`,`st`.`schoolid`,`st`.`name`,`st`.`createtime` 
     from `student` st left join `school` sc on ( `st`.`id` = `sc`.`id` )  
      where ( `st`.`id` =(select `id` from `school` where ( `id` = `st`.`id` ) limit 0,1))

 

一对一的查询

var getall = db.queryable<student, school>((st, sc) => new object[] {
jointype.left,st.id==sc.id})
.select(st =>
       new{
              name = st.name,
              id = sqlfunc.subqueryable<school>().where(s => s.id == st.id).select(s => s.id)
       }).tolist();

 

 本文只讲重点,更多多表查询请看 api

 http://www.codeisbug.com/doc/8/1124

 

mapper功能

如果说 .select() 也可以实现一对一的查询或者一些sql函数但是毕竟是用来生成sql的所以有很多局限性,mapper是在查询出结果后进行处理所以任何c#方法都支持

也更强大

 var s12 = db.queryable<student, school>((st, sc) => st.schoolid == sc.id).select<viewmodelstudent3>()

                .mapper(it =>
                {

                    it.name = md5(it.name);
                    //有多少列要处理写多少列,能用mapper的就少用select兼容性更好些

                }).tolist();

 

高性能的一对多查询

我们也可以用mapper来实现一对多,弥补.select()不足

var s12 = db.queryable<student, school>((st, sc) => st.schoolid == sc.id).select<viewmodelstudent3>()

.mapper((it, cache) =>
{

    var allschools = cache.getlistbyprimarykeys<school>(vmodel => vmodel.schoolid);
    //in(viewmodelstudent3[0].schoolid , viewmodelstudent3[1].schoolid...)


    /*one to many*/
    it.schools = allschools.where(i => i.id == it.schoolid).tolist();


    /*c# syntax conversion*/
    it.name = it.name == null ? "null" : it.name;

}).tolist();

一对多查询的性能可以秒杀其它orm ,因为生成的sql只有2条,并且这2条不会多查询一条没用的记录,有幸趣的可以研究一下,其它的都内存处理

 

多queryable查询

union all查询将结果集合并

var getunionalllist2 = db.unionall(db.queryable<student>(), db.queryable<student>()).tolist();//union all


 

两个queryable联表查询(有人说我只支持12表join,那这样就可以支持24张表了)

var q1 = db.queryable<student, school>((st,sc)=>new object[] {
                jointype.left,st.schoolid==sc.id
            }).select((st, sc) => new viewmodelstudent4() { id=st.id, name=st.name,schoolname=sc.name });
 
var q2 = db.queryable<school>();
 
 
var innerjoinlist = db.queryable(q1, q2, (j1, j2) => j1.id == j2.id).select((j1, j2) => j1).tolist();//inner join
 
var leftjoinlist = db.queryable(q1, q2,jointype.left, (j1, j2) => j1.id == j2.id).select((j1, j2) => j1).tolist();/

 

二级缓存支持

二级缓存功能是对查询出来的数据进行缓存,在缓存不失效的情况下,下次同样的查询操作都会从缓存内读取

 

使用缓存查询

var list=db.queryable<student, school>((s1, s2) => s1.id == s2.id).select(s1 => s1).withcache().tolist();//可以设置过期时间withcache(60)

 

删除缓存

我们需要删除缓存也相当方便,只需要在对该表操作的时候加 removedatacache 就能把查询中引用该表的缓存全部清除

db.deleteable<student>().where(it => it.id == 1).removedatacache().executecommand();

//updateable和insertable一样用法

 

自动删除缓存

sqlsugarclient db = new sqlsugarclient(new connectionconfig() {
                connectionstring = config.connectionstring,
                moresettings =new  connmoresettings(){
                      isautoremovedatacache=true
                }

 

创建db对象

我们需要创建一个mycache类,你可以用我写好的也可以用你自已写的实现缓存

 

icacheservice mycache = new rediscache("10.1.249.196");//icacheservice
sqlsugarclient db = new sqlsugarclient(new connectionconfig()
{
connectionstring = config.connectionstring,
dbtype = dbtype.sqlserver,
isautocloseconnection = true,
configureexternalservices = new configureexternalservices()
{
datainfocacheservice = new rediscache() //rediscache是继承icacheservice自已实现的一个类
}
});

 

我写好的cache类可以作为参考

redis:

https://github.com/sunkaixuan/sqlsugar/blob/dev/src/asp.net/sqlsugar.extensions.datacache/rediscache.cs

.net自带cache:

https://github.com/sunkaixuan/sqlsugar/blob/dev/src/asp.net/sqlsugar.extensions.datacache/httpruntimecache.cs

 

永久开源,源码下:

https://github.com/sunkaixuan/sqlsugar

sqlsugar一直在默默进步,得到了一些大型企业的认可,但这只是开始,希望你喜欢。

 

上一篇