Intention: speed up Sql insert operation with a transaction commit / rollback support
- Have a pretty big amount of data (about 900 k entities)
- Would like to put down the data to SQL table in parallel, gonna to split the data per connection (e.g. 3 active connections, thus 300 k per a connection)
- Would like to have possibility to rollback if something goes wrong, so try to using Transaction Scope (nested-TransactionScopeOption.Required)
Issue:
It looks like Transaction scope couldn't support such kind of Task.WhenAll thus, if ever there will be an error during execution the intermediate writes won't be rollback
Q: So I've wondered if it can be work around, or it just not appropriate way to leverage the transaction scope ?
public void Upsert(IEnumerable<IEnumerable<Item>> splitPerConnection, DateTime endDate)
{
using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
{
try
{
UpdateEndDate(endDate);
var insertTasks = splitPerConnection.Select(ch => Task.Run(() => Insert(ch)));
Task.WhenAll(insertTasks).GetAwaiter().GetResult();
scope.Complete();
}
catch (Exception ex)
{
throw ex;
}
}
}
private int Insert(IEnumerable<Item> items)
{
int affectedRows;
using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
using (var sqlConnection = new SqlConnection(DockerMsSqlServerConnectionString))
{
sqlConnection.Open();
affectedRows = sqlConnection.Execute(ItemsQueries.Insert, items, commandTimeout: 0);
scope.Complete();
}
return affectedRows;
}
private int UpdateEndDate(DateTime endDate)
{
int affectedRows;
using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
using (var sqlConnection = new SqlConnection(DockerMsSqlServerConnectionString))
{
sqlConnection.Open();
affectedRows = sqlConnection.Execute(ItemsQueries.UpdateEndDate, new { EndDate = endDate }, commandTimeout: 0);
scope.Complete();
}
return affectedRows;
}
