Getting feedback from your data objects by William Ryan

In most applications, you'll want to provide your users with feedback of one sort or another whenever a process runs.  Sometimes this takes the form of changing the screen's cursor or incrementing a ProgressBar or whatever you think your users will like.  This is particularly important in data access scenarios because it's common for them to be fairly long running.  I'm going to walk you through a few simple techniques to make your user interfaces more reponsive and hopefully make your applications more user friendly.  

The first thing I'd like to mention is an overall philosophy regarding data access.  When I first started playing with ADO.NET a few years ago, I was focused on learning the inner workings of it and making things do what I wanted.  As such I didn't attempt to do anything 'fancy', I just wanted to get things working.  It didn't take long for my aspirations to become a bit more ambitious.  I'm going to say something that will no doubt sound controversial but oh well.  If your data access techniques aren't primarily composed of Asynchronous calls/MultiThreading then you probably ought to reconsider why not (translation "you're doing it wrong").  In data access, whether you are writing to an XML file, invoking a web service or submitting a elementary database query, a lot of things can go wrong.  Writing to an XML file invariably involves, you guessed it, File IO.  Unless that file happens to be sitting on the local machine, there's a lot of room for stuff to go wrong (like an unplugged network cable).  A web service could be down or your internet connection could be unavailable.  Similarly, your db may have no available connections, the DBA could be reindexing the table or you could have connectivity problems.  While this isn't an exhaustive list of all of the things that could go wrong, it should give you a feel for the type of problems you may encounter.  In any of these instances, you have to balance allowing a reasonable amount of time for network traffic or similar issues with the amount of time a user is willing to wait for something to happen.  For instance if a database connection never timed out, you'd have some confused users the next time they tried to connect when the db was down.  So here's the point:  If you don't use Async methods, you're going to freeze the UI in this period.  The users of your app won't be able to do anything else within your application and if they click to another app for a few seconds and then click back to your app, they'll get that very annoying white blotch on the screen.  I've heard every excuse you can imagine as to why this is acceptable and it's usually something like "The users can't do anything with the app if the db is down" or "The users shouldn't be doing anything else anyway."  Even if this is true, why freeze the application? You know you don't like it when an app freezes on you and your users aren't any different.  Let's face it, those two 'reasons' are excuses and poor ones at that.  They are a rationalization for not learning how to run things asynchronously and in today's environment, there's very little excuse for it.  I'm not going to get into a discussion on the finer nuances of threading and asynchronous methods.  But let me say this:  Threading/Async calls are neither easy nor hard.  To get them to 'work', it takes very little work.  To make them work as they are supposed to, it takes a good deal of understanding.  Don't think you can just throw your methods into a few different threads.  If you don't understand what you are doing, you're going to screw something up.  Instead, I'd recommend approaching it with a fair amount of respect but not fear.  If you take the time to understand what's going on, it's not as daunting as you may think it is.

Ok, the first thing you need to do is hook up some events to handle things.  In this scenario, we're going to update a progress bar control as a SqlDataAdapter fills a DataTable.  On the DataTable side, we're going to trap the RowChanged event (there's also a RowChanging event which works essentially the same way).  On the SqlDataAdapter side we're going to trap the OnRowUpdated event.  For the sake of consistency, I decided to use the prolog events but there's a prelog counterpart for each one (RowUpdating, RowChanging).  

Since we're going to be good database programmers we're going to start out with an async strategy.  There are at least 10 different ways I could implement this and my point isn't that my way is the 'best'.  In fact I'm using one of the simplest implementations.  My main intent is simply to move the data access stuff out of the UI Thread.  If you are interested in some more complex variations, please drop me a line in the forums and I'll be glad to go into it with you. So first I create two delegates:

private delegate void SelectQueryDelegate(); private delegate void UpdateQueryDelegate();

I'm also going to declare and instantiate a DataTable at the module level so it's visible throughout the form.  This code is all included in the form, but in reality you'd probably want to move this into a class and make the DataTable a property.  

private DataTable dt =  new DataTable();

Now, I'm going to add two event handlers at Form_Load, one for RowUpdating and one for RowChanged.  The RowUpdating handler will fire each time we send an Update to the DB.  It's VERY important to note that this is accomplished through the SqlDataAdapter (although other DataAdpater implementation have this as well) not System.Data.  This is a big distinction b/c the OnRowChanged is implemented through the DataTable class (System.Data).

private void Form1_Load(object sender, System.EventArgs e) {    this.dt.RowChanging += new DataRowChangeEventHandler(OnRowChanging);    this.da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated); }

