"The timeout period elapsed prior to completion of the operation or the server
is not responding."
"The timeout period elapsed
prior to completion of the operation or the server is not responding."
If
this sounds familiar using the SqlClient Class or the Data Access Application
Blocks "SqlHelper", and you have increased the Connection Timeout and
the Connect Lifetime (for pooling) in your connection string(s), its probably
because you forgot to set the COMMAND timeout!
A command can be
timed out after a certain number of seconds. You might want to set this limit
if you foresee to run across particularly lengthy operations. As in ADO, the
property to check is CommandTimeout. Its default value is 30 seconds.
You
can set this once the command instance has been created. A value of "0"
(zero) means the command will wait for completion indefinitely, but this is not
recommended by Microsoft. Better to set a large value in seconds.
Unlike
ADO, ADO.NET lets you specify the expected behavior of the command through the
CommandBehavior enum. Such values specify a description of the results and how
the query should affect the data source. In Beta 1, you had a CommandBehavior
property to set for each command. Starting with Beta 2, you use values from the
CommandBehavior enum only as an argument for ExecuteReader.
Among
the other options, you can ask a query command to limit to obtain key and schema
information. In this case, the command will be executed without any locking on
the selected rows. This behavior is given by the KeyInfo flag. If you have long
running queries or multiple threads accessing Sql Server simultaneously,
this
can be very helpful.
As an alternative, you might want to obtain
column information only, without affecting the database state with locks. This
option is SchemaOnly. Another option, SingleResult, lets you specify that you
want back only one resultset, no matter how many would originate from the command.
In this case, the command returns only the first resultset found. A fourth option
is CloseConnection that forces the SqlDataReader object associated with a query
command to automatically close the connection as the final step of its Close
method.
If you use the SqlHelper "Best practices" class
as I do, it might be a good idea to recompile it, setting "cmd.CommandTimeout=howmanyseconds.
There are a number of instances of this in the class.
And, as a
final caveat, don't call Dispose() on a SqlConnection unless you want to have
it removed from the connection pool, because that's what Dispose() does! In almost
all cases, you would simple call the Close() method and let ADO.NET take care
of returning the connection to the pool.