views:

219

answers:

2

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.

+1  A: 

best bet to to write a wrapper function at the application level that contains specific code to interpret each DB and then return a common format

KM
I thought that the model was supposed to stop you having to do that? Can I be sure that using an Odbc connection that this new format will be consistent regardless of the driver that the odbc connection is set up to use? if not then what happens if they choose some odbc driver that I have not written the specific code for?
Sam Holder
and web standards were to make html work the same everywhere in all browsers... make a block of code for sql server, one for oracle, one for mysql, one for sqllite, one for postgress, etc. Each DB may output be very similar, but I don't think they all will be 100% exactly the same
KM
+1  A: 

You shouldn't require a user to pick a provider. Let the user pick the database and get the application to work out which provider to use.

If you use an OLE DB provider, can you use the OleDbType Enumeration?

There is a built-in .NET and native OLE DB provider for Oracle.

See also Data Type Mappings in ADO.NET

Mitch Wheat