ADO.Net DataReader timeout issue

Question

Hello everyone,

I am using ADO.Net + C# + VSTS 2008 + ADO.Net to connect to SQL Server 2008 Enterprise. I am using almost the same pattern/sample mentioned here -- using ADO.Net DataReader to retrieve data one entry (row) by one entry (row).

http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

My question is, if I set the SqlCommand timeout in this sample, 1. I think the timeout applies to how much time we could use as maximum value to retrieve one specifc row, not the total timeout for the whole data entry-by-entry loop?

BTW: loop I mean,

while (reader.Read())
{
    Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
        reader.GetString(1));
}

2. and this timeout only matters how much time it takes to retrieve data entry from database, and this timeout has nothing to do with how much time we deal with each entry (e.g. if we set timeout to 20 seconds, and if it takes 1 second to retrieve one data entry from database, and it takes 30 seconds for my application logics to manipulate the data entry, timeout will never happen).

Correct understanding?

Answer 1

Yes you are right. The CommandTimeout means the Time the Database needs to execute the command (any command)

Answer 2

The command timeout that you can set applies to how long you give ADO.NET to do its job.

If you call cmdQuery.ExecuteNonQuery() which returns nothing but performs a SQL statement it's the time needed to perform that statement.

If you call cmdQuery.ExecuteReader() which returns a data reader, it's the time needed for ADO.NET to ste up / construct that data reader so that you can then use it.

If you call cmdQuery.ExecuteScalar() which returns a single scalar value, it's the time needed to execute the query and grab that single result.

If you use the dataAdapter.Fill() to fill a data table or data set, it's the time needed for ADO.NET to retrieve the data and then fill the data table or data set.

So overall : the timeout applies to the portion of the job that ADO.NET can do - execute the statement, fill a data set, return a scalar value.

Of course it does NOT apply to the time it takes YOU to iterate through the results (in case of a data reader). That wouldn't make sense at all...

Marc