I want to allow the user to define a query which will be used to fetch some information. The database the user connects to is also user definable. Basically the steps that the user needs to perform are:
1/ Select the data provider (this is one of the providers returned by DbProviderFactories.GetFactoryClasses ())
2/ Configure the selected provider by filling in a property grid which is bound to the DbConnectionStringBuilder returned by the chosen DbProviderFactory.
3/ Select a table that the query will operate on. The list of tables comes from the DbConnection.GetSchema("Tables") (with the DbConnection coming from chosen DbProviderFactory)
4/ Select the columns (2 columns are needed) that contains the data needed for the query. One of these columns must be either text or an integer and the other must be numeric.
I have been able to successfully do the first 3 steps, but have run into a problem on step 4. Originally I was connected to an sql server database and got everything working. Now that I have tried it with an oracle XE database I seem to have a problem.
Using an SQLServer2005 db when I query the DATA_TYPES from the schema I get information like:
Type name ProviderDbType DataType
nchar 10 System.String
int 8 System.Int32
and when I then query the schema for the columns in my table I get:
Column_Name Data_Type
Id nchar
Length int
So when trying to map the column type (via the Data_type column value) to a system type I keyed against Type_name from the DATA_TYPES Schema table. This was all fine.
Then I started using oracle (via the Odbc. Whilst the DATA_TYPES info seems to be the same what I get back from the columns query is different. What I get is this:
Column_Name Data_Type Type_Name
Id 8 VARCHAR2
Length 10 int
so my previous strategy of keying on Type_Name from the DATA_TYPES schema table and looking up the value of the Data_Type to get the system type doesn't work because it stores the system type with the key nchar and then tries to look it up with the key 8.
looking through the various things available from the schema I can't see how I'm meant to know which column in the columns query maps to which column in the DATA_TYPES schema table.
Apologies for the long question, hope the issue is clear.