Limit The Time A Routine Can Run - VBNET

Question

I'm trying to fill a dataset, but I want to limit the amount of time the system has to fill this dataset to 30 seconds.

I have tried (as suggested elsewhere on SO):

Dim T As Date = Date.Now
da.Fill(ds, "DATASET")
Do

    If (Date.Now - T).TotalSeconds >= 30 Then
        Main.VIEW_Title.Text = "Error In Connection..."
        Exit Sub
    End If
    Exit Do
Loop

But the system just hangs anyway during the da.Fill(ds, "DATASET") section and doesn't ever exectute the "Error In Connection" message. It doesn't matter if I put the line inside the DO either, because it stops there. What I need is for it to execute the fill command, and then if it doesn't complete in 30 seconds, to allow me to handle that error.

Thanks

Set the timeout in the connection string and handle the exception afterwards. — Alejandro, May 26 '15 at 10:19
Oh boy, there is quite a lot wrong here. First, you are immediately exiting the Do loop with the `Exit Do`, so no wonder the message does not appear. Second, the `da.Fill` is prabably a blocking call, meaning it needs to complete before the code execution continues. You would in general need an async method in another thread, that can be cancled, which is not always trivial. — Jens, May 26 '15 at 10:19
@Jens - That makes sense, however I've not really done anything with Async calls... Is it possible to provide an example, i'll be doing some research too. Thanks — LBPLC, May 26 '15 at 10:21
Use a task block to execute your Fill Call Check this link http://stackoverflow.com/questions/4238345/asynchronously-wait-for-taskt-to-complete-with-timeout — jereesh thomas, May 26 '15 at 10:24

score 0 · Answer 1 · answered May 26 '15 at 10:21

Unfortunately, that's not so easy.

What you can do is:

Start a background thread and do the fill operation in that thread.
In the main thread, wait for 100ms, check if the new thread completed, repeat at most 300 times.

Now the real problem is: What do you do if the main thread determines that the background thread has not finished after 30 seconds? DataAdapter.Fill does not provide a method to gracefully interrupt the operation, so you'll have to let it continue in the background.

This also means that you must use a separate data connection in the background thread, since the ADO.NET classes are not thread-safe.

I'd rather suggest a different approach: A data selection operation taking more than 30 seconds implies that either:

you select too much data (there's really no use showing 1 million rows to the user) or
your query needs optimization (such as a well-placed index).

Thus, I suggest to fix the root cause instead of using complicated workarounds to cover-up the symptom.

Limit The Time A Routine Can Run - VBNET

1 Answers1