“Command Timeout” is not equal to “Connection Timeout”.
A few days ago I had an application that was throwing the following error:
“Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding…”
It was an obvious error, a database timeout. I reviewed the Connection String and it didn’t have the property “Connection Timeout” (The default connection timeout is 30 seconds). So I added the property and set it to 600 seconds.
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI;Connection Timeout=600;" />
</connectionStrings>
The result was the same: “Timeout expired”. The new timeout wasn’t being applied. I searched in several forums about the error and all said: “Increase the Connection Timeout”.
So, I looked at the code and we had the CommandTimeout hardcoded:
private void ExecuteCommand(string commandText, string connectionString)
{
using (var connection = new SqlConnection(connectionInfo))
{
connection.Open();
var command =
new SqlCommand(commandText, connection)
{
CommandType = CommandType.Text,
CommandTimeout = 120
};
command.ExecuteNonQuery();
}
}
It was obvious for me. Remove the hardcoded CommandTimeout .Letting us the following code:
private void ExecuteCommand(string commandText, string connectionString)
{
using (var connection = new SqlConnection(connectionInfo))
{
connection.Open();
var command =
new SqlCommand(commandText, connection)
{
CommandType = CommandType.Text,
CommandTimeout = 120
};
command.ExecuteNonQuery();
}
}
However it didn’t work.
After some research I found on the documentation that the ConnectionTimeout on the connection object it is NOT inherited by the command object (I think should be). So, you have to set the CommandTimeout property of the SQLCommand.
The ConnectionTimeout handles the time to wait while trying to establish a connection.
The CommandTimeout handles the time to wait before terminating the attempt to execute a command.
And we finished with something like this:
private void ExecuteCommand(string commandText, string connectionString)
{
using (var connection = new SqlConnection(connectionInfo))
{
connection.Open();
var command =
new SqlCommand(commandText, connection)
{
CommandType = CommandType.Text,
CommandTimeout = connection.ConnectionTimeout
};
command.ExecuteNonQuery();
}
}
We set SqlCommand.CommandTimeout equal to Connection.ConnectionTimeout. So we will have the same timeout for the Connection and for the Command that executes the instruction.
Using this approach you’ll have the conclusion:
• The timeout will be configurable from the web.config (or app.config) file.
• You just will be worried by ONE AND ONLY ONE timeout.
• You’ll save a lot of headaches with the “Timeout errors”