98

Is there any difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout in .NET?

Dinah
  • 52,922
  • 30
  • 133
  • 149
Dhanapal
  • 14,239
  • 35
  • 115
  • 142

6 Answers6

144

Yes. CommandTimeout is how long a single command can take to complete. ConnectionTimeout is how long it can take to establish a connection to the server to start with.

For instance, you may be executing relatively long-running queries - it's perfectly okay for them to take 10 minutes to complete, but if it took 10 minutes to make the connection to start with, you'd know that something was badly wrong.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Out of curiosity: Since the default timeout is 30 seconds, what happens if I set CommandTimeout to 60 seconds but do not change ConnectionTimeout from the default? – flipdoubt Aug 05 '09 at 15:05
  • 6
    For the sake of argument, let's say I have a !**Q@? query that takes 32 seconds to run. If I set SqlCommand.CommandTimeout = 40 but leave SqlConnection.ConnectionTimeout at its default (presumably 30), will the connection timeout? In other words, do I have to set both properties? It sounds like you are saying "no," but I must have forgotten about the SqlConnection.ConnectionTimeout property and started questioning whether setting CommandTimeout does everything I need it to. – flipdoubt Aug 05 '09 at 18:32
  • 34
    flipdoubt - the CommandTimeout will affect the query, the ConnectionTimout won't. The ConnectionTimout isn't a timeout for the connection to peform queries - it's just the timeout for the connection to connect to the database in the first place. – Robin Bennett Nov 05 '09 at 16:11
  • 1
    @JonSkeet do CommandTimeout and ConnectionTimeout, both generate the same common exception ? Or different exceptions ? – Yasser Shaikh May 03 '16 at 08:05
  • 1
    @Yasser: I don't know, i'm afraid. – Jon Skeet May 03 '16 at 08:05
  • 8
    Keep in mind that CommandTimeout appears to apply to only up until the point where the first row is returned from SQL Server to the client code. Eg, if you have a query that returns the first row within 30 seconds, but the complete rowset takes hours to return, setting the timeout to 30 seconds will not timeout the query and the query will run for hours. This is mainly the case if you don't have blocking operators (such as an Order by) on the LHS of your execution plan. (Posting because I spent 2 days debugging this and no other reference on the subject) – Dave Nov 14 '16 at 04:43
  • 1
    @Yasser I'd a problem with this these days and I can say: both ConnectionTimeout and CommandTimeout generate the same exception (with the -2 code on SqlException.Number) – Claudio Nastasi Junior Jun 13 '18 at 16:11
32

SqlCommand.CommandTimeout = timeout limit for your SQL query. Means, how much time a (eg: SELECT, UPDATE) query can take for its execution. If it exceeds SqlCommand.CommandTimeout, then it stops execution. A command timeout error will occur.

SqlConnection.ConnectionTimeout = timeout limit for your connection. Means, how much time your connection object can try to connect. If it exceeds the specified time, it stops connecting. A connection timeout error will occur.

Nathan Getachew
  • 783
  • 5
  • 16
NinethSense
  • 8,824
  • 2
  • 23
  • 23
11

ConnectionTimeout specifies the duration to wait before timing out when attempting to open an SqlConnection. It is relevant to the Connection.Open() command.

while

SqlCommand.CommandTimeout specified the duration for an SqlCommand to wait before timing out. This happens after a connection has been opened and one of the ExecuteXXX methods have been called on the Command object.

Cerebrus
  • 25,615
  • 8
  • 56
  • 70
8

Additional Info

Default value of CommandTimeout is 30 seconds. Zero(0) indicates no limit. You can set CommandTimeout value in Coding only.

Default value of ConnectiontTimeout is 15 seconds. Zero(0) indicates no limit as well. Less than zero value (minus value) will get ArgumentException. You can set ConnectionTimeout value in both Coding and Config file.

Htin Aung
  • 531
  • 3
  • 8
0
select @@LOCK_TIMEOUT //get the TIMEOUT,default is -1
set LOCK_TIMEOUT = 600//set TIMEOUT with ms
CoolBeans
  • 20,654
  • 10
  • 86
  • 101
dodng
  • 9
  • 1
  • 2
    This is a 3rd type of timeout. Not one of the two the topic owner asks about. And doesn't answer the question. I won't -1 it, because I don't know what was the question before edits. – Csaba Toth Apr 17 '13 at 18:55
0

Quick note regarding CommandTimeout, since it is a property of both Connection and Command objects...

The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object's CommandTimeout property does not inherit the value of the Connection object's CommandTimeout value.

So the CommandTimeout setting on a Connection object only affects commands executed under the Connection object only (without using a Command object).

e.g. When you're connecting to a Stored Proc and adding parameters to the command object, and executing the Command object using a Connection Object's connection, then you would need to set CommandTimeout on the Command object and the ConnectionTimeout on the Connection object to override both defaults. Setting the CommandTimeout on the Connection Object will not override the default timeout for the Command Object commands.

https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/commandtimeout-property-ado?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/connectiontimeout-property-ado?view=sql-server-ver15

Brad Skidmore
  • 1
  • 1