ASP.NET ORM框架-SqlSugar

SqlSugar

介绍

SqlSugar是一款国产ORM，除了拥有媲美原生的性能 另外还有满足各种需求的功能，简单好用一分钟就能够轻松上手。有详细文档和几年的大量用户积累，还提供完整的服务。

优点

简单易用、功能齐全、高性能、轻量级、服务齐全

支持数据库

MySql、SqlServer、Sqlite、Oracle 、 postgresql

功能

SqlSugar的功能需求都是来自1000多开发人员的真实项目需求，在这些需求上进行完美的设计，经过5年多的积累，可以完美满足企业级的所有需求特别是查询功能，支持了各种类型的返回例如DataTable、List、字典等，还支持各种查询，例如一次查询多个结果集，当然也支持存储过程

项目安装

1、点击项目引用 右键 Nuget管理

2、搜索名称点击安装

nuget只需要引用一个dll文件,开箱就用简单方便

.net 版本选择 sqlSugar

.net core版本选择 sqlSugarCore

简单使用

步骤

1. 创建数据库对象

 //创建数据库对象
 SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
 {
            ConnectionString = "Server=.xxxxx",//连接符字串
            DbType = DbType.SqlServer,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
 });

连接参数

名称	描述	必填
DbType	数据库类型	是
ConnectionString	连接字符串	是
IsAutoCloseConnection	自动释放和关闭数据库连接，如果有事务事务结束时关闭，否则每次操作后关闭
InitKeyType	ORM读取自增列和主键的方式 ，建议从特性读取，如果从数据库读取需要SA等高级权限账号
IsShardSameThread	同线程共享SqlConnection但是不共享SqlSugarClient，非特殊情况不建议使用，特别是异步
ConfigureExternalServices	一些扩展层务的集成
MoreSettings	更多设置
SlaveConnectionConfigs	主从设置

2. 调用方法

插入

• 根据实体插入

  //可以是 类 或者 List<类>
  db.Insertable(insertObj).ExecuteCommand();
   
  //如果需要获取数据库时间我们可以用  var dbTime = db.GetDate();
  

• 根据字典插入

  //可以是 Dictionary 或者 List<Dictionary >
  var dc= new Dictionary<string, object>();
              dt.Add("name", "1");
              dt.Add("CreateTime", null);
  db.Insertable(dc).ExecuteCommand();
  

• 根据Dattable插入

  Dictionary<string,object> dc= db.Utilities.DataTableToDictionary(dataTable);//转成字典就可以按上面的字典更新了
  db.Insertable(dc).AS("student").ExecuteReturnIdentity();
  

更新

• 根据实体对象更新

  根据实体更新需要给实体配置主键

  //根据主键更新单条 参数 Class
  var result= db.Updateable(updateObj).ExecuteCommand();
   
  //如果需要获取数据库时间我们可以用  var dbTime = db.GetDate();
   
  //批量更新参数 List<Class>
  var result= db.Updateable(updateObjs).ExecuteCommand();
  

  不更新 Name 和TestId

  var result=db.Updateable(updateObj).IgnoreColumns(it => new { it.CreateTime,it.TestId }).ExecuteCommand()
  

  只更新 Name 和 CreateTime

  var result=db.Updateable(updateObj).UpdateColumns(it => new { it.Name,it.CreateTime }).ExecuteCommand();
  

• 根据没有配置主键的实体更新

  var result= db.Updateable(updateObj).WhereColumns(it=>new { it.Id}).ExecuteCommand();//更新单 条根据ID
  var result= db.Updateable(updateObjs).WhereColumns(it=>new { it.Id}).ExecuteCommand();//更新集合根据ID by id
  

• 根据表达式更新

  //更新 name,createtime
  var result= db.Updateable<Student>()
  .SetColumns(it => new Student() { Name = "a", CreateTime = DateTime.Now })
  .Where(it => it.Id == 11)
  .ExecuteCommand();
   
  //只更新 name 条件id=1
  var result= db.Updateable<Student>()
  .SetColumns(it => it.Name == "jack")
  .Where(it => it.Id == 1)
  .ExecuteCommand();
   
  //如果需要获取数据库时间我们可以用 SqlFunc.GetDate()
  

