public async Task <List <Users> > GetUsers()
{
using (ReadConnection)
{
string sql = @"select * from Users;";
var res = await ReadConnection.QueryAsync <Users>(sql);
return(res.ToList());
}
}
public async Task <UserDO> Login(string username, string password)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from ums_user u where u.enabled=1 and u.username=@username and u.password=@password");
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@username", username);
parameters.Add("@password", password);
using (ReadConnection)
{
var result = await ReadConnection.QueryAsync <UserDO>(sql.ToString(), parameters);
return(result.FirstOrDefault());
}
}
public async override Task <Tuple <int, IList <TEntity> > > GetListAsync(IQueryObject <TEntity> query)
{
var parameters = new DynamicParameters();
var select = ExpressionHelper.Select <TEntity>();
var count = ExpressionHelper.Count <TEntity>();
foreach (var where in query.QueryExpression)
{
select.Where(where);
}
foreach (var where in query.QueryExpression)
{
count.Where(where);
}
select.Where(x => x.IsDeleted == false);
count.Where(x => x.IsDeleted == false);
if (query.OrderSort == SortOrder.ASC)
{
select.OrderBy(query.OrderField);
}
else if (query.OrderSort == SortOrder.DESC)
{
select.OrderByDesc(query.OrderField);
}
else
{
select.OrderByDesc(x => x.CreationTime);
}
if (query.Count >= 0)
{
select.Limit(query.Skip, query.Count);
}
foreach (KeyValuePair <string, object> item in select.DbParams)
{
parameters.Add(item.Key, item.Value);
}
using (Connection)
{
var customerRepresentativeList = await ReadConnection.QueryAsync <TEntity>(select.Sql, parameters);
int totalCount = await ReadConnection.QuerySingleAsync <int>(count.Sql, parameters);
return(new Tuple <int, IList <TEntity> >(totalCount, customerRepresentativeList.ToList()));
}
}
public async Task <List <MachineryInfo> > GetListAsync(MachineryInfoSearchRequest request)
{
StringBuilder sbSql = new StringBuilder();
DynamicParameters parameters = new DynamicParameters();
sbSql.Append(" SELECT * FROM machineryinfo WHERE IsDeleted = 0 ");
var where = this.GetWhere(request, parameters);
if (where?.Item1?.Length > 0)
{
sbSql.Append(where.Item1);
}
//添加排序
sbSql.Append(" Order By CreationTime DESC ");
using (ReadConnection)
{
var r1 = await ReadConnection.QueryAsync <MachineryInfo>(sbSql.ToString(), parameters);
return(r1.ToList());
}
}
public override async Task <Tuple <int, IList <GoodsInfo> > > GetListAsync(IQueryObject <GoodsInfo> query)
{
StringBuilder sb = new StringBuilder();
sb.Append("select g.*, u.* from goodsinfo g LEFT JOIN userinfo u ON g.UserId=u.Id WHERE 1=1 and g.IsDelete=0 ");
StringBuilder sbCount = new StringBuilder();
sbCount.Append("select COUNT(g.Id) from goodsinfo g LEFT JOIN userinfo u ON g.UserId=u.Id WHERE 1=1 and g.IsDelete=0 ");
var queryandsort = query.QuerySql.FirstOrDefault();
if (query.QuerySql.Count > 0)
{
sb.Append(queryandsort.Key);
sbCount.Append(queryandsort.Key);
}
if (query.Count <= 0)
{
query.Count = int.MaxValue;
}
sb.Append(" LIMIT " + query.Skip + "," + query.Count);
using (ReadConnection)
{
var GoodsList = await ReadConnection.QueryAsync <GoodsInfo, UserInfo, GoodsInfo>(sb.ToString(), (goodsinfo, usersinfo) =>
{
if (goodsinfo != null)
{
goodsinfo.UserInfo = usersinfo;
}
return(goodsinfo);
}, queryandsort.Value);
int totalCount = await ReadConnection.QuerySingleAsync <int>(sbCount.ToString(), queryandsort.Value);
return(new Tuple <int, IList <GoodsInfo> >(totalCount, GoodsList.ToList()));
}
}