/// <summary> /// Counts all the number of data from the database table. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="CountAllRequest"/> object.</param> /// <param name="param">The mapped object parameters.</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> /// <returns>An integer value that holds the number of data from the database.</returns> internal static long CountAllInternalBase(this IDbConnection connection, CountAllRequest request, object param, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null) { // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetCountAllText(request); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(commandText, param, null); trace.BeforeCountAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(default(int)); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = ExecuteScalarInternal <long>(connection: connection, commandText: commandText, param: param, commandType: commandType, commandTimeout: commandTimeout, transaction: transaction, skipCommandArrayParametersCheck: true); // After Execution if (trace != null) { trace.AfterCountAll(new TraceLog(commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Result return(result); }
/// <summary> /// Deletes an existing data from the database in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="DeleteRequest"/> object.</param> /// <param name="param">The mapped object parameters.</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> /// <returns>The number of rows affected by the execution.</returns> internal static async Task <int> DeleteAsyncInternalBase(this IDbConnection connection, DeleteRequest request, object param, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null) { // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetDeleteText(request); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(commandText, param, null); trace.BeforeDelete(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(0); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = await ExecuteNonQueryAsyncInternal(connection : connection, commandText : commandText, param : param, commandType : commandType, commandTimeout : commandTimeout, transaction : transaction, skipCommandArrayParametersCheck : true); // After Execution if (trace != null) { trace.AfterDelete(new TraceLog(commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Result return(result); }
/// <summary> /// Truncates a table from the database in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="TruncateRequest"/> object.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param> /// <returns>The number of rows affected.</returns> internal static async Task <int> TruncateAsyncInternalBase(this IDbConnection connection, TruncateRequest request, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, CancellationToken cancellationToken = default) { // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetTruncateText(request); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, null, null); trace.BeforeTruncate(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } } commandText = (cancellableTraceLog.Statement ?? commandText); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = await ExecuteNonQueryAsyncInternal(connection : connection, commandText : commandText, param : null, commandType : commandType, commandTimeout : commandTimeout, transaction : transaction, cancellationToken : cancellationToken, entityType : request.Type, dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken), skipCommandArrayParametersCheck : true); // After Execution trace?.AfterTruncate(new TraceLog(sessionId, commandText, null, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); // Return the result return(result); }
/// <summary> /// Delete all the rows from the table. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="DeleteAllRequest"/> object.</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> /// <returns>The number of rows that has been deleted from the table.</returns> internal static int DeleteAllInternalBase(this IDbConnection connection, DeleteAllRequest request, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null) { // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetDeleteAllText(request); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, null, null); trace.BeforeDeleteAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(0); } commandText = (cancellableTraceLog.Statement ?? commandText); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = ExecuteNonQueryInternal(connection: connection, commandText: commandText, param: null, commandType: commandType, commandTimeout: commandTimeout, transaction: transaction, skipCommandArrayParametersCheck: true); // After Execution if (trace != null) { trace.AfterDeleteAll(new TraceLog(sessionId, commandText, null, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Result return(result); }
/// <summary> /// Truncates a table from the database. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="TruncateRequest"/> object.</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> /// <returns>The number of rows affected.</returns> internal static int TruncateInternalBase(this IDbConnection connection, TruncateRequest request, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null) { // Validate InvokeValidatorValidateTruncate(connection); // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetTruncateText(request); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(commandText, null, null); trace.BeforeTruncate(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } } commandText = (cancellableTraceLog.Statement ?? commandText); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = ExecuteNonQueryInternal(connection: connection, commandText: commandText, param: null, commandType: commandType, commandTimeout: commandTimeout, transaction: transaction, skipCommandArrayParametersCheck: true); // After Execution if (trace != null) { trace.AfterTruncate(new TraceLog(commandText, null, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <summary> /// CountAlls the number of table data from the database in an asynchronous way. /// </summary> /// <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="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</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>An integer value that holds the number of data from the database.</returns> internal static Task <long> CountAllAsyncInternal(this IDbConnection connection, string tableName, string hints = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null) { // Variables var request = new CountAllRequest(tableName, connection, hints, statementBuilder); var commandText = CommandTextCache.GetCountAllText(request); var param = (object)null; // Return the result return(CountAllInternalAsyncBase(connection: connection, request: request, param: param, commandTimeout: commandTimeout, transaction: transaction, trace: trace)); }
/// <summary> /// Inserts multiple data in the database 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="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 number of inserted rows.</returns> internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection, string tableName, IEnumerable <TEntity> entities, int batchSize = Constant.DefaultBatchOperationSize, IEnumerable <Field> fields = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null, bool skipIdentityCheck = false) where TEntity : class { // Guard the parameters var count = GuardInsertAll(entities); // Validate the batch size batchSize = Math.Min(batchSize, count); // Get the function var callback = new Func <int, InsertAllExecutionContext <TEntity> >((int batchSizeValue) => { // Variables needed var identity = (Field)null; var dbFields = DbFieldCache.Get(connection, tableName); var inputFields = (IEnumerable <DbField>)null; var outputFields = (IEnumerable <DbField>)null; var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity); // Set the identity value if (skipIdentityCheck == false) { identity = IdentityCache.Get <TEntity>()?.AsField(); if (identity == null && identityDbField != null) { identity = FieldCache.Get <TEntity>().FirstOrDefault(field => field.UnquotedName.ToLower() == identityDbField.UnquotedName.ToLower()); } } // Filter the actual properties for input fields inputFields = dbFields? .Where(dbField => dbField.IsIdentity == false) .Where(dbField => fields.FirstOrDefault(field => field.UnquotedName.ToLower() == dbField.UnquotedName.ToLower()) != null) .AsList(); // Set the output fields if (batchSizeValue > 1) { outputFields = identityDbField?.AsEnumerable(); } // 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 (skipIdentityCheck == false && identity != null) { if (batchSizeValue <= 1) { identitySetterFunc = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity); } else { identitySettersFunc = new List <Action <TEntity, DbCommand> >(); for (var index = 0; index < batchSizeValue; index++) { identitySettersFunc.Add(FunctionCache.GetDataEntityPropertySetterFromDbCommandParameterFunction <TEntity>(identity, identity.UnquotedName, index)); } } } // Identity which objects to set if (batchSizeValue <= 1) { singleEntityFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"), inputFields?.AsList(), null); } else { multipleEntitiesFunc = FunctionCache.GetDataEntitiesDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"), inputFields?.AsList(), outputFields, batchSizeValue); } // Identify the requests var insertAllRequest = (InsertAllRequest)null; var insertRequest = (InsertRequest)null; // Create a different kind of requests if (typeof(TEntity) == typeof(object)) { if (batchSizeValue > 1) { insertAllRequest = new InsertAllRequest(tableName, connection, fields, batchSizeValue, statementBuilder); } else { insertRequest = new InsertRequest(tableName, connection, fields, statementBuilder); } } else { if (batchSizeValue > 1) { insertAllRequest = new InsertAllRequest(typeof(TEntity), connection, fields, batchSizeValue, statementBuilder); } else { insertRequest = new InsertRequest(typeof(TEntity), connection, fields, statementBuilder); } } // Return the value return(new InsertAllExecutionContext <TEntity> { CommandText = batchSizeValue > 1 ? CommandTextCache.GetInsertAllText(insertAllRequest) : CommandTextCache.GetInsertText(insertRequest), InputFields = inputFields, OutputFields = outputFields, BatchSize = batchSizeValue, SingleDataEntityParametersSetterFunc = singleEntityFunc, MultipleDataEntitiesParametersSetterFunc = multipleEntitiesFunc, IdentityPropertySetterFunc = identitySetterFunc, IdentityPropertySettersFunc = identitySettersFunc }); }); // Get the context var context = (InsertAllExecutionContext <TEntity>)null; // Identify the number of entities (performance), get an execution context from cache context = batchSize == 1 ? InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, 1, callback) : InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchSize, callback); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(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); 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) { // Set the values context.SingleDataEntityParametersSetterFunc(command, entity); // Actual Execution var returnValue = ObjectConverter.DbNullToNull(await command.ExecuteScalarAsync()); // Set the return value if (returnValue != null) { context.IdentityPropertySetterFunc?.Invoke(entity, returnValue); } // Iterate the result result++; } } else { foreach (var batchEntities in entities.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 = InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchItems.Count, callback); // Set the command properties command.CommandText = context.CommandText; // Prepare the command command.Prepare(); } // Set the values context.MultipleDataEntitiesParametersSetterFunc(command, batchItems); // Actual Execution result += await command.ExecuteNonQueryAsync(); // Set the identities if (context.IdentityPropertySettersFunc != null && command.Parameters.Count > 0) { for (var index = 0; index < batchItems.Count; index++) { var func = context.IdentityPropertySettersFunc.ElementAt(index); func(batchItems[index], command); } } } } } 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(context.CommandText, entities, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <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. See <see cref="SqlTableHints"/> class.</param> /// <param name="cacheKey"> /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this /// to null would force to query from the database. /// </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(cacheKey, false); if (item != null) { return((IEnumerable <dynamic>)item.Value); } } // Check the fields if (fields?.Any() != true) { fields = DbFieldCache.Get(connection, tableName)?.Select(f => f.AsField()); } // Variables var commandType = CommandType.Text; var request = new QueryAllRequest(tableName, connection, 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); } // Result return(result); }
/// <summary> /// Query all the data from the database. /// </summary> /// <typeparam name="TEntity">The type of the data entity object.</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. See <see cref="SqlTableHints"/> class.</param> /// <param name="cacheKey"> /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this /// to null would force to query from the database. /// </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> > QueryAllAsyncInternalBase <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(cacheKey, false); if (item != null) { return(Task.FromResult((IEnumerable <TEntity>)item.Value)); } } // Variables var commandType = CommandType.Text; var request = new QueryAllRequest(typeof(TEntity), connection, FieldCache.Get <TEntity>(), orderBy, hints, statementBuilder); var commandText = CommandTextCache.GetQueryAllText(request); var param = (object)null; // Database pre-touch for field definitions DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>()); // 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(Task.FromResult <IEnumerable <TEntity> >(null)); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = ExecuteQueryAsyncInternal <TEntity>(connection: connection, commandText: commandText, param: param, commandType: commandType, commandTimeout: commandTimeout, transaction: transaction, basedOnFields: false, skipCommandArrayParametersCheck: true); // After Execution if (trace != null) { trace.AfterQueryAll(new TraceLog(commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Set Cache if (cacheKey != null /* && result.Result?.Any() == true */) { cache?.Add(cacheKey, result, cacheItemExpiration); } // Result return(result); }
/// <summary> /// Query all the data from the table. /// </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 objects.</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; var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, 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(sessionId, commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Set Cache if (cacheKey != null) { cache?.Add(cacheKey, result, cacheItemExpiration, false); } // Result return(result); }
/// <summary> /// Inserts a new row in the table in an asynchronous way. /// </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="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> /// <param name="skipIdentityCheck">True to skip the identity check.</param> /// <returns>The value of the identity field if present, otherwise, the value of the primary field.</returns> internal async static Task <TResult> InsertAsyncInternalBase <TEntity, TResult>(this IDbConnection connection, string tableName, TEntity entity, IEnumerable <Field> fields = null, string hints = 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 database fields var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction); // Get the function var callback = new Func <InsertExecutionContext <TEntity> >(() => { // Variables needed var identity = (Field)null; 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) == StaticType.Object) { insertRequest = new InsertRequest(tableName, connection, transaction, fields, hints, statementBuilder); } else { insertRequest = new InsertRequest(typeof(TEntity), connection, transaction, fields, hints, statementBuilder); } // Return the value return(new InsertExecutionContext <TEntity> { CommandText = CommandTextCache.GetInsertText(insertRequest), InputFields = inputFields, ParametersSetterFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".Insert"), inputFields?.AsList(), null, dbSetting), IdentityPropertySetterFunc = identityPropertySetter }); }); // Get the context var context = InsertExecutionContextCache <TEntity> .Get(tableName, fields, callback); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, 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)(await connection.EnsureOpenAsync()).CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Set the values context.ParametersSetterFunc(command, entity); // Actual Execution result = Converter.ToType <TResult>(await command.ExecuteScalarAsync()); // Get explicity if needed if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false) { result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(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(sessionId, context.CommandText, entity, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }