# sqlsugar

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

```csharp
//方式一： 通过实体更新，并指定更新的列
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 
```

- 获取数据时间

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

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

```csharp
//执行带参数的原始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 };

```

- 返回定义类型数据

```csharp
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;
```

- 连表查询

```csharp
//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用法看下面文档
```

- 分页查询

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

- 子查询

```csharp
//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();
```

- 分组

```csharp
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语句

```csharp
//⚠️原生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

```csharp
//等同于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` ) ))
	*/
```

- 动态表达式

```csharp
//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

```csharp
//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
  - where 条件判断某个字段 is not null
  - 等于(支持 is null 的参数)
  - 判段不是NULL
- 模糊查询 like %@p%

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