• 根据字典更新

  //字典
  var dt = new Dictionary<string, object>();
              dt.Add("id", 1);
              dt.Add("name", null);
              dt.Add("createTime", DateTime.Now);
  var t66 = db.Updateable(dt).AS("student").WhereColumns("id").ExecuteCommand();
   
  //字典集合
  var dtList = new List<Dictionary<string, object>>();
  dtList.Add(dt);
  dtList.Add(dt2);
  var t666 = db.Updateable(dtList).AS("student").WhereColumns("id").ExecuteCommand();
  

• DataTable更新

Dictionary<string,object> dc= db.Utilities.DataTableToDictionary(dt);//转成字典就可以按上面的字典更新了

删除

• 根据实体删除（需要配置主键）

  db.Deleteable<Student>().Where(new Student() { Id = 1 }).ExecuteCommand();
  

• 根据主键

  db.Deleteable<Student>().In(1).ExecuteCommand();
  //也可根据主键数组,批量删除
  db.Deleteable<Student>().In(new int[] { 1, 2 }).ExecuteCommand();
  

• 根据表达式

  db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();
  

• 子查询删除

  db.Deleteable<Student>().Where(p => p.SchoolId == SqlFunc.Subqueryable<School>().Where(s => s.Id == p.SchoolId).Select(s => s.Id)).ExecuteCommand()
  

查询

• 简单查询

  var getAll = db.Queryable<Student>().ToList();//查询所有
  var getFirst = db.Queryable<Student>().First(it=>it.Id=1);//查询单条
  var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();//SqlServer里面的withnolock
  var getByPrimaryKey = db.Queryable<Student>().InSingle(2);//根据主键查询
  var sum = db.Queryable<Student>().Sum(it=>it.Id);//查询总和
  var isAny = db.Queryable<Student>().Where(it=>it.Id==-1).Any();//是否存在
  var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
  var getListByRename = db.Queryable<School>().AS("Student").ToList();
  var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
  var list= db.Queryable<Student>().AS("student2019").ToList();//select * from student2019
  var list2 = db.Queryable<Order>().Where(it =>it.Name.Contains("jack")).ToList();//模糊查询 name like '%'+@name+'%'
  

• 分组查询

  var list = db.Queryable<Student>()
               .GroupBy(it => new { it.Id, it.Name }).Having(it => SqlFunc.AggregateAvg(it.Id) > 0)
               .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name }).ToList();
      SELECT AVG([Id]) AS[idAvg], [Name] AS[name]  FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([Id]) > 0 )
  

• 去重查询

  var list = db.Queryable<Student>().Distinct().Select(it => new { it.Name }).ToList();
  SELECT  DISTINCT  [Name] AS [Name]  FROM [STudent]
  

• 分页

  int pageIndex = 1; 
   int pageSize = 20;
   int totalCount=0;
   var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
    //SELECT * FROM 
              //(SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() 
              //   OVER(ORDER BY GetDate())AS RowIndex FROM [STudent]) T
     //WHERE RowIndex BETWEEN 1 AND 20
  

  异步分页需要注意区别

  RefAsync<int> total = 0;
  Db.Queryable<Order>().ToPageListAsync(1, 2, total);
  

• 并集查询

  var q1 = db.Queryable<Student>().Select(it=>new Model{ name=it.Name });
  var q2 = db.Queryable<School>().Select(it => new Model { name = it.Name });
  var list = db.UnionAll(q1, q2).ToList();
  SELECT * FROM  (SELECT  [Name] AS [name]  FROM [STudent]  UNION ALL
  SELECT  [Name] AS [name]  FROM [School] ) unionTable
  

• 使用sql函数

  var getByFuns = db.Queryable<Student>().Where(it => SqlFunc.Between(it.Id,1,2)).ToList();
  

