Accessing Row based data in an efficient and maintainable manner

Introduction

In a perfect system there is no reason for maintenance. Everything is well designed from the onset. Stored Procedures never change their return cursors and data is always of the right size and type. However, in the real-world, things change. Even more importantly, in the real-world you will work with multiple developers. Throw in a good helping of corporate and cultural communication barriers and you have a recipe for developing code the quickly becomes unmanageable. This article is written specifically for accessing fields from rows from a dataset and a small subset of difficulties that crop up.

The problem

Accessing row based data in C# against any database is a fairly simple task.

foreach(DataRow row in table.Rows){
    customer = new Customer();
    customer.ID = (Int32)row[0];
    customer.FirstName = row[1].ToString();
    customer.LastName = row[2].ToString();
}//end foreach

This is fast and easy. It is also very common as I see it implemented fairly regularly.

On a single developer team/small project this does not present a problem. However, consider the larger case. The business group at the Fortune 500 you are working for has just decided that MiddleName is a required field in the database. The dba adds the field and changes the stored procedures and now your code does not work. Even worse, it does not fail.

A Solution

Finally the email gets distributed and the responsible team spots the error immediately. The new code is written as follows:

foreach(DataRow row in table.Rows){
    customer = new Customer();
    customer.ID = (Int32)row["customerID"];
    customer.FirstName = row["firstName"].ToString();
    customer.LastName = row["lastName"].ToString();
}//end foreach

Unfortunately, this is 2 orders of magnitude slower! In most cases no one will ever notice. But in other cases, this will stand out like a *insert lame cliché here*

Another Solution

After staying up for weeks straight and rewriting stored procedures to use fully qualified names and tweaking bits of logic there is still more to be saved in the application. Looking back at the for loop to load 50,000 customers into some customer objects it becomes obvious that there is another way:

int customerIDIndex = table.Columns.IndexOf("customerID");
int customerFirstNameIndex = table.Columns.IndexOf("firstName");
int customerLastNameIndex = table.Columns.IndexOf("lastName");

foreach(DataRow row in table.Rows){
    customer = new Customer();
    customer.ID = (Int32)row[customerIDIndex];
    customer.FirstName = row[customerFirstNameIndex].ToString();
    customer.LastName = row[customerLastNameIndex].ToString();
}//end foreach

Possible Problems

In any sufficiently large situation it is possible for code to be out of sync with the database. Especially with many companies now applying SOX standards to even the most trivial data. With this in mind there is one more step to increase the maintainability of this code. Throwing in some assertions will give any debugger immediate access to the logical root of all evil, rather than spending an hour deciphering "friendly" error messages.

int customerIDIndex = table.Columns.IndexOf("customerID");
int customerFirstNameIndex = table.Columns.IndexOf("firstName");
int customerLastNameIndex = table.Columns.IndexOf("lastName");

System.Diagnostics.Debug.Assert(customerIDIndex > -1, <BR>    "Database out of sync");
System.Diagnostics.Debug.Assert(customerFirstNameIndex > -1, <BR>    "Database out of sync");
System.Diagnostics.Debug.Assert(customerLastNameIndex > -1, <BR>    "Database out of sync");

foreach(DataRow row in table.Rows){
    customer = new Customer();
    customer.ID = (Int32)row[customerIDIndex];
    customer.FirstName = row[customerFirstNameIndex].ToString();
    customer.LastName = row[customerLastNameIndex].ToString();
}//end foreach
<P><FONT face="Courier New"></FONT></P>

Conclusion

There are likely other solutions. Relying on better error handling will even remove the need for the assertions. However, when writing maintainable code, you have to code defensively. Hopefully this small article has illustrated a method of defensive programming that also keeps intact that notion of efficient coding.

As a small but relevant aside, it is also possible to do the following:

int customerIDIndex = table.Columns.IndexOf("customerID");
int customerFirstNameIndex = table.Columns.IndexOf("firstName");
int customerLastNameIndex = table.Columns.IndexOf("lastName");

foreach(DataRow row in table.Rows){
    customer = new Customer();
    if(customerIDIndex > -1)
        customer.ID = (Int32)row[customerIDIndex];
    if(customerFirstNameIndex > -1)
        customer.FirstName = row[customerFirstNameIndex].ToString();
    if(customerLastNameIndex > -1)  
        customer.LastName = row[customerLastNameIndex].ToString();
}//end foreach

But I would not recommend this as it just masks the error and creates many unnecessary if statements.

Points of Interest