Jan Schreuder on .Net

Using CommandBuilder to create dynamic command objects

At the moment, I'm working on a conversion between 2 databases. I was looking for a way of avoiding the tedious job of creating command objects for each of the tables I needed to insert data into. In most projects I worked on over the last couple of years, I was able to use code generation to do this. But not in this project. And I did not have the time to write new templates, or evaluate existing ones.

So I went back to another option which I used before I had access to code generation, the CommandBuilder object. The following example shows you how this works on the Customers table in the Northwind database:

   SqlConnection dcNorthwind = new SqlConnection();

   SqlDataAdapter daCustomers = new SqlDataAdapter("Select * From Customers". dcNorthwind);

   SqlCommandBuilder cb = new SqlCommandBuilder(daCustomers);

   SqlCommand cmdInsert = cb.GetInsertCommand();

   SqlCommand cmdDelete = cb.GetDeleteCommand();

   SqlCommand cmdUpdate = cb.GetUpdateCommand();

The CommandBuilder object builds the commands for insert, update and delete using the results of the SQL query. Using this I created the following generic method to create the command objects I needed:

public static void BuildCommandObjects(string conString, string tableName,
         ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
{
   if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
   if ((tableName == null) || (tableName.Length == 0)) throw new ArgumentNullException( "tableName" );

   try
   {
      using (SqlConnection sqlConnection = new SqlConnection(conString))
      {
         using (SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from " + tableName, sqlConnection))
         {
            using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
            {
               insertCmd = cmdBuilder.GetInsertCommand();
               updateCmd = cmdBuilder.GetUpdateCommand();
               deleteCmd = cmdBuilder.GetDeleteCommand();
            }
         }
      }
   }
   catch (SqlException ex)
   {
      throw new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
   }
}

The  SqlCommand objects created by this procedure can be used in the UpdateDataSet procedure from the Microsoft.ApplicationBlock.Data.SqlHelper class. Creating objects in this manner comes at a price, as usual. And that price is database performance. Another issue is that when you have an auto-identify column as your primary key, the value is not return by the insert command. More details on performance problems and limitations can be found in this MSDN article. However, for the project I work on now, these limitations are not really a problem.

And then there is a big advantage as well:

The command object is truly dynamic. A change in your table automatically changes the insert, update and delete commands. So no need to change your code, or re-generate it.

Using the CommandBuilder object was ideal for my application. I could write the code to convert data from one table to another, without worrying about the insert statements for the target tables. I did not need to re-write some of the code after the table was changed. And those changes include type and size changes for some of the columns. However, if wether you should use CommandBuilder or code-generation really depends on your project.

Regular expressions library

For the project I work on now, I needed to know if the string contained a valid dutch postcode value. Best way to do this is using a regular expression. For example:

int IsPostcode(string value)
{
   Regex regex = new Regex(@"^[1-9][0-9]{3}\s?[a-zA-Z]{2}$");
   Match m = regex.Match(value);
   return m.Success;
}

The problem is of course finding the right expressions. For this, I now use Regular Expression Library on the internet. Most of you probably already know this web site. For those who don't, check it out. It's a really good resource for regular expression.

Visual Studio 2005 Express series

Someone might have blogged this, and for us professional developers it might not even be such a issue. But I found the Express products for Visual Studio at Microsofts corporate website.

“The Express products, expanding the Visual Studio product line to include lightweight, easy-to-use, easy-to-learn tools for hobbyists, enthousiasts, and novices who want to build dynamic Windows applications and Web sites.“

As I said, for professional developers these downsized versions might not be interesting. But if you hate running the full version at home, it will provide you with a good alternative. And since they are available for download now, you have the opportunity to start looking at the new versions of these development tools. The beta's include the following versions:

1. Visual Basic
2. Visual C#
3. Visual J#
4. Visual C++
5. Visual Web Developer
6. SQL server

Powertoys weblog

I've visited the PowerToys weblog on several occasions. It is good source for VS.Net add-ins and stuff. Today I found two interesting items there which I just had to share with you:

1. For all you VB.Net lovers out there, a new version of VBCommenter has been released. It gives you, as a VB.Net developer, the same XML commenting capabilities that we C# developers simply adore :-)
   
2. An VS.Net add-in for the CodeHound web site. CodeHound is a search engine which you can use to find code samples on the internet. Using the add-in, you can quickly search for code samples. Only problem is that codehound is a little slow. Advantage is that you only get .Net resources.

So have a peek at PowerToys weblog.

Update: You gotta be patient when you use CodeHound. It works, but's it's a bit slow...

Microsoft Anti-Spyware

On December 16, 2004, Microsoft aquired Giant Company Software Inc. Giant sold and maintained an anti-spyware application. A few weeks later, Microsoft released a beta version of their new anti-spyware application. 

I've been running it both at home and at the office since then, and I'm really enthousiastic about this product. Once installed, the application can be configured to automatically detect the installation of spyware. On my home systems, it found spyware that AdAware left untouched. So I'm convinced. Especially now that I've read that Microsoft is considering to ship the tool as part of Internet Explorer 7.

More information on this tool can be found here: Microsoft Windows Antispyware. If you want to download the beta, click here.

DataSet Quick watch

I know a lot of people use the DataSet QuickWatch utiltity developed by Mohammed Barqawi. The first version of this was posted May 2004.

Matt Simner implemented a few additional features which are very interesting, such as:

• Typed DataSet support.
• Support for DataTable and DataRow
• Row Filter option
• Visual changes.

Those interested can find the full article here. From here, you can also download the code.

Code inspections. What do you think?

In one of my previous posts I tried to make a case for code inspections. I personally believe that regular code inspections, provided they are conducted correctly, can greatly improve the quality of your code and thus the quality of your end product.

The post contained an example to prove my point. The wrong example as it turned out, since the code that was delivered after the inspection contained a new error. However, three developers actually saw that and pointed them out to me. So to me, another example that code inspections can work. However, one of the commentors (Dennis van der Stelt) argued that this proved that code inspections did not work! One comment even said that he'd never seen documentation on the subject or large companies using this method.

Therefore some resources on code inspections:

• Michael E. Fagan worked at IBM and formalised and introduced code inspections back in 1976. His website, as well as electronic versions of his books can be found at www.mfagan.com. Check out the resources section. IBM still promotes code reviews, for example in Java development.
• GoldPractices.Com has a section on code inspections. You can find a full description of the code inspection process here.
• Another interesting read is “Software inspections are not for quality, but for engineering economics“, by Tom Gilb. Tom Gilb teaches code inspection methods and is considered an authority on code inspections. Some of the companies he works for: Nokia, Intel, Microsoft, Sun Microsystems. The document argues that “Inspection should be used as a specification Quality control tool. The 'quality being checked' is the specification consistency with your best practice standards.

So let's start a nice discussion here and let me know your opinion on code inspections. Do you think code inspections can help improve the quality of software, or do you think it's utterly useless? Or maybe you have experience with code inspections?

How to write unmaintainable code

Making a case for code inspections

Last week, I was asked to look at a problem in our companies application framework. It appeared that our database component sometimes failed when using transactions. While looking at that problem, I also saw some other regularities that, in most cases, might not cause a problem. But looking at the code, one could easily see that something was wrong.

Take for example this piece of code which I've editted for the purpose of this article. Oh, and if you recognise this code as being yours, don't be offended. We all make mistakes like this one.