How do i get the ordinal of a column in a DataReader

Question

How can i find out if a column exists in a DataReader's results set?

i try:

int columnOrdinal = reader.GetOrdinal("LastName");
columnExists = (columnOrdinal < 0);

but GetOrdinal throws an exception if the column does not exist. My case is not exceptional. It's the opposite. It's...ceptional.

Note: Not related to my question but, the real reason i want to know if a column exists is because i want to get the ordinal position of a column, without throwing an exception if the column doesn't exist:

int columnOrdinal = reader.GetOrdinal("Lastname");

Note: Not related to my question but, the real reason i want to know if a column exists, because i want to know if the column contains null:

itIsNull = reader.IsDBNull(reader.GetOrdinal("Lastname"));

Unfortunately IsDBNull only takes an ordinal, and GetOrdinal throws an exception. So i'm left with:

if (ColumnExists(reader, "Lastname"))
{
   itIsNull = reader.IsDBNull(reader.GetOrdinal("Lastname"));
}
else
   itIsNull = false;

Note: Not related to my question but, the real reason i want to know if a column exists is because there will be times where the column will not be present in the results set, and i don't want to throw an exception processing database results, since it's not exceptional.

possible duplicate of http://stackoverflow.com/questions/373230/check-for-column-name-in-a-sqldatareader-object — hatchet - done with SOverflow, Nov 22 '11 at 21:10
@hatchet That's certainly not a technique i'm going to call on every row of the data set — Ian Boyd, Nov 22 '11 at 21:15
@TomasVoracek i'm trying to **avoid** exceptions, and here you are throwing more! — Ian Boyd, Nov 22 '11 at 21:16
If you read the other answers in the stackoverflow question linked above, you'll see that GetSchemaTable may contain columns not present in the actual datareader. — hatchet - done with SOverflow, Nov 22 '11 at 21:37
@hatchet If you read MSDN there is important info: "To make sure that metadata columns return the correct information, you must call ExecuteReader with the behavior parameter set to KeyInfo. Otherwise, some of the columns in the schema table may return default, null, or incorrect data." — Tomas Voracek, Nov 22 '11 at 21:50

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

There is a limit to what you can do since the IDataReader doesn't expose much that helps. Using the loop as shown in the answer to a similar question

Check for column name in a SqlDataReader object

You could, with the first row you process, build a simple dictionary that is keyed by column name with ordinals as values (or a HashSet if you don't care about the ordinal values). Then you can just use columnDictionary.ContainsKey("LastName") as your test. You would only build the dictionary once, for the first row encountered, then all the subsequent rows would be fast.

But to be honest, compared with database time, the time consumed by using as-is the solution in that other stackoverflow qeustion would probably be negligible.

Edit: additional possibilities here: Checking to see if a column exists in a data reader

edited May 23 '17 at 12:21

Community

1
1

answered Nov 22 '11 at 21:30

hatchet - done with SOverflow

15,919
5
47
57

Problem is that the code is executed once for each row in the `IDataReader`. With the possibility of four missing columns each iteration, and 40,000 iterations, thats 80,000 exceptions (rather than zero). The answer seems to be "You cannot do that". i'll twist *"There is a limit..."* into *"Cannot be done"*, and call it accepted. +1 for some workarounds that might help other people in other situations, but my question has been answered. – Ian Boyd Nov 23 '11 at 14:33
Using the method in the top answer of the question I linked to would avoid all exceptions. Yes, the loop would get a lot of executions, but the total cost may not be much, and it would be cleaner and look nicer than trapping exceptions. – hatchet - done with SOverflow Nov 23 '11 at 15:38

How do i get the ordinal of a column in a DataReader

1 Answers1