ODP.NET User Defined Type Implementation

Introduction

ODP.NET supports Oracle User Defined Type (UDT). It can use custom type to represent Oracle UDT. This is useful in passing complex entity parameter to Oracle stored procedure. Instead of passing many parameters, one .NET custom object parameter is passed. This technique makes code cleaner and easier to maintain. This article presents a sample code walkthrough to help undestand how ODP.NET UDT can be implemented.

Using the code

Step 1: Create a sample table in Oracle.
 

create table articles
  ( article_id number(6) not null,
    article_name varchar(50) not null,
    update_date date,
    constraint articles_pk primary key (article_id)
  );

Step 2: Create an object type at schema level. Create a function to return a UDT parameter and a procedure with a UDT parameter.

create or replace type articles_typ as object (
  article_id number(6),
  article_name varchar(50),
  update_date date
);

create or replace function get_article(i_article_id number)
  return articles_typ
is
  l_article articles_typ;
begin
  l_article := new articles_typ(null, null, null);
  select article_id, article_name, update_date
  into l_article.article_id, l_article.article_name, l_article.update_date
  from articles where article_id = i_article_id;
  return l_article;
end;

create or replace procedure add_article(i_article in articles_typ)
is
begin
  if i_article is null then
    raise_application_error(-20000, 'Parameter i_article can not be null!');
  end if;
  insert into articles (article_id, article_name, update_date)
  values (i_article.article_id, i_article.article_name, i_article.update_date);
end;

Step 3: Create a nested table type at schema level. Create a function to return a collection parameter and a procedure with a collection parameter.

create or replace type articles_list_typ as table of articles_typ;

create or replace procedure add_articles(i_articles_list in articles_list_typ)
is
begin
  if i_articles_list is null then
    raise_application_error(-20000, 'Parameter i_articles_list can not be null!');
  end if;
  for i in i_articles_list.first .. i_articles_list.last
  loop
    insert into articles (article_id, article_name, update_date)
    values (i_articles_list(i).article_id, i_articles_list(i).article_name, i_articles_list(i).update_date);
  end loop;
end;

create or replace function get_all_articles
  return articles_list_typ
is
  l_articles_list articles_list_typ;
  cursor c is
    select * from articles;
begin
  l_articles_list := new articles_list_typ();
  for article_rec in c
  loop
    l_articles_list.extend;
    l_articles_list(l_articles_list.last) := new articles_typ(article_rec.article_id,
      article_rec.article_name, article_rec.update_date);
  end loop;
  return l_articles_list;
end;

Step 4: Create a Visual Studio C# project. Create a custom class mapping to an Oracle user defined type. The custom class must implement IOracleCustomType and INullable interfaces. Create a custom type factory class for the creation of the custom instance. The custom type factory class must implement IOracleCustomTypeFactory class. Here is the code listing. The code comments provide all the implementation details.

// A custom class mapping to an Oracle user defined type.
// Provided all required implementations by ODP.NET developer guide to represent the Oracle UDT as custom type.
// The custom class must implement IOracleCustomType and INullable interfaces.
// Note: Any Oracle UDT name must be uppercase.
public class Article : IOracleCustomType, INullable
{
    // A private member indicating whether this object is null.
    private bool ObjectIsNull;

    // The OracleObjectMapping attribute is required to map .NET custom type member to Oracle object attribute.
    [OracleObjectMapping("ARTICLE_ID")]
    public int ArticleId { get; set; }
    [OracleObjectMapping("ARTICLE_NAME")]
    public string ArticleName { get; set; }
    [OracleObjectMapping("UPDATE_DATE")]
    public DateTime UpdateDate { get; set; }

