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(); |
| private DataTable dt = new DataTable(); |
| private void Form1_Load(object sender, System.EventArgs e) { this.dt.RowChanging += new DataRowChangeEventHandler(OnRowChanging); this.da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated); } |
| private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e) { this.dt.RowChanging -= new DataRowChangeEventHandler(OnRowChanging); this.da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated); } |
| 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++; } |
| 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; } |