/// <averagemary> /// Computes the average value of the target field in an asynchronous way. /// </averagemary> /// <typeparam name="TResult">The type of the result.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="request">The actual <see cref="AverageAllRequest"/> 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> /// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param> /// <returns>The average value of the target field.</returns> internal static async Task <TResult> AverageAllAsyncInternalBase <TResult>(this IDbConnection connection, AverageAllRequest request, object param, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, CancellationToken cancellationToken = default) { // Variables var commandType = CommandType.Text; var commandText = CommandTextCache.GetAverageAllText(request); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, param, null); trace.BeforeAverageAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(commandText); } return(default(TResult)); } commandText = (cancellableTraceLog.Statement ?? commandText); param = (cancellableTraceLog.Parameter ?? param); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution var result = await ExecuteScalarAsyncInternal <TResult>(connection : connection, commandText : commandText, param : param, commandType : commandType, cacheKey : null, cacheItemExpiration : null, commandTimeout : commandTimeout, transaction : transaction, cache : null, cancellationToken : cancellationToken, entityType : request.Type, dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken), skipCommandArrayParametersCheck : true); // After Execution if (trace != null) { trace.AfterAverageAll(new TraceLog(sessionId, commandText, param, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Result return(result); }
/// <summary> /// Truncates a table from the database in an asychronous 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 if (trace != null) { trace.AfterTruncate(new TraceLog(sessionId, commandText, null, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <summary> /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object. /// </summary> /// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param> public async Task InitializeAsync(CancellationToken cancellationToken = default) { if (IsInitialized) { return; } var dbFields = (IEnumerable <DbField>)null; if (Connection != null) { dbFields = await DbFieldCache.GetAsync(Connection, TableName, Transaction, false, cancellationToken); } InitializeInternal(dbFields); }
/// <summary> /// /// </summary> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="fields"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> private static async Task <IEnumerable <Field> > GetActualFieldsAsync(IDbConnection connection, string tableName, IEnumerable <Field> fields, IDbTransaction transaction, CancellationToken cancellationToken = default) { if (fields?.Any() != true) { return(null); } var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); return(GetActualFieldsInternal(fields, dbFields, connection.GetDbSetting())); }
/// <summary> /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object. /// </summary> public async Task InitializeAsync() { if (IsInitialized) { return; } var dbFields = (IEnumerable <DbField>)null; if (Connection != null) { dbFields = await DbFieldCache.GetAsync(Connection, ClassMappedNameCache.Get <TEntity>(), Transaction); } InitializeInternal(dbFields); }
/// <summary> /// Ensures that the <see cref="DbFieldCache.GetAsync(IDbConnection, string, IDbTransaction)"/> method is being called one. /// </summary> /// <typeparam name="TEntity">The type of the entity to check.</typeparam> /// <param name="transaction">The transaction object that is currently in used.</param> private async Task EnsureSingleCallForDbFieldCacheGeAsync <TEntity>(IDbTransaction transaction) where TEntity : class { var key = GetDbFieldGetCallsCacheKey <TEntity>(); var dbFields = (IEnumerable <DbField>)null; // Try get the value if (cache.TryGetValue(key, out dbFields) == false) { using (var connection = (IDbConnection)Activator.CreateInstance(this.connection.GetType(), new object[] { connectionString })) { dbFields = await DbFieldCache.GetAsync(connection, ClassMappedNameCache.Get <TEntity>(), transaction, false); cache.TryAdd(key, dbFields); } } }
public static async Task <IEnumerable <OrderField> > GetValidatedDbFieldsAsync( this DbConnection dbConnection, string tableName, IEnumerable <OrderField> sortFields ) { if (dbConnection == null || sortFields == null || string.IsNullOrWhiteSpace(tableName)) { return(null); } //FILTER for only VALID fields from the sort fields by safely comparing to the valid fields from the DB Schema! //NOTE: Per RepoDb source we need to compare unquoted names to get pure matches... var dbSetting = dbConnection.GetDbSetting(); var dbFields = await DbFieldCache .GetAsync(dbConnection, tableName, null, false) .ConfigureAwait(false); var dbFieldLookup = dbFields.ToLookup(f => f.Name.AsUnquoted(dbSetting).ToLower()); var safeSortFields = sortFields.Where(s => dbFieldLookup.Contains(s.Name.AsUnquoted(dbSetting).ToLower())); return(safeSortFields); }
/// <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); }
/// <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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</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, CancellationToken cancellationToken = default) where TEntity : class { // Variables needed var dbSetting = connection.GetDbSetting(); // Get the database fields var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); // Get the context var context = await InsertExecutionContextProvider.CreateAsync <TEntity>(connection, tableName, fields, hints, transaction, statementBuilder, cancellationToken); 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(cancellationToken)).CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Set the values context.ParametersSetterFunc(command, entity); // Actual Execution result = Converter.ToType <TResult>(await command.ExecuteScalarAsync(cancellationToken)); // Get explicity if needed if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false) { result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction, cancellationToken)); } // 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); }
/// <summary> /// /// </summary> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="reader"></param> /// <param name="qualifiers"></param> /// <param name="mappings"></param> /// <param name="bulkCopyTimeout"></param> /// <param name="keepIdentity"></param> /// <param name="pseudoTableType"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> private static async Task <int> BinaryBulkDeleteBaseAsync(this NpgsqlConnection connection, string tableName, DbDataReader reader, IEnumerable <Field> qualifiers = null, IEnumerable <NpgsqlBulkInsertMapItem> mappings = null, int?bulkCopyTimeout = null, bool keepIdentity = true, BulkImportPseudoTableType pseudoTableType = default, NpgsqlTransaction transaction = null, CancellationToken cancellationToken = default) { var dbSetting = connection.GetDbSetting(); var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var pseudoTableName = tableName; var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified; return(await PseudoBasedBinaryImportAsync(connection, tableName, bulkCopyTimeout, dbFields, // getPseudoTableName () => pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName, dbSetting), // getMappings () => { var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity; var includePrimary = true; return mappings = mappings?.Any() == true ? mappings : GetMappings(reader, dbFields, includePrimary, includeIdentity, dbSetting); }, // binaryImport async (tableName) => await connection.BinaryImportAsync(tableName, reader, mappings, dbFields, bulkCopyTimeout, identityBehavior, dbSetting, transaction, cancellationToken), // getDeleteToPseudoCommandText () => GetDeleteCommandText(pseudoTableName, tableName, mappings.Select(mapping => new Field(mapping.DestinationColumn)), qualifiers, dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(), dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(), identityBehavior, dbSetting), // setIdentities null, qualifiers, false, identityBehavior : identityBehavior, pseudoTableType : pseudoTableType, dbSetting, transaction : transaction, cancellationToken)); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="entities"></param> /// <param name="qualifiers"></param> /// <param name="mappings"></param> /// <param name="bulkCopyTimeout"></param> /// <param name="batchSize"></param> /// <param name="keepIdentity"></param> /// <param name="pseudoTableType"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> private static async Task <int> BinaryBulkDeleteBaseAsync <TEntity>(this NpgsqlConnection connection, string tableName, IEnumerable <TEntity> entities, IEnumerable <Field> qualifiers = null, IEnumerable <NpgsqlBulkInsertMapItem> mappings = null, int?bulkCopyTimeout = null, int?batchSize = null, bool keepIdentity = true, BulkImportPseudoTableType pseudoTableType = default, NpgsqlTransaction transaction = null, CancellationToken cancellationToken = default) where TEntity : class { var entityType = entities?.First()?.GetType() ?? typeof(TEntity); // Solving the anonymous types var isDictionary = entityType.IsDictionaryStringObject(); var dbSetting = connection.GetDbSetting(); var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var pseudoTableName = tableName; var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified; return(await PseudoBasedBinaryImportAsync(connection, tableName, bulkCopyTimeout, dbFields, // getPseudoTableName () => pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName ?? ClassMappedNameCache.Get <TEntity>(), dbSetting), // getMappings () => { var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity; var includePrimary = true; return mappings = mappings?.Any() == true ? mappings : isDictionary ? GetMappings(entities?.First() as IDictionary <string, object>, dbFields, includePrimary, includeIdentity, dbSetting) : GetMappings(dbFields, PropertyCache.Get(entityType), includePrimary, includeIdentity, dbSetting); }, // binaryImport async (tableName) => await connection.BinaryImportAsync <TEntity>(tableName, entities, mappings, dbFields, bulkCopyTimeout, batchSize, identityBehavior, dbSetting, transaction, cancellationToken), // getDeleteToPseudoCommandText () => GetDeleteCommandText(pseudoTableName, tableName, mappings.Select(mapping => new Field(mapping.DestinationColumn)), qualifiers, dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(), dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(), identityBehavior, dbSetting), // setIdentities (identityResults) => SetIdentities(entityType, entities, dbFields, identityResults, dbSetting), qualifiers, false, identityBehavior, pseudoTableType, dbSetting, transaction, cancellationToken)); }
/// <summary> /// /// </summary> /// <typeparam name="TPrimaryKey"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="primaryKeys"></param> /// <param name="bulkCopyTimeout"></param> /// <param name="batchSize"></param> /// <param name="pseudoTableType"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> private static async Task <int> BinaryBulkDeleteByKeyBaseAsync <TPrimaryKey>(this NpgsqlConnection connection, string tableName, IEnumerable <TPrimaryKey> primaryKeys, int?bulkCopyTimeout = null, int?batchSize = null, BulkImportPseudoTableType pseudoTableType = default, NpgsqlTransaction transaction = null, CancellationToken cancellationToken = default) { var identityBehavior = BulkImportIdentityBehavior.Unspecified; var dbSetting = connection.GetDbSetting(); var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var primaryKey = dbFields.FirstOrDefault(dbField => dbField.IsPrimary); var pseudoTableName = tableName; IEnumerable <NpgsqlBulkInsertMapItem> mappings = null; return(await PseudoBasedBinaryImportAsync(connection, tableName, bulkCopyTimeout, dbFields, // getPseudoTableName () => pseudoTableName = GetBinaryBulkDeleteByKeyPseudoTableName(tableName ?? ClassMappedNameCache.Get <TPrimaryKey>(), dbSetting), // getMappings () => mappings = new[] { new NpgsqlBulkInsertMapItem(primaryKey.Name, primaryKey.Name) }, // binaryImport async (tableName) => await connection.BinaryImportAsync(tableName, GetExpandoObjectData(primaryKeys, primaryKey.AsField()), mappings, dbFields, bulkCopyTimeout, batchSize, identityBehavior, dbSetting, transaction, cancellationToken), // getDeleteToPseudoCommandText () => GetDeleteByKeyCommandText(pseudoTableName, tableName, dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(), dbSetting), // setIdentities null, null, false, identityBehavior, pseudoTableType, dbSetting, transaction, cancellationToken)); }
/// <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 async 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 <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; // Database pre-touch for field definitions await DbFieldCache.GetAsync(connection, ClassMappedNameCache.Get <TEntity>(), transaction); // 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 = await ExecuteQueryAsyncInternal <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> /// Bulk insert an instance of <see cref="DataTable"/> object into the database in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The target table for bulk-insert operation.</param> /// <param name="dataTable">The <see cref="DataTable"/> object to be used in the bulk-insert operation.</param> /// <param name="rowState">The state of the rows to be copied to the destination.</param> /// <param name="dbFields">The list of <see cref="DbField"/> objects.</param> /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param> /// <param name="options">The bulk-copy options to be used.</param> /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param> /// <param name="batchSize">The size per batch to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <returns>The number of rows affected by the execution.</returns> internal static async Task <int> BulkInsertAsyncInternal(SqlConnection connection, string tableName, DataTable dataTable, DataRowState?rowState = null, IEnumerable <DbField> dbFields = null, IEnumerable <BulkInsertMapItem> mappings = null, SqlBulkCopyOptions?options = null, int?bulkCopyTimeout = null, int?batchSize = null, SqlTransaction transaction = null) { // Validate the objects SqlConnectionExtension.ValidateTransactionConnectionObject(connection, transaction); // Get the DB Fields dbFields = dbFields ?? await DbFieldCache.GetAsync(connection, tableName, transaction); if (dbFields?.Any() != true) { throw new InvalidOperationException($"No database fields found for '{tableName}'."); } // Variables needed var result = 0; var tableFields = GetDataColumns(dataTable) .Select(column => column.ColumnName); var mappingFields = new List <Tuple <string, string> >(); // To fix the casing problem of the bulk inserts foreach (var dbField in dbFields) { var tableField = tableFields.FirstOrDefault(field => string.Equals(field, dbField.Name, StringComparison.OrdinalIgnoreCase)); if (tableField != null) { mappingFields.Add(new Tuple <string, string>(tableField, dbField.Name)); } } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution using (var sqlBulkCopy = new SqlBulkCopy(connection, options.GetValueOrDefault(), transaction)) { // Set the destinationtable sqlBulkCopy.DestinationTableName = tableName; // Set the timeout if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue) { sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value; } // Set the batch szie if (batchSize != null && batchSize.HasValue) { sqlBulkCopy.BatchSize = batchSize.Value; } // Add the mappings if (mappings == null) { // Iterate the filtered fields foreach (var field in mappingFields) { sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2); } } else { // Iterate the provided mappings foreach (var mapItem in mappings) { sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn); } } // Open the connection and do the operation connection.EnsureOpen(); if (rowState.HasValue == true) { await sqlBulkCopy.WriteToServerAsync(dataTable, rowState.Value); } else { await sqlBulkCopy.WriteToServerAsync(dataTable); } // Set the return value result = GetDataRows(dataTable, rowState).Count(); } // Result return(result); }
/// <summary> /// Bulk insert an instance of <see cref="DbDataReader"/> object into the database in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The target table for bulk-insert operation.</param> /// <param name="reader">The <see cref="DbDataReader"/> object to be used in the bulk-insert operation.</param> /// <param name="dbFields">The list of <see cref="DbField"/> objects.</param> /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param> /// <param name="options">The bulk-copy options to be used.</param> /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param> /// <param name="batchSize">The size per batch to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <returns>The number of rows affected by the execution.</returns> internal static async Task <int> BulkInsertAsyncInternal(SqlConnection connection, string tableName, DbDataReader reader, IEnumerable <DbField> dbFields = null, IEnumerable <BulkInsertMapItem> mappings = null, SqlBulkCopyOptions?options = null, int?bulkCopyTimeout = null, int?batchSize = null, SqlTransaction transaction = null) { // Validate the objects SqlConnectionExtension.ValidateTransactionConnectionObject(connection, transaction); // Get the DB Fields dbFields = dbFields ?? await DbFieldCache.GetAsync(connection, tableName, transaction); if (dbFields?.Any() != true) { throw new InvalidOperationException($"No database fields found for '{tableName}'."); } // Variables needed var result = 0; var readerFields = Enumerable .Range(0, reader.FieldCount) .Select((index) => reader.GetName(index)); var mappingFields = new List <Tuple <string, string> >(); // To fix the casing problem of the bulk inserts foreach (var dbField in dbFields) { var readerField = readerFields.FirstOrDefault(field => string.Equals(field, dbField.Name, StringComparison.OrdinalIgnoreCase)); if (!string.IsNullOrEmpty(readerField)) { mappingFields.Add(new Tuple <string, string>(readerField, dbField.Name)); } } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution using (var sqlBulkCopy = new SqlBulkCopy(connection, options.GetValueOrDefault(), transaction)) { // Set the destinationtable sqlBulkCopy.DestinationTableName = tableName; // Set the timeout if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue) { sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value; } // Set the batch szie if (batchSize != null && batchSize.HasValue) { sqlBulkCopy.BatchSize = batchSize.Value; } // Add the mappings if (mappings == null) { // Iterate the filtered fields foreach (var field in mappingFields) { sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2); } } else { // Iterate the provided mappings foreach (var mapItem in mappings) { sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn); } } // Open the connection and do the operation await connection.EnsureOpenAsync(); await sqlBulkCopy.WriteToServerAsync(reader); // Hack the 'SqlBulkCopy' object var copiedField = GetRowsCopiedFieldFromMicrosoftDataSqlBulkCopy(); // Set the return value result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected; } // Result return(result); }
/// <summary> /// Bulk insert an instance of <see cref="DataTable"/> object into the database in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The target table for bulk-insert operation.</param> /// <param name="dataTable">The <see cref="DataTable"/> object to be used in the bulk-insert operation.</param> /// <param name="rowState">The state of the rows to be copied to the destination.</param> /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param> /// <param name="options">The bulk-copy options to be used.</param> /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param> /// <param name="batchSize">The size per batch to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <returns>The number of rows affected by the execution.</returns> internal static async Task <int> BulkInsertAsyncInternal(SqlConnection connection, string tableName, DataTable dataTable, DataRowState rowState = DataRowState.Unchanged, IEnumerable <BulkInsertMapItem> mappings = null, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default, int?bulkCopyTimeout = null, int?batchSize = null, SqlTransaction transaction = null) { // Validate the objects DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction); // Variables for the operation var result = 0; // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Actual Execution using (var sqlBulkCopy = new SqlBulkCopy(connection, options, transaction)) { // Set the destinationtable sqlBulkCopy.DestinationTableName = tableName; // Set the timeout if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue) { sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value; } // Set the batch szie if (batchSize != null && batchSize.HasValue) { sqlBulkCopy.BatchSize = batchSize.Value; } // Add the mappings if (mappings == null) { // Variables needed var dbSetting = connection.GetDbSetting(); var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction); var fields = GetDataColumns(dataTable).Select(column => column.ColumnName); var filteredFields = new List <Tuple <string, string> >(); // To fix the casing problem of the bulk inserts foreach (var dbField in dbFields) { var field = fields.FirstOrDefault(f => string.Equals(f.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)); if (field != null) { filteredFields.Add(new Tuple <string, string>(field, dbField.Name.AsUnquoted(true, dbSetting))); } } // Iterate the filtered fields foreach (var field in filteredFields) { sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2); } } else { // Iterate the provided mappings foreach (var mapItem in mappings) { sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn); } } // Open the connection and do the operation connection.EnsureOpen(); await sqlBulkCopy.WriteToServerAsync(dataTable, rowState); // Set the return value result = GetDataRows(dataTable, rowState).Count(); } // Result return(result); }
/// <summary> /// /// </summary> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="table"></param> /// <param name="rowState"></param> /// <param name="mappings"></param> /// <param name="bulkCopyTimeout"></param> /// <param name="batchSize"></param> /// <param name="identityBehavior"></param> /// <param name="pseudoTableType"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> private static async Task <int> BinaryBulkInsertBaseAsync(this NpgsqlConnection connection, string tableName, DataTable table, DataRowState?rowState = null, IEnumerable <NpgsqlBulkInsertMapItem> mappings = null, int?bulkCopyTimeout = null, int?batchSize = null, BulkImportIdentityBehavior identityBehavior = default, BulkImportPseudoTableType pseudoTableType = default, NpgsqlTransaction transaction = null, CancellationToken cancellationToken = default) { var dbSetting = connection.GetDbSetting(); var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var pseudoTableName = tableName; return(await PseudoBasedBinaryImportAsync(connection, tableName, bulkCopyTimeout, dbFields, // getPseudoTableName () => pseudoTableName = GetBinaryBulkInsertPseudoTableName(tableName, dbSetting), // getMappings () => { var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity; var isPrimaryAnIdentity = IsPrimaryAnIdentity(dbFields); var includePrimary = isPrimaryAnIdentity == false || (isPrimaryAnIdentity && includeIdentity); return mappings = mappings?.Any() == true ? mappings : GetMappings(table, dbFields, includePrimary, includeIdentity, dbSetting); }, // binaryImport async (tableName) => await connection.BinaryImportAsync(tableName, table, rowState, mappings, dbFields, bulkCopyTimeout, batchSize, identityBehavior, dbSetting, transaction, cancellationToken), // getMergeToPseudoCommandText () => GetInsertCommandText(pseudoTableName, tableName, mappings.Select(mapping => new Field(mapping.DestinationColumn)), dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(), identityBehavior, dbSetting), // setIdentities (identityResults) => SetDataTableIdentities(table, dbFields, identityResults, dbSetting), null, true, identityBehavior : identityBehavior, pseudoTableType : pseudoTableType, dbSetting, transaction : transaction, cancellationToken)); }
/// <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. 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 = (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); }
/// <summary> /// /// </summary> /// <typeparam name="TSqlBulkCopy"></typeparam> /// <typeparam name="TSqlBulkCopyOptions"></typeparam> /// <typeparam name="TSqlBulkCopyColumnMappingCollection"></typeparam> /// <typeparam name="TSqlBulkCopyColumnMapping"></typeparam> /// <typeparam name="TSqlTransaction"></typeparam> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="dataTable"></param> /// <param name="qualifiers"></param> /// <param name="rowState"></param> /// <param name="mappings"></param> /// <param name="options"></param> /// <param name="hints"></param> /// <param name="bulkCopyTimeout"></param> /// <param name="batchSize"></param> /// <param name="usePhysicalPseudoTempTable"></param> /// <param name="transaction"></param> /// <param name="cancellationToken"></param> /// <returns></returns> internal static async Task <int> BulkUpdateAsyncInternalBase <TSqlBulkCopy, TSqlBulkCopyOptions, TSqlBulkCopyColumnMappingCollection, TSqlBulkCopyColumnMapping, TSqlTransaction>(DbConnection connection, string tableName, DataTable dataTable, IEnumerable <Field> qualifiers = null, DataRowState?rowState = null, IEnumerable <BulkInsertMapItem> mappings = null, TSqlBulkCopyOptions options = default, string hints = null, int?bulkCopyTimeout = null, int?batchSize = null, bool?usePhysicalPseudoTempTable = null, TSqlTransaction transaction = null, CancellationToken cancellationToken = default) where TSqlBulkCopy : class, IDisposable where TSqlBulkCopyOptions : Enum where TSqlBulkCopyColumnMappingCollection : class where TSqlBulkCopyColumnMapping : class where TSqlTransaction : DbTransaction { // Validate ThrowIfNullOrEmpty(dataTable); // Variables var dbSetting = connection.GetDbSetting(); var hasTransaction = (transaction != null); var result = default(int); // Check the transaction if (transaction == null) { // Add the transaction if not present transaction = (TSqlTransaction)(await connection.EnsureOpenAsync(cancellationToken)).BeginTransaction(); } else { // Validate the objects SqlConnectionExtension.ValidateTransactionConnectionObject(connection, transaction); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Must be fixed name so the RepoDb.Core caches will not be bloated var tempTableName = string.Concat("_RepoDb_BulkUpdate_", GetTableName(tableName, dbSetting)); // Add a # prefix if not physical if (usePhysicalPseudoTempTable != true) { tempTableName = string.Concat("#", tempTableName); } try { // Get the DB Fields var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, true, cancellationToken); // Variables needed var tableFields = Enumerable.Range(0, dataTable.Columns.Count) .Select((index) => dataTable.Columns[index].ColumnName); var fields = dbFields?.Select(dbField => dbField.AsField()); var primaryDbField = dbFields?.FirstOrDefault(dbField => dbField.IsPrimary); var identityDbField = dbFields?.FirstOrDefault(dbField => dbField.IsIdentity); var primaryOrIdentityDbField = (primaryDbField ?? identityDbField); // Validate the primary keys if (qualifiers?.Any() != true) { if (primaryOrIdentityDbField == null) { throw new MissingPrimaryKeyException($"No primary key or identity key found for table '{tableName}'."); } else { qualifiers = new[] { primaryOrIdentityDbField.AsField() }; } } // Filter the fields (based on the data table) if (tableFields?.Any() == true) { fields = fields .Where(e => tableFields.Any(fieldName => string.Equals(fieldName, e.Name, StringComparison.OrdinalIgnoreCase)) == true); } // Filter the fields (based on the mappings) if (mappings?.Any() == true) { fields = fields .Where(e => mappings.Any(m => string.Equals(m.SourceColumn, e.Name, StringComparison.OrdinalIgnoreCase)) == true); } // Throw an error if there are no fields if (fields?.Any() != true) { throw new MissingFieldException("There are no field(s) found for this operation."); } // Create a temporary table var sql = GetCreateTemporaryTableSqlText(tableName, tempTableName, fields, dbSetting); await connection.ExecuteNonQueryAsync(sql, transaction : transaction, cancellationToken : cancellationToken); // Set the options to KeepIdentity if needed if (object.Equals(options, default(TSqlBulkCopyOptions)) && identityDbField?.IsIdentity == true && fields?.FirstOrDefault( field => string.Equals(field.Name, identityDbField.Name, StringComparison.OrdinalIgnoreCase)) != null) { options = Compiler.GetEnumFunc <TSqlBulkCopyOptions>("KeepIdentity")(); } // Filter the DB Fields var filteredDbFields = dbFields? .Where(dbField => fields?.Any(field => string.Equals(field.Name, dbField.Name, StringComparison.OrdinalIgnoreCase)) == true); // Do the bulk insertion first await BulkInsertAsyncInternalBase <TSqlBulkCopy, TSqlBulkCopyOptions, TSqlBulkCopyColumnMappingCollection, TSqlBulkCopyColumnMapping, TSqlTransaction>(connection, tempTableName, dataTable, rowState, filteredDbFields, mappings, options, hints, bulkCopyTimeout, batchSize, false, false, transaction, cancellationToken); // Create the clustered index sql = GetCreateTemporaryTableClusteredIndexSqlText(tempTableName, qualifiers, dbSetting); await connection.ExecuteNonQueryAsync(sql, transaction : transaction, cancellationToken : cancellationToken); // Update the actual update sql = GetBulkUpdateSqlText(tableName, tempTableName, fields, qualifiers, primaryDbField?.AsField(), identityDbField?.AsField(), hints, dbSetting); result = await connection.ExecuteNonQueryAsync(sql, commandTimeout : bulkCopyTimeout, transaction : transaction, cancellationToken : cancellationToken); // Drop the table after used sql = GetDropTemporaryTableSqlText(tempTableName, dbSetting); await connection.ExecuteNonQueryAsync(sql, transaction : transaction, cancellationToken : cancellationToken); // Commit the transaction if (hasTransaction == false) { transaction?.Commit(); } } catch { // Rollback the transaction if (hasTransaction == false) { transaction?.Rollback(); } // Throw throw; } finally { // Dispose the transaction if (hasTransaction == false) { transaction?.Dispose(); } } // Result return(result); }