Enterprise Library, OracleTypes and their mapping

· August 27, 2008

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 data types (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 CreateOracleSPCommand(ByVal spName As String) As OracleCommand
        Return DirectCast(m_gbpDB.GetStoredProcCommand(spName), 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 name As String, ByVal typ As OracleType) As OracleParameter
          Dim parameter As New OracleParameter(name, typ)
          parameter.Direction = ParameterDirection.Output
          Return parameter
      End Function
    
      Private Function CreateOracleInParameter(ByVal name As String, ByVal typ As OracleType, ByVal varde As Object) As OracleParameter
          Dim parameter As New OracleParameter(name, 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_UPPDRAGS_NR", OracleType.VarChar, uppdrag.FaktureringsUppdragsID.ToString()))
      cmd.Parameters.Add(CreateOracleOutParameter(paramUtName, 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.

Twitter, Facebook