Derik Whittaker



Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at
Why using the index for data retrieval is evil

Today I was refactoring some sql procs along with the corresponding .net code that called the procs.

My primary focus was to implement some new business rules (ways to get totals) in the proc.  In doing so, I completely re-wrote the proc.  In the end the return values (column names) were the same, but the column order was different.  To my amazement when I ran the application the numbers where in the wrong fields.

As I started to track down the error I stumbled across this ugly evil.

Estimate = (int)dataTable.Rows[0][0];
InProgress = (int)dataTable.Rows[0][2] + (int)dataTable.Rows[0][1];

Please people don't access data in a data table by hardcoding the index value.  Yes I know this is the 'fastest' way get the data out, but it is also the most brittle.  For less brittle code try the following.  Yes I know that if the column name changes your code breaks.  But at least in this case the application will throw an exception, not return the wrong value

Estimate = (int)dataTable.Rows[0][ dataTable.Columns.IndexOf("Estimated") ];

Sorry, just rate it when i have to fix evil code by lazy developers.

Till next time,

Posted 12-28-2007 1:26 PM by Derik Whittaker



Dave wrote re: Why using the index for data retrieval is evil
on 12-28-2007 3:14 PM

I couldn't agree more.  

It pains me to no end when I have to work with this kind of code.  The performance difference is ridiculously trivial at this point, but the difference in maintainability is massive.

From time to time, I have to support a VB6/SQL Server 6.5 legacy app that takes it even farther.  It uses column indexes and SELECT * in a way that precludes even appending a new column to certain tables in the database.

Johan wrote re: Why using the index for data retrieval is evil
on 12-29-2007 11:09 AM


You're right. But even in such trival examples i see authors still use "Estimated". Please even though it speeks for itself use constants or readonly properties.

No offence but some people just take the word 'writing' of the author.

Derik Whittaker wrote re: Why using the index for data retrieval is evil
on 12-29-2007 2:51 PM


I don't disagree that 'magic' strings are bad.  But here is the rule of thumb I use when deciding if i should create a constant/property or use a magic string.

Will the string be used in more than once place?  If yes, create a constant.  If no, use the string literal'

The way i see it, if you create a constant that will be used only once, then you still have 1 point of failure so why not use it in line.

Kalpesh wrote re: Why using the index for data retrieval is evil
on 12-30-2007 12:24 AM


Usage of constant instead of magic string is better, if used in multiple places.

If one is worried about column name changes, use of an alias can help.

e.g. if The column name in TABLE is "Estimate" - the query could be written as "SELECT Estimate as EstimatedDate FROM TABLEA"

In this case, the .net code can use alias name to retrieve values. Changing of the column name in table won't break the code.

Lets further break the code.

Datarow firstrow = table.rows[0];

int estimatevalue = firstrow["estimateddate"];

Pardon my c# code above.

Johan wrote re: Why using the index for data retrieval is evil
on 12-30-2007 10:45 AM


In my experience if you introduce a constant, even if it is only used once other developers are more eager to use it. I've seen a lot of copy paste of the 'Magic string'. But this is just my experience.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Google Reader or Homepage Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of
Red-Gate Tools For SQL and .NET


SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
NHibernate Profiler
Balsamiq Mockups
JetBrains - ReSharper
Web Sequence Diagrams
Ducksboard<-- NEW Friend!


Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers


Community Server (Commercial Edition)