Generic way to determine if a column exists in ADO.NET

Question

I'm using the 2.0 framework and I'm looking for a generic way to determine if a column exists in a table. I want to use this code with multiple database types and providers.

The GetSchema method will return schema information but the format of the information and the data to restrict the information returned both seem to be specific to the provider.

The other solutions I've seen seem to boil down to Select * from table and then search the results to see if the column exists. This will work but it seems crazy to issue a select for the whole table to see if a column exists.

Answer 1

Two options I can think of right away:

It would be nice to use the INFORMATION_SCHEMA views that are part of the sql standard, but not all Database Systems implement them. But if the set of databases you care about do implement it, that's your best choice.

Another option is to take your query, but add a "WHERE 1 = 0" clause on it so it doesn't return any rows. ADO.NET will still return the schema in that case

EDIT: Actually, the second method will give you the existence of columns and their data types. However, I'm not sure you're going to get full schema information like maximum length, NULLable, etc. The INFORMATION_SCHEMA views really are the best bet, but ORACLE does not implement them.

I did come across this:

http://database-geek.com/2009/04/30/oracle-information_schema/

which is an open source effort to mock up the INFORMATION_SCHEMA views in Oracle. I have no idea how complete or functional this effort is at this point.

Answer 2

Instead of select * from the table you could do:

select * from table
where true=false

This will let ADO see the names of the columns without returning any of the data. There might be a better more generic way to query system tables accross DB providers but I don't know of it.