How do I integrate ODP.NET into Repository classes?

Question

My domain model provides e.g. following object Report : IEntity<Report>. I usually retrieve Oracle user defined type object (or collection types) form stored procedures. For all types C# classes are generated by the ODP.NET Visual Studio Custom Class Wizard. So I have following class e.g.:

public class UDT_REPORT : INullable, IOracleCustomType, IXmlSerializable {
    private bool m_IsNull;
    private decimal m_REPORT_ID;
    private decimal m_VALUE;                
    // etc
}

Currently I'm trying to create a new data access layer for a C# application. I would like to apply the repository pattern in this case in order to achieve loose coupling and better testability. But how to integrate these generated classes into repositories? How to design the ReportRepository class?

public interface IReportRepository
{         
    Report Find(ReportId id);
    IList<Report> FindAll();
    void Store(Report Report);
}

Should I use following approach? A static DB proxy class that e.g. exposes a generic Read<T>() method with a given delegate for data retrieval and mapping. And a factory for creating the db provider.

public static T Read<T>(string sql, Func<IDataReader, T> fetch, object[] parms = null)
{
    using (var connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;

        using (var command = factory.CreateCommand())
        {
            // Add connection; sql command text; 
                     // add parameters via some extension method   
            // Open and close connection                                                                         
            T t = default(T);
            var reader = command.ExecuteReader();
            if (reader.Read()) {
                t = fetch(reader);
            }
            return t;
        }
    }
}

And following delegate is used for fetching the related UDT object and mapping it to the domain object e.g. Report.

private static Func<IDataReader, Customer> Fetch = reader =>
{                                             
            UDT_REPORT report = reader.GetValue(reader.GetOrdinal("out_param_report"));
            Report report = Mapping.Map(reportEntity);          
            return report;
};

What do you think about this approach? Are there better approaches for integrating ODP.NET types in Repositories? Or should I avoid generated UDT classes and add some ORM frame instead?

Answer 1

When I first adopted EF, I used Interfaces & Repositories, but I found they added very little value. Here is an example of how our stack looks now.

Context:

Imports System.Configuration
Imports System.Data.Entity
Imports System.Collections.Specialized

Imports Oracle.DataAccess.Client
Imports System.Reflection

Public Class MyContext
    Inherits System.Data.Entity.DbContext

    Public Property MyTables As DbSet(Of MyTable)

    Public Sub New()
        MyBase.New(
            New OracleConnection(
                ConfigurationManager.ConnectionStrings(
                    "Entities").ConnectionString
                ),
            True
        )
    End Sub

End Class

Sample Entity:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Public Class MyTable

    <Key()>
    <Required()>
    Public Property KeyColumn As Int64

    <StringLength(50)>
    <Column("LegacyColumnName")>
    Public Property Name As String

    <StringLength(1000)>
    Public Property Description As String

    <Required()>
    Public Property IsActive As Int64

    Public Property DateCreated As DateTime?

End Class

Sample LINQ:

Public Function GetMyTables()
    Try
        Using MCTX As New MyContext()
            Return (
                From T In MCTX.MyTables
                Order By T.Name
                Select New With {
                    T.KeyColumn,
                    T.Name,
                    T.IsActive
                }).ToArray
        End Using
    Catch ex As Exception
        Return ReportException(
            "An error occurred trying to retrieve the table list", ex)
    End Try
End Function

Side-note, I'm not sure why you would want to write the procs to do the retrieval. Doesn't that kind of defeat the purpose of using EF? It does that for you.