ADO.NET SQLAdapter Timeout Error

2004 September 20
tags: ,
by Ben Gracewood

OK, here’s a really annoying trap for ADO.NET users that I thought I’d document, just in case someone is searching for “why the hell is my damned ADO.NET fill function timing out when I’ve set all the timeout values correctly!?” on Google.

Scenario: you have a big nasty SQL query or stored procedure that you are using to fill a DataSet. Something like this:

DataSet resultSet = new DataSet();
String mySQL = "Some big nasty SQL query";
SqlDataAdapter myAdapter = new SqlDataAdapter(mySQL, myConnString);
myAdapter.Fill(resultSet);

Pretty simple really. You’ve set your connection timeout correctly in the connection string, and yet you keep getting messages like:

The timeout period elapsed prior to completion of the operation or the server is not responding.

You bang your head against the wall for a while, before realising that when you instansiate a SqlDataAdapter, that first parameter (String mySQL), is actually the SQL string required to create the DataAdapter’s SelectCommand property. Aha! There’s your hint: SelectCommand is a SqlCommand object.

SqlCommand objects have a property called CommandTimeout. Soooo, how about some little ditty like:

myAdapter.SelectCommand.CommandTimeout = int.MaxValue;

NB: I’m kidding with that int.MaxValue business. Use something sane like 60 seconds. You shouldn’t be copy-pasting this snippet anyway - as with any web coding solution, learn why and how it works before using it.

from → Uncategorized

Viewing 24 Comments