    // Implementation of interface IOracleCustomType method FromCustomObject.
    // Set Oracle object attribute values from .NET custom type object.
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "ARTICLE_ID", ArticleId);
        OracleUdt.SetValue(con, pUdt, "ARTICLE_NAME", ArticleName);
        OracleUdt.SetValue(con, pUdt, "UPDATE_DATE", UpdateDate);
    }

    // Implementation of interface IOracleCustomType method ToCustomObject.
    // Set .NET custom type object members from Oracle object attributes.
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        ArticleId = (int)OracleUdt.GetValue(con, pUdt, "ARTICLE_ID");
        ArticleName = (string)OracleUdt.GetValue(con, pUdt, "ARTICLE_NAME");
        UpdateDate = (DateTime)OracleUdt.GetValue(con, pUdt, "UPDATE_DATE");
    }

    // A property of interface INullable. Indicate whether the custom type object is null.
    public bool IsNull
    {
        get { return ObjectIsNull; }
    }

    // Static null property is required to return a null UDT.
    public static Article Null
    {
        get
        {
            Article obj = new Article();
            obj.ObjectIsNull = true;
            return obj;
        }
    }
}

// A custom type factory class is required to create an instance of a custom type representing an Oracle object type.
// The custom type factory class must implement IOralceCustomTypeFactory class.
// The OracleCustomTypeMapping attribute is required to indicate the Oracle UDT for this factory class.
[OracleCustomTypeMapping("YOUR_SCHEMA_NAME.ARTICLES_TYP")]
public class ArticleFactory : IOracleCustomTypeFactory
{
    // Implementation of interface IOracleCustomTypeFactory method CreateObject.
    // Return a new .NET custom type object representing an Oracle UDT object.
    public IOracleCustomType CreateObject()
    {
        return new Article();
    }
}

Step 5: Create a custom class mapping to an Oracle collection type. The custom class must implement IOracleCustomType and INullable interfaces. Create a custom type factory class for the creation of the collection instance. The custom type factory class must implement IOracleCustomTypeFactory and IOracleArrayTypeFactory. Here is the code listing. The code comments provide all the implementation details.

// A custom class mapping to an Oracle collection type.
public class ArticleList : IOracleCustomType, INullable
{
    // The OracleArrayMapping attribute is required to map .NET class member to Oracle collection type.
    [OracleArrayMapping()]
    public Article[] objArticles;

    // A private member indicating whether this object is null.
    private bool ObjectIsNull;

    // Implementation of interface IOracleCustomType method FromCustomObject.
    // Set Oracle collection value from .NET custom type member with OracleArrayMapping attribute.
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, objArticles);
    }

    // Implementation of interface IOracleCustomType method ToCustomObject.
    // Set .NET custom type member with OracleArrayMapping attribute from Oracle collection value.
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        objArticles = (Article[])OracleUdt.GetValue(con, pUdt, 0);
    }

    // A property of interface INullable. Indicate whether the custom type object is null.
    public bool IsNull
    {
        get { return ObjectIsNull; }
    }

    // Static null property is required to return a null UDT.
    public static ArticleList Null
    {
        get
        {
            ArticleList obj = new ArticleList();
            obj.ObjectIsNull = true;
            return obj;
        }
    }
}

// A custom type factory class is required to crate an instance of a custom type representing an Oracle collection type.
// The custom type factory class must implement IOralceCustomTypeFactory and IOracleArrayTypeFactory class.
// The OracleCustomTypeMapping attribute is required to indicate the Oracle UDT for this factory class.
[OracleCustomTypeMapping("YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP")]
public class ArticleListFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    // Implementation of interface IOracleCustomTypeFactory method CreateObject.
    // Return a new .NET custom type object representing an Oracle UDT collection object.
    public IOracleCustomType CreateObject()
    {
        return new ArticleList();
    }

    // Implementation of interface IOracleArrayTypeFactory method CreateArray to return a new array.
    public Array CreateArray(int numElems)
    {
        return new Article[numElems];
    }

    // Implementation of interface IOracleArrayTypeFactory method CreateStatusArray to return a new OracleUdtStatus array.
    public Array CreateStatusArray(int numElems)
    {
        return null;
    }
}

Step 6: Create a class to use these UDT classes.

public class UdtClass1
{
    private string strConStr = "DATA SOURCE=YOUR_ORALCE_DATA_SOURCE;USER ID=YOUR_USER_ID;PASSWORD=YOUR_PASSWORD";

    public bool InsertArticleRecord(Article a)
    {
        OracleConnection cn = new OracleConnection(strConStr);
        cn.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = "add_article";
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter p = new OracleParameter();
        p.OracleDbType = OracleDbType.Object;
        p.Direction = ParameterDirection.Input;
        p.Value = a;
        p.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_TYP";
        cmd.Parameters.Add(p);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cn.Close();
        cn.Dispose();
        return true;
    }

    public Article GetArticleRecord(int intArticleId)
    {
        Article a;
        OracleConnection cn = new OracleConnection(strConStr);
        cn.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = "get_article";
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter pRet = new OracleParameter();
        pRet.OracleDbType = OracleDbType.Object;
        pRet.Direction = ParameterDirection.ReturnValue;
        pRet.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_TYP";
        cmd.Parameters.Add(pRet);
        OracleParameter pIn = new OracleParameter();
        pIn.OracleDbType = OracleDbType.Int32;
        pIn.Direction = ParameterDirection.Input;
        pIn.Value = intArticleId;
        cmd.Parameters.Add(pIn);
        cmd.ExecuteNonQuery();
        a = (Article)cmd.Parameters[0].Value;
        cmd.Dispose();
        cn.Close();
        cn.Dispose();
        return a;
    }

    public bool InsertArticleRecords(ArticleList al)
    {
        OracleConnection cn = new OracleConnection(strConStr);
        cn.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = "add_articles";
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter p = new OracleParameter();
        p.OracleDbType = OracleDbType.Array;
        p.Direction = ParameterDirection.Input;
        p.Value = al;
        p.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP";
        cmd.Parameters.Add(p);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cn.Close();
        cn.Dispose();
        return true;
    }

    public ArticleList GetAllArticles()
    {
        OracleConnection cn = new OracleConnection(strConStr);
        ArticleList al;
        cn.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = "get_all_articles";
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter pRet = new OracleParameter();
        pRet.OracleDbType = OracleDbType.Array;
        pRet.Direction = ParameterDirection.ReturnValue;
        pRet.UdtTypeName = "YOUR_SCHEMA_NAME.ARTICLES_LIST_TYP";
        cmd.Parameters.Add(pRet);
        cmd.ExecuteNonQuery();
        al = (ArticleList)cmd.Parameters[0].Value;
        cmd.Dispose();
        cn.Close();
        cn.Dispose();
        return al;
    }
}

Step 7: Create a test class to test these UDT classes.

[TestClass()]
public class UdtClass1Test
{
    [TestMethod()]
    public void InsertArticleRecordTest()
    {
        UdtClass1 target = new UdtClass1();
        Article a = new Article() { ArticleId = 1, ArticleName = "article one", UpdateDate = DateTime.Today };
        bool actual;
        actual = target.InsertArticleRecord(a);
        Assert.AreEqual(true, actual);
    }

    [TestMethod()]
    public void GetArticleRecordTest()
    {
        UdtClass1 target = new UdtClass1();
        int intArticleId = 1;
        Article actual;
        actual = target.GetArticleRecord(intArticleId);
        Assert.AreEqual("article one", actual.ArticleName);
    }

    [TestMethod()]
    public void InsertArticleRecordsTest()
    {
        UdtClass1 target = new UdtClass1();
        ArticleList al = new ArticleList();
        Article[] articles = new Article[2]
            {
                new Article() { ArticleId = 2, ArticleName = "article two", UpdateDate = DateTime.Today },
                new Article() { ArticleId = 3, ArticleName = "article three", UpdateDate = DateTime.Today }
            };
        al.objArticles = articles;
        bool actual;
        actual = target.InsertArticleRecords(al);
        Assert.AreEqual(true, actual);
    }

    [TestMethod()]
    public void GetAllArticlesTest()
    {
        UdtClass1 target = new UdtClass1();
        ArticleList actual;
        actual = target.GetAllArticles();
        Assert.AreEqual(3, actual.objArticles.Length);
    }
}

Points of Interest

Using the ODP.NET user defined type feature can pass complex custom objects and collections as parameters to Oracle stored procedures.