up vote 0 down vote favorite

I'm trying to use a SqlDataReader (I'm quite aware of the beauty of Linq, etc, but the application I'm building is partly a Sql Generator, so Linq doesn't fit my needs). Unfortunately, I'm not sure what the best practices are when using SqlDataReader. I use code like the following in several places in my code:

using (SqlDataReader reader = ...)
{
    int ID = reader.GetInt32(0);
    int tableID = reader.GetInt32(1);
    string fieldName = reader[2] as string;
    ...//More, similar code
}

But it feels very unstable. If the database changes (which is actually extremely unlikely in this case) the code breaks. Is there an equivalent to SqlDataReader's GetInt32, GetString, GetDecimal, that takes a column name instead of an index? What's considered best practice in this case? What's fastest? These parts of my code are the most time intensive portions of my code (I've profiled it a few times) and so speed is important.

[EDIT]

I'm aware of using the indexer with a string, I misworded the above. I'm running into slow runtime. My code works fine, but I am looking for any way I can steal back a few seconds inside these loops. Would accessing by string slow me down? I know that the db-access is the primary time intensive operation, there's nothing I can do about that, so I want to cut back the processing time for each element accessed.

[EDIT]

I've decided to just run with GetOrdinal unless someone has more concrete examples. I'll run efficiency test later. I'll try to remember to post them when I actually run the tests.

share|improve this question
100% accept rate
feedback

1 Answer

up vote 3 down vote accepted

The indexer property takes a string key, so you can do the following.

reader["text_column"] as string;
Convert.ToInt32(reader["numeric_column"]);

Additional suggestion

If you're concerned about the string lookup being slow, and assuming numeric lookup is quicker, you could try using GetOrdinal to find the column indices before looping through a large result set.

int textColumnIndex = reader.GetOrdinal("text_column");
int numericColumnIndex = reader.GetOrdinal("numeric_column");
while (reader.Read())
{
    string text = reader[textColumnIndex] as string;
    int number = Convert.ToInt32(reader[numericColumnIndex]);
}
share|improve this answer
1  
Use int.Parse/TryParse instead. – Femaref Nov 10 '10 at 14:55
Yes, I knew that. I was mainly curious about the speed and if this would slow things down even more in my most time intensive portion of code. – Christopher Pfohl Nov 10 '10 at 15:01
@Femaref: why? Is there an appreciable difference between Convert.ToInt32 and int.Parse that I'm not aware of? – batwad Nov 10 '10 at 16:37
Convert.ToXXX checks any and all possible conversions from a boxed value type. Also, I didn't mean int.Parse/TryParse (that would apply to string) but use an explicit cast to unbox. See this blog by Eric Lippert: blogs.msdn.com/b/ericlippert/archive/2009/03/19/… – Femaref Nov 10 '10 at 16:47
Ok, that's helpful. @Femaref, do you know if reader.GetInt(x) behaves similarly to Convert.To***()? – Christopher Pfohl Nov 10 '10 at 17:06
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.