public T Single <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
if (!string.IsNullOrEmpty(attr.Sort))
{
sql.Append(" ORDER BY " + attr.Sort);
}
var instance = _dapper.QueryFirstOrDefault <T>(sql.ToString(), condition.DapperArguments);
instance?.Inital();
return(instance);
}
public List <T> Query <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
if (!string.IsNullOrEmpty(attr.Sort))
{
sql.Append(" ORDER BY " + attr.Sort);
}
var list = _dapper.Query <T>(sql.ToString(), condition.DapperArguments).ToList();
if (list.Count > 0)
{
list.ForEach(x => x.Inital());
}
return(list);
}
public int Update <T>(T instance, Expression <Func <T, bool> > whereBy) where T : class, IDao
{
var listCol = new List <string>();
var sqlPara = new DynamicParameters(new { });
foreach (var pi in instance.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
var attrCol = GetColumnAttr(pi);
if (attrCol != null)
{
var value = pi.GetValue(instance, null);
listCol.Add($"{attrCol.Name} = @{attrCol.Name}");
sqlPara.Add(attrCol.Name, value == null && pi.PropertyType == typeof(string) ? string.Empty : value);
}
}
var attr = GetTableAttr <T>();
if (listCol.Count > 0 && sqlPara.ParameterNames.Any())
{
var sql = new StringBuilder();
sql.Append($"UPDATE {attr.Name} SET {string.Join(", ", listCol.ToArray())}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
foreach (var p in condition.DapperArguments)
{
sqlPara.Add(p.Key, p.Value);
}
return(_dapper.Execute(sql.ToString(), sqlPara));
}
return(-1);
}
public int Count <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao
{
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append($"SELECT COUNT(*) FROM {attr.Name}");
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
return(_dapper.ExecuteScalar <int>(sql.ToString(), condition.DapperArguments));
}
public List <T> Query <T>(IPager pager, Expression <Func <T, bool> > whereBy, string orderBy = null)
where T : class, IDao, new()
{
var attr = GetTableAttr <T>();
// Generate WhereBy Clause
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
// Generate OrderBy Clause
var strOrderBy = !string.IsNullOrEmpty(attr.Sort) ? attr.Sort : attr.Key;
if (!string.IsNullOrEmpty(orderBy))
{
strOrderBy = orderBy;
}
// Get TotalCount First
var countSql = $"SELECT COUNT({attr.Key}) AS TotalCount FROM {attr.Name} WHERE {condition.Condition}";
pager.SetTotalCount(_dapper.ExecuteScalar <int>(countSql, condition.DapperArguments));
// Build Sql and Execute
var innerSql = $"SELECT ROW_NUMBER() OVER(ORDER BY {strOrderBy}) AS RowNo, * FROM {attr.Name} WHERE {condition.Condition}";
string sql =
$"SELECT {BuildSelectSqlColumn(attr)} FROM ({innerSql}) AS t WHERE t.RowNo BETWEEN {pager.CurrentPage * pager.PagingSize + 1} AND {(pager.CurrentPage + 1) * pager.PagingSize}";
var list = _dapper.Query <T>(sql, condition.DapperArguments).ToList();
if (list.Count > 0)
{
list.ForEach(x => x.Inital());
}
return(list);
}
