项目使用的是.net9 webapi。

1、安装包

SqlSugarCore

2、配置sqlsugar

在Program.cs中添加配置。

// 注册 SqlSugar
builder.Services.AddScoped<ISqlSugarClient>(provider => 
{
    var connectionString = builder.Configuration.GetConnectionString("connStr");
    var db = new SqlSugarClient(new ConnectionConfig
    {
        ConnectionString = connectionString, //连接字符串
        DbType = DbType.SqlServer, // 设置数据库类型
        IsAutoCloseConnection = true, // 自动释放连接
    });
    return db;
});

3、配置数据库连接字符串

在appsettings.json中添加配置。

"ConnectionStrings": {
    "ConnStr": "server=localhost;database=SqlSugarTest2Db;uid=sa;pwd=root;TrustServerCertificate=true"
  }

4、实体类

[SugarTable("T_UserInfos")] //数据库表名映射
public class UserInfo
{
    [SugarColumn(IsPrimaryKey = true)] //标记主键
    public int Id { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public int Age { get; set; }
}

5、控制器

使用的是webapi。

 [Route("api/[controller]")]
    [ApiController]
    public class UserController : ControllerBase
    {
        //依赖注入
        private readonly ISqlSugarClient _db;
        public UserController(ISqlSugarClient db)
        {
            _db = db;
        }
        
        
        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult FindUsers()
        {
            List<UserInfo> userInfos = _db.Queryable<UserInfo>().ToList();
            return Ok(userInfos);
        }

        /// <summary>
        /// 根据用户名获取
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        [HttpGet("{uname}")]
        public async Task<IActionResult> QueryName([FromRoute] string uname)
        {
            UserInfo userInfo = await _db.Queryable<UserInfo>().Where(a => a.UserName == uname).FirstAsync();
            return Ok(userInfo);
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <returns></returns>
        [HttpGet("add")]
        public IActionResult AddUser()
        {
            UserInfo userInfo = new UserInfo()
            {
                Id = 5,
                UserName = "admin",
                Password = "admin",
                Email = "admin@admin.com",
                Age = 20
            };

            int rows = _db.Insertable(userInfo).ExecuteCommand();
            return Ok(rows);
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="uname"></param>
        /// <returns></returns>
        [HttpGet("{id}/{uname}")]
        public async Task<IActionResult> UpdateUser([FromRoute] int id, [FromRoute] string uname)
        {
            
            // 需要标记id为主键，不然会报错。
            //UserInfo userInfo = await _db.Queryable<UserInfo>().FirstAsync(a => a.Id == id);
            UserInfo userInfo = await _db.Queryable<UserInfo>().Where(a => a.Id == id).FirstAsync();
            
            //_db.Tracking(userInfo);
            
            userInfo.UserName = uname;
            int rows = await _db.Updateable(userInfo).ExecuteCommandAsync();
            return Ok(rows);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("del/{id}")]
        public IActionResult DeleteUser([FromRoute] int id)
        {
            //int rows = _db.Deleteable<UserInfo>(new UserInfo {Id = id}).ExecuteCommand();
            int rows = _db.Deleteable<UserInfo>().Where(a => a.Id == id).ExecuteCommand();
            return Ok(rows);
        }
        
        
        
        
        
    }

6、效果演示

1、查询所有

2、根据用户名查询

3、根据用户id修改用户名

4、删除用户

5、添加用户