How to best show progress info when using ADO.NET?

Question

I want to show the user detailed progress information when performing potentially lengthy database operations. Specifically, when inserting/updating data that may be on the order of hundreds of KB or MB.

Currently, I'm using in-memory DataTables and DataRows which are then synced with the database via TableAdapter.Update calls. This works fine and dandy, but the single call leaves little opportunity to glean any kind of progress info to show to the user. I have no idea how much data is passing through the network to the remote DB or its progress. Basically, all I know is when Update returns and it is assumed complete (barring any errors or exceptions). But this means all I can show is 0% and then a pause and then 100%.

I can count the number of rows, even going so far to cound how many are actually Modified or Added, and I could even maybe calculate per DataRow its estimated size based on the datatype of each column, using sizeof for value types like int and checking length for things like strings or byte arrays. With that, I could probably determine, before updating, an estimated total transfer size, but I'm still stuck without any progress info once Update is called on the TableAdapter.

Am I stuck just using an indeterminate progress bar or mouse waiting cursor? Would I need to radically change our data access layer to be able to hook into this kind of information? Even if I can't get it down to the precise KB transferred (like a web browser file download progress bar), could I at least know when each DataRow/DataTable finishes or something?

How do you best show this kind of progress info using ADO.NET?

Answer 1

There's a semi-solution for the SELECT part, which is to issue a COUNT query first to get the number of rows you expect to receive. This is only practical if the COUNT query can return a result very fast (i.e. in a fraction of a second) - on the other hand, if it takes several seconds to run, then the query execution itself (as opposed to result enumeration) might take longer than the data transfer, in which case it's not worth trying to show a discrete progress bar at all.

As for UPDATE and INSERT - no, there's not really any simple solution for that, especially using TableAdapters. If you have a huge amount of data to send, you might want to consider using the SqlBulkCopy class to upload to a staging table and then perform the actual update on the server. That class offers the NotifyAfter property along with the SqlRowsCopied event which can give you a reasonable approximation of the current progress (you already know the total number of rows in this case, since they're in memory).

That will of course require significant changes from your current TableAdapter implementation, but the typed dataset system in .NET really wasn't designed to handle recordsets of that size over anything other than a LAN connection.

I think that most people would simply choose to use a marquee progress bar. Users expect this nowadays; even if you could accurately predict the number of rows and the data transfer rate, you still have no idea how long it's going to take for the query to actually execute, especially if the server is under heavy load, and it's arguably worse to provide bad estimates than no estimates.

If there's a realistic chance that the query (or update) itself will take a long time to run on the server itself, not counting any time to upload/download the records, then I would definitely use a marquee progress bar instead. Otherwise... good luck.

Answer 2

As Aaronaught said - SqlBulkCopy with NotifyAfter should work (it works for me though).

I don't think this would be 'significant change' to your current DAL implementation...