/// <averagemary>
/// Computes the average value of the target field in an asynchronous way.
/// </averagemary>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="request">The actual <see cref="AverageAllRequest"/> object.</param>
/// <param name="param">The mapped object parameters.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The average value of the target field.</returns>
internal static async Task <TResult> AverageAllAsyncInternalBase <TResult>(this IDbConnection connection,
AverageAllRequest request,
object param,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
CancellationToken cancellationToken = default)
{
// Variables
var commandType = CommandType.Text;
var commandText = CommandTextCache.GetAverageAllText(request);
var sessionId = Guid.Empty;
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, param, null);
trace.BeforeAverageAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(commandText);
}
return(default(TResult));
}
commandText = (cancellableTraceLog.Statement ?? commandText);
param = (cancellableTraceLog.Parameter ?? param);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = await ExecuteScalarAsyncInternal <TResult>(connection : connection,
commandText : commandText,
param : param,
commandType : commandType,
cacheKey : null,
cacheItemExpiration : null,
commandTimeout : commandTimeout,
transaction : transaction,
cache : null,
cancellationToken : cancellationToken,
entityType : request.Type,
dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken),
skipCommandArrayParametersCheck : true);
// After Execution
if (trace != null)
{
trace.AfterAverageAll(new TraceLog(sessionId, commandText, param, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Result
return(result);
}
/// <summary>
/// Truncates a table from the database in an asychronous way.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="request">The actual <see cref="TruncateRequest"/> object.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows affected.</returns>
internal static async Task <int> TruncateAsyncInternalBase(this IDbConnection connection,
TruncateRequest request,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
CancellationToken cancellationToken = default)
{
// Variables
var commandType = CommandType.Text;
var commandText = CommandTextCache.GetTruncateText(request);
var sessionId = Guid.Empty;
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, null, null);
trace.BeforeTruncate(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(commandText);
}
}
commandText = (cancellableTraceLog.Statement ?? commandText);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = await ExecuteNonQueryAsyncInternal(connection : connection,
commandText : commandText,
param : null,
commandType : commandType,
commandTimeout : commandTimeout,
transaction : transaction,
cancellationToken : cancellationToken,
entityType : request.Type,
dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken),
skipCommandArrayParametersCheck : true);
// After Execution
if (trace != null)
{
trace.AfterTruncate(new TraceLog(sessionId, commandText, null, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}
/// <summary>
/// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
/// </summary>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
public async Task InitializeAsync(CancellationToken cancellationToken = default)
{
if (IsInitialized)
{
return;
}
var dbFields = (IEnumerable <DbField>)null;
if (Connection != null)
{
dbFields = await DbFieldCache.GetAsync(Connection, TableName, Transaction, false, cancellationToken);
}
InitializeInternal(dbFields);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <IEnumerable <Field> > GetActualFieldsAsync(IDbConnection connection,
string tableName,
IEnumerable <Field> fields,
IDbTransaction transaction,
CancellationToken cancellationToken = default)
{
if (fields?.Any() != true)
{
return(null);
}
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
return(GetActualFieldsInternal(fields, dbFields, connection.GetDbSetting()));
}
/// <summary>
/// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
/// </summary>
public async Task InitializeAsync()
{
if (IsInitialized)
{
return;
}
var dbFields = (IEnumerable <DbField>)null;
if (Connection != null)
{
dbFields = await DbFieldCache.GetAsync(Connection, ClassMappedNameCache.Get <TEntity>(), Transaction);
}
InitializeInternal(dbFields);
}
/// <summary>
/// Ensures that the <see cref="DbFieldCache.GetAsync(IDbConnection, string, IDbTransaction)"/> method is being called one.
/// </summary>
/// <typeparam name="TEntity">The type of the entity to check.</typeparam>
/// <param name="transaction">The transaction object that is currently in used.</param>
private async Task EnsureSingleCallForDbFieldCacheGeAsync <TEntity>(IDbTransaction transaction)
where TEntity : class
{
var key = GetDbFieldGetCallsCacheKey <TEntity>();
var dbFields = (IEnumerable <DbField>)null;
// Try get the value
if (cache.TryGetValue(key, out dbFields) == false)
{
using (var connection = (IDbConnection)Activator.CreateInstance(this.connection.GetType(), new object[] { connectionString }))
{
dbFields = await DbFieldCache.GetAsync(connection, ClassMappedNameCache.Get <TEntity>(), transaction, false);
cache.TryAdd(key, dbFields);
}
}
}
public static async Task <IEnumerable <OrderField> > GetValidatedDbFieldsAsync(
this DbConnection dbConnection,
string tableName,
IEnumerable <OrderField> sortFields
)
{
if (dbConnection == null || sortFields == null || string.IsNullOrWhiteSpace(tableName))
{
return(null);
}
//FILTER for only VALID fields from the sort fields by safely comparing to the valid fields from the DB Schema!
//NOTE: Per RepoDb source we need to compare unquoted names to get pure matches...
var dbSetting = dbConnection.GetDbSetting();
var dbFields = await DbFieldCache
.GetAsync(dbConnection, tableName, null, false)
.ConfigureAwait(false);
var dbFieldLookup = dbFields.ToLookup(f => f.Name.AsUnquoted(dbSetting).ToLower());
var safeSortFields = sortFields.Where(s => dbFieldLookup.Contains(s.Name.AsUnquoted(dbSetting).ToLower()));
return(safeSortFields);
}
/// <summary>
/// Inserts a new row in the table in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
/// <typeparam name="TResult">The target type of the result.</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="entity">The data entity or dynamic object to be inserted.</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 value of the identity field if present, otherwise, the value of the primary field.</returns>
internal async static Task <TResult> InsertAsyncInternalBase <TEntity, TResult>(this IDbConnection connection,
string tableName,
TEntity entity,
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();
// Get the database fields
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);
// Get the function
var callback = new Func <InsertExecutionContext <TEntity> >(() =>
{
// Variables needed
var identity = (Field)null;
var inputFields = (IEnumerable <DbField>)null;
var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity);
// Set the identity field
if (skipIdentityCheck == false)
{
identity = IdentityCache.Get <TEntity>()?.AsField();
if (identity == null && identityDbField != null)
{
identity = FieldCache.Get <TEntity>().FirstOrDefault(field =>
string.Equals(field.Name.AsUnquoted(true, dbSetting), identityDbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase));
}
}
// Filter the actual properties for input fields
inputFields = dbFields?
.Where(dbField => dbField.IsIdentity == false)
.Where(dbField =>
fields.FirstOrDefault(field =>
string.Equals(field.Name.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) != null)
.AsList();
// Variables for the entity action
var identityPropertySetter = (Action <TEntity, object>)null;
// Get the identity setter
if (skipIdentityCheck == false && identity != null)
{
identityPropertySetter = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity);
}
// Identify the requests
var insertRequest = (InsertRequest)null;
// Create a different kind of requests
if (typeof(TEntity) == StaticType.Object)
{
insertRequest = new InsertRequest(tableName,
connection,
transaction,
fields,
hints,
statementBuilder);
}
else
{
insertRequest = new InsertRequest(typeof(TEntity),
connection,
transaction,
fields,
hints,
statementBuilder);
}
// Return the value
return(new InsertExecutionContext <TEntity>
{
CommandText = CommandTextCache.GetInsertText(insertRequest),
InputFields = inputFields,
ParametersSetterFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>(
string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".Insert"),
inputFields?.AsList(),
null,
dbSetting),
IdentityPropertySetterFunc = identityPropertySetter
});
});
// Get the context
var context = InsertExecutionContextCache <TEntity> .Get(tableName, fields, callback);
var sessionId = Guid.Empty;
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entity, null);
trace.BeforeInsert(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(context.CommandText);
}
return(default(TResult));
}
context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
entity = (TEntity)(cancellableTraceLog.Parameter ?? entity);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Execution variables
var result = default(TResult);
// Create the command
using (var command = (DbCommand)(await connection.EnsureOpenAsync()).CreateCommand(context.CommandText,
CommandType.Text, commandTimeout, transaction))
{
// Set the values
context.ParametersSetterFunc(command, entity);
// Actual Execution
result = Converter.ToType <TResult>(await command.ExecuteScalarAsync());
// Get explicity if needed
if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false)
{
result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction));
}
// Set the return value
if (Equals(result, default(TResult)) == false)
{
context.IdentityPropertySetterFunc?.Invoke(entity, result);
}
}
// After Execution
if (trace != null)
{
trace.AfterInsert(new TraceLog(sessionId, context.CommandText, entity, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}
/// <summary>
/// Inserts a new row in the table in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
/// <typeparam name="TResult">The target type of the result.</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="entity">The data entity or dynamic object to be inserted.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The value of the identity field if present, otherwise, the value of the primary field.</returns>
internal async static Task <TResult> InsertAsyncInternalBase <TEntity, TResult>(this IDbConnection connection,
string tableName,
TEntity entity,
IEnumerable <Field> fields = null,
string hints = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
// Variables needed
var dbSetting = connection.GetDbSetting();
// Get the database fields
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
// Get the context
var context = await InsertExecutionContextProvider.CreateAsync <TEntity>(connection,
tableName,
fields,
hints,
transaction,
statementBuilder,
cancellationToken);
var sessionId = Guid.Empty;
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entity, null);
trace.BeforeInsert(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(context.CommandText);
}
return(default(TResult));
}
context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
entity = (TEntity)(cancellableTraceLog.Parameter ?? entity);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Execution variables
var result = default(TResult);
// Create the command
using (var command = (DbCommand)(await connection.EnsureOpenAsync(cancellationToken)).CreateCommand(context.CommandText,
CommandType.Text, commandTimeout, transaction))
{
// Set the values
context.ParametersSetterFunc(command, entity);
// Actual Execution
result = Converter.ToType <TResult>(await command.ExecuteScalarAsync(cancellationToken));
// Get explicity if needed
if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false)
{
result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction, cancellationToken));
}
// Set the return value
if (Equals(result, default(TResult)) == false)
{
context.IdentityPropertySetterFunc?.Invoke(entity, result);
}
}
// After Execution
if (trace != null)
{
trace.AfterInsert(new TraceLog(sessionId, context.CommandText, entity, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Return the result
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="reader"></param>
/// <param name="qualifiers"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="keepIdentity"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryBulkDeleteBaseAsync(this NpgsqlConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
bool keepIdentity = true,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
{
var dbSetting = connection.GetDbSetting();
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var pseudoTableName = tableName;
var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;
return(await PseudoBasedBinaryImportAsync(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName, dbSetting),
// getMappings
() =>
{
var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
var includePrimary = true;
return mappings = mappings?.Any() == true ? mappings :
GetMappings(reader,
dbFields,
includePrimary,
includeIdentity,
dbSetting);
},
// binaryImport
async (tableName) =>
await connection.BinaryImportAsync(tableName,
reader,
mappings,
dbFields,
bulkCopyTimeout,
identityBehavior,
dbSetting,
transaction,
cancellationToken),
// getDeleteToPseudoCommandText
() =>
GetDeleteCommandText(pseudoTableName,
tableName,
mappings.Select(mapping => new Field(mapping.DestinationColumn)),
qualifiers,
dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
identityBehavior,
dbSetting),
// setIdentities
null,
qualifiers,
false,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
dbSetting,
transaction : transaction,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="qualifiers"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="keepIdentity"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryBulkDeleteBaseAsync <TEntity>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool keepIdentity = true,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
var entityType = entities?.First()?.GetType() ?? typeof(TEntity); // Solving the anonymous types
var isDictionary = entityType.IsDictionaryStringObject();
var dbSetting = connection.GetDbSetting();
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var pseudoTableName = tableName;
var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;
return(await PseudoBasedBinaryImportAsync(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName ?? ClassMappedNameCache.Get <TEntity>(), dbSetting),
// getMappings
() =>
{
var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
var includePrimary = true;
return mappings = mappings?.Any() == true ? mappings :
isDictionary ?
GetMappings(entities?.First() as IDictionary <string, object>,
dbFields,
includePrimary,
includeIdentity,
dbSetting) :
GetMappings(dbFields,
PropertyCache.Get(entityType),
includePrimary,
includeIdentity,
dbSetting);
},
// binaryImport
async (tableName) =>
await connection.BinaryImportAsync <TEntity>(tableName,
entities,
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction,
cancellationToken),
// getDeleteToPseudoCommandText
() =>
GetDeleteCommandText(pseudoTableName,
tableName,
mappings.Select(mapping => new Field(mapping.DestinationColumn)),
qualifiers,
dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
identityBehavior,
dbSetting),
// setIdentities
(identityResults) =>
SetIdentities(entityType, entities, dbFields, identityResults, dbSetting),
qualifiers,
false,
identityBehavior,
pseudoTableType,
dbSetting,
transaction,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TPrimaryKey"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="primaryKeys"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryBulkDeleteByKeyBaseAsync <TPrimaryKey>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TPrimaryKey> primaryKeys,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
{
var identityBehavior = BulkImportIdentityBehavior.Unspecified;
var dbSetting = connection.GetDbSetting();
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var primaryKey = dbFields.FirstOrDefault(dbField => dbField.IsPrimary);
var pseudoTableName = tableName;
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null;
return(await PseudoBasedBinaryImportAsync(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkDeleteByKeyPseudoTableName(tableName ?? ClassMappedNameCache.Get <TPrimaryKey>(), dbSetting),
// getMappings
() =>
mappings = new[]
{
new NpgsqlBulkInsertMapItem(primaryKey.Name, primaryKey.Name)
},
// binaryImport
async (tableName) =>
await connection.BinaryImportAsync(tableName,
GetExpandoObjectData(primaryKeys, primaryKey.AsField()),
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction,
cancellationToken),
// getDeleteToPseudoCommandText
() =>
GetDeleteByKeyCommandText(pseudoTableName,
tableName,
dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
dbSetting),
// setIdentities
null,
null,
false,
identityBehavior,
pseudoTableType,
dbSetting,
transaction,
cancellationToken));
}
/// <summary>
/// Query all the data from the table.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="cacheKey">
/// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database.
/// This will only work if the 'cache' argument is set.
/// </param>
/// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="cache">The cache object 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>
/// <returns>An enumerable list of data entity objects.</returns>
internal static async Task <IEnumerable <TEntity> > QueryAllAsyncInternalBase <TEntity>(this IDbConnection connection,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
// Get Cache
if (cacheKey != null)
{
var item = cache?.Get <IEnumerable <TEntity> >(cacheKey, false);
if (item != null)
{
return(item.Value);
}
}
// Variables
var commandType = CommandType.Text;
var request = new QueryAllRequest(typeof(TEntity),
connection,
transaction,
FieldCache.Get <TEntity>(),
orderBy,
hints,
statementBuilder);
var commandText = CommandTextCache.GetQueryAllText(request);
var param = (object)null;
var sessionId = Guid.Empty;
// Database pre-touch for field definitions
await DbFieldCache.GetAsync(connection, ClassMappedNameCache.Get <TEntity>(), transaction);
// Before Execution
if (trace != null)
{
sessionId = Guid.NewGuid();
var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, param, null);
trace.BeforeQueryAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(commandText);
}
return(null);
}
commandText = (cancellableTraceLog.Statement ?? commandText);
param = (cancellableTraceLog.Parameter ?? param);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = await ExecuteQueryAsyncInternal <TEntity>(connection : connection,
commandText : commandText,
param : param,
commandType : commandType,
commandTimeout : commandTimeout,
transaction : transaction,
skipCommandArrayParametersCheck : true);
// After Execution
if (trace != null)
{
trace.AfterQueryAll(new TraceLog(sessionId, commandText, param, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Set Cache
if (cacheKey != null)
{
cache?.Add(cacheKey, result, cacheItemExpiration, false);
}
// 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>
/// 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="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 = DataRowState.Unchanged,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
SqlTransaction transaction = null)
{
// Validate the objects
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(connection, options, 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)
{
// Variables needed
var dbSetting = connection.GetDbSetting();
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.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase));
if (field != null)
{
filteredFields.Add(new Tuple <string, string>(field, dbField.Name.AsUnquoted(true, dbSetting)));
}
}
// 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>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="table"></param>
/// <param name="rowState"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="identityBehavior"></param>
/// <param name="pseudoTableType"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryBulkInsertBaseAsync(this NpgsqlConnection connection,
string tableName,
DataTable table,
DataRowState?rowState = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
{
var dbSetting = connection.GetDbSetting();
var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);
var pseudoTableName = tableName;
return(await PseudoBasedBinaryImportAsync(connection,
tableName,
bulkCopyTimeout,
dbFields,
// getPseudoTableName
() =>
pseudoTableName = GetBinaryBulkInsertPseudoTableName(tableName, dbSetting),
// getMappings
() =>
{
var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
var isPrimaryAnIdentity = IsPrimaryAnIdentity(dbFields);
var includePrimary = isPrimaryAnIdentity == false ||
(isPrimaryAnIdentity && includeIdentity);
return mappings = mappings?.Any() == true ? mappings :
GetMappings(table,
dbFields,
includePrimary,
includeIdentity,
dbSetting);
},
// binaryImport
async (tableName) =>
await connection.BinaryImportAsync(tableName,
table,
rowState,
mappings,
dbFields,
bulkCopyTimeout,
batchSize,
identityBehavior,
dbSetting,
transaction,
cancellationToken),
// getMergeToPseudoCommandText
() =>
GetInsertCommandText(pseudoTableName,
tableName,
mappings.Select(mapping => new Field(mapping.DestinationColumn)),
dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
identityBehavior,
dbSetting),
// setIdentities
(identityResults) =>
SetDataTableIdentities(table, dbFields, identityResults, dbSetting),
null,
true,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
dbSetting,
transaction : transaction,
cancellationToken));
}
/// <summary>
/// Query all the data from the database.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be queried.</param>
/// <param name="orderBy">The order definition of the fields to be used.</param>
/// <param name="hints">The table hints to be used.</param>
/// <param name="cacheKey">
/// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
/// to null would force to query from the database.
/// </param>
/// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
/// <param name="commandTimeout">The command timeout in seconds to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <param name="cache">The cache object 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>
/// <returns>An enumerable list of data entity object.</returns>
internal static async Task <IEnumerable <dynamic> > QueryAllAsyncInternalBase(this IDbConnection connection,
string tableName,
IEnumerable <Field> fields = null,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
{
// Get Cache
if (cacheKey != null)
{
var item = cache?.Get(cacheKey, false);
if (item != null)
{
return((IEnumerable <dynamic>)item.Value);
}
}
// Check the fields
if (fields?.Any() != true)
{
fields = (await DbFieldCache.GetAsync(connection, tableName, transaction))?.AsFields();
}
// Variables
var commandType = CommandType.Text;
var request = new QueryAllRequest(tableName,
connection,
transaction,
fields,
orderBy,
hints,
statementBuilder);
var commandText = CommandTextCache.GetQueryAllText(request);
var param = (object)null;
// Before Execution
if (trace != null)
{
var cancellableTraceLog = new CancellableTraceLog(commandText, param, null);
trace.BeforeQueryAll(cancellableTraceLog);
if (cancellableTraceLog.IsCancelled)
{
if (cancellableTraceLog.IsThrowException)
{
throw new CancelledExecutionException(commandText);
}
return(null);
}
commandText = (cancellableTraceLog.Statement ?? commandText);
param = (cancellableTraceLog.Parameter ?? param);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = await ExecuteQueryAsyncInternal(connection : connection,
commandText : commandText,
param : param,
commandType : commandType,
commandTimeout : commandTimeout,
transaction : transaction,
tableName : tableName,
skipCommandArrayParametersCheck : true);
// After Execution
if (trace != null)
{
trace.AfterQueryAll(new TraceLog(commandText, param, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Set Cache
if (cacheKey != null)
{
cache?.Add(cacheKey, result, cacheItemExpiration, false);
}
// 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);
}