Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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))));
        }
Пример #5
0
        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);
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        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);
        }
Пример #11
0
        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);
        }