sqlsugar

官网文档： https://www.donet5.com/Doc/1/1180
特点
- 支持对象模型，类linq的方式操作数据库查询「生成sql语句」
- 也支持执行原始sql语句的形式。
最佳实践：
- 增加、修改、删除都直接采用对象操作方式
- 简单的sql语句可以使用类linq的模式
- ⚠️复杂的查询、统计等，推荐使用原始sql语句，翻译参见：http://www.rstone.com.cn:3000/docs/develop/sql#_3sqlserver
- ⚠️复杂查询的处理
  - A、主sql语句只是用主表和其他必须的表，进行统计计算
  - B、其他字段可以在A查出的一页记录基础上，在查询其他数据采用linq进行拼接，生成整个行数据
修改数据

//方式一： 通过实体更新，并指定更新的列
Models.DictNewSickness sickness = new Models.DictNewSickness();
    sickness.DName = name;
    sickness.ParentID = pid;
    sickness.DID = id;
db.Updateable(sickness)
  //.UpdateColumns(item => item.DName) //指定要修改数据的列 单列
  //.UpdateColumns(t=>new {t.DName， t.ParentID} //多列
  .ExecuteCommand();

//方式二 通过设置更新字段和内容， 并设置更新条件更新
//更新 name,createtime 条件id=11
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")//SetColumns是可以叠加的 写2个就2个字段赋值
.Where(it => it.Id == 1)
.ExecuteCommand();
 
// Sql 
// Update Student  set Name='jack' where id=1

获取数据时间

//如果需要获取数据库时间我们可以用 SqlFunc.GetDate()

原始sql语句分页，列数据后期合并

//执行带参数的原始sql语句，翻页
 var list = db.SqlQueryable<dynamic>("select ID, 0 as OutNum from M_InMedicine where name like @name")
    .AddParameters(new { name = "%%" })
    .ToPageList(page, limit, ref total);
//获取本分页的id数组
var midArray = from item in list select item.ID;
//根据id获取M_InMedicine表数据
var medicine_list = db.Queryable<Models.M_InMedicine>()
    .Where((it) => midArray.Contains(it.ID))
    .ToList();
//列合并
var tlist = from p in list
            join medicine in medicine_list on p.ID equals medicine.ID
            select new { ID = p.ID, Name = medicine.Name, OutNum = p.OutNum, Unit = medicine.Unit, Price = medicine.Price, Num = medicine.Num, Dose = medicine.Dose };

返回定义类型数据

public class ItemName
{
    public string name { get; set; }
    public string value { get; set; }
}
 var l = db.Queryable<Models.M_InMedicine>()
            .OrderBy(it => it.Name)
            .Select(it => new ItemName { name = it.Name, value = it.Name })
            .Distinct()
            .ToList();
        return l;

连表查询

//1、连表查询
 SqlSugar.SqlSugarClient db = Rstone.SqlSugar.GetDb();
var query5 = db.Queryable<Order>()    // 第一个表 
     .LeftJoin<Custom>   ((o, cus ) => o.CustomId == cus.Id)//多个条件用&&
     .LeftJoin<OrderDetail> ((o, cus, oritem) => o.Id == oritem.OrderId)
     .Where(o => o.Id == 1)  
 .WhereIF(!string.IsNullOrEmpty(name), (it) =>  it.Name.Contains(name) )
 .OrderByDescending((v, e) => v.BuildDT).OrderByDescending((v,e) => v.ID)
 //查询结果数据的匿名类定义
     .Select((o, cus , oritem) => new  { Id = o.Id, CustomName = cus.Name })
     .ToList();  //ViewOrder是一个新建的类，更多Select用法看下面文档

分页查询

//分页查询
 ToPageList(pageIndex, pageSize, ref totalCount); // pagenumber是从1开始的不是从零开始的

子查询

//3、子查询
var t = db.Queryable<Models.Employee>()
        .Select( item =>
            new
            {
                id = item.id,
                sum = SqlFunc.Subqueryable<Models.Employee>()
                .Where(s => s.ID == item.ID)
                .Sum( s => s.ID)
            }
        )
        .ToList();

分组

var list = db.Queryable<Student>()
            .GroupBy(it => new { it.Id, it.Name }) //可以多字段
            .Where(it=>it.Id>0)//普通过滤
            //.Having(it => SqlFunc.AggregateCount(it.Id) > 0)//聚合函数过滤
            .Select(it => new { 
                        idAvg = SqlFunc.AggregateAvg(it.Id??0),
                        count = SqlFunc.AggregateCount(it.Id),  
                        name = it.Name })
            .ToList();
