Tuesday, December 15, 2009

ADO.NET and Connection Timeouts

Today I've written some code at work which is connecting to a SQL Server DB via C# code. The code is executing some heavy SQL transactions, so it is supposed to take a while before it completes its tasks.

Knowing this, I've set the "Connection Timeout" correctly on the connection string, to a big enough number, so my connection won't timeout during the execution of the query.

Setting this option seem to be ineffective in some cases, as my code kept getting kicked with a "Connection Timeout" exception. After over 30 minutes of digging around I finally found the culprit. It seems that SqlCommand objects doesn't inherit the timeout configuration from the connection string, rather they have their own 30 seconds timeout default. So in order to overcome this, one should set "command.CommandTimeout=...".

Still didn't find any explanation for this behavior that would convince me it is rational.

No comments:

Post a Comment