Unclear exception from TransactionScope

I use TransactionScrope in my code to execute 50 SQL command on SQL Server 2008 R2. this is an example for my code:

   private void DoSomething()
    {
        bool IsComplete = false;
        SqlCommand sqlComm = null;
        //6 hours!!!
        TimeSpan ts1 = new TimeSpan(6, 0, 0);
        try
        {
            using (TransactionScope t = new TransactionScope(TransactionScopeOption.RequiresNew, ts1))
            {
                using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
                {
                    //open sql connection
                    sqlConn.Open();
                    try
                    {
                        //create new sqlCommand
                        sqlComm = new SqlCommand();
                        for (int i = 1; i <= 2; i++)
                        {
                            IsComplete = true;
                            //This command takes 15 minutes
                            sqlComm.CommandText = "exec TestSp";
                            sqlComm.Connection = sqlConn;
                            sqlComm.CommandType = CommandType.Text;
                            sqlComm.CommandTimeout = 18000;
                            //Executing my command
                            int j = sqlComm.ExecuteNonQuery();
                            sw.WriteLine("Finsh Executing SQL Command:" + DateTime.Now.ToLongTimeString());
                            sw.Flush();
                        }
                        //End
                        IsComplete = true;
                    }
                    catch (Exception ex)
                    {
                        IsComplete = false;
            开发者_开发知识库            string Message = ex.Message;
                    }
                    finally
                    {
                        if (sqlComm != null)
                            sqlComm.Dispose();
                        if (IsComplete)
                            t.Complete();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            string messagee = ex.Message;
            //do something
        }
        finally
        {
            MessageBox.Show("Finsh");
        }
    }

When the procedure takes more than 10 minutes I get this exception:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

I tired a lot of options by changing the TimeSpam and changing the SqlCommand.Timout but for some reason i get this exception. The exception occur after the first execution that takes a lot of time. For example if i have 100 stored procedure and text command and all the command takes less than 5 minutes and only one command takes more than 10 minutes the execution of the next command after the long command will cause the exception to occur.

Does anybody get any idea for the cause?

Thanks for your help!

---

I think the thing to try here is simply to move the transaction complete outside the connection, i.e.

using(var tran = ...)
{
    bool isComplete;
    using(var conn = ...)
    {
         //...
    }

    if(isComplete)
        tran.Complete();
}

Also note that most times, it is easier to simply let exception handling bypass the Complete() for us, i.e.

using(var tran = ...)
{
    using(var conn = ...)
    {
        //...
    }

    // if we get an exception, we won't get here
    tran.Complete();
}

---

This error can occur when the transaction runs for a longer period than the maxTimeout for System.Transactions. The default value for maxTimeout is 10 minutes.

You can read more about it in this answer: https://stackoverflow.com/a/10017056/205023 or in this blog post: http://thecodesaysitall.blogspot.se/2012/04/long-running-systemtransactions.html

---

Sounds like the transaction is just timing out seeing it it fails after a set amount of time.