What is the best way to display progress information when using ADO.NET?
I want to show detailed information about the state of a user while doing long running database operations. In particular, when inserting / updating data, which can be on the order of hundreds of KB or MB.
I am currently using in-memory DataTables and DataRows, which are then synchronized to the database via TableAdapter.Update calls. This works great for dandy too, but the single challenge leaves little room for collecting progress information to show the user. I have no idea how much data is going through the network for the remote DB or its progress. Basically all I know is when Update is returned and considered complete (barring any errors or exceptions). But that means all I can show is 0% and then pause and then 100%.
I can count the number of rows, even until I close how many of them have actually been modified or added, and I could even calculate my estimated size on the DataRow based on the data type for each column using sizeof for value types such as int and length checking for strings like strings or byte arrays. With that, I could probably figure out, before the update, the approximate total size of the transfer, but I'm still stuck without any progress information as soon as Update is called on the TableAdapter.
Am I stuck just using an undefined progress bar or mouse cursor? Do I need to radically change our data access layer to be able to connect to such information? Even though I can't get it down to the exact KB passed (like the web browser file upload progress bar), can I even know when each DataRow / DataTable ends or something?
How do you best display this progress information with ADO.NET?
source to share
There is a half-solution for the part SELECT
that has to run the query first COUNT
to get the number of rows you expect to receive. This is only practical if the query COUNT
can return a result very quickly (i.e. in a split second) - on the other hand, if it takes a few seconds to start, then the query itself (as opposed to enumerating the result) can take longer than sending data, in which case you shouldn't try to show a discrete progress bar at all.
As for UPDATE
and INSERT
- no, there really isn't an easy solution to this, especially with TableAdapters. If you have a huge amount of data to submit, you might consider using the SqlBulkCopy class to load into a staging table and then perform the actual update on the server. This class offers the NotifyAfter property along with SqlRowsCopied , which can give you a reasonable approximation of the current progress (you already know the total number of rows in this case, since they are in memory).
This would, of course, require significant changes to your current TableAdapter implementation, but the typed dataset system in .NET was really not designed to handle recordsets of this size versus LAN connection.
I think most people would just prefer to use a step progress bar. Users expect this these days; even though you can accurately predict the number of rows and the baud rate, you still don't know how long it will take to complete the query, especially if the server is under heavy load, and it may be worse to provide bad estimates than no estimates.
If there is a real possibility that the query (or update) itself will take a long time to run on the server itself, apart from the time it takes to upload / download records, then I would definitely use the marquee progress bar. Otherwise ... good luck.
source to share