CODEWITHSUNDEEP
c#sqlado.net
Flávio Ivan
Flávio Ivan

Reputation: 475

CommandTimeout not working

I am trying to change the timeout for a SqlCommand query, in a method that tests my connection for a given connection string. The code is similar to this:

using (SqlConnection connection = new SqlConnection(connectionString))
    {
      SqlCommand cmd = new SqlCommand("SELECT ...", connection);
      cmd.CommandTimeout = 10;
      connection.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      ...
      connection.Close();
    }

I would like to have a short timeout here, since I just want to test if this connection string is okay. But, no matter what number I set on CommandTimeout (I tried 0, 1, 2, 4, 10, 30, 60, 120), my real time obtained for a dummy connection string is always about the same (total running time of about 15 seconds).

So, seems to me that the value I set on CommandTimeout is being ignored for some reason.

Any ideas why?

Upvotes: 13

Views: 24307

Answers (4)

Liam
Liam

Reputation: 5476

You can also specify this in your connection string in your config file:

<connectionStrings>
  <add name="*con_name*" connectionString="data source=*dsource*;initial catalog=*catalog*;integrated security=True;Connect Timeout=300;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Upvotes: 0

user596075
user596075

Reputation:

I think you're confusing what exactly SqlCommand.CommandTimeout is for. As per this MSDN reference:

Gets or sets the wait time before terminating the attempt to execute a command and generating an error.

In your case, you are executing a DataReader and stepping through your query (whatever it may be). It is taking minimal time for each Read() which is why you wouldn't be hitting your timeout.

Edit:

If you are using a wrong connection string, your Timeout will not be the Command timeout, but it'll be the Connection time. This defaults to 15 seconds. That is the timeout that is effective in your situation.

You're going to timeout on the method call SqlConnection.Open(), not SqlCommand.ExecuteReader(). Therefore the ConnectionTimeout property is going to be the effective timeout value.

SqlConnection.ConnectionTimeout Property MSDN Reference

Upvotes: 7

Tim Lehner
Tim Lehner

Reputation: 15251

You may want to change your SqlConnection.ConnectionTimeout in this case.

Upvotes: 0

kaj
kaj

Reputation: 5251

You also need to check the connection timeout which has a default of 15 seconds.

Also see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx - if your connection string has context then CommandTimeout is ignored

Upvotes: 1

Related Questions