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); }