/// <summary>
///
/// </summary>
/// <param name="entityType"></param>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="qualifiers"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <UpdateAllExecutionContext> CreateAsync(Type entityType,
IDbConnection connection,
string tableName,
IEnumerable <object> entities,
IEnumerable <Field> qualifiers,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
{
var key = GetKey(entityType, tableName, qualifiers, fields, batchSize, hints);
// Get from cache
var context = UpdateAllExecutionContextCache.Get(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var request = new UpdateAllRequest(tableName,
connection,
transaction,
fields,
qualifiers,
batchSize,
hints,
statementBuilder);
var commandText = await CommandTextCache.GetUpdateAllTextAsync(request, cancellationToken);
// Call
context = CreateInternal(entityType,
connection,
tableName,
entities,
dbFields,
qualifiers,
batchSize,
fields,
commandText);
// Add to cache
UpdateAllExecutionContextCache.Add(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="qualifiers"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <MergeExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection,
string tableName,
IEnumerable <Field> qualifiers,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, qualifiers, fields, hints);
// Get from cache
var context = MergeExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var request = new MergeRequest(tableName,
connection,
transaction,
fields,
qualifiers,
hints,
statementBuilder);
var commandText = await CommandTextCache.GetMergeTextAsync(request, cancellationToken);
// Call
context = CreateInternal <TEntity>(connection,
dbFields,
tableName,
fields,
commandText);
// Add to cache
MergeExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <param name="entityType"></param>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <InsertExecutionContext> CreateAsync(Type entityType,
IDbConnection connection,
string tableName,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
{
var key = GetKey(entityType, tableName, fields, hints);
// Get from cache
var context = InsertExecutionContextCache.Get(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var request = new InsertRequest(tableName,
connection,
transaction,
fields,
hints,
statementBuilder);
var commandText = await CommandTextCache.GetInsertTextAsync(request, cancellationToken);
// Call
context = CreateInternal(entityType,
connection,
dbFields,
tableName,
fields,
commandText);
// Add to cache
InsertExecutionContextCache.Add(entityType, key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="entities"></param>
/// <param name="tableName"></param>
/// <param name="qualifiers"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="skipIdentityCheck"></param>
/// <returns></returns>
public static async Task <MergeAllExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection,
IEnumerable <TEntity> entities,
string tableName,
IEnumerable <Field> qualifiers,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
bool skipIdentityCheck = false)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, qualifiers, fields, batchSize, hints);
// Get from cache
var context = MergeAllExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);
context = CreateInternal <TEntity>(connection,
entities,
dbFields,
tableName,
qualifiers,
batchSize,
fields,
hints,
transaction,
statementBuilder,
skipIdentityCheck);
// Add to cache
MergeAllExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="where"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static async Task <UpdateExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection,
string tableName,
QueryGroup where,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, fields, hints, where);
// Get from cache
var context = UpdateExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);
context = CreateInternal <TEntity>(connection,
tableName,
where,
dbFields,
fields,
hints,
transaction,
statementBuilder);
// Add to cache
UpdateExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <InsertAllExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection,
string tableName,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, fields, batchSize, hints);
// Get from cache
var context = InsertAllExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var commandText = (string)null;
// Create a different kind of requests
if (batchSize > 1)
{
var request = new InsertAllRequest(tableName,
connection,
transaction,
fields,
batchSize,
hints,
statementBuilder);
commandText = await CommandTextCache.GetInsertAllTextAsync(request, cancellationToken);
}
else
{
var request = new InsertRequest(tableName,
connection,
transaction,
fields,
hints,
statementBuilder);
commandText = await CommandTextCache.GetInsertTextAsync(request, cancellationToken);
}
// Call
context = CreateInternal <TEntity>(connection,
tableName,
dbFields,
batchSize,
fields,
commandText);
// Add to cache
InsertAllExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <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>
public async Task <int> BulkInsertAsync(IDbConnection connection,
string tableName,
DataTable dataTable,
DataRowState rowState = DataRowState.Unchanged,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
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((SqlConnection)connection, options, (SqlTransaction)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)
{
// Get the actual DB fields
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, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (field != null)
{
filteredFields.Add(new Tuple <string, string>(field, dbField.UnquotedName));
}
}
// 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>
/// 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="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 async Task <int> BulkInsertAsync(IDbConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
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((SqlConnection)connection, options, (SqlTransaction)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)
{
// Get the actual DB fields
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);
var fields = Enumerable.Range(0, reader.FieldCount).Select((index) => reader.GetName(index));
var filteredFields = new List <Tuple <string, string> >();
// To fix the casing problem of the bulk inserts
foreach (var dbField in dbFields)
{
var readerField = fields.FirstOrDefault(field => string.Equals(field, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (!string.IsNullOrEmpty(readerField))
{
filteredFields.Add(new Tuple <string, string>(readerField, dbField.UnquotedName));
}
}
// 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
await connection.EnsureOpenAsync();
await sqlBulkCopy.WriteToServerAsync(reader);
// Hack the 'SqlBulkCopy' object
var copiedField = GetRowsCopiedField();
// Set the return value
result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
}
// Result
return(result);
}