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