Click here to Skip to main content
12,691,453 members (29,495 online)
Click here to Skip to main content
Add your own
alternative version

Stats

56.8K views
30 bookmarked
Posted

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>

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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionHi Pin
yan32123-Jul-12 0:36
memberyan32123-Jul-12 0:36 
JokeThis is revolutionary Pin
joelhegeman7-May-09 7:39
memberjoelhegeman7-May-09 7:39 
GeneralRe: This is revolutionary Pin
Ennis Ray Lynch, Jr.7-May-09 8:02
memberEnnis Ray Lynch, Jr.7-May-09 8:02 
QuestionWhat about using a generated typed DataSet ? Pin
hleuze10-Aug-06 5:38
memberhleuze10-Aug-06 5:38 
AnswerRe: What about using a generated typed DataSet ? Pin
Ennis Ray Lynch, Jr.10-Aug-06 6:00
memberEnnis Ray Lynch, Jr.10-Aug-06 6:00 
GeneralRe: What about using a generated typed DataSet ? Pin
hleuze10-Aug-06 23:17
memberhleuze10-Aug-06 23:17 
QuestionNice but ... Pin
Sébastien Lorion7-Aug-06 20:13
memberSébastien Lorion7-Aug-06 20:13 
AnswerPersonally Pin
Ennis Ray Lynch, Jr.7-Aug-06 20:28
memberEnnis Ray Lynch, Jr.7-Aug-06 20:28 
GeneralRe: Personally Pin
Chris S Kaiser9-Aug-06 8:57
memberChris S Kaiser9-Aug-06 8:57 
GeneralUnf, yes Pin
Ennis Ray Lynch, Jr.9-Aug-06 12:48
memberEnnis Ray Lynch, Jr.9-Aug-06 12:48 
GeneralRe: Unf, yes Pin
Chris S Kaiser9-Aug-06 12:58
memberChris S Kaiser9-Aug-06 12:58 
GeneralRe: Nice but ... Pin
Andrew Rissing8-Aug-06 5:41
memberAndrew Rissing8-Aug-06 5:41 
AnswerRe: Nice but ... Pin
Sébastien Lorion8-Aug-06 6:15
memberSébastien Lorion8-Aug-06 6:15 
GeneralRe: Nice but ... Pin
Andrew Rissing8-Aug-06 6:39
memberAndrew Rissing8-Aug-06 6:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.170117.1 | Last Updated 1 Aug 2006
Article Copyright 2006 by Ennis Ray Lynch, Jr.
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid