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.