Enterprise Library, OracleTypes and their mapping

· August 27, 2008

Tools   .NET   Life of a consultant

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