TransactionScope maximumTimeout

Question

I use TransactionScope in this code:

private void ExecuteSP()
{
    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();                       
                    }
                    //End
                    t.Complete();
                }
                catch (Exception ex)
                {
                    IsComplete = false;
                    string Message = ex.Message;
                }
                finally
                {
                    if (sqlComm != null)
                        sqlComm.Dispose();                 
                }
            }
        }
    }
    catch (Exception ex)
    {
        string messagee = ex.Message;
        //do something
    }
    finally
    {
        MessageBox.Show("Finsh");
    }
}

It's happens after one execution (sqlCommand.ExecuteNonQuery();) that take more than 10 minutes be execute. I don't get any excpetion in in this point by in the next excection 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.

It's because that System.Transactions.TransactionManager.MaximumTimeout is set to TimeSpan of 10 Minutes.

I search and I been found that maybe it related to "System.Transactions-->machine.config's maxTimeout" but i get an exception after change my config to this file :

<?xml version="1.0"?>
<configuration> 
  <system.transactions>
    <machineSettings maxTimeout="10:00:00"/>
  </system.transactions> 
  <appSettings>    
    <add key="FileName" value="MyFileName" />
    <add key="MySpace" value="5 MB" />       
    <add key="ClientSettingsProvider.ServiceUri" value="" />        
  </appSettings>      
</configuration>

when i try to get System.Transactions.TransactionManager.MaximumTimeout in run time after i changed the config file i get this exception:

"Configuration system failed to initialize"

Does anybody have any clue how to solve this issue?

(General note about my case: i need to do stored procedure that takes about 20 minutes because i need to convert table that contains int to bigint in SQL (int = 32bit, bigint =64 bit). I need to create new tables and insert the data from old table to the new table with int64. The table connected by ID to other 4 tables, each one contains more than 20 million rows and also binding,indexing and more. I can't split this procedure to small stored procedure so i need to change the maximum timeout to one hour or more, 10 minutes is not enough!).

Answer 1

If you aren't afraid of using reflection, you can actually override the maximum timeout programmatically. This code isn't guaranteed to be future-proof, but it works as of .NET 4.0.

public static class TransactionmanagerHelper
{
    public static void OverrideMaximumTimeout(TimeSpan timeout)
    {
        //TransactionScope inherits a *maximum* timeout from Machine.config.  There's no way to override it from
        //code unless you use reflection.  Hence this code!
        //TransactionManager._cachedMaxTimeout
        var type = typeof(TransactionManager);
        var cachedMaxTimeout = type.GetField("_cachedMaxTimeout", BindingFlags.NonPublic | BindingFlags.Static);
        cachedMaxTimeout.SetValue(null, true);

        //TransactionManager._maximumTimeout
        var maximumTimeout = type.GetField("_maximumTimeout", BindingFlags.NonPublic | BindingFlags.Static);
        maximumTimeout.SetValue(null, timeout);
    }
}

You can use it like this:

            TransactionmanagerHelper.OverrideMaximumTimeout(TimeSpan.FromMinutes(30));