How do I map from Oracle UDTs with ODP.NET without using OracleObjectMappingAttribute?

Question

The data access layer in our application is going to be using the UDT functionality of Oracle. We will only be passing UDT objects to and from the database.

At the moment, we generate the custom classes using the function provided with ODP.NET (which creates a truly horrible looking class that we really don't want in our codebase).

We then use a separate mapping class, to map the custom class to one of our business objects (and back when saving).

I am trying to find a better way of doing this.

I thought I would just do-away with the generated classes and just write a mapping class that implemented IOracleCustomType. The From/ToCustomObject methods would then map from my UDT to my business objects. However, this caused me problems when I tried it - I got the error "Object attribute is not mapped to a custom type member". It appears that as well the two methods, I also need attributes in my mapping class - one attribute for each item in the UDT.

For example - a workflow UDT contains three items - a status, created time and created by. My UDT is nice and simple:

TYPE workflow_type AS OBJECT
(status                                  VARCHAR2(8)
,created_by                              VARCHAR2(30)
,created_datetime            DATE
);

As is the business object I want it to end up in:

public class Workflow
{
    /// <summary>
    /// Gets the status of the workflow.
    /// </summary>
    /// <value>The status.</value>
    public string Status { get; private set; }

    /// <summary>
    /// Gets the Windows Logon Id of the user performing the action
    /// </summary>
    public string CreatedBy{ get; private set; }
    /// <summary>
    /// Gets the time of the action 
    /// </summary>
    public DateTime CreatedTime { get; private set; }
}

I want to get from one to the other without having to add Oracle code to the business object.

So my thought was to create a mapping class like this:

public class WorkFlowMapper : IOracleCustomType
{
    public BusinessObjects.WorkFlow BusinessObject {get; private set;}

    public WorkFlowMapper(BusinessObjects.WorkFlow businessObject)
    {
        BusinessObject = businessObject;
    }

    public WorkFlowMapper(){}

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "STATUS", BusinessObject.Status);
        OracleUdt.SetValue(con, pUdt, "CREATED_BY", BusinessObject.CreatedBy);
        OracleUdt.SetValue(con, pUdt, "CREATED_DATETIME", BusinessObject.CreatedTime);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {

        BusinessObject = new BusinessObjects.WorkFlow(
            (string)OracleUdt.GetValue(con, pUdt, "STATUS"),
            (string)OracleUdt.GetValue(con, pUdt, "CREATED_BY"),
            (string)OracleUdt.GetValue(con, pUdt, "CREATED_DATETIME")
        );
    }
}

// Factory to create an object for the above class
[OracleCustomTypeMappingAttribute("MYUSER.WORKFLOW_TYPE")]
public class CurrencyExposureFactory : IOracleCustomTypeFactory
{

    public virtual IOracleCustomType CreateObject()
    {
        WorkFlowMapper obj = new WorkFlowMapper();
        return obj;
    }
}

But this doesn't work thanks to the requirement of needing OracleObjectMappingAttribute for each attribute to be mapped (as in the ODP.NET generated classes). This appears really stupid as I won't be using them at all. In fact, I can get my mapping class to work, just by adding in three lines:

    [OracleObjectMappingAttribute("STATUS")] public string a;
    [OracleObjectMappingAttribute("CREATED_BY")] public string b;
    [OracleObjectMappingAttribute("CREATED_DATETIME")] public DateTime c;

Surely there must be a better way than putting in such a horrible hack? Afterall, these variables never get used at all - ODP.NET just appears to need them for getting the type to map to - but I would have thought this could be achieved in a different way. Thoughts?

Answer 1

What is so bad about those extra attributes? There is already a ginormous amount of attributes in the .net classes and frameworks (for instance WCF). Disliking attributes is almost the same as disliking .NET .

Anyway you can explore the possibilities of devart's Oracle provider (http://www.devart.com/dotconnect/oracle/). They have a free version too. Its dealing with udts is based on strings not on attributes.

Answer 2

Welcome to Oracle. You'll hate it here. You're right, it's absolutely ridiculous that you'd have to provide those attributes when you have to explicitly get/set the value anyway. The .NET ecosystem is so wonderfully generic (specifically in the more modern .NET Core), that you'd fully expect the framework to handle something like this. However Oracle completely missed that memo (as they often do). Most Oracle frameworks (and the entire Java language in general) has this issue. Here's my workaround:

Start with a base UDT class that implements IOracleCustomType

public abstract class BaseUDT : IOracleCustomType
    {
        private IEnumerable<PropertyInfo> GetTypeProperties() => GetType()
                .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(p => p.GetCustomAttribute<OracleObjectMappingAttribute>() != null);

        public virtual void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            foreach (var prop in GetTypeProperties())
            {
                var attr = prop.GetCustomAttribute<OracleObjectMappingAttribute>();
                prop.SetValue(this, OracleUdt.GetValue(con, pUdt, attr.AttributeName));
            }
        }

        public virtual void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            foreach (var prop in GetTypeProperties())
            {
                var attr = prop.GetCustomAttribute<OracleObjectMappingAttribute>();
                OracleUdt.SetValue(con, pUdt, attr.AttributeName, prop.GetValue(this));
            }
        }
    }

The above class will look for all public instance properties that have the OracleObjectMappingAttribute attribute applied, and then dynamically get/set there value. Your UDT then becomes just

public class Workflow : BaseUDT
{
    /// <summary>
    /// Gets the status of the workflow.
    /// </summary>
    /// <value>The status.</value>
    [OracleObjectMapping("STATUS")]
    public string Status { get; private set; }

    /// <summary>
    /// Gets the Windows Logon Id of the user performing the action
    /// </summary>
    [OracleObjectMapping("CREATED_BY")]
    public string CreatedBy{ get; private set; }
    /// <summary>
    /// Gets the time of the action 
    /// </summary>
    [OracleObjectMapping("CREATED_DATETIME")]
    public DateTime CreatedTime { get; private set; }
}

I LOVE attributes, as it makes it possible to leverage Reflection in order to cut down on repetitive code. With this approach, the BaseUDT class will iterate all properties and set them accordingly. I understand you hesitation to add business logic to your models, but this is unavoidable in a lot of scenarios.