sqlsugar的增删改查及封装_sqlsugar 查询

 最基本的增删改查

           查询（Select）

// 查询单个实体对象
var result = db.Queryable<User>().Where(u => u.Id == 1).Single();
 
// 查询多个实体对象
var userList = db.Queryable<User>().Where(u => u.Age > 18).ToList();
 
// 使用LINQ进行查询
var query = from u in db.Queryable<User>()
            where u.Age > 18
            select u;
var userList = query.ToList();

          插入（Insert）

// 单条插入
var user = new User
{
    Name = "John",
    Age = 25,
    Gender = Gender.Male
};
var id = db.Insertable(user).ExecuteReturnIdentity();
 
// 批量插入
var userList = new List<User>
{
    new User { Name = "Alice", Age = 20, Gender = Gender.Female },
    new User { Name = "Bob", Age = 22, Gender = Gender.Male }
};
var count = db.Insertable(userList).ExecuteCommand();

        更新（Update）

// 更新单个实体对象
var user = db.Queryable<User>().Where(u => u.Id == 1).Single();
user.Age = 26;
var count = db.Updateable(user).ExecuteCommand();
 
// 批量更新
var count = db.Updateable<User>().Where(u => u.Age < 18).SetColumns(u => new User { Age = u.Age + 1 }).ExecuteCommand();

         删除（Delete）

// 删除单个实体对象
var user = new User { Id = 1 };
var count = db.Deleteable(user).ExecuteCommand();
 
// 删除符合条件的数据
var count = db.Deleteable<User>().Where(u => u.Age >= 30).ExecuteCommand();

针对增删改查进行一个封装进项调用

public interface IBaseRespository<T> where T : class
    {
        Task<int> AddAsync(T obj);  //添加
        Task<int> DeleteOneAsync(long id);  //删除根据ID
        Task<long> DeleteAsync(Expression<Func<T, bool>> expression);   //删除根据条件进行删除
 
        Task<long> UpdateAsync(T obj);   //修改
 
        Task<long> UpdateWhereAsync(Action<T> update,Expression<Func<T, bool>> expression);  //批量条件修改
 
        Task<T> GetByIdAsync(long id);  //查询单个
 
        ISugarQueryable<T> GetAll();   //查询全部        
 
        Task<PageResult<T>> PagingAsync(int pageIndex, int pageSize, Expression<Func<T, bool>> expression);  //分页
    }
 
    public class PageResult<T>
    {
        public List<T> Items{ get; set; }
        public int TotalCount { get; set; }
        public int TotalPages { get; set; }
    }

public class BaseRespository<T> : IBaseRespository<T> where T : class, new()
    {
        public BaseRespository(ISqlSugarClient db)
        {
            this.db = db;
        }
 
        private readonly ISqlSugarClient db;
 
        //添加
        public async Task<int> AddAsync(T obj)
        {
            return await db.Insertable<T>(obj).ExecuteCommandAsync();
        }
 
        public async Task<int> DeleteOneAsync(long id)
        {
            var result = await db.Deleteable<T>().In(id).ExecuteCommandAsync();
            return result;
        }
 
        //带条件删除
        public async Task<long> DeleteAsync(Expression<Func<T, bool>> expression)
        {
            var deltable = db.Deleteable<T>().Where(expression);
            return await deltable.ExecuteCommandAsync();
        }
 
        //修改
        public async Task<long> UpdateAsync(T obj)
        {
            return await db.Updateable<T>(obj).ExecuteCommandAsync();
        }
 
        //批量修改条件
        public async Task<long> UpdateWhereAsync(Action<T> update, Expression<Func<T, bool>> expression)
        {
            var data = await db.Queryable<T>().Where(expression).ToListAsync();
            foreach (var item in data)
            {
                update(item);
            }
            return await db.Updateable(data).ExecuteCommandAsync();
        }
 
        //查询全部
        public ISugarQueryable<T> GetAll()
        {
            return db.Queryable<T>();
        }
 
        //查询单个
        public async Task<T> GetByIdAsync(long id)
        {
            return await db.Queryable<T>().InSingleAsync(id);
        }
 
        //分页
        public async Task<PageResult<T>> PagingAsync(int pageIndex, int pageSize, Expression<Func<T, bool>> expression)
        {
            var query = db.Queryable<T>().Where(expression);
 
            var total = await query.CountAsync();
            var pageTotal = (int)Math.Ceiling(total * 1.0f / pageSize);
            return new PageResult<T>
            {
                Items = query.Skip(pageIndex * pageSize).Take(pageSize).ToList(),
                TotalCount = total,
                TotalPages = pageTotal,
            };
        }
    }