Mapping of columns in SqlDataReader

Hi,

I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:

string myString = myReader.GetValue(0) // this sets myString to first value in a row

If, however, I change order of columns returned by stored procedure myString would be set to wrong value.

Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"];

you can get the ordinal position of your column like this

//dynamicall get our columns position in our readerint myOrdinal = myReader.GetOrdinal["ColumnName"];//use the discovered ordinal to retrieve the datastring myString = myReader.GetValue(myOrdinal);

|||

This looks ok.

One more question. Does this impact performance and how (much)?

|||

it will use a few cpu cycles - but everything does...
If you are iterating over a reader, you can collect all the ordinal positions up front before entering your loop and then you've minimized the overhead.

i can give you an exact performance impact, but i think it's relatively insignificant and i always do it like this so i'm not dependent on the position of the columns in the data being returned.

|||OK, thanks.|||

it should be also possible to do this:

string myString = myReader.GetValue("Column name")

so you can save some processor cycles and memory

Thanks

|||

jpazgier:

it should be also possible to do this:

string myString = myReader.GetValue("Column name")

so you can save some processor cycles and memory

When you access a reader column using the column name, the ordinal lookup is still performed (behind the scenes) so you wont actually get any cpu savings.

when you are iterating over a datareader and you access all your columns by name, you are actually performing this ordinal lookup on each row of data which results in a performance penalty. since the columns cannot move around after you have created your reader, you can collect up all the ordinal positions outside of your loop (before the: while reader.read). then you would use only the pre-collected ordinal positions when inside of your loop.

this should result in a net performance gain.

in my testing, i actually saw about a 10% performance increase when using ordinal column positions over using column names inside the loop.

|||Thank you both. You really pointed out some interesting things. Really helpful.