SqlCommand.CommandTimeout value ignored

1.3k views Asked by At

I'm trying to run a stored procedure on SQL Server 2012 using a Windows service written in C#. The procedure returns no data, but takes a long time to execute, so I'm changing the CommandTimeout property of the SqlCommand to 5 minutes. Whenever I run the code though, I get a timeout error exactly 30s after execution started. Does anyone know why my value is being ignored, or is there another timeout that I've forgotten to set?

app.config file:

<configuration>
  <appSettings>
    <add key="myTimeout" value="600" />
  </appSettings>
  <connectionStrings>
    <add name="myConnectionString" connectionString=";data source=myServer,123456;Initial catalog=myDB;integrated security=SSPI;MultipleActiveResultSets=True;App=EntityFramework" />
  </connectionStrings>
</configuration>

Code:

var connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    cmd = new SqlCommand("usp_myLongRunningOperation", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // Change timeout if value present
    var commandTimeout = System.Configuration.ConfigurationManager.AppSettings["myTimeout"];
    int timeout;
    if (!string.IsNullOrEmpty(commandTimeout) && int.TryParse(commandTimeout, out timeout))
    {
        cmd.CommandTimeout = timeout;
    }

    cmd.ExecuteNonQuery();
}

(I've tested the timeout value parsing in isolation, and it's setting the value correctly)

0

There are 0 answers