A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!!

Wed Jan 31, 2007 by Mladen Prajdić in net

If you're reading this blog you're probably familiar with ADO.Net and it's SqlCommand object.

Now this SqlCommand object has a CommandTimeout property that specifies the number of seconds

after which the command will terminate. The default value is 30.

Now we get to the SqlConnection and the connection string. in the connection string you can specify

Connection Timeout = numberOfSeconds

after which the connection timeouts.

We also have to set the SqlConnection object to the SqlCommand.Connection property.

Nothing special so far.

Now my question is:

If we set the Connection Timeout in our connection string to higher value than the SqlCommand.CommandTimeout's is,

WHY DOESN'T the SqlCommand.CommandTimeout get set to that higher value???

I really can't think of a good reason.

OK, I can... :)

Legacy Comments


Sanjay Sheth 2007-01-31	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! The Connect or Connection Timeout in the connection string is actually "The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error." [1] .. it has nothing to do with how long a command can execute for .. it only sets how long to try to wait while trying to connect to a database server. [1] http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

JanezM 2007-01-31	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! Mighty Microsoft knows why this should be the way as it is

James Curran 2007-01-31	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! Because you might have just one really long query. Now, if we assume that the Connect string timeout value is the query timeout (ie, we ignore the correction Sanjay made above), then we'd have to set that value to the longest time any query in our system might take. That could be 5, 10, 20 minutes. OK, we set that to 1200 seconds. Now, would you want to set every SqlCOmmand down to 30 seconds, or just one to 1200?

Mladen 2007-01-31	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! @Sanjay: I know the difference :)) @James: I'm not sure what you mean. my opinion is that it's better for all Commands to have 1200 then just one and all others 30. maybe i misunderstood you...

Andrej Tozon 2007-01-31	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! I don't really see why changing any of those timeout values should affect the other one, they are not so related. Why would you want CommandTimeout set to what ConnectionTimeout is set to?

Dewayne Christensen 2007-02-01	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! >I really can't think of a good reason. I really can't think of a good reason why you want them tied together. One determines how long you wait for TCP/IP to establish a connection with the server, the other determines how long you wait for a query to execute once it gets there. They're completely orthogonal activities. You may be using an overseas link where a single round trip takes a minute or two to complete, but you're just doing a series of simple single-record inserts. So you set a two-minute timeout on the connection, and five-second timeout on the command. High-speed, high-latency certainly isn't a very _common_ scenario, but it does exist.

Vic Berggren 2007-02-01	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! Why don't you manage that yourself with a config value that is shared for both properties or your own object that returns a value based on the type of operation you need to make?

Mladen 2007-02-05	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! Ok maybe i should explain myself. This post was a bit of a rant because an app i got was constantly timing out and the only parameter i could set in config file was a connection string... so i just thought it would be nice to have the functionality i posted. i do know that those 2 thing are completly different. have fun all!

Anon 2008-07-15	re: A little ADO.NET 2.0 detail that's REALLY TICKING ME OFF!!! I agree! I'm in the same situation. I have a big ole query that times out so now instead of changing my .config file, I have to change my code.