/// <summary>
/// Bulk insert a list of data entity objects into 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="entities">The list of the data entities to be bulk-inserted.</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>
public static int BulkInsert <TEntity>(this SqlConnection connection,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
SqlTransaction transaction = null)
where TEntity : class
{
using (var reader = new DataEntityDataReader <TEntity>(entities))
{
return(BulkInsertInternal(connection: connection,
tableName: ClassMappedNameCache.Get <TEntity>(),
reader: reader,
mappings: mappings,
options: options,
bulkCopyTimeout: bulkCopyTimeout,
batchSize: batchSize,
transaction: transaction));
}
}
/// <summary>
/// Bulk insert a list of data entity objects into the database in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="tableName">The target table for bulk-insert operation.</param>
/// <param name="connection">The connection object to be used.</param>
/// <param name="entities">The list of the data entities to be bulk-inserted.</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>
public static async Task <int> BulkInsertAsync <TEntity>(this SqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
SqlTransaction transaction = null)
where TEntity : class
{
using (var reader = new DataEntityDataReader <TEntity>(entities))
{
return(await BulkInsertAsyncInternal(connection : connection,
tableName : tableName,
reader : reader,
mappings : mappings,
options : options,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
transaction : transaction));
}
}
/// <summary>
/// Bulk insert a list of data entity objects into the database in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="tableName">The target table for bulk-insert operation.</param>
/// <param name="connection">The connection object to be used.</param>
/// <param name="entities">The list of the data entities to be bulk-inserted.</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>
/// <param name="trace">The trace object to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public static async Task <int> BulkInsertAsync <TEntity>(this IDbConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null,
ITrace trace = null)
where TEntity : class
{
// Validate
InvokeValidatorValidateBulkInsertAsync(connection);
// Get the provider
var provider = connection.GetDbOperation();
// Before Execution
if (trace != null)
{
var cancellableTraceLog = new CancellableTraceLog("BulkInsert.Before", entities, null);
trace.BeforeBulkInsert(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException("BulkInsert.Cancelled");
}
return(0);
}
entities = (IEnumerable <TEntity>)cancellableTraceLog.Parameter ?? entities;
}
// Variables for the operation
var result = 0;
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual execution
using (var reader = new DataEntityDataReader <TEntity>(entities))
{
result = await provider.BulkInsertAsync(connection : connection,
tableName : tableName,
reader : reader,
mappings : mappings,
options : options,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
transaction : transaction);
}
// After Execution
if (trace != null)
{
trace.AfterBulkInsert(new TraceLog("BulkInsert.After", entities, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="mappings"></param>
/// <param name="options"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="hasOrderingColumn"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static int WriteToServerInternal <TEntity>(SqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = default,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool hasOrderingColumn = false,
SqlTransaction transaction = null)
where TEntity : class
{
// Throw an error if there are no mappings
if (mappings?.Any() != true)
{
throw new MissingMappingException("There are no mapping(s) found for this operation.");
}
// Variables needed
int result;
// Actual Execution
using (var sqlBulkCopy = (SqlBulkCopy)Activator.CreateInstance(typeof(SqlBulkCopy), connection, options, transaction))
{
// Set the destinationtable
Compiler.SetProperty(sqlBulkCopy, "DestinationTableName", tableName);
// Set the timeout
if (bulkCopyTimeout.HasValue)
{
Compiler.SetProperty(sqlBulkCopy, "BulkCopyTimeout", bulkCopyTimeout.Value);
}
// Set the batch size
if (batchSize.HasValue)
{
Compiler.SetProperty(sqlBulkCopy, "BatchSize", batchSize.Value);
}
// Add the order column
if (hasOrderingColumn)
{
mappings = AddOrderColumnMapping(mappings);
}
// Add the mappings
AddMappings(sqlBulkCopy, mappings);
// Open the connection and do the operation
connection.EnsureOpen();
using (var reader = new DataEntityDataReader <TEntity>(tableName, entities, connection, transaction, hasOrderingColumn))
{
var writeToServerMethod = Compiler.GetParameterizedVoidMethodFunc <SqlBulkCopy>("WriteToServer", new[] { typeof(DbDataReader) });
writeToServerMethod(sqlBulkCopy, new[] { reader });
result = reader.RecordsAffected;
}
// Ensure the result
if (result <= 0)
{
// Set the return value
var rowsCopiedFieldOrProperty = Compiler.GetFieldGetterFunc <SqlBulkCopy, int>("_rowsCopied") ??
Compiler.GetPropertyGetterFunc <SqlBulkCopy, int>("RowsCopied");
result = (int)rowsCopiedFieldOrProperty?.Invoke(sqlBulkCopy);
}
}
// Return the result
return(result);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <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="entities"></param>
/// <param name="mappings"></param>
/// <param name="options"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="hasOrderingColumn"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> WriteToServerAsyncInternal <TEntity, TSqlBulkCopy, TSqlBulkCopyOptions, TSqlBulkCopyColumnMappingCollection,
TSqlBulkCopyColumnMapping, TSqlTransaction>(DbConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <BulkInsertMapItem> mappings = null,
TSqlBulkCopyOptions options = default,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool hasOrderingColumn = false,
TSqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class
where TSqlBulkCopy : class, IDisposable
where TSqlBulkCopyOptions : Enum
where TSqlBulkCopyColumnMappingCollection : class
where TSqlBulkCopyColumnMapping : class
where TSqlTransaction : DbTransaction
{
// Throw an error if there are no mappings
if (mappings?.Any() != true)
{
throw new MissingMappingException("There are no mapping(s) found for this operation.");
}
// Variables needed
var result = default(int);
var entityType = entities?.FirstOrDefault()?.GetType() ?? typeof(TEntity);
var entityFields = entityType.IsDictionaryStringObject() ?
GetDictionaryStringObjectFields(entities?.FirstOrDefault() as IDictionary <string, object>) :
FieldCache.Get(entityType);
// Actual Execution
using (var sqlBulkCopy = (TSqlBulkCopy)Activator.CreateInstance(typeof(TSqlBulkCopy), connection, options, transaction))
{
// Set the destinationtable
Compiler.SetProperty <TSqlBulkCopy>(sqlBulkCopy, "DestinationTableName", tableName);
// Set the timeout
if (bulkCopyTimeout.HasValue)
{
Compiler.SetProperty <TSqlBulkCopy>(sqlBulkCopy, "BulkCopyTimeout", bulkCopyTimeout.Value);
}
// Set the batch size
if (batchSize.HasValue)
{
Compiler.SetProperty <TSqlBulkCopy>(sqlBulkCopy, "BatchSize", batchSize.Value);
}
// Add the order column
if (hasOrderingColumn)
{
mappings = AddOrderColumnMapping(mappings);
}
// Add the mappings
AddMappings <TSqlBulkCopy, TSqlBulkCopyColumnMappingCollection, TSqlBulkCopyColumnMapping>(sqlBulkCopy, mappings);
// Open the connection and do the operation
await connection.EnsureOpenAsync();
using (var reader = new DataEntityDataReader <TEntity>(tableName, entities, connection, transaction, hasOrderingColumn))
{
var writeToServerMethod = Compiler.GetParameterizedMethodFunc <TSqlBulkCopy, Task>("WriteToServerAsync", new[] { typeof(DbDataReader), typeof(CancellationToken) });
await writeToServerMethod(sqlBulkCopy, new object[] { reader, cancellationToken });
result = reader.RecordsAffected;
}
// Ensure the result
if (result <= 0)
{
// Set the return value
var rowsCopiedFieldOrProperty = Compiler.GetFieldGetterFunc <TSqlBulkCopy, int>("_rowsCopied") ??
Compiler.GetPropertyGetterFunc <TSqlBulkCopy, int>("RowsCopied");
result = (int)rowsCopiedFieldOrProperty?.Invoke(sqlBulkCopy);
}
}
// Return the result
return(result);
}