#group by 统计 然后排序
var list = db.Queryable<Student>()
             .GroupBy(it => new { it.Id, it.Name })  
             .Where(it=>it.Id>0) 
             .Select(it => new { 
                          idAvg = SqlFunc.AggregateAvg(it.Id??0),
                          count = SqlFunc.AggregateCount(it.Id),  
                           name = it.Name })
             .MergeTable()//需要加MergeTable才能排序统计过的列
             .OrderBy(it=>it.count)
             .ToList();

使用原生sql语句

//⚠️原生sql语句需要自己写 翻页
//使用原生sql语句：
//语句 和 参数
var dt=db.Ado.GetDataTable("select * from table where id=@id and name like @name",
new List<SugarParameter>(){
  new SugarParameter("@id",1),
  new SugarParameter("@name","%"+jack+"%") //执行sql语句
});
//4.1 使用原生sql语句：
//语句 、匿名类形式 参数
string sql = "SELECT * FROM Users WHERE Id = @id";
    var parameters = new { id = 1 };
 
    var users = db.Ado.SqlQuery<User>(sql, parameters);
 
    // 处理查询结果
    foreach (var user in users)
    {
    Console.WriteLine($"Id: {user.Id}, Name: {user.Name}");
    }
//4.2 使用原生sql语句：
//使用 Ado.SqlQueryDynamic
string sql = "SELECT * FROM Users WHERE Id = @id";
var parameters = new { id = 1 };
var users = db.Ado.SqlQueryDynamic(sql, parameters);
// 处理查询结果
foreach (var user in users)
{
  Console.WriteLine($"Id: {user["Id"]}, Name: {user["Name"]}");
}

IN、not in

//等同于IN
    var getAll7 = db.Queryable<Student>()
    .Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();
     
    /*生成的SQL(等于同于it.id in(select id from school)只是写法不一样
    SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
    WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) 
    */

//等同于NOT IN
    var getAll8 = db.Queryable<Student>().Where(it => 
    SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();
     
    /*生成的SQL
    SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it  
    WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
    */

动态表达式

//7. 动态表达式
    var exp = Expressionable.Create<Order>() //创建表达式
          .AndIF(p > 0, it => it.Id == p)
          .AndIF(name != null, it => it.Name == name && it.Sex==1)
          .ToExpression();//注意 这一句 不能少
           
    var list = db.Queryable<Order>().Where(exp).ToList();//直接用就行了不需要判段 null和加true

distinct

//8. distinct
var list = db.Queryable<Student>().Distinct().Select(it => new {CsName = it.Name }).ToList();

//SELECT  DISTINCT  `Name` AS `CsName`  FROM `Student`

is null、is not null

where 条件判断某个字段 is null

// 查询某个字段为NULL的记录
SqlSugar.SqlSugarClient db = Rstone.SqlSugar.GetDb();
int c = db.Queryable<Models.M_Prescription>()
            .Where(it => SqlFunc.IsNullOrEmpty(it.OutStorageUserID))
            .Count();
//对应的sql 语句
SELECT COUNT(1) FROM [M_Prescription]  WHERE ( [OutStorageUserID] IS NULL   OR [OutStorageUserID]='')

where 条件判断某个字段 is not null

//查询某个字段为is not NULL的记录
SqlSugar.SqlSugarClient db = Rstone.SqlSugar.GetDb();
int c = db.Queryable<Models.M_Prescription>()
            .Where(it => !SqlFunc.IsNullOrEmpty(it.OutStorageUserID))
            .Count();
//对应的sql 语句
SELECT COUNT(1) FROM [M_Prescription]  WHERE NOT( [OutStorageUserID] IS NULL   OR [OutStorageUserID]='')

等于(支持 is null 的参数)

//用例：1
SqlFunc.EqualsNull(it.Name,null) 
//SQL: it.Name is null

//用例：2
SqlFunc.EqualsNull(it.Name,"a") 
//SQL:  it.Name='a'

判段不是NULL

SqlFunc.HasValue(object thisValue)
//或者
it.xx!=null // xx is not null
it.xx==null //xx is null

模糊查询 like %@p%

SqlFunc.Contains(string thisValue, string parameterValue)
//也可以使用 .Where(it=>it.Name.Contains("a"));
  
//模糊查询 not like %@p%
!SqlFunc.Contains(string thisValue, string parameterValue) //前面加个!

目录

sqlsugar