I'm wondering if there is a tried/true method for reporting the progress of a DB call from a .Net application to its user. I'm wondering if it's even possible to actually indicate a percentage of completion, or is the best/only approach to simply display a "loading" animation to indicate that something is happening?
Also, does SQL2008 address this to anyone's knowledge?
You have to load things deterministically. For example if you know that you'll be fetching a lot of data, for example, you might do something like:
-get a count of all of the records -get 500 of them -report status as 500/total % -get 500 more -report status as 1000/total % -... continue until you've gotten them all of the user has canceled
This would be incredibly wasteful on something that takes no time at all, since the mere fact of going to the database is a large part of the overhead.
As far as I know there is no way to do this. My suggestion is use one of the circular progress bars that just spins forever. Microsoft uses these in database operations in SQL and Project.
Here is an article on CodeProject that has a variety of these:
Adam Berent
You could use raiserror with the no wait option to send a message back to the remote caller without ending the sql transaction. Of course this will break the usual try/catch conventions, so caution is advised.
On the other hand, "a db call" could also refer to a restore or backup operation, in which case you could simply use the progress event in the SMO (server management objects) library.
Additionally, an ORM solution such as Linq could be used to handle "db call"s such as batch update operations. If that is the case, then this answer could be useful: How can I get a percentage of LINQ to SQL submitchanges?
