.net6 SqlSugar配置及增删改查（webapi项目）

NuGet包：

1.SqlSugarCore

2.System.Data.SqlClient

Program配置:

builder.Services.AddScoped(x =>{    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()    {        ConnectionString = "Data Source=.;Initial Catalog=hx;Integrated Security=True;TrustServerCertificate=True", // 替换为实际的数据库连接字符串        DbType = DbType.SqlServer, // 数据库类型，这里以 SQL Server 为例        IsAutoCloseConnection = true, // 自动关闭连接        MoreSettings = new ConnMoreSettings()        {            IsWithNoLockQuery = true, // 默认查询时加上 WITH (NOLOCK)            IsAutoRemoveDataCache = true// 自动清除缓存        }    });    // 配置输出 SQL 语句到控制台    db.Aop.OnLogExecuting = (sql, pars) =>    {        Console.WriteLine($"Executing SQL: {sql}\nParameters: {JsonSerializer.Serialize(pars)}\n");    };    return db;});

生成实体类：

可以放在一个控制台程序中，需要的时候在运行就好

using SqlSugar;using DbType = SqlSugar.DbType;// 配置连接字符串var db = new SqlSugarClient(new ConnectionConfig(){    ConnectionString = "Data Source=.;Initial Catalog=hx;Integrated Security=True;TrustServerCertificate=True",//数据库连接字符串    DbType = DbType.SqlServer, // 数据库类型，这里以 SQL Server 为例    IsAutoCloseConnection = true, // 自动关闭连接    InitKeyType = InitKeyType.Attribute // 通过特性标识主键});// DbFirst是SQLSugar中的一个功能，可以让我们通过数据库表自动生成实体类(生成全部表)db.DbFirst.CreateClassFile("生成文件路径", "命名空间");//只生成YourTable表(要生成某个表直接改YourTable替换成要生成的表名)db.DbFirst.Where("TableName='YourTable'").CreateClassFile("生成文件路径", "命名空间");//不生成YourTable表db.DbFirst.Where("YourTable").CreateClassFile("生成文件路径", "命名空间");

增：

 [HttpPost] public IActionResult Insert(Student add)//添加使用的是实体类 {     var result = _sqlSugarClient.Insertable(add)                 .IgnoreColumns(it => it.ID)//不添加此字段                 .ExecuteCommand() > 0;     JsonResult json = new JsonResult(new     {         msg = result ? "ok" : "no"     });     return json; }

删：

  [HttpPost]  public IActionResult Delete(int id)  {      bool result = _sqlSugarClient          .Deleteable(x => x.ID == id)//删除行的id          .ExecuteCommandHasChange();      JsonResult json = new JsonResult(new      {          msg = result ? "ok" : "no"      });      return json;  }

改：

 [HttpPost] public IActionResult UpdateStudent(Stu_Update update)//修改使用DTO {     var result = _sqlSugarClient.Updateable()                  .SetColumns(it => new Student { Name = update.Name }) // 设置要更新的字段及其值                  .Where(it => it.ID == update.ID) // 提供更新条件                  .ExecuteCommandHasChange();     JsonResult json = new JsonResult(new     {         msg = result ? "ok" : "no"     });     return json; }

查（内连）：

 [HttpGet] public IActionResult Select() {     var result = _sqlSugarClient.Queryable((t1, t2, t3) => t1.ClassId == t2.ID && t2.ID == t3.ClassId)//关联条件                                 .Select((t1, t2, t3) => new Stu                                 {                                     ID = t1.ID,                                     Name = t1.Name,                                     ClassName = t2.Name,                                     TeacherName = t3.Name                                 })                                 .ToList();     JsonResult json = new JsonResult(new     {         msg = result     });     return json; }

查（左联）：

  [HttpGet]  public IActionResult Select()  {      var result = _sqlSugarClient.Queryable((t1, t2, t3) => new JoinQueryInfos(                                      JoinType.Left, t1.ClassId == t2.ID, //左连接 左链接 左联                                      JoinType.Left, t2.ID == t3.ID                                  ))//关联条件                                 .Select((t1, t2, t3) => new Stu                                 {                                     ID = t1.ID,                                     Name = t1.Name,                                     ClassName = t2.Name,                                     TeacherName = t3.Name                                 })                                 .ToList();      JsonResult json = new JsonResult(new      {          msg = result      });      return json;  }

查（模糊查询）：

[HttpGet]public IActionResult Select_S(string Name){    var result = _sqlSugarClient.Queryable((t1, t2, t3) => new JoinQueryInfos(                                    JoinType.Left, t1.ClassId == t2.ID, //左连接 左链接 左联                                    JoinType.Left, t2.ID == t3.ID                                ))//关联条件                               .Select((t1, t2, t3) => new Stu                               {                                   ID = t1.ID,                                   Name = t1.Name,                                   ClassName = t2.Name,                                   TeacherName = t3.Name                               }).ToList();    if (Name != null)    {        result = result.Where(x => x.Name.Contains(Name)).ToList();//模糊查询    }    JsonResult json = new JsonResult(new    {        msg = result    });    return json;}