public int BulkSelectAndUpdate(IQueryable query, IContextTransaction contextTransaction)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
var selectBindings = this.ExtractSelectBindings(query);
var mappings = this.GetMapping();
var selectQuery = this.BuildSelect(query);
var selectParameters = selectQuery.Item2;
var requiredSelectLines = this.FixAdditionalSelectedColumn(selectQuery.Item1, selectBindings.Count);
var actualSelectQuery = string.Join(Environment.NewLine, requiredSelectLines);
// gets alias using Regex which might be a bit brittle,
// possibily find a way to get alias's from IQueryable
var columnAliasMappings = this.MapColumnsToAliases(selectBindings, mappings, requiredSelectLines);
const string fromAlias = "a";
var tableName = mappings.FullyQualifiedTableName;
var setQuery = this.BuildComplexUpdateSetQuery(mappings, fromAlias, columnAliasMappings);
var whereTableKeyEqualsSelectKey = this.BuildComplexUpdateWhere(mappings, fromAlias, columnAliasMappings);
var updateQuery =
$"UPDATE {tableName} SET {setQuery} FROM ({actualSelectQuery}) AS {fromAlias} WHERE {whereTableKeyEqualsSelectKey}";
return(this.Execute(updateQuery, selectParameters, contextTransaction));
}
public int Truncate(IContextTransaction contextTransaction)
{
var tableName = this.GetMapping().FullyQualifiedTableName;
var truncateQuery = $"TRUNCATE TABLE {tableName}";
return(this.Execute(truncateQuery, null, contextTransaction));
}
public IBulkInserter <TEntity> BuildInserter(int bufferSize, IContextTransaction contextTransaction)
{
return(this.bulkInserterFactory.BuildInserter(
this.GetMapping(),
contextTransaction,
bufferSize,
this.contextSettingsCacheService.BulkInsertTimeout <TContext>(),
true));
}
public IBulkInserter <TEntity> BuildInserter <TEntity, TContext>(
TableMapping <TEntity, TContext> mapping,
IContextTransaction contextTransaction,
int bufferSize,
int timeout,
bool preInitialise)
where TEntity : class, IEntity <TContext>, new()
where TContext : IDbContext
{
return(new BulkInserter <TEntity, TContext>(mapping, contextTransaction, bufferSize, timeout, preInitialise));
}
public int TruncateWithForeignKeys(IContextTransaction contextTransaction)
{
var tableName = this.GetMapping().FullyQualifiedTableName;
// Adapted from http://stackoverflow.com/questions/472578/dbcc-checkident-sets-identity-to-0
// Issue when previously no records and reseed to 0, will start ids at 0 instead of 1
// Changing RESEED to 1, could potentially start ids at 2
// Logic below checks if the Id column has already been seeded and if so then reseeds to 0, which will result in the next Id of 1
// if Id column has not been used then the RESEED is not required, as the next Id will be 1 anyway
var truncateQuery = $"DELETE FROM {tableName}; IF EXISTS (SELECT * FROM sys.identity_columns WHERE QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) = '{tableName}' AND last_value IS NOT NULL) DBCC CHECKIDENT ('{tableName}',RESEED, 0);";
return(this.Execute(truncateQuery, null, contextTransaction));
}
private int ExecuteAdd(IEnumerable <TEntity> items, int bufferSize, IContextTransaction contextTransaction)
{
if (contextTransaction == null)
{
if (this.session.HasCurrentTransaction())
{
return(this.ExceuteAddWithExternalTransaction(items, bufferSize, this.session.CurrentTransaction()));
}
return(this.ExecuteAddWithLocalTransaction(items, bufferSize));
}
return(this.ExceuteAddWithExternalTransaction(items, bufferSize, contextTransaction));
}
public BulkInserter(TableMapping <TEntity, TContext> tableMapping, IContextTransaction contextTransaction, int bufferSize, int timeout, bool preInitialise)
{
this.noOfInsertedItems = 0;
this.bufferSize = bufferSize;
this.timeout = timeout;
this.loclObj = new object();
this.queue = new List <TEntity>();
this.contextTransaction = contextTransaction;
this.tableMapping = tableMapping;
if (preInitialise)
{
this.EnsureInitialised();
}
}
protected int Execute(
string sql,
IEnumerable <ObjectParameter> parameters,
IContextTransaction contextTransaction)
{
if (contextTransaction == null)
{
if (this.session.HasCurrentTransaction())
{
return(this.ExecuteWithExternalTransaction(sql, parameters, this.session.CurrentTransaction()));
}
return(this.ExecuteWithLocalTransaction(sql, parameters));
}
return(this.ExecuteWithExternalTransaction(sql, parameters, contextTransaction));
}
public int BulkDelete(
IQueryable <TEntity> target,
IContextTransaction contextTransaction)
{
const string wrapperAlias = "a";
const string originalAlias = "b";
var tableName = this.GetMapping().FullyQualifiedTableName;
var queryWithPrimaryKeySelected = this.BuildSelectExtractPrimaryKeys(target);
var joinWrapperAndOriginal = this.BuildWrapperJoinOnPrimaryKeys(wrapperAlias, originalAlias);
// Delete using inner join on SELECT statement, which returns primary key collections of records to be removed
// More effecient than using WHERE PK1 in (SELECT PK1 from ....) type syntax, esp. when hundreds of records
var deleteQuery =
$"DELETE {wrapperAlias} FROM {tableName} AS {wrapperAlias} INNER JOIN ({queryWithPrimaryKeySelected.Item1}) AS {originalAlias} ON {joinWrapperAndOriginal}";
return(this.Execute(deleteQuery, queryWithPrimaryKeySelected.Item2, contextTransaction));
}
public OperationTypeFamilyService(
IOperationTypeFamilyRepository operationTypeFamilyRepository,
ISelectService selectService,
IMovementService movementService,
IMapper mapper,
IHostingEnvironment hostingEnvironment,
IContextTransaction contextTransaction,
IMailService mailService
//IOperationTypeService operationTypeService
)
{
_operationTypeFamilyRepository = operationTypeFamilyRepository;
_selectService = selectService;
_mapper = mapper;
_movementService = movementService;
_hostingEnvironment = hostingEnvironment;
_contextTransaction = contextTransaction;
_mailService = mailService;
//_operationTypeService = operationTypeService;
}
private void ExecuteSelectAndAdd(IQueryable query, IContextTransaction contextTransaction)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
var mappings = this.GetMapping();
var selectBindings = this.ExtractSelectBindings(query);
var selectQuery = this.BuildSelect(query);
var selectParameters = selectQuery.Item2;
var tableName = mappings.FullyQualifiedTableName;
var orderedColumns = this.MapPropertiesToColumns(selectBindings, mappings);
var requiredSelectLines = this.FixAdditionalSelectedColumn(selectQuery.Item1, selectBindings.Count);
var actualSelectQuery = string.Join(Environment.NewLine, requiredSelectLines);
var insertQuery = $"INSERT INTO {tableName} ({orderedColumns}) {actualSelectQuery}";
this.Execute(insertQuery, selectParameters, contextTransaction);
}
protected int ExecuteWithExternalTransaction(
string sql,
IEnumerable <ObjectParameter> parameters,
IContextTransaction contextTransaction)
{
if (contextTransaction == null)
{
throw new ArgumentNullException(nameof(contextTransaction));
}
var sqlTransaction = contextTransaction.UnderlyingTransaction <SqlTransaction>();
if (sqlTransaction == null)
{
throw new InvalidOperationException("Bulk Inserter requires a SQL Transaction");
}
var sqlConnection = sqlTransaction.Connection;
return(this.ExecuteBulkNonQuery(sql, parameters, sqlTransaction, sqlConnection));
}
public int BulkUpdate(
IQueryable <TEntity> target,
Expression <Func <TEntity, TEntity> > updateExpression,
IContextTransaction contextTransaction)
{
const string wrapperAlias = "a";
const string originalAlias = "b";
var tableName = this.GetMapping().FullyQualifiedTableName;
var setQuery = this.BuildSetQuery(updateExpression, wrapperAlias);
var selectQuery = this.BuildSelectExtractPrimaryKeys(target);
var joinWrapperAndOriginal = this.BuildWrapperJoinOnPrimaryKeys(wrapperAlias, originalAlias);
var allParameters = setQuery.Item2.ToList().Union(selectQuery.Item2.ToList());
// Update using inner join on SELECT statement, which returns primary key collections of records to be updated
// More effecient than using WHERE PK1 in (SELECT PK1 from ....) syntax
var updateQuery =
$"UPDATE {wrapperAlias} SET {setQuery.Item1} FROM {tableName} AS {wrapperAlias} INNER JOIN ({selectQuery.Item1}) AS {originalAlias} ON {joinWrapperAndOriginal}";
return(this.Execute(updateQuery, allParameters, contextTransaction));
}
public AccountStatementImportService(
IMapper mapper,
IAccountStatementImportRepository accountStatementImportRepository,
IBankFileDefinitionService bankFileDefinitionService,
IAccountStatementImportFileService accountStatementImportFileService,
IAccountService accountService,
IParameterService parameterService,
IContextTransaction contextTransaction,
IBanquePopulaireImportFileService banquePopulaireImportFileService,
ICreditAgricoleImportFileService creditAgricoleImportFileService)
{
_mapper = mapper;
_accountStatementImportRepository = accountStatementImportRepository;
_bankFileDefinitionService = bankFileDefinitionService;
_accountStatementImportFileService = accountStatementImportFileService;
_accountService = accountService;
_parameterService = parameterService;
_contextTransaction = contextTransaction;
_banquePopulaireImportFileService = banquePopulaireImportFileService;
_creditAgricoleImportFileService = creditAgricoleImportFileService;
}
public int BulkAdd(IEnumerable <TEntity> items, int bufferSize, IContextTransaction contextTransaction)
{
return(this.ExecuteAdd(items, bufferSize, contextTransaction));
}
public int BulkAdd(IEnumerable <TEntity> items, IContextTransaction contextTransaction)
{
return(this.BulkAdd(items, 5000, contextTransaction));
}
public int BulkAdd(TEntity item, IContextTransaction contextTransaction)
{
return(this.BulkAdd(new[] { item }, contextTransaction));
}
private int ExceuteAddWithExternalTransaction(IEnumerable <TEntity> items, int bufferSize, IContextTransaction contextTransaction)
{
using (
var inserter = this.bulkInserterFactory.BuildInserter(
this.GetMapping(),
contextTransaction,
bufferSize,
this.contextSettingsCacheService.BulkInsertTimeout <TContext>(),
true))
{
inserter.Insert(items);
return(inserter.InsertedCount());
}
}
public IContextTransaction BeginTransaction(IsolationLevel isolationLevel)
{
transaction = new DbContextTransactionProxy(Database.BeginTransaction(isolationLevel));
return(transaction);
}
public NestedContextTransaction(IContextTransaction transaction)
{
this.transaction = transaction;
}
public int BulkUpdate(Expression <Func <TEntity, bool> > predicate, Expression <Func <TEntity, TEntity> > updateExpression, IContextTransaction contextTransaction)
{
return(this.BulkUpdate(this.FindAll(predicate), updateExpression, contextTransaction));
}
public int BulkDelete(Expression <Func <TEntity, bool> > predicate, IContextTransaction contextTransaction)
{
return(this.BulkDelete(this.FindAll(predicate), contextTransaction));
}
public void BulkSelectAndAdd(IQueryable query, IContextTransaction contextTransaction)
{
this.ExecuteSelectAndAdd(query, contextTransaction);
}