Ejemplo n.º 1
0
        /// <summary>
        /// Executes a T-SQL SELECT statement in format only mode (SET FMTONLY ON) which validates the query and returns the result metadata.
        /// </summary>
        /// <param name="query">The query builder instance.</param>
        /// <param name="schema">Outputs the query metadata.</param>
        /// <param name="errors">Outputs any validation or execution errors encountered.</param>
        /// <returns></returns>
        public static bool GetSchemaTable(this IDbQueryBuilder query, out DataTable schema, out ICollection <Exception> errors)
        {
            var cmd = query.CreateCommand();

            errors = null;
            schema = null;

            try
            {
                using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                    if (reader.FieldCount <= 0)
                    {
                        return(false);
                    }

                    schema = reader.GetSchemaTable();
                }

                return(true);
            }
            catch (Exception ex)
            {
                errors = new List <Exception>();
                errors.Add(ex);
                return(false);
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Sets the resulting query to use SELECT DISTINCT.
        /// </summary>
        /// <param name="query">The query builder instance.</param>
        /// <returns></returns>
        public static IDbQueryBuilder Distinct(this IDbQueryBuilder query)
        {
            var fork = query.Fork();

            fork.Distinct = true;
            return(fork);
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Executes a T-SQL SELECT statement in format only mode (SET FMTONLY ON) which validates the query and returns the result metadata.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="schema"></param>
        /// <param name="errors"></param>
        /// <returns></returns>
        public static bool GetColumnSchema(this IDbQueryBuilder query, out ReadOnlyCollection <DbColumn> schema, out ICollection <Exception> errors)
        {
            var cmd = query.CreateCommand();

            errors = null;
            schema = null;

            try
            {
                using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                    if (reader.FieldCount <= 0)
                    {
                        return(false);
                    }

                    if (reader is System.Data.Common.IDbColumnSchemaGenerator)
                    {
                        schema = ((System.Data.Common.IDbColumnSchemaGenerator)reader).GetColumnSchema();
                        return(true);
                    }
                    else
                    {
                        throw new NotSupportedException("The interface System.Data.Common.IDbColumnSchemaGenerator is not supported by the underlying data provider.");
                    }
                }
            }
            catch (Exception ex)
            {
                errors = new List <Exception>();
                errors.Add(ex);
                return(false);
            }
        }
Ejemplo n.º 4
0
 public static void ApplySort(IListQueryBuilder listQuery, IDbQueryBuilder queryBuilder)
 {
     if (listQuery.Configuration.QueryDefinition.Order != null)
     {
         foreach (var column in listQuery.Configuration.QueryDefinition.Order)
         {
             if (listQuery.Configuration.Validations.TryGetValue(column.Name, out ListFieldMetadata config) && config.IsSortable == true)
             {
                 queryBuilder.OrderBy.Add(new Csg.Data.Sql.SqlOrderColumn()
                 {
                     ColumnName    = config.Name,
                     SortDirection = column.SortDescending ? Csg.Data.Sql.DbSortDirection.Descending : Csg.Data.Sql.DbSortDirection.Ascending
                 });
             }
             else if (listQuery.Configuration.UseValidation)
             {
                 throw new Exception($"The sort field '{column.Name}' does not exist.");
             }
             else
             {
                 queryBuilder.OrderBy.Add(new Csg.Data.Sql.SqlOrderColumn()
                 {
                     ColumnName    = column.Name,
                     SortDirection = column.SortDescending ? Csg.Data.Sql.DbSortDirection.Descending : Csg.Data.Sql.DbSortDirection.Ascending
                 });
             }
         }
     }
 }
Ejemplo n.º 5
0
        public static void ApplyFilters(IListQueryBuilder listQuery, IDbQueryBuilder queryBuilder)
        {
            if (listQuery.Configuration.QueryDefinition.Filters != null)
            {
                var where = new DbQueryWhereClause(queryBuilder.Root, Csg.Data.Sql.SqlLogic.And);

                //tODO: IsFilterable is not being enforced. Shoult it be?

                foreach (var filter in listQuery.Configuration.QueryDefinition.Filters)
                {
                    var hasConfig = listQuery.Configuration.Validations.TryGetValue(filter.Name, out ListFieldMetadata validationField);

                    if (listQuery.Configuration.Handlers.TryGetValue(filter.Name, out ListQueryFilterHandler handler))
                    {
                        handler(where, filter, validationField);
                    }
                    else if (hasConfig || !listQuery.Configuration.UseValidation)
                    {
                        where.AddFilter(filter.Name, filter.Operator ?? ListFilterOperator.Equal, filter.Value, validationField?.DataType ?? System.Data.DbType.String, validationField?.DataTypeSize);
                    }
                    else if (listQuery.Configuration.UseValidation)
                    {
                        throw new Exception($"No handler is defined for the filter '{filter.Name}'.");
                    }
                }

                if (where.Filters.Count > 0)
                {
                    where.ApplyToQuery(queryBuilder);
                }
            }
        }
Ejemplo n.º 6
0
 /// <summary>
 /// Applies filters to the given query builder.
 /// </summary>
 /// <param name="builder"></param>
 public void ApplyToQuery(IDbQueryBuilder builder)
 {
     if (this.Filters.Count > 0)
     {
         builder.AddFilter(this.Filters);
     }
 }
 /// <summary>
 /// Invokes callbacks registered for the <see cref="AfterApply"/> event
 /// </summary>
 internal protected void OnAfterApply(IDbQueryBuilder queryBuilder)
 {
     if (this.AfterApply != null)
     {
         this.AfterApply(this, new ApplyEventArgs(this, queryBuilder));
     }
 }
Ejemplo n.º 8
0
        /// <summary>
        /// Adds a set of WHERE clause conditions defined by the given expression, grouped by logical OR.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static IDbQueryBuilder WhereAny(this IDbQueryBuilder query, Action <IDbQueryWhereClause> expression)
        {
            query = query.Fork();
            var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);

            expression.Invoke(group);

            query.AddFilter(group.Filters);

            return(query);
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Adds a set of WHERE clause conditions defined by the given expression, grouped by logical AND.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static IDbQueryBuilder Where(this IDbQueryBuilder query, Action <IDbQueryWhereClause> expression)
        {
            query = query.Fork();
            var group = new DbQueryWhereClause(query.Root, SqlLogic.And);

            expression.Invoke(group);

            group.ApplyToQuery(query);

            return(query);
        }
Ejemplo n.º 10
0
 public static void ApplyLimit(IListQueryBuilder listQuery, IDbQueryBuilder queryBuilder)
 {
     if (listQuery.Configuration.QueryDefinition.Limit > 0)
     {
         queryBuilder.PagingOptions = new Csg.Data.Sql.SqlPagingOptions()
         {
             Limit  = listQuery.Configuration.UseLimitOracle && !listQuery.Configuration.UseStreamingResult ? listQuery.Configuration.QueryDefinition.Limit + 1 : listQuery.Configuration.QueryDefinition.Limit,
             Offset = listQuery.Configuration.QueryDefinition.Offset
         };
     }
 }
Ejemplo n.º 11
0
        /// <summary>
        /// Sets the execution command timeout for the query.
        /// </summary>
        /// <param name="query">The query builder instance</param>
        /// <param name="timeout">The timeout value in seconds.</param>
        /// <returns></returns>
        public static IDbQueryBuilder Timeout(this IDbQueryBuilder query, int timeout)
        {
            if (timeout <= 0)
            {
                throw new ArgumentException(ErrorMessage.TimeoutValueMustBeGreater, nameof(timeout));
            }

            var fork = query.Fork();

            fork.CommandTimeout = timeout;
            return(fork);
        }
Ejemplo n.º 12
0
        /// <summary>
        /// Executes the query and returns the first column from the first row in the result set, casted to the given type.
        /// </summary>
        /// <param name="query">The query builder instance.</param>
        public static T ExecuteScalar <T>(this IDbQueryBuilder query) where T : struct
        {
            var value = query.ExecuteScalar();

            if (value is T)
            {
                return((T)value);
            }
            else
            {
                return(default(T));
            }
        }
Ejemplo n.º 13
0
        /// <summary>
        /// Sets the columns that will be selected on the given query builder and replaces any existing selections.
        /// </summary>
        /// <param name="queryBuilder"></param>
        /// <param name="columns"></param>
        /// <remarks>This method replaces any existing selected columns on the query builder.</remarks>
        /// <returns></returns>
        public static IDbQueryBuilder SelectOnly(this IDbQueryBuilder queryBuilder, params ISqlColumn[] columns)
        {
            var query = queryBuilder.Fork();

            query.SelectColumns.Clear();

            foreach (var col in columns)
            {
                query.SelectColumns.Add(col);
            }

            return(query);
        }
Ejemplo n.º 14
0
        /// <summary>
        /// Adds ORDER BY fields to a query for descending sort.
        /// </summary>
        /// <param name="query">The query builder instance</param>
        /// <param name="fields">A set of field expressions to order the query by.</param>
        /// <returns></returns>
        public static IDbQueryBuilder OrderByDescending(this IDbQueryBuilder query, params string[] fields)
        {
            var fork = query.Fork();

            foreach (var field in fields)
            {
                fork.OrderBy.Add(new Csg.Data.Sql.SqlOrderColumn()
                {
                    ColumnName = field, SortDirection = Csg.Data.Sql.DbSortDirection.Descending
                });
            }
            return(fork);
        }
Ejemplo n.º 15
0
 /// <summary>
 /// Creates a <see cref="ListQueryBuilder"/> from the given query builder and query definition.
 /// </summary>
 /// <param name="queryBuilder"></param>
 /// <param name="queryDefinition"></param>
 /// <returns></returns>
 public static IListQueryBuilder Create(IDbQueryBuilder queryBuilder, ListQueryDefinition queryDefinition)
 {
     return(new ListQueryBuilder()
     {
         Configuration = new ListQueryBuilderConfiguration()
         {
             QueryBuilder = queryBuilder,
             DataAdapter = new DapperListDataAdapter(queryBuilder.Connection, queryBuilder.Transaction),
             QueryDefinition = queryDefinition,
             Validations = new Dictionary <string, ListFieldMetadata>(StringComparer.OrdinalIgnoreCase),
             Handlers = new Dictionary <string, ListQueryFilterHandler>(StringComparer.OrdinalIgnoreCase)
         }
     });
 }
Ejemplo n.º 16
0
        /// <summary>
        /// Adds an arbitrary parameter to the resulting query.
        /// </summary>
        /// <param name="query">The query builder instance</param>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <param name="dbType">The data type of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <returns></returns>
        public static IDbQueryBuilder AddParameter(this IDbQueryBuilder query, string name, object value, DbType dbType, int?size = null)
        {
            var fork = query.Fork();

            fork.Parameters.Add(new DbParameterValue()
            {
                ParameterName = name,
                DbType        = dbType,
                Value         = value,
                Size          = size
            });

            return(fork);
        }
Ejemplo n.º 17
0
        /// <summary>
        /// 失败表示存在更新影响条数为0的update
        /// </summary>
        public override bool UpdateAll <T>(DbSession session, IDbQueryBuilder queryBuilder)
        {
            bool      result  = true;
            DbCommand command = session.CreateCommand();

            foreach (var updateBuilder in queryBuilder.UpdateBuilders)
            {
                command.CommandText = updateBuilder.ToQueryString(session, new T().TableName);
                updateBuilder.AddParameter(command, session);
                WriteQueryLog(command, session);
                result = result && session.ExecuteNonQuery(command) > 0;
            }
            return(result);
        }
Ejemplo n.º 18
0
        /// <summary>
        /// 返回 全部新增都成功
        /// </summary>
        public override bool InsertAll <T>(IDbQueryBuilder queryBuilder)
        {
            bool      result    = true;
            DbCommand command   = Session.CreateCommand();
            string    tableName = new T().TableName;

            foreach (var insertBuilder in queryBuilder.InsertBuilders)
            {
                command.CommandText = insertBuilder.ToQueryString(Session, tableName);
                insertBuilder.AddParameter(command, Session);
                WriteQueryLog(command, Session);
                result = result && Session.ExecuteNonQuery(command) > 0;
            }
            return(result);
        }
Ejemplo n.º 19
0
        /// <summary>
        /// Adds limit or offset conditions to the query.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="limit">The maximum number of rows to return.</param>
        /// <param name="offset">The zero-based index of the first row to return.</param>
        /// <returns></returns>
        public static IDbQueryBuilder Limit(this IDbQueryBuilder query, int limit = 0, int offset = 0)
        {
            if (query.OrderBy.Count <= 0)
            {
                throw new InvalidOperationException(ErrorMessage.LimitOrOffsetWithoutOrderBy);
            }

            query = query.Fork();
            query.PagingOptions = new SqlPagingOptions()
            {
                Limit  = limit,
                Offset = offset
            };
            return(query);
        }
Ejemplo n.º 20
0
        /// <summary>
        /// Adds the given fields to the selection list, optionally replacing existing selections.
        /// </summary>
        /// <param name="query">The query builder instance.</param>
        /// <param name="fields">A set of field names to select.</param>
        /// <param name="replace">If true, replaces any existing selections, if false, adds to existing selectoins.</param>
        /// <returns></returns>
        public static IDbQueryBuilder Select(this IDbQueryBuilder query, IEnumerable <ISqlColumn> fields, bool replace = false)
        {
            var fork = query.Fork();

            if (replace)
            {
                query.SelectColumns.Clear();
            }

            foreach (var field in fields)
            {
                fork.SelectColumns.Add(field);
            }

            return(fork);
        }
Ejemplo n.º 21
0
        /// <summary>
        /// Adds a set of WHERE clause conditions by looping over the given collection, and then joining them together with the given logic.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="collection"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static IDbQueryBuilder WhereAny <TItem>(this IDbQueryBuilder query, IEnumerable <TItem> collection, Action <IDbQueryWhereClause, TItem> expression)
        {
            query = query.Fork();
            var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);

            foreach (var item in collection)
            {
                var innerGroup = new DbQueryWhereClause(query.Root, SqlLogic.And);
                expression.Invoke(innerGroup, item);
                group.AddFilter(innerGroup.Filters);
            }

            query.AddFilter(group.Filters);

            return(query);
        }
