/// <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); } }
/// <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); }
/// <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); } }
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 }); } } } }
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); } } }
/// <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)); } }
/// <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); }
/// <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); }
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 }; } }
/// <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); }
/// <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)); } }
/// <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); }
/// <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); }
/// <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) } }); }
/// <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); }
/// <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); }
/// <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); }
/// <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); }
/// <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); }
/// <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); }
/// <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); }
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)); } } } }
/// <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())); }
public static IListQueryBuilder ListQuery(this IDbQueryBuilder queryBuilder, ListQueryDefinition queryDef) { return(Csg.ListQuery.Sql.ListQueryBuilder.Create(queryBuilder, queryDef)); }
/// <summary> /// 失败表示存在更新影响条数为0的update /// </summary> public override bool Update <T>(DbSession session, IDbQueryBuilder queryBuilder) { var updateBuilder = queryBuilder.UpdateBuilders.First(); return(Update <T>(session, updateBuilder)); }
/// <summary> /// 失败表示影响数据为0 /// </summary> public override bool Delete <T>(DbSession session, IDbQueryBuilder queryBuilder) { var deleteBuilder = queryBuilder.DeleteBuilder; return(Delete <T>(session, deleteBuilder)); }
/// 组合查询 /// </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(); }
/// <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)); }
/// <summary> /// 返回 是否成功新增 /// </summary> public override bool Insert <T>(DbSession session, IDbQueryBuilder queryBuilder) { var insertBuilder = queryBuilder.InsertBuilders.First(); return(Insert <T>(session, insertBuilder)); }