/// <summary>
/// Queries a data from the database table function in an asynchronous way.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="funcName">Function name.</param>
/// <param name="parameters">Collection of function parameters.</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 Task <IEnumerable <TEntity> > QueryAllTableFuncAsyncInternal <TEntity>(this IDbConnection connection,
string funcName,
IEnumerable <QueryField> parameters,
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
{
return(QueryAllTableFuncAsyncInternalBase <TEntity>(connection: connection,
funcName: funcName,
parameters: parameters,
fields: FieldCache.Get <TEntity>(),
orderBy: orderBy,
hints: hints,
cacheKey: cacheKey,
cacheItemExpiration: cacheItemExpiration,
commandTimeout: commandTimeout,
transaction: transaction,
cache: cache,
trace: trace,
statementBuilder: statementBuilder));
}
/// <summary>
/// Inserts a new data in the database.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity object.</typeparam>
/// <typeparam name="TResult">The target type of the result.</typeparam>
/// <param name="connection">The connection object to be used.</param>
/// <param name="entity">The data entity object to be inserted.</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>
/// <returns>The value of the identity field if present, otherwise, the value of primary field.</returns>
internal static TResult InsertInternal <TEntity, TResult>(this IDbConnection connection,
TEntity entity,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
return(InsertInternalBase <TEntity, TResult>(connection: connection,
tableName: ClassMappedNameCache.Get <TEntity>(),
entity: entity,
fields: FieldCache.Get <TEntity>(),
commandTimeout: commandTimeout,
transaction: transaction,
trace: trace,
statementBuilder: statementBuilder,
skipIdentityCheck: false));
}
/// <summary>
/// Inserts a new row in the table.
/// </summary>
/// <typeparam name="TEntity">The type of the data entity.</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 object to be inserted.</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>
/// <returns>The value of the identity field if present, otherwise, the value of the primary field.</returns>
public static TResult Insert <TEntity, TResult>(this IDbConnection connection,
string tableName,
TEntity entity,
string hints = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
return(InsertInternal <TResult>(connection: connection,
tableName: tableName,
entity: entity,
fields: FieldCache.Get <TEntity>() ?? Field.Parse(entity),
hints: hints,
commandTimeout: commandTimeout,
transaction: transaction,
trace: trace,
statementBuilder: statementBuilder));
}
/// <summary>
/// Inserts multiple data in 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 data entity objects to be inserted.</param>
/// <param name="batchSize">The batch size of the insertion.</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>
/// <returns>The number of inserted rows.</returns>
internal static int InsertAllInternal <TEntity>(this IDbConnection connection,
IEnumerable <TEntity> entities,
int batchSize = Constant.DefaultBatchOperationSize,
int?commandTimeout = null,
IDbTransaction transaction = null,
ITrace trace = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
// Return the result
return(InsertAllInternalBase <TEntity>(connection: connection,
tableName: ClassMappedNameCache.Get <TEntity>(),
entities: entities,
batchSize: batchSize,
fields: FieldCache.Get <TEntity>(),
commandTimeout: commandTimeout,
transaction: transaction,
trace: trace,
statementBuilder: statementBuilder,
skipIdentityCheck: false));
}
/// <summary>
/// Append the mapped properpties name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The current instance.</returns>
public QueryBuilder ParametersFrom <TEntity>(int index,
IDbSetting dbSetting)
where TEntity : class
{
return(ParametersFrom(FieldCache.Get <TEntity>(), index, dbSetting));
}
/// <summary>
/// Appends a stringified fields and parameters to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
/// <returns>The current instance.</returns>
public QueryBuilder FieldsAndParametersFrom <TEntity>(int index,
IDbSetting dbSetting)
where TEntity : class
{
return(Append(FieldCache.Get <TEntity>()?.AsFieldsAndParameters(index, dbSetting).Join(", ")));
}
/// <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);
}
/// <summary>
/// Query all the data from 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="orderBy">The order definition of the fields to be used.</param>
/// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</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 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(cacheKey, false);
if (item != null)
{
return(Task.FromResult((IEnumerable <TEntity>)item.Value));
}
}
// Variables
var commandType = CommandType.Text;
var request = new QueryAllRequest(typeof(TEntity),
connection,
FieldCache.Get <TEntity>(),
orderBy,
hints,
statementBuilder);
var commandText = CommandTextCache.GetQueryAllText(request);
var param = (object)null;
// Database pre-touch for field definitions
DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>());
// 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(Task.FromResult <IEnumerable <TEntity> >(null));
}
commandText = (cancellableTraceLog.Statement ?? commandText);
param = (cancellableTraceLog.Parameter ?? param);
}
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
var result = ExecuteQueryAsyncInternal <TEntity>(connection: connection,
commandText: commandText,
param: param,
commandType: commandType,
commandTimeout: commandTimeout,
transaction: transaction,
basedOnFields: false,
skipCommandArrayParametersCheck: true);
// After Execution
if (trace != null)
{
trace.AfterQueryAll(new TraceLog(commandText, param, result,
DateTime.UtcNow.Subtract(beforeExecutionTime)));
}
// Set Cache
if (cacheKey != null /* && result.Result?.Any() == true */)
{
cache?.Add(cacheKey, result, cacheItemExpiration);
}
// Result
return(result);
}
/// <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 IEnumerable <TEntity> QueryAllInternalBase <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;
// 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 = ExecuteQueryInternal <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>
/// 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>
/// Append the mapped properpties name to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="prefix">The prefix to the parameters.</param>
/// <returns>The current instance.</returns>
public QueryBuilder ParametersFrom <TEntity>(int index = 0, string prefix = Constant.DefaultParameterPrefix)
where TEntity : class
{
return(ParametersFrom(FieldCache.Get <TEntity>(), index, prefix));
}
/// <summary>
/// Appends a stringified fields and parameters to the SQL Query Statement.
/// </summary>
/// <typeparam name="TEntity">The type of data entity object bound for the SQL Statement to be created.</typeparam>
/// <param name="index">The parameter index.</param>
/// <param name="prefix">The prefix to the parameters.</param>
/// <returns>The current instance.</returns>
public QueryBuilder FieldsAndParametersFrom <TEntity>(int index = 0, string prefix = Constant.DefaultParameterPrefix)
where TEntity : class
{
return(Append(FieldCache.Get <TEntity>()?.AsFieldsAndParameters(index, prefix).Join(", ")));
}
/// <summary>
/// Selecting data from Sql with Sql IN clause usually requires 1 Parameter for every value, and this result in
/// safe Sql Queries, but there is a limit of 2100 parameters on a Sql Command. This method provides a safe
/// alternative implementation that is highly performant for large data sets using a list of int values (e.g Ids).
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sqlConnection"></param>
/// <param name="idList"></param>
/// <param name="filterFieldName"></param>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="orderBy"></param>
/// <param name="hints"></param>
/// <param name="cacheKey"></param>
/// <param name="cacheItemExpiration"></param>
/// <param name="commandTimeout"></param>
/// <param name="transaction"></param>
/// <param name="logTrace"></param>
/// <param name="cancellationToken"></param>
/// <param name="cache"></param>
/// <returns></returns>
public static async Task <IEnumerable <TEntity> > QueryBulkResultsByIdAsync <TEntity>(
this SqlConnection sqlConnection,
IEnumerable <int> idList,
string filterFieldName = null,
string tableName = null,
IEnumerable <Field> fields = null,
IEnumerable <OrderField> orderBy = null,
string hints = null,
string cacheKey = null,
int?cacheItemExpiration = null,
int?commandTimeout = null,
IDbTransaction transaction = null,
ICache cache = null,
Action <string> logTrace = null,
CancellationToken cancellationToken = default
) where TEntity : class
{
var connection = sqlConnection ?? throw new ArgumentNullException(nameof(sqlConnection));
var timer = Stopwatch.StartNew();
Field filterField;
if (string.IsNullOrWhiteSpace(filterFieldName))
{
//Attempt to dynamically resolve the Filter Field as the Identity or Primary Key field (if the field is a Numeric Type)!
var classProp = IdentityCache.Get <TEntity>() ?? PrimaryCache.Get <TEntity>();
if (classProp == null || !classProp.PropertyInfo.PropertyType.IsNumericType())
{
throw new ArgumentException(
$"The filter field name was not specified and an Int Id could not be dynamically resolved from the Identity or Primary Key properties for the type [{typeof(TEntity).Name}]",
nameof(filterFieldName)
);
}
filterField = new Field(classProp.GetMappedName());
}
else
{
//If Specified then we use the Filter Field Name specified and attempt to resolve it on the Model!
filterField = new Field(PropertyMappedNameCache.Get <TEntity>(filterFieldName) ?? filterFieldName);
}
var dbTableName = string.IsNullOrWhiteSpace(tableName)
? ClassMappedNameCache.Get <TEntity>()
: tableName;
//Ensure we have default fields; default is to include All Fields...
var fieldsList = fields?.ToList();
var selectFields = fieldsList?.Any() == true
? fieldsList
: FieldCache.Get <TEntity>();
//Retrieve only the select fields that are valid for the Database query!
//NOTE: We guard against duplicate values as a convenience.
var validSelectFields = await connection
.GetValidatedDbFieldsAsync(dbTableName, selectFields.Distinct())
.ConfigureAwait(false);
var dbSetting = connection.GetDbSetting();
var query = new QueryBuilder()
.Clear()
.Select().FieldsFrom(validSelectFields, dbSetting)
.From().TableNameFrom(dbTableName, dbSetting).WriteText("data")
.WriteText("INNER JOIN STRING_SPLIT(@StringSplitCsvValues, ',') split")
.On().WriteText("(data.").FieldFrom(filterField).WriteText("= split.value)")
.OrderByFrom(orderBy, dbSetting)
.HintsFrom(hints)
.End();
var commandText = query.GetString();
var commandParams = new { StringSplitCsvValues = idList.ToCsvString(false) };
logTrace?.Invoke($"Query: {commandText}");
logTrace?.Invoke($"Query Param @StringSplitCsvValues: {commandParams.StringSplitCsvValues}");
await connection.EnsureOpenAsync(cancellationToken : cancellationToken);
logTrace?.Invoke($"DB Connection Established in: {timer.ToElapsedTimeDescriptiveFormat()}");
//By creating a View Model of the data we are interested in we can easily query the View
// and teh complex many-to-many join is now encapsulated for us in the SQL View...
var results = await connection.ExecuteQueryAsync <TEntity>(
commandText,
commandParams,
commandType : CommandType.Text,
commandTimeout : commandTimeout,
transaction : transaction,
cancellationToken : cancellationToken,
cacheKey : cacheKey,
cacheItemExpiration : cacheItemExpiration,
cache : cache
).ConfigureAwait(false);
logTrace?.Invoke($"Query Execution Completed in: {timer.ToElapsedTimeDescriptiveFormat()}");
return(results);
}
/// <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>
/// <returns></returns>
private static int WriteToServerInternal <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)
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
connection.EnsureOpen();
using (var reader = new DataEntityDataReader <TEntity>(tableName, entities, connection, transaction, hasOrderingColumn))
{
var writeToServerMethod = Compiler.GetParameterizedVoidMethodFunc <TSqlBulkCopy>("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 <TSqlBulkCopy, int>("_rowsCopied") ??
Compiler.GetPropertyGetterFunc <TSqlBulkCopy, int>("RowsCopied");
result = (int)rowsCopiedFieldOrProperty?.Invoke(sqlBulkCopy);
}
}
// 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="dbFields"></param>
/// <param name="mappings"></param>
/// <param name="options"></param>
/// <param name="hints"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="batchSize"></param>
/// <param name="isReturnIdentity"></param>
/// <param name="usePhysicalPseudoTempTable"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static int BulkInsertInternalBase <TEntity>(SqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <DbField> dbFields = null,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = default,
string hints = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
bool?isReturnIdentity = null,
bool?usePhysicalPseudoTempTable = null,
SqlTransaction transaction = null)
where TEntity : class
{
// Validate
// ThrowIfNullOrEmpty(entities);
// Variables needed
var dbSetting = connection.GetDbSetting();
var hasTransaction = transaction != null;
int result;
transaction = CreateOrValidateCurrentTransaction(connection, transaction);
try
{
// Get the DB Fields
dbFields ??= DbFieldCache.Get(connection, tableName, transaction, true);
// Variables needed
var identityDbField = dbFields?.FirstOrDefault(dbField => dbField.IsIdentity);
var entityType = entities?.FirstOrDefault()?.GetType() ?? typeof(TEntity);
var entityFields = entityType.IsDictionaryStringObject() ?
GetDictionaryStringObjectFields(entities?.FirstOrDefault() as IDictionary <string, object>) :
FieldCache.Get(entityType);
var fields = dbFields?.Select(dbField => dbField.AsField());
// Filter the fields (based on mappings)
if (mappings?.Any() == true)
{
fields = fields?
.Where(e =>
mappings.Any(mapping => string.Equals(mapping.DestinationColumn, e.Name, StringComparison.OrdinalIgnoreCase)) == true);
}
else
{
// Filter the fields (based on the data entity)
if (entityFields?.Any() == true)
{
fields = fields?
.Where(e =>
entityFields.Any(f => string.Equals(f.Name, e.Name, StringComparison.OrdinalIgnoreCase)) == true);
}
// Explicitly define the mappings
mappings = fields?
.Select(e =>
new BulkInsertMapItem(e.Name, e.Name));
}
// Throw an error if there are no fields
if (fields?.Any() != true)
{
throw new MissingFieldException("There are no field(s) found for this operation.");
}
// Pseudo temp table
var withPseudoExecution = isReturnIdentity == true && identityDbField != null;
var tempTableName = CreateBulkInsertTempTableIfNecessary(connection,
tableName,
usePhysicalPseudoTempTable,
transaction,
withPseudoExecution,
dbSetting,
fields);
// WriteToServer
result = WriteToServerInternal(connection,
tempTableName ?? tableName,
entities,
mappings,
options,
bulkCopyTimeout,
batchSize,
withPseudoExecution,
transaction);
// Check if this is with pseudo
if (withPseudoExecution)
{
// Merge the actual data
var sql = GetBulkInsertSqlText(tableName,
tempTableName,
fields,
identityDbField?.AsField(),
hints,
dbSetting,
withPseudoExecution);
// Execute the SQL
using (var reader = (DbDataReader)connection.ExecuteReader(sql, commandTimeout: bulkCopyTimeout, transaction: transaction))
{
var mapping = mappings?.FirstOrDefault(e => string.Equals(e.DestinationColumn, identityDbField.Name, StringComparison.OrdinalIgnoreCase));
var identityField = mapping != null ? new Field(mapping.SourceColumn) : identityDbField.AsField();
result = SetIdentityForEntities(entities, reader, identityField);
}
// Drop the table after used
sql = GetDropTemporaryTableSqlText(tempTableName, dbSetting);
connection.ExecuteNonQuery(sql, transaction: transaction);
}
CommitTransaction(transaction, hasTransaction);
}
catch
{
RollbackTransaction(transaction, hasTransaction);
throw;
}
finally
{
DisposeTransaction(transaction, hasTransaction);
}
// Return the result
return(result);
}