public static IDbCommand BuildSum <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, TProperty> > property, Expression <Func <T, bool> > predicate) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (property == null) { throw new ArgumentNullException("property"); } if (predicate == null) { throw new ArgumentNullException("predicate"); } var ctx = new QueryContext(classMap); var command = DbCommandDecorator.Create(connection); var visitor = new PredicateVisitor(ctx); visitor.Visit(predicate.Body); foreach (var pair in visitor.Parameters) { command.AddParam(pair.Key, pair.Value); } var propertyMap = classMap.Properties.Single(it => it.Alias == MemberHelper.GetName(property)); var sumStr = String.Format(propertyMap.IsColumnDiffer ? "sum({0})" : "sum({1}.[{0}])", propertyMap.ColumnNameOrFormula, ctx.AliasForTable(classMap)); command.CommandText = String.Format(SELECT, sumStr, classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString()); return(command); }
public static IDbCommand BuildJoin <T, T1>(IDbConnection connection, IClassMap <T> classMap, IClassMap <T1> joinMap, Expression <Func <T, T1, bool> > predicate) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (joinMap == null) { throw new ArgumentNullException("joinMap"); } if (predicate == null) { throw new ArgumentNullException("predicate"); } var ctx = new QueryContext(classMap, joinMap); var columnList = classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap))); var command = DbCommandDecorator.Create(connection); var visitor = new PredicateVisitor(ctx); visitor.Visit(predicate.Body); foreach (var pair in visitor.Parameters) { command.AddParam(pair.Key, pair.Value); } command.CommandText = String.Format(SELECTJOIN, columnList, classMap.TableName, ctx.AliasForTable(classMap), joinMap.TableName, ctx.AliasForTable(joinMap), visitor.ToString()); return(command); }
public static IDbCommand BuildDelete <T>(IDbConnection connection, IClassMap <T> classMap, T item) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (item == null) { throw new ArgumentNullException("item"); } if (!classMap.Properties.Any(it => it.IsPrimaryKey)) { throw new InvalidOperationException("Primary key is not configured."); } var command = DbCommandDecorator.Create(connection); var where = new Dictionary <string, string>(); foreach (var primaryKeyMap in classMap.Properties.Where(it => it.IsPrimaryKey)) { var param = command.AddParam(null, GetPropertyValue(item, primaryKeyMap)); where.Add(primaryKeyMap.ColumnNameOrFormula, param.ParameterName); } command.CommandText = String.Format(DELETE, classMap.TableName, where.Aggregate(string.Empty, GetWhereAggregator())); return(command); }
public static IDbCommand BuildSelect <T>(IDbConnection connection, IClassMap <T> classMap) { var ctx = new QueryContext(classMap); var columnList = classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap))); return(DbCommandDecorator.Create(connection, String.Format(SELECTALL, columnList, classMap.TableName, ctx.AliasForTable(classMap)))); }
public static IDbCommand BuildCount <T>(IDbConnection connection, IClassMap <T> classMap) { if (classMap == null) { throw new ArgumentNullException("classMap"); } var ctx = new QueryContext(classMap); var command = DbCommandDecorator.Create(connection); command.CommandText = String.Format(SELECTALL, "count(1)", classMap.TableName, ctx.AliasForTable(classMap)); return(command); }
public static IDbCommand BuildSum <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, TProperty> > property) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (property == null) { throw new ArgumentNullException("property"); } var ctx = new QueryContext(classMap); var command = DbCommandDecorator.Create(connection); var propertyMap = classMap.Properties.Single(it => it.Alias == MemberHelper.GetName(property)); var sumStr = String.Format(propertyMap.IsColumnDiffer ? "sum({0})" : "sum({1}.[{0}])", propertyMap.ColumnNameOrFormula, ctx.AliasForTable(classMap)); command.CommandText = String.Format(SELECTALL, sumStr, classMap.TableName, ctx.AliasForTable(classMap)); return(command); }
public static IDbCommand BuildSelect <T, TProperty>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, bool> > predicate, PageFilter <T, TProperty> pageFilter) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (pageFilter == null) { throw new ArgumentNullException("pageFilter"); } var ctx = new QueryContext(classMap); var filterColumn = ctx.AliasForColumn(); var columnList = String.Format("row_number() over (order by [{0}] {1}) {2}, {3}", MemberHelper.GetName(pageFilter.OrderProperty), pageFilter.Desc ? "desc" : "asc", filterColumn, classMap.Properties.Aggregate(string.Empty, GetColumnListAggregator(ctx.AliasForTable(classMap)))); var command = DbCommandDecorator.Create(connection); if (predicate != null) { var visitor = new PredicateVisitor(ctx); visitor.Visit(predicate.Body); foreach (var pair in visitor.Parameters) { command.AddParam(pair.Key, pair.Value); } command.CommandText = String.Format(SELECT, columnList, classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString()); } else { command.CommandText = String.Format(SELECTALL, columnList, classMap.TableName, ctx.AliasForTable(classMap)); } var fromParam = command.AddParam(null, pageFilter.From); var toParam = command.AddParam(null, pageFilter.To); command.CommandText = String.Format("select {4} from ({0}) d where d.[{1}] between {2} and {3} order by d.[{1}] asc", command.CommandText, filterColumn, fromParam.ParameterName, toParam.ParameterName, classMap.Properties.Aggregate(string.Empty, GetPropListAggregator("d"))); return(command); }
public static IDbCommand BuildExecProc <T>(IDbConnection connection, IClassMap <T> classMap, string procName, object parameters) { if (procName == null) { throw new ArgumentNullException("procName"); } var command = DbCommandDecorator.Create(connection, procName, CommandType.StoredProcedure); if (parameters != null) { foreach (var prop in parameters.GetType().GetProperties()) { if (prop.CanRead) { command.AddParam("@" + prop.Name, prop.GetValue(parameters, null)); } } } return(command); }
public static IDbCommand BuildCount <T>(IDbConnection connection, IClassMap <T> classMap, Expression <Func <T, bool> > predicate) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (predicate == null) { throw new ArgumentNullException("predicate"); } var ctx = new QueryContext(classMap); var command = DbCommandDecorator.Create(connection); var visitor = new PredicateVisitor(ctx); visitor.Visit(predicate.Body); foreach (var pair in visitor.Parameters) { command.AddParam(pair.Key, pair.Value); } command.CommandText = String.Format(SELECT, "count(1)", classMap.TableName, ctx.AliasForTable(classMap), visitor.ToString()); return(command); }
public static IDbCommand BuildUpdate <T>(IDbConnection connection, IClassMap <T> classMap, T item) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (item == null) { throw new ArgumentNullException("item"); } if (!classMap.Properties.Any(it => it.IsPrimaryKey)) { throw new InvalidOperationException("Primary key is not configured."); } var command = DbCommandDecorator.Create(connection); var where = new Dictionary <string, string>(); foreach (var primaryKeyMap in classMap.Properties.Where(it => it.IsPrimaryKey)) { var param = command.AddParam(null, GetPropertyValue(item, primaryKeyMap)); where.Add(primaryKeyMap.ColumnNameOrFormula, param.ParameterName); } var set = new Dictionary <string, string>(); foreach (var columnMap in classMap.Properties.Where(it => !it.IsIdentity && !it.IsReadOnly)) { var param = command.AddParam(null, GetPropertyValue(item, columnMap)); set.Add(columnMap.ColumnNameOrFormula, param.ParameterName); } command.CommandText = String.Format(UPDATE, classMap.TableName, set.Aggregate(string.Empty, (accum, value) => String.Format("{0}, [{1}] = {2}", accum, value.Key, value.Value)).Trim(',').Trim(), where.Aggregate(string.Empty, GetWhereAggregator())); return(command); }
public static IDbCommand BuildInsert <T>(IDbConnection connection, IClassMap <T> classMap, T item) { if (classMap == null) { throw new ArgumentNullException("classMap"); } if (item == null) { throw new ArgumentNullException("item"); } var command = DbCommandDecorator.Create(connection); var columnList = classMap.Properties.Where(it => !it.IsIdentity && !it.IsReadOnly) .Aggregate(string.Empty, (accum, val) => String.Format("{0}, [{1}]", accum, val.ColumnNameOrFormula)).Trim(',').Trim(); foreach (var columnMap in classMap.Properties.Where(it => !it.IsIdentity && !it.IsReadOnly)) { command.AddParam(null, GetPropertyValue(item, columnMap)); } var paramList = command.Parameters.OfType <IDbDataParameter>().Aggregate(string.Empty, (accum, val) => String.Format("{0}, {1}", accum, val)).Trim(',').Trim(); command.CommandText = String.Format(INSERT, classMap.TableName, columnList, paramList); return(command); }