• 排序

  //表达示排序
  var list = db.Queryable<Student, School>((st, sc) =>new  JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
  .OrderBy(st=>st.Id)
  .OrderBy((st,sc)=>sc.Id,OrderByType.Desc)
  .Select<ViewModelStudent>().ToList();
   
  //表达示连写
  var list = db.Queryable<Student, School>((st, sc) =>new  JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
  .OrderBy(st=>new {st.Id,sc.Id})
  .Select<ViewModelStudent>().ToList();
   
  //动态排序
  var list = db.Queryable<Student, School>((st, sc) =>new  JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
  .OrderBy("st.id asc,sc.Id desc ")  //单表不需要加st.这种前缀
  .Select<ViewModelStudent>().ToList();
   
  //上面多表查询的时候动态排序需要加别名 ，如何去掉别名呢代码如下
  var pageJoin =db.Queryable<Student,School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.Id))
  .Select((st, sc) => new {
                    id = st.Id,
                   name = sc.Name
                })
  .MergeTable()//将查询结果集变成表  MergeTable
  .Where(it => it.id == 1).OrderBy("name asc").ToList();//对表MergeTable进行排序
  

• 没有实体的查询

  var list = db.Queryable<dynamic>().AS("order ").Where("id=id", new { id = 1 }).ToList();//没实体一样用
  

• 异步

  异步就是在原有的方法后面加Async例如，ToList改成ToListAsync

  异步分页需要注意传参有点不同代码如下

  var task1=db.Queryable<Order>().FirstAsync();
  var task2 = db.Queryable<Order>().Where(it=>it.Id==1).ToListAsync();
    
  //分页需要特别注意用法
  RefAsync<int> total = 0;
  Db.Queryable<Order>().ToPageListAsync(1, 2, total);
  

• 分组取第一条，部分数据库支持

  var list3 = db.Queryable<Student>()
  .PartitionBy(it => new { it.Id, it.Name }).Take(1).ToList();
  

• 操作In(1,2,3)

  int [] allIds=new int[]{1,2,3};
  db.Queryable<OrderItem>().Where(it => allIds.Contains(it.OrderId)).ToList() 
  //sql select * from  OrderItem where odrderid in (1,3,3)
  

完整实例

这里创建的是WebAPI，其他项目使用方法一致

Student.cs

using System;
using SqlSugar;

namespace SqlSugarTest
{
 public class Student
 {
     [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
     public int StudentId { get; set; }
     public String StudentName { get; set; }
     public String Email { get; set; }
     public bool IsDeleted { get; set; }
     public int DepartmentId { get; set; }
 }
}

TestSqlSugarController.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using SqlSugar;

namespace SqlSugarTest.Controllers
{
 [ApiController]
 [Route("[controller]/[action]")]
 public class TestSqlSugarController : ControllerBase
 {

     private SqlSugarClient GetInstance()
     {
         SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
         {
             ConnectionString = "Data Source=.;Initial Catalog=testdb;Persist Security Info=True;User ID=sa;Password=123456;Max Pool Size=300", //连接字符串
             DbType = DbType.SqlServer,
             IsAutoCloseConnection = true,
             InitKeyType = InitKeyType.Attribute
         });

         return db;
     }

     [HttpGet]
     public Boolean InsertStudent()
     {
         var db = GetInstance();

         Student stu1 = new Student();
         stu1.StudentName = "wang";
         stu1.Email = "1111@email.com";
         stu1.IsDeleted = false;
         stu1.DepartmentId = 1;
         Student stu2 = new Student();
         stu2.StudentName = "hong";
         stu2.Email = "2222@email.com";
         stu2.IsDeleted = true;
         stu2.DepartmentId = 2;
         List<Student> list = new List<Student>();
         list.Add(stu1);
         list.Add(stu2);

         int result = db.Insertable(list).ExecuteCommand();

         if (result > 0)
             return true;
         else
             return false;
     }
 }
}

更多方法请查看SqlSugar官网