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