in Search

Do you Twitter? Follow us @CodeBetter

Peter's Gekko

public Blog MyNotepad : Imho { }

Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.

When your app runs into a timeout on a database operation the error message may lead you in a wrong direction to fix things. Which happened to me so this is my little index to the docs.

A sqlConnection has a ConnectionTimeout property. It is a property which determines the maximum number of seconds your code will wait for a connection to the database to open. It is set in the connectionstring to the database

data source=BROCHIS;initial catalog=Indato;integrated security=SSPI;Connect Timeout=60;

Opening the connection does not do anything in the database yet. That is handled by a SqlCommand, which could be part of a SqlDataAdapter. The SqlCommand has a CommandTimeOut. This is a settable property which determines the maximum number of seconds the command is given to execute the sql. By default this is 30 seconds. When you perform something costly in the database this is the one whose value you'll have to change.

SqlCommand cmd;
.....
if (bigJob)
   cmd.CommandTimeout = 60;

For both properties a value of 0 indicates to wait forever. Both properties also apply to non sqlserver database operations. They are defined in IdbConnection and IdbCommand.

Published Mar 20 2006, 09:03 AM by pvanooijen

Filed under: Data

Comments

Christopher Steen said:

ACE Threat Modeling
[Via: Keith Brown ]
AJAX Myth - I love it............. [Via: Wallym ]
Article:...

# March 22, 2006 12:18 AM

Simon Munro said:

Around the time .NET 1.1 had just come out, I was on a project where we needed a long timeout for our commands due to the nature of the architecture (large databases with back-end tasks that prefetched data for processing). The CommandTimeout property is set arbitrarily at 60s and we needed more... across all our commands. The property couldn't be set easily in a .config file.

Easy solution, in an OO sense, descend from SqlCommand and set the timeout in the constructor... not possible because SqlCommand is sealed (not inheritable) and we had to hand code (or generate) the timeout in every command in our DAL - a real pain.

The timeout works well... I just wish a simple OO concept such as inheritance was actually allowed on the SQLCommand class to make it easier to use across the entire project.

# March 22, 2006 8:55 AM

Khuzema said:

Thanks

# March 25, 2006 8:51 AM

Peter's Gekko said:

In some cases it can take quite some for the web server to complete your webpage. Setting a time out...

# June 15, 2006 8:33 AM

Peter's Gekko said:

Another story from the app with the sprocs. That was a classical 2-tier CS application which we transformed...

# June 20, 2006 4:14 AM

Gurjinder Singh Brar said:

Yes, you are right this CommandTimeOut error also exist in SQLHelper.cs class.

To avoid this problem use

cmd.CommandTimeout=con.ConnectionTimeout;

Thanks,

Gurjinder Singh Brar

http://agileguru.blogspot.com

# December 29, 2006 2:37 AM

pvanooijen said:

Not always. What if several commands have to complete on one conection on one roundtrip ?

The formula would be con.ConnectionTimeout = cmd1.CommandTimeout + cmd2.CommandTimeout + ......

# December 29, 2006 4:14 AM

ZAZe said:

No way

# June 25, 2007 4:24 AM

John Halliday said:

Thanks for the simple explanation of timeout settings.

I have been working to find an answer to my own connection timeout problem for some time. I run a 4 hour stored procedure and it keeps timing out after just over an hour. I have the command timeout set to:

da.SelectCommand.CommandTimeout = 900000;

... this times out after about 80 minutes. After reading your article I altered the connection string to include the same timeout amount and re-ran. It timed out after 2 hours this time (better!) with:

"System.Data.OleDb.OleDbException: Timeout exceeded.\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)\r\n at ARTS_Billing.Bill_Main.GetBills(String BillType, Int32 BEG_APPL_ID, Int32 END_APPL_ID) in C:\\ARTS_Project\\Code\\Utilities\\Billing\\ARTS_Billing\\ARTS_Billing\\Bill_Main.cs:line 444"

Thanks again for the hints.

# April 29, 2008 1:35 PM

pvanooijen said:

Looking at your stacktrace you have resultsets. Why do you need a result at all when it takes so long. Imho it would be a better approach to fire off your sproc with an executeNonQuery.

And check the results after some time, like explainded here :

codebetter.com/.../146446.aspx

# May 13, 2008 3:53 AM