ORM style mapping of Oracle Stored Procedures with .Net

I am looking for some sort of tool to help with calling Oracle stored procedures from .Net code. We have a large legacy database that is frankly a bit messy (no id fields, large composite keys and duplicate data). Currently, we have to do do all data 开发者_JAVA技巧access via stored procedures through a custom library that is old and buggy and I would like to replace it.

I have some experience with ORM tools like nHibernate but after playing around with it a bit in our environment it doesn't really seem to be the best option for working with a legacy database like this.

Does anyone know of a good tool that will allow stored procedures to be called easily and map the results into sets/collections of objects? A nice bonus would be the ability to handle connections transactions as well.

Thanks

---

The new Oracle beta Entity Framework driver lets you do that. You can map SPs into the model and either to entities (if they return the equivalent of a table) or create a "complex type" which is a class built around what the SP returns.

I don't know how many SPs your calling, but for the ones I've tried it's worked out.

Another option is to write your own library that just calls the procedures and returns the results as .net classes, but that will require a lot of setup work on your part in terms of repetitive code (mapping parameters to procedures in Oracle gets tedious real fast).

edit - Here's a config file entry to use a stored procedure where the results are coming from a cursor that's an OUT parameter.

  <oracle.dataaccess.client>
    <settings>
      <add name="ENVMSTR.P_ORG_UNIT_R_BY_STAFF.RefCursor.RESULT_CURSOR_P" value="implicitRefCursor bindinfo='mode=Output'" />
    </settings>
  </oracle.dataaccess.client>

edit 2 - And the stored procedure in question:

create or replace
PROCEDURE                 P_ORG_UNIT_R_BY_STAFF 
(
  STAFF_ID_P IN NUMBER
, RESULT_CURSOR_P OUT SYS_REFCURSOR  
) AS 
BEGIN
  OPEN RESULT_CURSOR_P FOR
    select *
      from dept_organizational_unit
      start with deptorgunit_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
      connect by prior deptorgunit_parent_cd = deptorgunit_cd;
END P_ORG_UNIT_R_BY_STAFF;

---