Ejemplo n.º 22
0
        /// <summary>
        /// Adds a set of WHERE clause conditions by looping over the given collection, and then joining them together with the given logic.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="list"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static IDbQueryBuilder WhereAny <TItem>(this IDbQueryBuilder query, IList <TItem> list, Action <IDbQueryWhereClause, TItem, int> expression)
        {
            query = query.Fork();
            var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);

            for (var i = 0; i < list.Count; i++)
            {
                var innerGroup = new DbQueryWhereClause(query.Root, SqlLogic.And);
                expression.Invoke(innerGroup, list[i], i);
                group.AddFilter(innerGroup.Filters);
            }

            query.AddFilter(group.Filters);

            return(query);
        }
Ejemplo n.º 23
0
 public static void ApplySelections(IListQueryBuilder listQuery, IDbQueryBuilder queryBuilder)
 {
     if (listQuery.Configuration.QueryDefinition.Fields != null)
     {
         foreach (var column in listQuery.Configuration.QueryDefinition.Fields)
         {
             if (listQuery.Configuration.Validations.TryGetValue(column, out ListFieldMetadata config))
             {
                 queryBuilder.SelectColumns.Add(new Csg.Data.Sql.SqlColumn(queryBuilder.Root, config.Name));
             }
             else if (listQuery.Configuration.UseValidation)
             {
                 throw new Exception($"The selection field '{column}' does not exist.");
             }
             else
             {
                 queryBuilder.SelectColumns.Add(new Csg.Data.Sql.SqlColumn(queryBuilder.Root, column));
             }
         }
     }
 }
