最基本的增删改查
查询(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,
};
}
}