Click here to Skip to main content
Click here to Skip to main content
Go to top

Accessing Row based data in an efficient and maintainable manner

, 1 Aug 2006
Rate this:
Please Sign up or sign in to vote.
Methods for accessing row based data in C# (.NET)

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

A good place to find answers to hard problems: Google

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ennis Ray Lynch, Jr.
Architect ERL GLOBAL, INC
United States United States
My company is ERL GLOBAL, INC. I develop Custom Programming solutions for business of all sizes. I also do Android Programming as I find it a refreshing break from the MS.

Comments and Discussions

 
QuestionHi Pinmemberyan32122-Jul-12 23:36 
JokeThis is revolutionary Pinmemberjoelhegeman7-May-09 6:39 
GeneralRe: This is revolutionary PinmemberEnnis Ray Lynch, Jr.7-May-09 7:02 
QuestionWhat about using a generated typed DataSet ? Pinmemberhleuze10-Aug-06 4:38 
AnswerRe: What about using a generated typed DataSet ? PinmemberEnnis Ray Lynch, Jr.10-Aug-06 5:00 
GeneralRe: What about using a generated typed DataSet ? Pinmemberhleuze10-Aug-06 22:17 
QuestionNice but ... PinmemberSébastien Lorion7-Aug-06 19:13 
AnswerPersonally PinmemberEnnis Ray Lynch, Jr.7-Aug-06 19:28 
GeneralRe: Personally PinmemberChris S Kaiser9-Aug-06 7:57 
GeneralUnf, yes PinmemberEnnis Ray Lynch, Jr.9-Aug-06 11:48 
GeneralRe: Unf, yes PinmemberChris S Kaiser9-Aug-06 11:58 
GeneralRe: Nice but ... PinmemberAndrew Rissing8-Aug-06 4:41 
AnswerRe: Nice but ... PinmemberSébastien Lorion8-Aug-06 5:15 
GeneralRe: Nice but ... PinmemberAndrew Rissing8-Aug-06 5:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 1 Aug 2006
Article Copyright 2006 by Ennis Ray Lynch, Jr.
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid