how to handle ASP.NET dbtransaction timeout

Question

I have been experiencing db transaction timemouts in an ASP.NET 2.0 & SQL Server 2008 application for several days now.

The issue is something using SQL Server profiler tracing to "a function calling web service".

We have transaction include several stored procedure (sp). But stored procedure #3 has some bytes to save in DB, it consumes time. When the time goes over 25 seconds, it throws a timeout exception message:

Message : System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

My code:

using (System.Data.Common.DbConnection connection = o_DB.CreateConnection())
{
     connection.Open();

     System.Data.Common.DbTransaction o_Transaction = connection.BeginTransaction();

     try
     {
        - exec sp1
        - exec sp2
        foreach{
            - exec sp3
        }
     }

We tried some solutions in the web but it did not work. I hope someone can give a hand to me. Many Thanks.

We do not have timeout in the connection string in web.config, it does not suffer on exception after 15s (default)

We set the transaction timeout in web.config:

<system.transactions>
    <machineSettings maxTimeout="00:00:30" />
</system.transactions>

Answer 1

Something like that

using (var ts = CreateTransactionScope(TimeSpan.FromSeconds(mySecondsVar)))
{ 
    using (System.Data.Common.DbConnection connection = o_DB.CreateConnection())
    {
        using (IDbTransaction tran = connection.BeginTransaction()) {
        try 
        {
            // your code
        }  
        catch {
            tran.Rollback();
        }
    }
}
    ts.Complete();
}

If is "ok", the dispose do the commit automatically.

Answer 2

This you can directly handle by code also. Please see the below code

public DataSet getData(string command)
{
    DataSet ds = new DataSet();
    string connectionString = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;
    using (var conn = new SqlConnection(connectionString))
    {
        using (var cmd = new SqlCommand(command, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 0;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            conn.Open();
            adapt.Fill(ds);
            conn.Close();
        }
    }
    return ds;
}

The blow line will change the execution time to infinity or until query execution complete.

cmd.CommandTimeout = 0;