/// <summary>
/// Insert multiple rows in the table in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</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="entities">The list of data entity or dynamic objects to be inserted.</param>
/// <param name="batchSize">The batch size of the insertion.</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 number of inserted rows in the table.</returns>
internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection,
string tableName,
IEnumerable <TEntity> entities,
int batchSize = Constant.DefaultBatchOperationSize,
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();
// Guard the parameters
GuardInsertAll(entities);
// Validate the batch size
batchSize = (dbSetting.IsMultiStatementExecutable == true) ? Math.Min(batchSize, entities.Count()) : 1;
// Get the context
var context = InsertAllExecutionContextProvider.Create <TEntity>(connection,
tableName,
batchSize,
fields,
hints,
transaction,
statementBuilder,
skipIdentityCheck);
var sessionId = Guid.Empty;
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entities, null);
trace.BeforeInsertAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(context.CommandText);
}
return(0);
}
context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
entities = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Execution variables
var result = 0;
// Make sure to create transaction if there is no passed one
var hasTransaction = (transaction != null || Transaction.Current != null);
try
{
// Ensure the connection is open
await connection.EnsureOpenAsync();
if (hasTransaction == false)
{
// Create a transaction
transaction = connection.BeginTransaction();
}
// Create the command
using (var command = (DbCommand)connection.CreateCommand(context.CommandText,
CommandType.Text, commandTimeout, transaction))
{
// Directly execute if the entities is only 1 (performance)
if (context.BatchSize == 1)
{
foreach (var entity in entities.AsList())
{
// Set the values
context.SingleDataEntityParametersSetterFunc?.Invoke(command, entity);
// Prepare the command
if (dbSetting.IsPreparable)
{
command.Prepare();
}
// Actual Execution
var returnValue = Converter.DbNullToNull(await command.ExecuteScalarAsync());
// Get explicity if needed
if (Equals(returnValue, null) == true && dbSetting.IsMultiStatementExecutable == false)
{
returnValue = Converter.DbNullToNull(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction));
}
// Set the return value
if (returnValue != null)
{
context.IdentityPropertySetterFunc?.Invoke(entity, returnValue);
}
// Iterate the result
result++;
}
}
else
{
foreach (var batchEntities in entities.AsList().Split(batchSize))
{
var batchItems = batchEntities.AsList();
// Break if there is no more records
if (batchItems.Count <= 0)
{
break;
}
// Check if the batch size has changed (probably the last batch on the enumerables)
if (batchItems.Count != batchSize)
{
// Get a new execution context from cache
context = await InsertAllExecutionContextProvider.CreateAsync <TEntity>(connection,
tableName,
batchItems.Count,
fields,
hints,
transaction,
statementBuilder,
skipIdentityCheck);
// Set the command properties
command.CommandText = context.CommandText;
}
// Set the values
if (batchItems?.Count == 1)
{
context.SingleDataEntityParametersSetterFunc?.Invoke(command, batchItems.First());
}
else
{
context.MultipleDataEntitiesParametersSetterFunc?.Invoke(command, batchItems);
}
// Prepare the command
if (dbSetting.IsPreparable)
{
command.Prepare();
}
// Actual Execution
if (context.IdentityPropertySetterFunc == null)
{
result += await command.ExecuteNonQueryAsync();
}
else
{
using (var reader = await command.ExecuteReaderAsync())
{
var index = 0;
// Get the results
do
{
if (await reader.ReadAsync())
{
var value = Converter.DbNullToNull(reader.GetValue(0));
context.IdentityPropertySetterFunc.Invoke(batchItems[index], value);
result++;
}
index++;
}while (await reader.NextResultAsync());
}
}
}
}
}
if (hasTransaction == false)
{
// Commit the transaction
transaction.Commit();
}
}
catch
{
if (hasTransaction == false)
{
// Rollback for any exception
transaction.Rollback();
}
throw;
}
finally
{
if (hasTransaction == false)
{
// Rollback and dispose the transaction
transaction.Dispose();
}
}
// After Execution
if (trace != null)
{
trace.AfterInsertAll(new TraceLog(sessionId, context.CommandText, entities, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}
/// <summary>
/// Inserts multiple data in the database in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</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="entities">The list of data entity or dynamic objects to be inserted.</param>
/// <param name="batchSize">The batch size of the insertion.</param>
/// <param name="fields">The mapping list of <see cref="Field"/> objects 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 number of inserted rows.</returns>
internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection,
string tableName,
IEnumerable <TEntity> entities,
int batchSize = Constant.DefaultBatchOperationSize,
IEnumerable <Field> fields = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null,
bool skipIdentityCheck = false)
where TEntity : class
{
// Guard the parameters
var count = GuardInsertAll(entities);
// Validate the batch size
batchSize = Math.Min(batchSize, count);
// Get the function
var callback = new Func <int, InsertAllExecutionContext <TEntity> >((int batchSizeValue) =>
{
// Variables needed
var identity = (Field)null;
var dbFields = DbFieldCache.Get(connection, tableName);
var inputFields = (IEnumerable <DbField>)null;
var outputFields = (IEnumerable <DbField>)null;
var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity);
// Set the identity value
if (skipIdentityCheck == false)
{
identity = IdentityCache.Get <TEntity>()?.AsField();
if (identity == null && identityDbField != null)
{
identity = FieldCache.Get <TEntity>().FirstOrDefault(field =>
field.UnquotedName.ToLower() == identityDbField.UnquotedName.ToLower());
}
}
// Filter the actual properties for input fields
inputFields = dbFields?
.Where(dbField => dbField.IsIdentity == false)
.Where(dbField =>
fields.FirstOrDefault(field => field.UnquotedName.ToLower() == dbField.UnquotedName.ToLower()) != null)
.AsList();
// Set the output fields
if (batchSizeValue > 1)
{
outputFields = identityDbField?.AsEnumerable();
}
// Variables for the context
var multipleEntitiesFunc = (Action <DbCommand, IList <TEntity> >)null;
var identitySettersFunc = (List <Action <TEntity, DbCommand> >)null;
var singleEntityFunc = (Action <DbCommand, TEntity>)null;
var identitySetterFunc = (Action <TEntity, object>)null;
// Get if we have not skipped it
if (skipIdentityCheck == false && identity != null)
{
if (batchSizeValue <= 1)
{
identitySetterFunc = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity);
}
else
{
identitySettersFunc = new List <Action <TEntity, DbCommand> >();
for (var index = 0; index < batchSizeValue; index++)
{
identitySettersFunc.Add(FunctionCache.GetDataEntityPropertySetterFromDbCommandParameterFunction <TEntity>(identity, identity.UnquotedName, index));
}
}
}
// Identity which objects to set
if (batchSizeValue <= 1)
{
singleEntityFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>(
string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"),
inputFields?.AsList(),
null);
}
else
{
multipleEntitiesFunc = FunctionCache.GetDataEntitiesDbCommandParameterSetterFunction <TEntity>(
string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"),
inputFields?.AsList(),
outputFields,
batchSizeValue);
}
// Identify the requests
var insertAllRequest = (InsertAllRequest)null;
var insertRequest = (InsertRequest)null;
// Create a different kind of requests
if (typeof(TEntity) == typeof(object))
{
if (batchSizeValue > 1)
{
insertAllRequest = new InsertAllRequest(tableName,
connection,
fields,
batchSizeValue,
statementBuilder);
}
else
{
insertRequest = new InsertRequest(tableName,
connection,
fields,
statementBuilder);
}
}
else
{
if (batchSizeValue > 1)
{
insertAllRequest = new InsertAllRequest(typeof(TEntity),
connection,
fields,
batchSizeValue,
statementBuilder);
}
else
{
insertRequest = new InsertRequest(typeof(TEntity),
connection,
fields,
statementBuilder);
}
}
// Return the value
return(new InsertAllExecutionContext <TEntity>
{
CommandText = batchSizeValue > 1 ? CommandTextCache.GetInsertAllText(insertAllRequest) : CommandTextCache.GetInsertText(insertRequest),
InputFields = inputFields,
OutputFields = outputFields,
BatchSize = batchSizeValue,
SingleDataEntityParametersSetterFunc = singleEntityFunc,
MultipleDataEntitiesParametersSetterFunc = multipleEntitiesFunc,
IdentityPropertySetterFunc = identitySetterFunc,
IdentityPropertySettersFunc = identitySettersFunc
});
});
// Get the context
var context = (InsertAllExecutionContext <TEntity>)null;
// Identify the number of entities (performance), get an execution context from cache
context = batchSize == 1 ? InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, 1, callback) :
InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchSize, callback);
// Before Execution
if (trace != null)
{
var cancellableTraceLog = new CancellableTraceLog(context.CommandText, entities, null);
trace.BeforeInsertAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(context.CommandText);
}
return(0);
}
context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
entities = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Execution variables
var result = 0;
// Make sure to create transaction if there is no passed one
var hasTransaction = (transaction != null);
try
{
// Ensure the connection is open
await connection.EnsureOpenAsync();
if (hasTransaction == false)
{
// Create a transaction
transaction = connection.BeginTransaction();
}
// Create the command
using (var command = (DbCommand)connection.CreateCommand(context.CommandText,
CommandType.Text, commandTimeout, transaction))
{
// Directly execute if the entities is only 1 (performance)
if (context.BatchSize == 1)
{
foreach (var entity in entities)
{
// Set the values
context.SingleDataEntityParametersSetterFunc(command, entity);
// Actual Execution
var returnValue = ObjectConverter.DbNullToNull(await command.ExecuteScalarAsync());
// Set the return value
if (returnValue != null)
{
context.IdentityPropertySetterFunc?.Invoke(entity, returnValue);
}
// Iterate the result
result++;
}
}
else
{
foreach (var batchEntities in entities.Split(batchSize))
{
var batchItems = batchEntities.AsList();
// Break if there is no more records
if (batchItems.Count <= 0)
{
break;
}
// Check if the batch size has changed (probably the last batch on the enumerables)
if (batchItems.Count != batchSize)
{
// Get a new execution context from cache
context = InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchItems.Count, callback);
// Set the command properties
command.CommandText = context.CommandText;
// Prepare the command
command.Prepare();
}
// Set the values
context.MultipleDataEntitiesParametersSetterFunc(command, batchItems);
// Actual Execution
result += await command.ExecuteNonQueryAsync();
// Set the identities
if (context.IdentityPropertySettersFunc != null && command.Parameters.Count > 0)
{
for (var index = 0; index < batchItems.Count; index++)
{
var func = context.IdentityPropertySettersFunc.ElementAt(index);
func(batchItems[index], command);
}
}
}
}
}
if (hasTransaction == false)
{
// Commit the transaction
transaction.Commit();
}
}
catch
{
if (hasTransaction == false)
{
// Rollback for any exception
transaction.Rollback();
}
throw;
}
finally
{
if (hasTransaction == false)
{
// Rollback and dispose the transaction
transaction.Dispose();
}
}
// After Execution
if (trace != null)
{
trace.AfterInsertAll(new TraceLog(context.CommandText, entities, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}