Oracle and data types

Today I was working on a factory class for database abstraction since the project I'm working on needs to support both SQL Server and Oracle. I had previously prototyped with Microsoft's .NET provider for Oracle but have switched to Oracle's ODP.NET data provider. When running my unit tests on the converted code I hit an error when creating an OracleParameter:

new OracleParameter(parameterName, parameterType)

The parameterName was a string and the parameterType was System.Data.DbType. Unfortunately, Oracle decided to create their own dbType enumeration (OracleDbType) so the above code no longer works (Grrrr!!!). I was intially frustrated and the thought of having to map the System.Data.DbType parameter to an OracelDbType parameter but there was an easier way: The OracleParameter class exposes a DbType property that accepts a System.Data.DbType. So I changed the code to:

OracleParameter p = new OracleParameter();
p.ParameterName = parameterName;
p.DbType = parameterType;

Now I don't need to do the mapping!

2 Comments

  • Can you tell me why you had to switched to ODP? What was not working when you used MS provider for Oracle? What are pros and cons?

    (our company use Oracle and developers use .NET which brings the same questions all over again, do we have to use MS data provider or Oracle's)



    Thanks,

    Alex.

  • Alex,



    Sorry for the delay in responding. To be honest, I can't recall why we switched. One of the developers I work with is an Oracle guru and he made some comments about using the Oracle one vs. the Microsoft one. I think there were some capabilities only available in the Oracle provider.

Comments have been disabled for this content.