CODEWITHSUNDEEP
c#sql.nettransactionscope
AllmanTool
AllmanTool

Reputation: 1514

Transaction Scope and Task.When all execution

Intention: speed up Sql insert operation with a transaction commit / rollback support

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;
    }

Upvotes: 1

Views: 1004

Answers (1)

Prateek
Prateek

Reputation: 145

You can try to leverage SqlBulkCopy, instead of splitting the items over different connection objects.

Insert 2 million rows into SQL Server quickly

You can create a dataset by modifying IEnumerable<Item>. All the parallel operations can be applied here to convert from IEnumerable<Item> to dataset. Once dataset is created, SqlBulkCopy will help you. You can create 3-4 sets of datasets and execute the insert query 3-4 times.

In this approach, you can maintain one database connection that also helps to follow ACID compliance of the database transactions.

Upvotes: 1

Related Questions