Wait all tasks one by one with the same transaction instance failed c#

Question

I am using the wait all one by one approach inside one transaction instance and I get this error when calling the Commit method on the transaction instance:

What Am I missing here?

This is the code:

.
.
.
using Dapper;
using DapperExtensions;
.
.
.    
using (var connection = new SqlConnection(connectionString))
            {

                connection.Open();
                var tlis = GetTlis(connection).ToList();
                using (var trans = connection.BeginTransaction())
                {
                    var tasks = tlis.Take(10).Select(tli => Task.Factory.StartNew(
                        (dynamic @params) =>
                        {
                            ProcessTli(@params.Connection, @params.Transaction, tli);
                        },
                        new { Connection = connection, Transaction = trans }
                        )).ToList();

                    var tlisAmount = 0;

                    while (tasks.Count > 0)
                    {
                        //const int timeout = 3600*1000;
                        var winner = Task.WaitAny(tasks.ToArray());

                        if (winner < 0)
                            break;

                        tlisAmount++;
                        tasks.RemoveAt(winner);

                        Cmd.Write("({0}%) ", tlisAmount*100/tlis.Count);

                        var timeSpan = TimeSpan.FromSeconds(Convert.ToInt32(stopWatch.Elapsed.TotalSeconds));
                        Cmd.Write(timeSpan.ToString("c") + "  ");

                        Cmd.Write("Processing {0} of {1}   ", tlisAmount, tlis.Count);
                        Cmd.Write('\r');
                    }
                    try
                    {
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        Cmd.WriteLine(e.Message);
                        trans.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }

        private static void ProcessTli(IDbConnection connection, IDbTransaction transaction, Tli tli)
        {
            var quotesTask = Task.Factory.StartNew(() => GetQuotesByTli(connection, transaction, tli));

            quotesTask.ContinueWith(quotes =>
            {
                quotes.Result.ToList().ForEach(quote =>
                {
                    var tliTransaction = new TliTransaction(quote);
                    connection.Insert(tliTransaction, transaction);
                });
            });

            var billOfLadingsTask = Task.Factory.StartNew(() =>GetBillOfLadings(connection, transaction, tli));

                billOfLadingsTask.ContinueWith(billOfLadings =>
                {
                    var bolGroupsByDate = from bol in billOfLadings.Result.ToList()
                        group bol by bol.Year;

                    bolGroupsByDate.ToList().ForEach(bolGroupByDate =>
                    {
                        var bol = new BillOfLading
                        {
                            As400FormatQuoteDate = bolGroupByDate.ElementAt(0).As400FormatQuoteDate,
                            CommodityCode = tli.CommodityCode,
                            TariffOcurrenciesAmount = bolGroupByDate.Count(),
                            TliNumber = tli.TliNumber
                        };
                        var tliTransaction = new TliTransaction(tli, bol);
                        connection.Insert(tliTransaction, transaction);
                    });
                });

                Task.WaitAll(quotesTask, billOfLadingsTask);
        }

Thanks in advance

Answer 1

I would do something like this (note this shows the process, not the extact code...)

public void ModifyData()
{
    using (var connection = new SqlConnection(connectionString))
    {
        var tlis = GetTlis(connection).ToList();
        connection.Open();

        Quotes quotes;
        BillOfLading billOfLading;

        using (var trans = connection.BeginTransaction())
        {
            quotes = GetQuotesByTli(connection, transaction, tli);

            billOfLading = GetBillOfLadings(connection, transaction, tli);
        }
    }

    // Process those items retrieved from the database.
    var processedItems = this.Process(/* the items that you want to process */);

    using (var connection = new SqlConnection(connectionString))
    {
        var tlis = GetTlis(connection).ToList();
        connection.Open();

        using (var trans = connection.BeginTransaction())
        {
            // do all your inserts.
        }
    }
}

Then you would run it:

await Task.Run(() => ModifyData());

This resource shows a really good example of running multiple tasks.