IF a commercial library is an option we are really happy with Devart (see http://www.devart.com/dotconnect/oracle/features.html)... they support LINQ and PLINQ and EF and Stored Procedure, REF Cursors etc. - from Oracle 7.3 till 11g / .NET 2 and up / 32 + 64 Bit...

not affiliated, just a happy customer...

---

If you just want SPROC handling with parametrization and materialization (data into objects), dapper-dot-net is simple, minimalist, and should work fine on Oracle; for example:

var user = cnn.Query<User>("spGetUser", new {Id = 1}, 
    commandType: CommandType.StoredProcedure).First();

points:

• spGetUser is the name of the sproc
• it is invoked as a sproc via commandType
• the parameters are deduced from the object passed in; in this case it is assumed that there is a parameter named Id that takes an integer, with the value of 1 passed in
• a direct column-to-property map is applied, constructing a User object for each row returned
• in this case we're also using LINQ-to-Objects to illustrate reading 1 row simply

note that mapping multiple data grids and horizontal partitioning (into different objects in a related graph) are also supported.

---

For those of you trying to get Entity Framework function imports working with Oracle, here is a walkthrough you can use: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm

There is also a magazine article that I wrote that includes a walkthough: http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

Note: At the time of this posting, these walkthoughs do not work due to some changes in the app.config keywords that occured in the beta3. See the readme file in the ODP.NET directory of the beta3.

The keywords that have been changed that affect the walkthoroughs can be modified as follows:

NATIVE_DATA_TYPE to NATIVEDATATYPE

and

PROVIDER_DB_TYPE to PROVIDERDBTYPE

See the readme file in the ODT directory for additional caveats regarding this. In the (future) production release (not available at the time of this posting) the Oracle Developer Tools for Visual Studio online help has a section called "Using Entity Framework". This section contains caveats about mapping to Stored Procedures and Functions. Please read this documentation.

For more information on the format of this app.config metadata, refer to ODP.NET online help in the “Implicit REF CURSOR Binding Support” section.

Note that this detailed app.config meta data is only needed if you are mapping with a complex type as the result. You won't need it if you return an entity.

Unfortunately, the process of modifying the app.config is fraught with the potential to make mistakes. Any mistake will result in the "Get Column Information" button on the Import Function wizard doing nothing. We are aware of this and are planning a configuration tool in a future release.

Christian Shay

Oracle

---

You'd better use a code generator for your data access layer. Writing it is quite easy. It could generate even CRUD PL/SQL.

Here you have a small example:

Private Sub GeneraCapaAccesoDato(ByVal tipo As Integer, ByVal modo As String)
    Dim sb As New StringBuilder()
    Dim Tabla As String = lbTabla.SelectedValue
    Dim dt As DataTable = RecuperarDatosTabla(Tabla)
    sb.Append(String.Format("public int {1}(OracleConnection con, BE{0} oBE{0})", Tabla, modo))
    sb.AppendLine("{")
    sb.AppendLine("int Resultado;")
    sb.AppendLine(String.Format("OracleCommand cmd = new OracleCommand(""Pa_{0}_{1}"", con);", Tabla, modo))
    sb.AppendLine("cmd.CommandType = CommandType.StoredProcedure;")
    sb.AppendLine("")
    Dim i As Integer
    Row = dt.Select()
    Dim NomTabla As String
    Dim Tamaño As Integer
    Dim scala As Integer
    Dim TipoDato As String = "Ninguno"
    Dim precision As Integer
    Dim aux As Object
    Dim aux1 As Object
    Dim llave As Integer
    For i = tipo To Row.Count() - 1
        llave = Int32.Parse(Row(i).Item(7))
        NomTabla = Row(i).Item(0).ToString()
        Tamaño = Integer.Parse(Row(i).Item(2).ToString())
        aux = Row(i).Item(4).ToString()
        scala = Integer.Parse(If(aux = "", 0, aux))

        aux1 = Row(i).Item(3).ToString()
        precision = Integer.Parse(If(aux1 = "", 0, aux1))

        If scala > 0 Then
            If scala >= 0 And scala <= 15 Then
                TipoDato = "OracleDbType.Double"
            End If
        ElseIf Row(i).Item(1).ToString() = "NUMBER" Then
            If precision < 2 Then
                TipoDato = "OracleDbType.Int16"
            ElseIf precision >= 2 And precision <= 9 Then
                TipoDato = "OracleDbType.Int32"
            ElseIf precision >= 10 And precision <= 18 Then
                TipoDato = "OracleDbType.Int64"
            End If
        Else
            If Row(i).Item(1).ToString() = "DATE" Then
                TipoDato = "OracleDbType.Date "
            End If
            If Row(i).Item(1).ToString() = "VARCHAR2" Then
                TipoDato = "OracleDbType.Varchar2  "
            End If
            If Row(i).Item(1).ToString() = "CHAR" Then
                TipoDato = "OracleDbType.Char "
            End If

        End If
        sb.AppendLine(String.Format("OracleParameter Par{1} = cmd.Parameters.Add(""P_{0}"",{2});", _
                                    NomTabla, If(tipo = 0, i + 1, i), TipoDato))

        If Row(i).Item(1).ToString() = "VARCHAR2" Or Row(i).Item(1).ToString() = "CHAR" Then
            sb.AppendLine(String.Format("Par{1}.Size = {0};", Tamaño, If(tipo = 0, i + 1, i)))
        End If

        sb.AppendLine(String.Format("Par{0}.Direction = ParameterDirection.Input;", If(tipo = 0, i + 1, i)))
        sb.AppendLine(String.Format("Par{0}.Value = oBE{2}.{1};", If(tipo = 0, i + 1, i), NomTabla, Tabla))
        sb.AppendLine("")
        TipoDato = ""

    Next
    sb.AppendLine("Resultado = cmd.ExecuteNonQuery();")
    sb.AppendLine("return Resultado;")
    sb.AppendLine("}")
    rtbVisor.Text = sb.ToString()
End Sub
Private Function RecuperarDatosTabla(ByVal NombreTabla As String) As DataTable
    sb = New StringBuilder
    sb.Append(" select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT ,column_id")
    sb.Append(" from(USER_TAB_COLUMNS)")
    sb.Append(String.Format(" where TABLE_NAME = '{0}' order by table_name,column_id ", NombreTabla))
    Using con As New OracleConnection(strConexion)
        con.Open()
        dt = New DataTable
        Dim da As OracleDataAdapter = New OracleDataAdapter(sb.ToString(), con)
        da.Fill(dt)
    End Using
    Return dt
End Function