Since our class is in the middle of the ADO.Net material, I feel it's my responsibility to share some lessons from the real (as opposed to the academic) ADO.Net world; the class material and the Microsoft Press book skirt around a lot of information that is important to any real world development effort. Honestly, we could devote an entire 14 sessions just to ADO.Net (and WeProgram.Net has talked about offering something like this in a “ADO.Net for Enterprise Applications“ bootcamp format -- shoot me an email if you'd be interested). Here are some good references I frequently use for ADO.Net . . .
First off, Bill Vaughn does a fine job of pointing out the shortcomings of using the commandbuilder in this MSDN piece; he also discusses some better alternatives for working with databases. Paul Laudeman turned me on to this link several months ago, and I break it out each time ADO.Net comes up in the ITPro curriculum.
Second, Microsoft has provided an open source layer between your ADO.Net objects and your business objects; it's called the Data Access Application Block and you can read all about it here.
Instead of writing something like this:
dim cn as new SqlConnection( K_DSN )
cn.Open()
dim cmd as new SqlCommand( sql, cn )
cmd.ExecuteNonQuery()
You can write this:
SqlHelper.ExecuteNonQuery( K_DSN, CommandType.Text, sql )
And that's just the tip of the iceburg; it's a big productivity boost as the SqlHelper class wraps the lower level ADO.Net objects in a more developer-friendly fashion. I reference it in most of my applications. Microsoft has a number of other Application Blocks for simplifying common development tasks, just search MSDN for "Application Blocks" and you'll turn them up.
Third, this link offers architectural guidelines for data access and serves as a solid real-world reference.
Finally, no data acess desicisions should be made without also considering performance. Here is a detailed treatment comparing various data access alternatives from Microsoft's performance testor/author Priya Dhawan.
This is enough to get you to off to a good start, but I'd be curious to learn what other resources people find useful.
I’ve been posting a bit on delegates and asynchronisity (wasn’t that a song by the Police?), and I just finished a messaging implementation for a project that speaks to this topic. I've simplified it some, hoping it's easier to follow this way. I’m using C# in this example, but many of the concepts apply to the VB.Net folks too.
The Problem: a long-running operation (minutes or hours in duration) needs to process without preventing the user from doing other things with the application; the user needs to have updates as to the progress of the operation.
The Solution Level 1: I decided to implement a winform application (to avoid timeouts and other ugly async issues with a web solution), using a progress bar to show how the long-running operation was doing, and display a dialog box when the long running task was completely finished. The long-running operation would be invoked asynchronously.
The Solution Level 2: The trick is communicating between the operation and the windows application. I need a thread-safe means to invoke methods on the windows application from within the asynchronous, long-running operation.
The Solution Level 3: I need to launch the long-running operation asynchronously (delegate #1), and I need to communicate with the windows app from within the long-running operation (delegate #2).
I started by building my own EventArgs class:
public class StatusEventArgs : EventArgs
{
public bool isComplete;
public int TotalProgress;
public int ProgressSoFar;
public StatusEventArgs( int totalProgress, int progressSoFar )
{
this.isComplete = false;
this.TotalProgress = totalProgress;
this.ProgressSoFar = progressSoFar;
}
}
This class extends the behaviour of the .Net EventArgs class and you can pass this object around in lieu of the generic EventArgs class; it contains the specific information I’m interested in communicating between my asynchronous operation and the windows client. File this away as this is a handy tactic to bring into your general .Net programming.
Next, I need a delegate (think typesafe function pointer) to invoke from my asynchronous operation:
public delegate void showProgressDelegate( object sender, StatusEventArgs e );
My long-running operation will accept a showProgressDelegate as a parameter to the method and make calls to the windows app via the delegate whenever it needs. Here’s a condensed form of the long-running operation:
public void populateProducts( showProgressDelegate ShowProgress )
{
StatusEventArgs e = new StatusEventArgs( 0, 10 );
ShowProgress( sender, e ); //call to win app
for( int x = 0; x < 10; x++ )
{
System.Threading.Sleep( 5000 );
e.ProgressSoFar+=1;
ShowProgress( sender, e ); //call to win app
}
e.isComplete = true;
ShowProgress(sender, e); //final call to win ap
}
You can see how convenient it is to have your own custom EventArgs class; I’m incrementing the progress while we loop through our time consuming process. Every call to ShowProgress communicates back to the windows client courtesy of .Net delegates.
Let me go ahead and show the windows client code (note that eManagerLib is the name of my class library containing the long-running operation and related code):
public delegate void ShowProgressHandler(object sender, eManagerLib.StatusEventArgs e);
private void btn_Click(object sender, System.EventArgs e)
{
eManagerLib.showProgressDelegate del =
new eManagerLib.showProgressDelegate( ShowProgress );
eManagerLib.ingramUtil obj = new eManagerLib.ingramUtil();
ClientShowProgressDelegate cliDel =
new ClientShowProgressDelegate( obj.populateProducts );
cliDel.BeginInvoke( del, null, null );
btn.Enabled = false;
}
void ShowProgress( object sender, eManagerLib.StatusEventArgs e)
{
if( this.InvokeRequired == false )
{
ShowProgressHandler showProgress =
new ShowProgressHandler( ShowProgress );
Invoke( showProgress, new object[] { sender, e } );
}
else
{
progressBar1.Maximum = e.TotalProgress;
progressBar1.Value = e.ProgressSoFar;
if( e.isComplete )
{
MessageBox.Show( "Finished" );
}
}
}
In a nutshell, in our button click event we construct a new showProgressDelegate (del) and a new ClientShowProgressDelegate (cliDel). Our cliDel delegate points to the long-running operation (obj.populateProducts) and we invoke this delegate using BeginInvoke and pass the second delegate (del) for use in the populateProducts method. When populateProducts chooses to notify the windows application, it will call the windows app’s ShowProgress method. The two delegates provide our asynchronous messaging system: ClientShowProgressDelegate launches our asynchronous operation, the ShowProgressDelegate calls back to the windows app to update the client. Our ShowProgress method receives the calls from the long-running operation, synchronizes them onto the proper UI thread (thanks again Peter), and updates the windows form appropriately.
Note that I’ve omitted error handling and some other tidbits in the hopes of making this example clear and terse. You can capitalize on this sort of framework to solve all sorts of messaging problems -- no need to continuously poll a database, file, or static variable, let the components of your system send their own messages with a little .Net delegate elbow grease.
Happy .Netting!