Enterprise Library, OracleTypes and their mapping

Posted by Marcus Hammarberg on August 27, 2008
Stats
Things has been quite hectic since we are closing in on a sprint demo... Seems to be working now, fingers crossed.

I have done a lot of findings the last days and most of them has to do with Oracle. This has to do with me not having any experience with Oracle.

We are using Enterprise Library 4 to access the Oracle database and it's stored procedures. It internal is using the ODP.NET client access components.

One problem we have encountered is that the data types used by Enterprise Library is generic (DBCommand, DBTypes etc) and it is mapped to the Oracle equivalents (OracleCommand, OracleTypes). So I needed a chart on my table showing me how to map between them.

But after a while I found a better way. Enterprise Library is exposing a OracleDatabase with the Oracle-flavor of the command types. By using that database I can use the same datatypes (NUMBER for example) as defined in the database.

To accomplish this I did some very small but useful tricks:

  • The OracleDatabase doesn't have a factory that takes the connectionstring-name as parameter. So I used the standard factory to get the connection-string and passed it as a parameter into the constructor of the OracleDatabase-class. Like so:
    New OracleDatabase(DatabaseFactory.CreateDatabase(CONNECTIONSTRING_CONFIG_GBP).ConnectionString)
  • To get a OracleCommand rather than the standard DbCommand I created a method that simple cast into the right command:
    Private Function SkapaOracleSPCommand(ByVal spNamn As String) As OracleCommand
    Return DirectCast(m_gbpDB.GetStoredProcCommand(spNamn), OracleCommand)
    End Function
  • Finally I created two methods that creates OracleParameters, one for in (with null-handling) and one for out-parameter (no value or size set):

    Private Function CreateOracleOutParameter(ByVal namn As String, ByVal typ As OracleType) As OracleParameter
    Dim parameter As New OracleParameter(namn, typ)
    parameter.Direction = ParameterDirection.Output
    Return parameter
    End Function

    Private Function CreateOracleInParameter(ByVal namn As String, ByVal typ As OracleType, ByVal varde As Object) As OracleParameter
    Dim parameter As New OracleParameter(namn, typ)
    parameter.Direction = ParameterDirection.Input

    parameter.Value = varde
    If varde Is Nothing Then
    parameter.IsNullable = True
    parameter.Value = DBNull.Value
    End If

    Return parameter
    End Function

    I then use these methods to add parameters to my command:

    cmd.Parameters.Add(CreateOracleInParameter("IN_UPPDRAGSNR", OracleType.VarChar, uppdrag.FaktureringsUppdragsID.ToString()))
    cmd.Parameters.Add(CreateOracleOutParameter(paramUtNamn, OracleType.Number))

So that's an easy way to use Oracle-types when using Enterprise Library (4) and get around the hustle and bustle to map the two back and forth.



Published by Marcus Hammarberg on Last updated