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