Fill method ignoring CommandTimeout value

[eric.burgin]

Under the .NET framework 2.0.50727.42, we are encountering a problem
with timeouts while performing a query using a SQL TableAdapter.
Whatever we set the timeout to gets ignored if we call the Fill method
overload that takes a DataTable (or DataSet) only as a parameter. No
matter what the CommandTimeout is set to, the command will run to
completion and never throw a timeout exception. (Note that, when we
run this query in SQL Server Query Analyzer, it takes far longer to
complete than the CommandTimeout we specified in code). The only way
we can get it to time out is to call the Fill method overload which
takes a DataSet, StartRow, MaxRow, Tablename. This is only true when
MaxRow is set to a relatively low value like 100. We are also seeing
that the specified timeout is honored when we use the ExecuteScalar
method of a SQLCommand object.

We have tried this code against 2 different SQL 2000 servers and one
SQL 7 server

Sample Code:

Dim myconnection As New SqlConnection("Data
Source=myDBServerName;Initial Catalog=DBName;Integrated Security=True")
myconnection.Open()
Dim mydatatable As New DataTable
Dim mydataset As New DataSet
Dim mycommand As New SqlCommand("SELECT * FROM tblSomeTable
WHERE (memo LIKE '%') AND (refcode LIKE '2%') AND (acctnumber LIKE
'%')", myconnection)
mycommand.CommandTimeout = 5
Dim mytableadapter As New SqlDataAdapter(mycommand)
Dim returnRows As Int32

mycommand.ExecuteScalar 'This Line WILL timeout
returnRows = mytableadapter.Fill(mydataset, 0, 100,
tblSomeTable) 'This line WILL timeout
returnRows = mytableadapter.Fill(mydatatable) 'This line will
NOT timeout

 

[William \(Bill\) Vaughn]

Ah, IIRC the CommandTimeout clock is reset once the first row arrives from
the result. While the fetch operation might take far longer, CommandTimeout
won't throw a TimeOut exception as long as at least one row has returned
from the query. I might investigate the async methods but consider that the
fetch operations are NOT async--even in ADO.NET 2.0--only the query portion
of the ExecuteReader is async. Fill does not expose an async option.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[eric.burgin]

This is exactly what is happening, thanks!

Is it possible to wrap the call to the fill method in a thread and then
abort the thread after a certain number of seconds? The developer
working on this project tried using the BackgroundWorker object but it
was ignoring the calls to CancelAsync.

His other thought is to use a System.Threading.Thread and use the abort
method but I was worried this was to abrupt an end to the thread.

 

[William \(Bill\) Vaughn]

Well, the server must deal with the Cancel method on the Command
object--assuming you're calling it. Calling the BeginExecuteReader to run
the query async is the first step. Next the BackgroundWorker thread can be
used to execute the Load method (DataSet or DataTable) and during that
operation you can call the Command.Cancel. But as I said, the server must
deal with this cancel request. It does not just stop immediately--it has to
clean up any work that's started and rollback transactions etc.

Frankly, the best approach is to fetch fewer rows--a few dozen to a couple
of hundred at a time. This gives you a lot more control over timing and the
ability to scale the application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...