Ejemplo n.º 24
0
 /// <summary>
 /// Sets the columns that will be selected on the given query builder and replaces any existing selections.
 /// </summary>
 /// <param name="queryBuilder"></param>
 /// <param name="columns"></param>
 /// <remarks>This method replaces any existing selected columns on the query builder.</remarks>
 /// <returns></returns>
 public static IDbQueryBuilder SelectOnly(this IDbQueryBuilder queryBuilder, params string[] columns)
 {
     return(SelectOnly(queryBuilder, queryBuilder.Root.Columns(columns).ToArray()));
 }
Ejemplo n.º 25
0
 public static IListQueryBuilder ListQuery(this IDbQueryBuilder queryBuilder, ListQueryDefinition queryDef)
 {
     return(Csg.ListQuery.Sql.ListQueryBuilder.Create(queryBuilder, queryDef));
 }
Ejemplo n.º 26
0
        /// <summary>
        /// 失败表示存在更新影响条数为0的update
        /// </summary>
        public override bool Update <T>(DbSession session, IDbQueryBuilder queryBuilder)
        {
            var updateBuilder = queryBuilder.UpdateBuilders.First();

            return(Update <T>(session, updateBuilder));
        }
Ejemplo n.º 27
0
        /// <summary>
        /// 失败表示影响数据为0
        /// </summary>
        public override bool Delete <T>(DbSession session, IDbQueryBuilder queryBuilder)
        {
            var deleteBuilder = queryBuilder.DeleteBuilder;

            return(Delete <T>(session, deleteBuilder));
        }
Ejemplo n.º 28
0
 /// 组合查询
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="session"></param>
 /// <param name="queryBuilder"></param>
 /// <returns></returns>
 public override List <T> SelectUnion <T>(DbSession session, IDbQueryBuilder queryBuilder)
 {
     throw new NotImplementedException();
 }
Ejemplo n.º 29
0
        /// <summary>
        /// 失败返回 new List<T>()
        /// </summary>
        public override List <T> SelectAll <T>(DbSession session, IDbQueryBuilder queryBuilder)
        {
            SelectBuilder selectBuilder = queryBuilder.SelectBuilders.First();

            return(SelectAll <T>(session, selectBuilder));
        }
Ejemplo n.º 30
0
        /// <summary>
        /// 返回 是否成功新增
        /// </summary>
        public override bool Insert <T>(DbSession session, IDbQueryBuilder queryBuilder)
        {
            var insertBuilder = queryBuilder.InsertBuilders.First();

            return(Insert <T>(session, insertBuilder));
        }