using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Threading.Tasks;
using Api.Contact;
using Api.Models;
using Api.Service.DB;
using Api.Service.SqlSugar;
using SqlSugar;
namespace Api.Service
{
public class BaseService<TEntity> where TEntity : class, new()
{
internal SqlSugarClient Db { get; }
internal SimpleClient<TEntity> EntityDb { get; }
public BaseService()
{
DbContext.Init(BaseDBConfig.ConnectionString);
var context = DbContext.GetDbContext();
Db = context.Db;
EntityDb = context.GetEntityDB<TEntity>(Db);
}
#region Query
/// <summary>
/// 根据ID查询数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<TEntity> QueryById(int id)
{
//return await Task.Run(() => _db.Queryable<TEntity>().InSingle(objId));
return await Db.Queryable<TEntity>().In(id).SingleAsync();
}
/// <summary>
/// 根据ID查询一条数据
/// </summary>
/// <param name="id">id(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件</param>
/// <param name="useCache">是否使用缓存</param>
/// <returns>数据实体</returns>
public async Task<TEntity> QueryById(int id, bool useCache = false)
{
//return await Task.Run(() => _db.Queryable<TEntity>().WithCacheIF(blnUseCache).InSingle(objId));
return await Db.Queryable<TEntity>().WithCacheIF(useCache).In(id).SingleAsync();
}
/// <summary>
/// 根据ID列表查询数据集合
/// </summary>
/// <param name="Ids">id列表(必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]),如果是联合主键,请使用Where条件</param>
/// <returns>数据实体列表</returns>
public async Task<List<TEntity>> QueryByIds(int[] Ids)
{
//return await Task.Run(() => _db.Queryable<TEntity>().In(lstIds).ToList());
return await Db.Queryable<TEntity>().In(Ids).ToListAsync();
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query()
{
//return await Task.Run(() => _entityDb.GetList());
return await Db.Queryable<TEntity>().ToListAsync();
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="strWhere">条件</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(string strWhere)
{
//return await Task.Run(() => _db.Queryable<TEntity>().WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToList());
return await Db.Queryable<TEntity>().WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToListAsync();
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="whereExpression">whereExpression</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression)
{
//return await Task.Run(() => _entityDb.GetList(whereExpression));
return await Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression).ToListAsync();
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderByFileds">排序字段,如name asc,age desc</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression, string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(whereExpression != null, whereExpression).ToList());
return await Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrWhiteSpace(orderByFileds), orderByFileds).ToListAsync();
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderByExpression">排序表达式</param>
/// <param name="isAsc">是否正序</param>
/// <returns></returns>
public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression,
Expression<Func<TEntity, object>> orderByExpression, bool isAsc = true)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(orderByExpression != null, orderByExpression, isAsc ? OrderByType.Asc : OrderByType.Desc).WhereIF(whereExpression != null, whereExpression).ToList());
return await Db.Queryable<TEntity>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(orderByExpression != null, orderByExpression, isAsc ? OrderByType.Asc : OrderByType.Desc)
.ToListAsync();
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="where">条件</param>
/// <param name="orderByFileds">排序字段,如name asc,age desc</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(string where, string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToList());
return await Db.Queryable<TEntity>()
.WhereIF(!string.IsNullOrEmpty(where), where)
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds)
.ToListAsync();
}
/// <summary>
/// 查询前N条数据
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="top">前N条</param>
/// <param name="orderByFileds">排序字段,如name asc,age desc</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(
Expression<Func<TEntity, bool>> whereExpression,
int top,
string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(whereExpression != null, whereExpression).Take(intTop).ToList());
return await Db.Queryable<TEntity>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds)
.Take(top).ToListAsync();
}
/// <summary>
/// 查询前N条数据
/// </summary>
/// <param name="where">条件</param>
/// <param name="top">前N条</param>
/// <param name="orderByFileds">排序字段,如name asc,age desc</param>
/// <returns>数据列表</returns>
public async Task<List<TEntity>> Query(string where, int top, string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).Take(intTop).ToList());
return await Db.Queryable<TEntity>()
.WhereIF(!string.IsNullOrEmpty(where), where)
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds)
.Take(top).ToListAsync();
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">数据量</param>
/// <param name="orderByFileds">排序方式,如name asc,age desc</param>
/// <returns></returns>
public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression, int pageIndex,
int pageSize, string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(whereExpression != null, whereExpression).ToPageList(intPageIndex, intPageSize));
return await Db.Queryable<TEntity>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds)
.ToPageListAsync(pageIndex, pageSize);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="where">条件</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">数据量</param>
/// <param name="orderByFileds">排序方式,如name asc,age desc</param>
/// <returns></returns>
public async Task<List<TEntity>> Query(string where, int pageIndex, int pageSize, string orderByFileds)
{
//return await Task.Run(() => _db.Queryable<TEntity>().OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToPageList(intPageIndex, intPageSize));
return await Db.Queryable<TEntity>()
.WhereIF(!string.IsNullOrEmpty(where), where)
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds)
.ToPageListAsync(pageIndex, pageSize);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="pageIndex">页码(下标0)</param>
/// <param name="pageSize">页大小</param>
/// <param name="orderByFileds">排序字段,如name asc,age desc</param>
/// <returns></returns>
public async Task<PageModel<TEntity>> QueryPage(Expression<Func<TEntity, bool>> whereExpression, int pageIndex = 1, int pageSize = 10, string orderByFileds = null)
{
RefAsync<int> totalCount = 0;
var list = await Db.Queryable<TEntity>()
.OrderByIF(!string.IsNullOrEmpty(orderByFileds), orderByFileds).WhereIF(whereExpression != null, whereExpression)
.ToPageListAsync(pageIndex, pageSize, totalCount);
int pageCount = Math.Ceiling(totalCount.ObjToDecimal() / pageSize.ObjToDecimal()).ObjToInt();
return new PageModel<TEntity>
{
DataCount = totalCount,
PageCount = pageCount,
PageIndex = pageIndex,
PageSize = pageSize,
Data = list
};
}
public async Task<int> QueryCount(Expression<Func<TEntity, bool>> whereExpression)
{
var count = await Db.Queryable<TEntity>()
.WhereIF(whereExpression != null, whereExpression)
.CountAsync();
return count;
}
#endregion
#region Add
/// <summary>
/// 添加
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public async Task<int> Add(TEntity entity)
{
//var i = await Task.Run(() => _db.Insertable(entity).ExecuteReturnBigIdentity());
返回的i是long类型,这里你可以根据你的业务需要进行处理
//return (int)i;
var insert = Db.Insertable(entity);
return await insert.ExecuteReturnIdentityAsync();
}
/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="insertColumns">指定只插入列</param>
/// <returns>返回自增量列</returns>
public async Task<int> Add(TEntity entity, Expression<Func<TEntity, object>> insertColumns = null)
{
var insert = Db.Insertable(entity);
if (insertColumns == null)
{
return await insert.ExecuteReturnIdentityAsync();
}
return await insert.InsertColumns(insertColumns).ExecuteReturnIdentityAsync();
}
/// <summary>
/// 批量插入实体(速度快)
/// </summary>
/// <param name="listEntity">实体集合</param>
/// <returns>影响行数</returns>
public async Task<int> Add(List<TEntity> listEntity)
{
return await Db.Insertable(listEntity.ToArray()).ExecuteCommandAsync();
}
#endregion
#region Delete
/// <summary>
/// 删除指定ID的数据
/// </summary>
/// <param name="id">主键ID</param>
/// <returns></returns>
public async Task<bool> DeleteById(int id)
{
//var i = await Task.Run(() => _db.Deleteable<TEntity>(id).ExecuteCommand());
//return i > 0;
return await Db.Deleteable<TEntity>(id).ExecuteCommandHasChangeAsync();
}
/// <summary>
/// 根据实体删除一条数据
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public async Task<bool> Delete(TEntity entity)
{
//var i = await Task.Run(() => _db.Deleteable(entity).ExecuteCommand());
//return i > 0;
return await Db.Deleteable(entity).ExecuteCommandHasChangeAsync();
}
/// <summary>
/// 删除指定ID集合的数据(批量删除)
/// </summary>
/// <param name="ids">主键ID集合</param>
/// <returns></returns>
public async Task<bool> DeleteByIds(int[] ids)
{
//var i = await Task.Run(() => _db.Deleteable<TEntity>().In(ids).ExecuteCommand());
//return i > 0;
return await Db.Deleteable<TEntity>().In(ids).ExecuteCommandHasChangeAsync();
}
#endregion
#region Update
/// <summary>
/// 更新实体数据
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public async Task<bool> Update(TEntity entity)
{
这种方式会以主键为条件
//var i = await Task.Run(() => _db.Updateable(entity).ExecuteCommand());
//return i > 0;
//这种方式会以主键为条件
return await Db.Updateable(entity).ExecuteCommandHasChangeAsync();
}
/// <summary>
/// 更新实体数据
/// </summary>
/// <param name="entity"></param>
/// <param name="where">条件</param>
/// <returns></returns>
public async Task<bool> Update(TEntity entity, string where)
{
//return await Task.Run(() => _db.Updateable(entity).Where(strWhere).ExecuteCommand() > 0);
return await Db.Updateable(entity).Where(where).ExecuteCommandHasChangeAsync();
}
/// <summary>
/// 更新实体数据
/// </summary>
/// <param name="entity">实体</param>
/// <param name="columns">更新的列</param>
/// <param name="ignoreColumns">跳过的列</param>
/// <param name="where">条件</param>
/// <returns></returns>
public async Task<bool> Update(TEntity entity, List<string> columns = null, List<string> ignoreColumns = null, string where = "")
{
//IUpdateable<TEntity> up = await Task.Run(() => _db.Updateable(entity));
//if (lstIgnoreColumns != null && lstIgnoreColumns.Count > 0)
//{
// up = await Task.Run(() => up.IgnoreColumns(it => lstIgnoreColumns.Contains(it)));
//}
//if (lstColumns != null && lstColumns.Count > 0)
//{
// up = await Task.Run(() => up.UpdateColumns(it => lstColumns.Contains(it)));
//}
//if (!string.IsNullOrEmpty(strWhere))
//{
// up = await Task.Run(() => up.Where(strWhere));
//}
//return await Task.Run(() => up.ExecuteCommand()) > 0;
IUpdateable<TEntity> up = Db.Updateable(entity);
if (ignoreColumns != null && ignoreColumns.Count > 0)
{
up = up.IgnoreColumns(ignoreColumns.ToArray());
}
if (columns != null && columns.Count > 0)
{
up = up.UpdateColumns(columns.ToArray());
}
if (!string.IsNullOrEmpty(where))
{
up = up.Where(where);
}
return await up.ExecuteCommandHasChangeAsync();
}
/// <summary>
/// 更新
/// </summary>
/// <param name="sql">SQL</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public async Task<bool> Update(string sql, SugarParameter[] parameters = null)
{
//return await Task.Run(() => _db.Ado.ExecuteCommand(strSql, parameters) > 0);
return await Db.Ado.ExecuteCommandAsync(sql, parameters) > 0;
}
/// <summary>
/// 更新实体数据
/// </summary>
/// <param name="entityList">实体类</param>
/// <returns></returns>
public async Task<bool> Update(List<TEntity> entityList)
{
//这种方式会以主键为条件
var i = await Db.Updateable(entityList).ExecuteCommandAsync();
return i > 0;
}
#endregion
}
}
BaseService
最新推荐文章于 2024-10-16 11:10:17 发布