SqlDataReader.GetOrdinal() question

[Mark]

Hi...

I came to a company with great volumes of legacy code, and I found an odd
pattern.

They use SqlDataReader for a lot of queries, but the reader is rather
unfriendly with GetOrdinal(); if the column isn't there, it throws an
exception, rather than returning -1 or some other friendlier value.

In several places in the code, they've simply put a try/catch around the
GetOrdinal() to make it less nasty, but given the expense of exceptions, that
seems pretty onerous.

Would SqlDataReader.GetSchemaTable().Columns.IndexOf ("name"); be cheaper?
I don't know if GetSchemaTable() is returning the DataTable it has
internally, or if it has to cobble one together from scratch, which would cut
into the time.

As an aside, what's the reason SqlDataReader.GetOrdinal() is so unfriendly?
I mean, I suppose it requires the reader users to have more explicit
knowledge of their queries, but as I've found, people can still come up with
rather expensive alternatives to keep vague about the details of their data.

Thanks
Mark

 

[Mark]

Would SqlDataReader.GetSchemaTable().Columns.IndexOf ("name"); be cheaper?

I don't know if GetSchemaTable() is returning the DataTable it has
internally, or if it has to cobble one together from scratch, which would cut
into the time.

Okay, I gave it a try and found that GetSchemaTable() doesn't get the schema
of the result set, as I'd thought, so the above just doesn't do what I'd
hoped.

Using a SqlDataReader, I don't see any non-explosive way to test if a result
set has a column, unfortunately...

 

[Kerry Moorman]

Mark,

The datareader's FieldCount property gives you the number of columns in the
current row.

Kerry Moorman

 

[Fred]

Dans : Mark écrivait :

Okay, I gave it a try and found that GetSchemaTable() doesn't get the
schema of the result set, as I'd thought, so the above just doesn't
do what I'd hoped.

Using a SqlDataReader, I don't see any non-explosive way to test if a
result set has a column, unfortunately...

Hello,

If you are not sure that the column exists, why not loop on the indexes
and use the GetName method ?
That's what the GetOrdinal function does, calling an internal IndexOf
method and throwing an exception when the IndexOf returns -1. It won't
be more expensive than use GetOrdinal, perhaps even less.

 

[Mark]

Hi Fred...

If you are not sure that the column exists, why not loop on the indexes
and use the GetName method ?
That's what the GetOrdinal function does, calling an internal IndexOf
method and throwing an exception when the IndexOf returns -1. It won't
be more expensive than use GetOrdinal, perhaps even less.

I used Reflector to look at what GetOrdinal() does, and it's not that
simple. The SqlDataReader keeps a hash table of the column names. Oddly,
it's a case-sensitive hash table, so if you call GetOrdinal() with with a
name matching the case of the original ddl, the look-up is much quicker.

If you don't call using the same case as the original ddl, then it devolves
to a linear, case-insensitive search.

Why the hash table isn't case-insensitive to start off probably has to do
with some strange edge-cases of internationalization.

Thanks
Mark