private static void BuildGetTableKeysQuery(ref IStatementBuilder pStatement, string pTableName) { pStatement.Append("select distinct isc.table_name "); pStatement.Append(", isc.COLUMN_NAME as 'Name' "); pStatement.Append(", upper(isc.DATA_TYPE) AS 'Type' "); pStatement.Append(", CASE WHEN isc.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND isc.DATA_TYPE not in ('TEXT','NTEXT','IMAGE') THEN CASE WHEN CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ='-1' THEN '-2' ELSE ISNULL(NULLIF(CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),''),-1) END ELSE '-1' END as 'Length'"); pStatement.Append(", col.PREC as 'Precision'"); pStatement.Append(", col.SCALE as 'Scale'"); pStatement.Append(", col.ISNULLABLE as 'Nullable'"); pStatement.Append(", ISNULL(ids.is_identity,0) as IsIdentity"); pStatement.Append(", max(CASE WHEN rc.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END) AS IsForeign "); pStatement.Append(", max(CASE WHEN cu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END) AS IsPrimary "); pStatement.Append("from INFORMATION_SCHEMA.COLUMNS isc "); pStatement.Append("inner join sys.objects obj on obj.name = isc.TABLE_NAME and obj.type = 'U' "); pStatement.Append("inner join syscolumns col on col.name = isc.COLUMN_NAME and obj.object_id = col.id "); pStatement.Append("left outer join sys.identity_columns ids on ids.name = isc.COLUMN_NAME and obj.object_id = ids.object_id "); pStatement.Append("left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.table_name = isc.table_name and tc.constraint_type = 'PRIMARY KEY' "); pStatement.Append("left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu on cu.constraint_name = tc.constraint_name and cu.column_name = isc.COLUMN_NAME "); pStatement.Append("left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on cc.table_name = isc.table_name and cc.column_name = isc.column_name "); pStatement.Append("left outer join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on cc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME "); pStatement.Append("where isc.TABLE_NAME = @TableName "); pStatement.Append("group by isc.table_name "); pStatement.Append(", isc.COLUMN_NAME "); pStatement.Append(", upper(isc.DATA_TYPE) "); pStatement.Append(", CASE WHEN isc.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND isc.DATA_TYPE not in ('TEXT','NTEXT','IMAGE') THEN CASE WHEN CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ='-1' THEN '-2' ELSE ISNULL(NULLIF(CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),''),-1) END ELSE '-1' END "); pStatement.Append(", col.ISNULLABLE "); pStatement.Append(", ISNULL(ids.is_identity,0) "); pStatement.Append(", col.PREC "); pStatement.Append(", col.SCALE "); pStatement.Append("order by isc.COLUMN_NAME "); pStatement.AddParameter("TableName", pTableName, DbType.String); }
private static void BuildGetViewsQuery(ref IStatementBuilder pStatement) { pStatement.Append("select obj.name as Name, 'View' as Type "); pStatement.Append("from sys.objects obj "); pStatement.Append("inner join sys.schemas sch on obj.schema_id = sch.schema_id "); pStatement.Append("where type = 'V' "); //pStatement.Append("and sch.name = 'dbo'"); }
private void BuildInsertQuery(ref IStatementBuilder pStatement, DateTime startDate, ELogLevel maxLevel, string title, string data) { pStatement.Append($"insert into {targetTable}({targetDateColumn},{targetLevelColumn},{targetTitleColumn},{targetDataColumn})"); pStatement.Append($"values(@{targetDateColumn},@{targetLevelColumn},@{targetTitleColumn},@{targetDataColumn})"); pStatement.AddParameter(targetDateColumn, startDate, DbType.DateTime); pStatement.AddParameter(targetDateColumn, maxLevel, DbType.String); pStatement.AddParameter(targetDateColumn, title, DbType.String); pStatement.AddParameter(targetDateColumn, data, DbType.String); }
private void BuildInsertQuery(ref IStatementBuilder pStatement, DateTime date, ELogLevel level, string data,string tag,string memberName = null, string filePath = null, int lineNumber = 0) { pStatement.Append($"insert into {targetTable}({targetDateColumn},{targetLevelColumn},{targetDataColumn},{targetTagColumn},{targetMemberNameColumn},{targetFilePathColumn},{targetLineNumberColumn})"); pStatement.Append($"values(@{targetDateColumn},@{targetLevelColumn},@{targetDataColumn},@{targetTagColumn},@{targetMemberNameColumn},@{targetFilePathColumn},@{targetLineNumberColumn})"); pStatement.AddParameter(targetDateColumn, date, DbType.DateTime); pStatement.AddParameter(targetLevelColumn, level, DbType.String); pStatement.AddParameter(targetDataColumn, data, DbType.String); pStatement.AddParameter(targetTagColumn, tag, DbType.String); pStatement.AddParameter(targetMemberNameColumn, memberName, DbType.String); pStatement.AddParameter(targetFilePathColumn, filePath, DbType.String); pStatement.AddParameter(targetLineNumberColumn, lineNumber, DbType.Int32); }
protected virtual IResult Execute(IStatementBuilder statement) { try { return _dataContext.Execute(statement); } catch (Exception ex) { _logger.Exception(statement.ToString(), ex, "sql"); throw; } }
public IResult CacheExecute(IStatementBuilder statement, TimeSpan duration) { if (statement.Type == QueryType.Delete || statement.Type == QueryType.Update || statement.Type == QueryType.Insert) { throw new System.Exception("Cache cannot be used with insert, update or delete query"); } else { string cacheKey = statement.ToString(); return _cacheService.Get(cacheKey, statement, Execute, duration); } }
public IResult CacheExecute(IStatementBuilder statement, Func<string, IResult, bool> expirationCallback) { if (statement.Type == QueryType.Delete || statement.Type == QueryType.Update || statement.Type == QueryType.Insert) { throw new System.Exception("Cache cannot be used with insert, update or delete query"); } else { string cacheKey = statement.ToString(); return _cacheService.Get(cacheKey, statement, expirationCallback, Execute); } }
protected virtual IResult CacheExecute(IStatementBuilder statement, TimeSpan duration) { try { return _dataContext.CacheExecute(statement, duration); } catch (Exception ex) { _logger.Exception(statement.ToString(), ex, "sql;cache"); throw; } }
protected virtual IResult CacheExecute(IStatementBuilder statement, Func<string, IResult, bool> expirationCallback) { try { return _dataContext.CacheExecute(statement, expirationCallback); } catch (Exception ex) { _logger.Exception(statement.ToString(), ex, "sql;cache"); throw; } }
public IResult Execute(IStatementBuilder statement) { IResponse response = null; try { using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); IDbCommand cmd = statement.Command; cmd.Connection = connection; cmd.CommandTimeout = _connectionTimeout; switch (statement.Type) { case QueryType.Insert: var sc = cmd.ExecuteScalar(); response = new SqlResponse(sc, statement.Type); break; case QueryType.Update: case QueryType.Delete: cmd.ExecuteNonQuery(); response = new SqlResponse(null, statement.Type); break; case QueryType.SelectSingleValue: var sr = cmd.ExecuteScalar(); response = new SqlResponse(sr, QueryType.SelectSingleValue); break; case QueryType.SelectSingleRow: var sdr = cmd.ExecuteReader(CommandBehavior.SingleRow); response = new SqlResponse(sdr, QueryType.SelectSingleRow); break; case QueryType.SelectMultipleRows: var mdr = cmd.ExecuteReader(CommandBehavior.Default); response = new SqlResponse(mdr, QueryType.SelectMultipleRows); break; } connection.Close(); } } catch (SqlException) { throw; } catch (System.Exception e) { throw new MappingException(e.Message, e.InnerException); } return new SqlResult(_mapper, response); }
private void BuildGetAllStoredProcedureParametersQuery(ref IStatementBuilder pStatement, string name) { pStatement.Append("SELECT "); pStatement.Append("replace(P.name,'@','') as Name, "); pStatement.Append("TYPE_NAME(P.user_type_id) as Type, "); pStatement.Append("P.max_length as Size, "); pStatement.Append("P.precision as Precision, "); pStatement.Append("P.scale as Scale "); pStatement.Append("FROM sys.objects AS SO "); pStatement.Append("INNER JOIN sys.parameters AS P "); pStatement.Append("ON SO.OBJECT_ID = P.OBJECT_ID "); pStatement.Append("WHERE SO.OBJECT_ID IN(SELECT OBJECT_ID "); pStatement.Append("FROM sys.objects "); pStatement.Append("WHERE TYPE IN('P', 'FN')) "); pStatement.Append("AND SO.name = @name "); pStatement.Append("ORDER BY SO.name, P.parameter_id "); pStatement.AddParameter("name",name,DbType.String); }
public MethodBodyBuilder(IMetadataHost host, IEnumerable<IParameterDefinition> parameters) { reflector = new UnitReflector(host); locals = new LocalVariableBindings(reflector); define = new DefinitionBuilder(reflector, locals, host.NameTable); create = new InstanceCreator(reflector, locals); declare = new DeclarationBuilder(define); call = new MethodCallBuilder(host, reflector, locals); changeType = new Converter(reflector); operators = new TypeOperatorBuilder(reflector); constant = new CompileTimeConstantBuilder(reflector); @if = new IfStatementBuilder(); @return = new CodeReturnStatementBuilder(); anonymousMethod = new AnonymousMethodTypeOptions(host, reflector); statement = new StatementBuilder(); @params = new ParameterBindings(); foreach (var parameter in parameters) { @params.AddBinding(parameter); } }
/// <summary> /// Creates a new instance of <see cref="BaseRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="statementBuilder">The statement builder.</param> public BaseRequest(Type entityType, IDbConnection connection, IStatementBuilder statementBuilder = null) { EntityType = entityType; Connection = connection; StatementBuilder = statementBuilder; }
private static void BuildGetAllQuery(ref IStatementBuilder pStatement) { pStatement.Append("select ROUTINE_NAME as Name from information_schema.routines "); pStatement.Append("where routine_type = 'PROCEDURE'"); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="batchSize"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <returns></returns> public static async Task <InsertAllExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection, string tableName, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) where TEntity : class { var key = GetKey <TEntity>(tableName, fields, batchSize, hints); // Get from cache var context = InsertAllExecutionContextCache.Get <TEntity>(key); if (context != null) { return(context); } // Create var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction); context = CreateInternal <TEntity>(connection, tableName, dbFields, batchSize, fields, hints, transaction, statementBuilder); // Add to cache InsertAllExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
/// <summary> /// Inserts a new data in the database. /// </summary> /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam> /// <typeparam name="TResult">The target type of the result.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table to be used.</param> /// <param name="entity">The data entity or dynamic object to be inserted.</param> /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <param name="skipIdentityCheck">True to skip the identity check.</param> /// <returns>The value of the identity field if present, otherwise, the value of primary field.</returns> internal static TResult InsertInternalBase <TEntity, TResult>(this IDbConnection connection, string tableName, TEntity entity, IEnumerable <Field> fields = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null, bool skipIdentityCheck = false) where TEntity : class { // Variables needed var dbSetting = connection.GetDbSetting(); // Get the function var callback = new Func <InsertExecutionContext <TEntity> >(() => { // Variables needed var identity = (Field)null; var dbFields = DbFieldCache.Get(connection, tableName, transaction); var inputFields = (IEnumerable <DbField>)null; var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity); // Set the identity field if (skipIdentityCheck == false) { identity = IdentityCache.Get <TEntity>()?.AsField(); if (identity == null && identityDbField != null) { identity = FieldCache.Get <TEntity>().FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), identityDbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)); } } // Filter the actual properties for input fields inputFields = dbFields? .Where(dbField => dbField.IsIdentity == false) .Where(dbField => fields.FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) != null) .AsList(); // Variables for the entity action var identityPropertySetter = (Action <TEntity, object>)null; // Get the identity setter if (skipIdentityCheck == false && identity != null) { identityPropertySetter = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity); } // Identify the requests var insertRequest = (InsertRequest)null; // Create a different kind of requests if (typeof(TEntity) == typeof(object)) { insertRequest = new InsertRequest(tableName, connection, transaction, fields, statementBuilder); } else { insertRequest = new InsertRequest(typeof(TEntity), connection, transaction, fields, statementBuilder); } // Return the value return(new InsertExecutionContext <TEntity> { CommandText = CommandTextCache.GetInsertText(insertRequest), InputFields = inputFields, ParametersSetterFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, ".", tableName, ".Insert"), inputFields?.AsList(), null, dbSetting), IdentityPropertySetterFunc = identityPropertySetter }); }); // Get the context var context = InsertExecutionContextCache <TEntity> .Get(tableName, fields, callback); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(context.CommandText, entity, null); trace.BeforeInsert(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(context.CommandText); } return(default(TResult)); } context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText); entity = (TEntity)(cancellableTraceLog.Parameter ?? entity); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Execution variables var result = default(TResult); // Create the command using (var command = (DbCommand)connection.EnsureOpen().CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Set the values context.ParametersSetterFunc(command, entity); // Actual Execution result = ObjectConverter.ToType <TResult>(command.ExecuteScalar()); // Get explicity if needed if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false) { result = ObjectConverter.ToType <TResult>(connection.GetDbHelper().GetScopeIdentity(connection, transaction)); } // Set the return value if (Equals(result, default(TResult)) == false) { context.IdentityPropertySetterFunc?.Invoke(entity, result); } } // After Execution if (trace != null) { trace.AfterInsert(new TraceLog(context.CommandText, entity, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="entities"></param> /// <param name="tableName"></param> /// <param name="qualifiers"></param> /// <param name="batchSize"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <MergeAllExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection, IEnumerable <TEntity> entities, string tableName, IEnumerable <Field> qualifiers, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null, CancellationToken cancellationToken = default) where TEntity : class { var key = GetKey <TEntity>(tableName, qualifiers, fields, batchSize, hints); // Get from cache var context = MergeAllExecutionContextCache.Get <TEntity>(key); if (context != null) { return(context); } // Create var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var commandText = (string)null; // Create a different kind of requests if (batchSize > 1) { var request = new MergeAllRequest(tableName, connection, transaction, fields, qualifiers, batchSize, hints, statementBuilder); commandText = await CommandTextCache.GetMergeAllTextAsync(request, cancellationToken); } else { var request = new MergeRequest(tableName, connection, transaction, fields, qualifiers, hints, statementBuilder); commandText = await CommandTextCache.GetMergeTextAsync(request, cancellationToken); } // Call context = CreateInternal <TEntity>(connection, entities, dbFields, tableName, qualifiers, batchSize, fields, commandText); // Add to cache MergeAllExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <InsertExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection, string tableName, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null, CancellationToken cancellationToken = default) where TEntity : class { var key = GetKey <TEntity>(tableName, fields, hints); // Get from cache var context = InsertExecutionContextCache.Get <TEntity>(key); if (context != null) { return(context); } // Create var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var request = new InsertRequest(tableName, connection, transaction, fields, hints, statementBuilder); var commandText = await CommandTextCache.GetInsertTextAsync(request, cancellationToken); // Call context = CreateInternal <TEntity>(connection, dbFields, tableName, fields, commandText); // Add to cache InsertExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
/// <summary> /// Throws an exception of the builder is not defined. /// </summary> /// <param name="connection">The connection object to identified.</param> /// <param name="builder">The builder to be checked.</param> /// <returns>The instance of available statement builder.</returns> private static IStatementBuilder EnsureStatementBuilder(IDbConnection connection, IStatementBuilder builder) { builder = builder ?? StatementBuilderMapper.Get(connection.GetProvider())?.StatementBuilder; if (builder == null) { throw new InvalidOperationException("There is no 'IStatementBuilder' object defined. Please visit your mapping and make sure to map a 'Provider' to a correct 'IStatementBuilder' object."); } return(builder); }
/// <summary> /// Query all the data from the database. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="fields">The list of fields to be queried.</param> /// <param name="orderBy">The order definition of the fields to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="cacheKey"> /// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database. /// This will only work if the 'cache' argument is set. /// </param> /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="cache">The cache object to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>An enumerable list of data entity object.</returns> internal static async Task <IEnumerable <dynamic> > QueryAllAsyncInternalBase(this IDbConnection connection, string tableName, IEnumerable <Field> fields = null, IEnumerable <OrderField> orderBy = null, string hints = null, string cacheKey = null, int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes, int?commandTimeout = null, IDbTransaction transaction = null, ICache cache = null, ITrace trace = null, IStatementBuilder statementBuilder = null) { // Get Cache if (cacheKey != null) { var item = cache?.Get <dynamic>(cacheKey, false); if (item != null) { return(item.Value); } } // Check the fields if (fields?.Any() != true) { fields = (await DbFieldCache.GetAsync(connection, tableName, transaction))?.AsFields(); } // Variables var commandType = CommandType.Text; var request = new QueryAllRequest(tableName, connection, transaction, fields, orderBy, hints, statementBuilder); var commandText = CommandTextCache.GetQueryAllText(request); var param = (object)null; // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(commandText, param, null); trace.BeforeQueryAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(null); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = await ExecuteQueryAsyncInternal(connection : connection, commandText : commandText, param : param, commandType : commandType, commandTimeout : commandTimeout, transaction : transaction, tableName : tableName, skipCommandArrayParametersCheck : true); // After Execution if (trace != null) { trace.AfterQueryAll(new TraceLog(commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Set Cache if (cacheKey != null) { cache?.Add(cacheKey, result, cacheItemExpiration, false); } // Result return(result); }
private void WriteInsertsWithStatementBuilder(TextWriter writer, IStatementBuilder statementBuilder) { writer.WriteLine($"-- INSERTS FOR: {statementBuilder.Identifier()}"); this.ReadAndReplace(writer, statementBuilder); writer.WriteLine(string.Empty); }
/// <summary> /// Query all the data from the table in an asynchronous way. /// </summary> /// <typeparam name="TEntity">The type of the data entity.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param> /// <param name="orderBy">The order definition of the fields to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="cacheKey"> /// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database. /// This will only work if the 'cache' argument is set. /// </param> /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="cache">The cache object to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>An enumerable list of data entity objects.</returns> internal static async Task <IEnumerable <TEntity> > QueryAllAsyncInternal <TEntity>(this IDbConnection connection, string tableName, IEnumerable <Field> fields = null, IEnumerable <OrderField> orderBy = null, string hints = null, string cacheKey = null, int?cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes, int?commandTimeout = null, IDbTransaction transaction = null, ICache cache = null, ITrace trace = null, IStatementBuilder statementBuilder = null) where TEntity : class { // Ensure the fields fields = GetQualifiedFields <TEntity>(fields) ?? (await DbFieldCache.GetAsync(connection, tableName, transaction))?.AsFields(); // Return return(await QueryAllAsyncInternalBase <TEntity>(connection : connection, tableName : tableName, fields : fields, orderBy : orderBy, hints : hints, cacheKey : cacheKey, cacheItemExpiration : cacheItemExpiration, commandTimeout : commandTimeout, transaction : transaction, cache : cache, trace : trace, statementBuilder : statementBuilder)); }
/// <summary> /// Creates a mapping between the <see cref="Provider"/> and <see cref="IStatementBuilder"/>) object. /// </summary> /// <param name="provider">The target provider.</param> /// <param name="statementBuilder">The statement builder to be mapped.</param> /// <param name="force">True if to overwrite existing mapping if present.</param> public static void Map(Provider provider, IStatementBuilder statementBuilder, bool force = false) { Map(new StatementBuilderMapItem(provider, statementBuilder)); }
/// <summary> /// Creates a new instance of <see cref="UpdateRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="where">The query expression.</param> /// <param name="statementBuilder">The statement builder.</param> public UpdateRequest(Type entityType, IDbConnection connection, QueryGroup where = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Where = where; }
/// <summary> /// /// </summary> /// <param name="entityType"></param> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="batchSize"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <InsertAllExecutionContext> CreateAsync(Type entityType, IDbConnection connection, string tableName, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null, CancellationToken cancellationToken = default) { var key = GetKey(entityType, tableName, fields, batchSize, hints); // Get from cache var context = InsertAllExecutionContextCache.Get(key); if (context != null) { return(context); } // Create var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var commandText = (string)null; // Create a different kind of requests if (batchSize > 1) { var request = new InsertAllRequest(tableName, connection, transaction, fields, batchSize, hints, statementBuilder); commandText = await CommandTextCache.GetInsertAllTextAsync(request, cancellationToken); } else { var request = new InsertRequest(tableName, connection, transaction, fields, hints, statementBuilder); commandText = await CommandTextCache.GetInsertTextAsync(request, cancellationToken); } // Call context = CreateInternal(entityType, connection, tableName, dbFields, batchSize, fields, commandText); // Add to cache InsertAllExecutionContextCache.Add(key, context); // Return return(context); }
/// <summary> /// Query all the data from the database in an asynchronous way. /// </summary> /// <typeparam name="TEntity">The type of the data entity.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="orderBy">The order definition of the fields to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="cacheKey"> /// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database. /// This will only work if the 'cache' argument is set. /// </param> /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="cache">The cache object to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>An enumerable list of data entity object.</returns> internal static Task <IEnumerable <TEntity> > QueryAllAsyncInternal <TEntity>(this IDbConnection connection, IEnumerable <OrderField> orderBy = null, string hints = null, string cacheKey = null, int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes, int?commandTimeout = null, IDbTransaction transaction = null, ICache cache = null, ITrace trace = null, IStatementBuilder statementBuilder = null) where TEntity : class { return(QueryAllAsyncInternalBase <TEntity>(connection: connection, orderBy: orderBy, hints: hints, cacheKey: cacheKey, cacheItemExpiration: cacheItemExpiration, commandTimeout: commandTimeout, transaction: transaction, cache: cache, trace: trace, statementBuilder: statementBuilder)); }
/// <summary> /// Creates a new instance of <see cref="InlineUpdateRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="where">The query expression.</param> /// <param name="fields">The list of the target fields.</param> /// <param name="statementBuilder">The statement builder.</param> public InlineUpdateRequest(Type entityType, IDbConnection connection, QueryGroup where = null, IEnumerable <Field> fields = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Where = where; Fields = fields; }
/// <summary> /// Query all the data from the database. /// </summary> /// <typeparam name="TEntity">The type of the data entity.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="orderBy">The order definition of the fields to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="cacheKey"> /// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database. /// This will only work if the 'cache' argument is set. /// </param> /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="cache">The cache object to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>An enumerable list of data entity object.</returns> internal static IEnumerable <TEntity> QueryAllInternalBase <TEntity>(this IDbConnection connection, IEnumerable <OrderField> orderBy = null, string hints = null, string cacheKey = null, int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes, int?commandTimeout = null, IDbTransaction transaction = null, ICache cache = null, ITrace trace = null, IStatementBuilder statementBuilder = null) where TEntity : class { // Get Cache if (cacheKey != null) { var item = cache?.Get <IEnumerable <TEntity> >(cacheKey, false); if (item != null) { return(item.Value); } } // Variables var commandType = CommandType.Text; var request = new QueryAllRequest(typeof(TEntity), connection, transaction, FieldCache.Get <TEntity>(), orderBy, hints, statementBuilder); var commandText = CommandTextCache.GetQueryAllText(request); var param = (object)null; // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(commandText, param, null); trace.BeforeQueryAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(null); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = ExecuteQueryInternal <TEntity>(connection: connection, commandText: commandText, param: param, commandType: commandType, commandTimeout: commandTimeout, transaction: transaction, skipCommandArrayParametersCheck: true); // After Execution if (trace != null) { trace.AfterQueryAll(new TraceLog(commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Set Cache if (cacheKey != null) { cache?.Add(cacheKey, result, cacheItemExpiration, false); } // Result return(result); }
/// <summary> /// Insert multiple rows in the table in an asynchronous way. /// </summary> /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table to be used.</param> /// <param name="entities">The list of data entity or dynamic objects to be inserted.</param> /// <param name="batchSize">The batch size of the insertion.</param> /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>The number of inserted rows in the table.</returns> internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection, string tableName, IEnumerable <TEntity> entities, int batchSize = Constant.DefaultBatchOperationSize, IEnumerable <Field> fields = null, string hints = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null) where TEntity : class { // Variables needed var dbSetting = connection.GetDbSetting(); // Guard the parameters ThrowIfNullOrEmpty(entities); // Validate the batch size batchSize = (dbSetting.IsMultiStatementExecutable == true) ? Math.Min(batchSize, entities.Count()) : 1; // Get the context var context = InsertAllExecutionContextProvider.Create <TEntity>(connection, tableName, batchSize, fields, hints, transaction, statementBuilder); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entities, null); trace.BeforeInsertAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(context.CommandText); } return(0); } context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText); entities = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Execution variables var result = 0; // Make sure to create transaction if there is no passed one var hasTransaction = (transaction != null || Transaction.Current != null); try { // Ensure the connection is open await connection.EnsureOpenAsync(); if (hasTransaction == false) { // Create a transaction transaction = connection.BeginTransaction(); } // Create the command using (var command = (DbCommand)connection.CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Directly execute if the entities is only 1 (performance) if (context.BatchSize == 1) { foreach (var entity in entities.AsList()) { // Set the values context.SingleDataEntityParametersSetterFunc?.Invoke(command, entity); // Prepare the command if (dbSetting.IsPreparable) { command.Prepare(); } // Actual Execution var returnValue = Converter.DbNullToNull(await command.ExecuteScalarAsync()); // Get explicity if needed if (Equals(returnValue, null) == true && dbSetting.IsMultiStatementExecutable == false) { returnValue = Converter.DbNullToNull(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction)); } // Set the return value if (returnValue != null) { context.IdentityPropertySetterFunc?.Invoke(entity, returnValue); } // Iterate the result result++; } } else { foreach (var batchEntities in entities.AsList().Split(batchSize)) { var batchItems = batchEntities.AsList(); // Break if there is no more records if (batchItems.Count <= 0) { break; } // Check if the batch size has changed (probably the last batch on the enumerables) if (batchItems.Count != batchSize) { // Get a new execution context from cache context = await InsertAllExecutionContextProvider.CreateAsync <TEntity>(connection, tableName, batchItems.Count, fields, hints, transaction, statementBuilder); // Set the command properties command.CommandText = context.CommandText; } // Set the values if (batchItems?.Count == 1) { context.SingleDataEntityParametersSetterFunc?.Invoke(command, batchItems.First()); } else { context.MultipleDataEntitiesParametersSetterFunc?.Invoke(command, batchItems); } // Prepare the command if (dbSetting.IsPreparable) { command.Prepare(); } // Actual Execution if (context.IdentityPropertySetterFunc == null) { result += await command.ExecuteNonQueryAsync(); } else { using (var reader = await command.ExecuteReaderAsync()) { var index = 0; // Get the results do { if (await reader.ReadAsync()) { var value = Converter.DbNullToNull(reader.GetValue(0)); context.IdentityPropertySetterFunc.Invoke(batchItems[index], value); result++; } index++; }while (await reader.NextResultAsync()); } } } } } if (hasTransaction == false) { // Commit the transaction transaction.Commit(); } } catch { if (hasTransaction == false) { // Rollback for any exception transaction.Rollback(); } throw; } finally { if (hasTransaction == false) { // Rollback and dispose the transaction transaction.Dispose(); } } // After Execution if (trace != null) { trace.AfterInsertAll(new TraceLog(sessionId, context.CommandText, entities, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <summary> /// Creates a new instance of <see cref="MergeRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="qualifiers">The list of qualifier fields.</param> /// <param name="connection">The connection object.</param> /// <param name="statementBuilder">The statement builder.</param> public MergeRequest(Type entityType, IDbConnection connection, IEnumerable <Field> qualifiers = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Qualifiers = qualifiers; }
/// <summary> /// Throws an exception of the builder is not defined. /// </summary> /// <param name="connection">The connection object to identified.</param> /// <param name="builder">The builder to be checked.</param> /// <returns>The instance of available statement builder.</returns> private static IStatementBuilder EnsureStatementBuilder(IDbConnection connection, IStatementBuilder builder) => builder ?? connection.GetStatementBuilder();
/// <summary> /// Creates a new instance of <see cref="InsertRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="statementBuilder">The statement builder.</param> public InsertRequest(Type entityType, IDbConnection connection, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { }
/// <summary> /// Creates a new instance of <see cref="BatchQueryRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="where">The query expression.</param> /// <param name="page">The page of the batch.</param> /// <param name="rowsPerBatch">The number of rows per batch.</param> /// <param name="orderBy">The list of order fields.</param> /// <param name="statementBuilder">The statement builder.</param> public BatchQueryRequest(Type entityType, IDbConnection connection, QueryGroup where = null, int?page = null, int?rowsPerBatch = null, IEnumerable <OrderField> orderBy = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Where = where; Page = page; RowsPerBatch = rowsPerBatch; OrderBy = orderBy; }
/// <summary> /// Creates a new instance of <see cref="QueryRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="where">The query expression.</param> /// <param name="orderBy">The list of order fields.</param> /// <param name="top">The filter for the rows.</param> /// <param name="statementBuilder">The statement builder.</param> public QueryRequest(Type entityType, IDbConnection connection, QueryGroup where = null, IEnumerable <OrderField> orderBy = null, int?top = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Where = where; OrderBy = orderBy; Top = top; }
/// <summary> /// Creates a new instance of <see cref="StatementBuilderMapItem"/> object. /// </summary> /// <param name="provider">The target provider.</param> /// <param name="statementBuilder">The statement builder to be used for mapping.</param> public StatementBuilderMapItem(Provider provider, IStatementBuilder statementBuilder) { Provider = provider; StatementBuilder = statementBuilder; }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="dbFields"></param> /// <param name="batchSize"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <returns></returns> private static InsertAllExecutionContext <TEntity> CreateInternal <TEntity>(IDbConnection connection, string tableName, IEnumerable <DbField> dbFields, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) where TEntity : class { var typeOfEntity = typeof(TEntity); var dbSetting = connection.GetDbSetting(); var identity = (Field)null; var inputFields = (IEnumerable <DbField>)null; var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity); // Set the identity value if (typeOfEntity.IsClassType()) { identity = IdentityCache.Get <TEntity>()?.AsField() ?? FieldCache .Get <TEntity>()? .FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), identityDbField?.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) ?? identityDbField?.AsField(); } // Filter the actual properties for input fields inputFields = dbFields? .Where(dbField => dbField.IsIdentity == false) .Where(dbField => fields.FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) != null) .AsList(); // Variables for the context var multipleEntitiesFunc = (Action <DbCommand, IList <TEntity> >)null; var identitySettersFunc = (List <Action <TEntity, DbCommand> >)null; var singleEntityFunc = (Action <DbCommand, TEntity>)null; var identitySetterFunc = (Action <TEntity, object>)null; // Get if we have not skipped it if (typeOfEntity.IsClassType() && identity != null) { identitySetterFunc = FunctionCache.GetDataEntityPropertySetterCompiledFunction <TEntity>(identity); } // Identity which objects to set if (batchSize <= 1) { singleEntityFunc = FunctionCache.GetDataEntityDbParameterSetterCompiledFunction <TEntity>( string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".InsertAll"), inputFields?.AsList(), null, dbSetting); } else { multipleEntitiesFunc = FunctionCache.GetDataEntityListDbParameterSetterCompiledFunction <TEntity>( string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".InsertAll"), inputFields?.AsList(), null, batchSize, dbSetting); } // Identify the requests var insertAllRequest = (InsertAllRequest)null; var insertRequest = (InsertRequest)null; // Create a different kind of requests if (batchSize > 1) { insertAllRequest = new InsertAllRequest(tableName, connection, transaction, fields, batchSize, hints, statementBuilder); } else { insertRequest = new InsertRequest(tableName, connection, transaction, fields, hints, statementBuilder); } // Return the value return(new InsertAllExecutionContext <TEntity> { CommandText = batchSize > 1 ? CommandTextCache.GetInsertAllText(insertAllRequest) : CommandTextCache.GetInsertText(insertRequest), InputFields = inputFields, BatchSize = batchSize, SingleDataEntityParametersSetterFunc = singleEntityFunc, MultipleDataEntitiesParametersSetterFunc = multipleEntitiesFunc, IdentityPropertySetterFunc = identitySetterFunc, IdentityPropertySettersFunc = identitySettersFunc }); }
/* * Add */ /* * Add */ /// <summary> /// Adds a mapping between the type of <see cref="DbConnection"/> and an instance of <see cref="IStatementBuilder"/> object. /// </summary> /// <typeparam name="TDbConnection">The type of <see cref="DbConnection"/> object.</typeparam> /// <param name="statementBuilder">The instance of <see cref="IStatementBuilder"/> object to mapped to.</param> /// <param name="override">Set to true if to override the existing mapping, otherwise an exception will be thrown if the mapping is already present.</param> public static void Add <TDbConnection>(IStatementBuilder statementBuilder, bool @override) where TDbConnection : DbConnection => Add(typeof(TDbConnection), statementBuilder, @override);
/// <summary> /// Creates a new instance of <see cref="CountRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="where">The query expression.</param> /// <param name="hints">The hints for the table.</param> /// <param name="statementBuilder">The statement builder.</param> public CountRequest(Type entityType, IDbConnection connection, QueryGroup where = null, string hints = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Where = where; Hints = hints; }
/// <summary> /// Throws an exception of the builder is not defined. /// </summary> /// <param name="connection">The connection object to identified.</param> /// <param name="builder">The builder to be checked.</param> /// <returns>The instance of available statement builder.</returns> private static IStatementBuilder EnsureStatementBuilder(IDbConnection connection, IStatementBuilder builder) { return(builder ?? connection.GetStatementBuilder()); }
/// <summary> /// /// </summary> /// <param name="entityType"></param> /// <param name="connection"></param> /// <param name="entities"></param> /// <param name="tableName"></param> /// <param name="qualifiers"></param> /// <param name="batchSize"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <returns></returns> public static MergeAllExecutionContext Create(Type entityType, IDbConnection connection, IEnumerable <object> entities, string tableName, IEnumerable <Field> qualifiers, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) { var key = GetKey(entityType, tableName, qualifiers, fields, batchSize, hints); // Get from cache var context = MergeAllExecutionContextCache.Get(key); if (context != null) { return(context); } // Create var dbFields = DbFieldCache.Get(connection, tableName, transaction); var commandText = (string)null; // Create a different kind of requests if (batchSize > 1) { var request = new MergeAllRequest(tableName, connection, transaction, fields, qualifiers, batchSize, hints, statementBuilder); commandText = CommandTextCache.GetMergeAllText(request); } else { var request = new MergeRequest(tableName, connection, transaction, fields, qualifiers, hints, statementBuilder); commandText = CommandTextCache.GetMergeText(request); } // Call context = CreateInternal(entityType, connection, entities, dbFields, tableName, qualifiers, batchSize, fields, commandText); // Add to cache MergeAllExecutionContextCache.Add(key, context); // Return return(context); }
/// <summary> /// Creates a new instance of <see cref="InlineMergeRequest"/> object. /// </summary> /// <param name="entityType">The entity type.</param> /// <param name="connection">The connection object.</param> /// <param name="fields">The list of the target fields.</param> /// <param name="qualifiers">The list of the qualifier fields.</param> /// <param name="overrideIgnore">The value whether to override the ignored fields.</param> /// <param name="statementBuilder">The statement builder.</param> public InlineMergeRequest(Type entityType, IDbConnection connection, IEnumerable <Field> fields = null, IEnumerable <Field> qualifiers = null, bool?overrideIgnore = null, IStatementBuilder statementBuilder = null) : base(entityType, connection, statementBuilder) { Fields = fields; Qualifiers = qualifiers; OverrideIgnore = overrideIgnore; }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="dbFields"></param> /// <param name="tableName"></param> /// <param name="qualifiers"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <returns></returns> private static MergeExecutionContext <TEntity> CreateInternal <TEntity>(IDbConnection connection, IEnumerable <DbField> dbFields, string tableName, IEnumerable <Field> qualifiers, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) where TEntity : class { var typeOfEntity = typeof(TEntity); var dbSetting = connection.GetDbSetting(); var identity = (Field)null; var inputFields = new List <DbField>(); var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity); // Set the identity field if (typeOfEntity.IsClassType()) { identity = IdentityCache.Get <TEntity>()?.AsField() ?? FieldCache .Get <TEntity>()? .FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), identityDbField?.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) ?? identityDbField?.AsField(); } // Filter the actual properties for input fields inputFields = dbFields? .Where(dbField => fields.FirstOrDefault(field => string.Equals(field.Name.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) != null) .AsList(); // Variables for the entity action var identityPropertySetter = (Action <TEntity, object>)null; // Get the identity setter if (typeOfEntity.IsClassType() == true && identity != null) { identityPropertySetter = FunctionCache.GetDataEntityPropertySetterCompiledFunction <TEntity>(identity); } // Identify the requests var mergeRequest = new MergeRequest(tableName, connection, transaction, fields, qualifiers, hints, statementBuilder); // Return the value return(new MergeExecutionContext <TEntity> { CommandText = CommandTextCache.GetMergeText(mergeRequest), InputFields = inputFields, ParametersSetterFunc = FunctionCache.GetDataEntityDbParameterSetterCompiledFunction <TEntity>( string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".Merge"), inputFields?.AsList(), null, dbSetting), IdentityPropertySetterFunc = identityPropertySetter }); }
/// <summary> /// Deletes all the target existing data from the database in an asynchronous way. It uses the <see cref="DeleteAsync(IDbConnection, string, QueryGroup, string, int?, IDbTransaction, ITrace, IStatementBuilder)"/> operation as the underlying operation. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="primaryKeys">The list of the primary keys to be deleted.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <returns>The number of rows affected by the execution.</returns> public static async Task <int> DeleteAllAsync(this IDbConnection connection, string tableName, IEnumerable <object> primaryKeys, string hints = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null) { var primary = GetAndGuardPrimaryKey(connection, tableName, transaction); var dbSetting = connection.GetDbSetting(); var hasImplicitTransaction = false; var count = primaryKeys?.AsList()?.Count; var deletedRows = 0; try { // Creates a transaction (if needed) if (transaction == null && count > ParameterBatchCount) { transaction = connection.EnsureOpen().BeginTransaction(); hasImplicitTransaction = true; } // Call the underlying method var splitted = primaryKeys.Split(ParameterBatchCount).AsList(); foreach (var keys in splitted) { if (keys.Any() != true) { break; } var field = new QueryField(primary.Name.AsQuoted(dbSetting), Operation.In, keys?.AsList()); deletedRows += await DeleteAsyncInternal(connection : connection, tableName : tableName, where : new QueryGroup(field), hints : hints, commandTimeout : commandTimeout, transaction : transaction, trace : trace, statementBuilder : statementBuilder); } // Commit the transaction if (hasImplicitTransaction) { transaction?.Commit(); } } finally { // Dispose the transaction if (hasImplicitTransaction) { transaction?.Dispose(); } } // Return the value return(deletedRows); }