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

安于an

已于 2023-12-09 00:17:48 修改

阅读量2.2k

点赞数 13

分类专栏： SqlSugar 文章标签： .net 数据库

于 2023-12-09 00:08:11 首次发布

本文链接：https://blog.csdn.net/weixin_67890610/article/details/134889213

版权

SqlSugar 专栏收录该内容

1 篇文章

订阅专栏

文章介绍了如何在.NET中使用SqlSugarCore库进行数据库操作，包括配置连接、生成实体类、CRUD操作以及关联和模糊查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

NuGet包：

1.SqlSugarCore

2.System.Data.SqlClient

Program配置:

builder.Services.AddScoped<ISqlSugarClient>(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<Student>(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<Student>()
                  .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<Student, Classes, Teacher>((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<Student, Classes, Teacher>((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<Student, Classes, Teacher>((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;
}