Pet Peeve 277: Ordinals vs key name

Pet Peeve #277

select FirstName, LastName, ShoeSize from MyFavoriteTable

... get a reader ...

while (reader.Read()) {
Console.WriteLine("First Name: " + (string)reader["firstname"]);
}

Per my previous post, I prefer (string)reader["firstname"] over reader["firstname"].ToString(). But it doesn't stop there. Oh no. There's more. I don't like that approach at all. I'd much rather see reader.GetString(FIRST_NAME) but more on that later.


Notice that the select statement queries for FirstName, but we're looking for "firstname" in our reader. The reader is going to do a case-sensitive search for "firstname". When that fails, it will do a case-insensitive search. Why search twice? In fact, why search at all? Just tell it the index number.

string firstName = reader.GetString(0);
string lastName = reader.GetString(1);
Int32 shoeSize = reader.GetInt32(2);

That's more efficient. Its going right to where it needs to go. But, 0, 1 and 2 aren't very readable, are they? Gosh no.

I use constants.

const int FIRST_NAME = 0;
const int LAST_NAME = 1;
const int SHOE_SIZE = 2;

get reader... create while loop, etc

string firstName = reader.GetString(FIRST_NAME);
string lastName = reader.GetString(LAST_NAME);
Int32 shoeSize = reader.GetInt32(SHOE_SIZE);

Of course, for this to work the fields need to be in a known predictable order, which in my experience is usually the case. Maybe it wouldn't fit if you have multiple existing procedures that return similar fields in different orders all going through the same method, but what are the odds of that? I've always been able to define the order.

Reader has another method called GetOrdinal() that you can use to look up the ordinals up front. So, if you really don't know the ordinal, you can do this:

int firstNameOrdinal = reader.GetOrdinal("FirstName");
int lastNameOrdinal = reader.GetOrdinal("LastName");
while (reader.Read()) {
string firstName = reader.GetString(firstNameOrdinal);
string firstName = reader.GetString(lastNameOrdinal);
...
}

That way, you only do the lookups once rather than at every iteration of the loop.

If you know they'll never change, then maybe you assign them to statics. I used to have a pattern to cover that scenario, but I quickly learned to dislike it because of the comingling of static/non-statics. Basically, when it got to the reader, it would check the value of one of the static ordinals. If it was -1, it would assign all of the statics. From there on, it wouldn't have to do it again. I don't like it.

The GetOrdinal follows the same steps as reader["fieldName"]. First it does a case-insensitive search, then a case sensitive search if necessary. So, if you don't have the ordinal, then hopefully you at least have the proper column name.

My position is clear: know the ordinals upfront and use them.

In order to do that effectively, you have to rely on the column order. If you do "select *", that's not reliable or portable. Always explicitly specify your select wether you're hitting a table or view directly, or when a proc does it for you.

Reader Source Code

I took a look at the reader source code to compare reader["string"] to reader.GetString(0). The by-ordinal approach doesn't immediately return the value... it does a little legwork first. By-string does the same legwork, plus the additional work of looking up the ordinal to get it started.