I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

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.

<rant over>

OK, I can... :)

Print | posted on Wednesday, January 31, 2007 6:42 PM | Filed Under [ .Net ]

Feedback

Gravatar

# 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
1/31/2007 7:25 PM | Sanjay Sheth
Gravatar

# 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
1/31/2007 7:33 PM | JanezM
Gravatar

# 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?
1/31/2007 8:21 PM | James Curran
Gravatar

# 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...
1/31/2007 9:54 PM | Mladen
Gravatar

# 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?
1/31/2007 11:49 PM | Andrej Tozon
Gravatar

# 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.
2/1/2007 3:03 AM | Dewayne Christensen
Gravatar

# 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?
2/1/2007 3:32 PM | Vic Berggren
Gravatar

# 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!
2/5/2007 5:01 PM | Mladen
Gravatar

# 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.
7/15/2008 1:56 AM | Anon
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET