General way to determine if a column exists in ADO.NET
I am using environment 2.0 and I am looking for a general way to determine if a column exists in a table. I want to use this code with multiple database types and vendors.
The GetSchema method will return schema information, but the format of the information and data appears to be provider specific to limit the information returned.
Other solutions I've seen seem to boil down to Select * from a table and then go through the results to see if the column exists. This will work, but it seems crazy to throw out a selection for the entire table to see if the column exists.
a source to share
Two options that I can immediately think of:
It would be nice to use the INFORMATION_SCHEMA views, which are part of the sql standard, but not all database systems implement them. But if the set of databases you care about actually implements it, this is your best bet.
Another option is to take your query, but add a WHERE 1 = 0 clause to it so that it doesn't return rows. ADO.NET will still return the schema in this case
EDIT: Actually the second method will give you the presence of the columns and their data types. However, I am not sure if you will get complete information about the schema, such as maximum length, NULLable, etc. The INFORMATION_SCHEMA views are actually the best, but ORACLE does not implement them.
I ran into this:
http://database-geek.com/2009/04/30/oracle-information_schema/
which is an open source attempt at mocking INFORMATION_SCHEMA views in Oracle. I don't know how complete or functional this effort is at the moment.
a source to share
Instead of selecting * from the table, you can do:
select * from table
where true=false
This will allow ADO to see the column names without returning any data. There might be a more general general way of querying system tables through database vendors, but I am not aware of this.
a source to share