As a course of habit, I immediately write the RemoveHandlers right after I add them so that I don't forget about them.  One logical place for these is in the Form_Closing event but this will vary depending on the application

private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e) {    this.dt.RowChanging -= new DataRowChangeEventHandler(OnRowChanging);    this.da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated); }

You can see that my delegates are pointing to OnRowChanging and OnRowUpdated so I'll add those now:

private  void OnRowChanging( object sender, DataRowChangeEventArgs e ) {    if(pb.Value < dt.Rows.Count-1)<BR>    {        pb.Value++;    }           tbEvents.Text += e.Row[0].ToString()+"\r\n"; } protected  void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) {     pb.Value++; }

As you can see, I'll I'm doing is incrementing a progress bar when the row Updates or is Selected, and just adding some text with the values to a text box when the DataTable is being 'filled'.

Now, I've created two methods that my delegates are going to address.  These are just plain old functions.  The main thing to note is that the function matches the signature of the delegate.  We're going to declare these as void and since I'm not implementing Callbacks, the call will take two null parameters (If you aren't familiar with BeginInvoke then you may think the signatures don't match.  They actually do.  There are two parameters that always need passed, even if they are null.  The first series of params are the ones that must match the function signature.)

Anyway, I declare an instance of each delegate pointing it to its respective function.  From there I create in IAsyncResult  and call the .BeginInvoke on the delegate:

private void btnFire_Click(object sender, System.EventArgs e) {     SelectQueryDelegate selectDel = new SelectQueryDelegate(SendSelect);     IAsyncResult myResult = selectDel.BeginInvoke(null, null); } private void SendSelect() {     pb.Maximum  = da.Fill(dt)-2;     pb.Value = 0; }       private void btnUpdate_Click(object sender, System.EventArgs e) {     UpdateQueryDelegate updateDel = new UpdateQueryDelegate(SendUpdate);     IAsyncResult myResult = updateDel.BeginInvoke(null, null); } private void SendUpdate() {     pb.Maximum = dt.Rows.Count-1;     da.Update(dt);     pb.Value = 0; }

That's pretty much all there is to it.  Now I know this might look like a lot of extra work just to do something this simple.  But actually it's not very complex and it takes very little time to build some real async support into your data access class (a while ago I added full async support w/ callbacks to Microsoft's Data Access Application block and it took me just short of two hours to add and test, most of which was testing).  Now, no matter what happens, the UI won't freeze.  Why?  Because the data access code isn't running in the UI thread.  If you've used ADO in the past and lost a DB connection, you know how ugly this can be.  If you've used ADO.NET now and run your data access code in the same thread as  the UI and ever had a connection/command timeout your users know how ugly this is.  Now the progress bar will update as the datatable is filling and as it's updating.  I actually did something kind of lame with the progress bar on the fill (I used DataTable.Rows.Count for the Maximum value - but that's not know until all the events have fired - I'm saving that for the next article).  Anyway, no matter what happens you have  a responsive UI. The users can flip back and forth and no annoying white screen.  Most Importantly though, I accomplished all of this WITHOUT DOEVENTS!  While DoEvents is very useful when used correctly, too many people wimp out and use it as a crutch - and this crutch is very costly in terms of performance and invariably creates another problem which is worse than the one it purports to serve.  (causing the process to run for 45 seconds for instance instead of 20 just to stop it from appearing frozen is exactly what I'm talking about).  I think the code is simple enough that hopefully it speaks for itself but if not, please feel free to Follow Up in the forums and I'll be glad to walk you through it.  Also, if you are just getting into threading and want to know a little more about what to look at, don't hesitate to ask me.  The rule of thumb on threading is that it's not easy, nor is it impossible.  There are a lot of pitfalls and you aren't going to fully understand it after an hour or two. It's going to take a few mistakes but it's well worth it.  I've tried to push the limits in regard to both threading and data access and while I don't profess to be an expert, I can hold my own in both regards and will do what I can to help if you have any questions.