• Download OracleUDT - 42.22 KB

Introduction

As a developer I am very much fond of OOPS and its implementation in C#. When we talk about OOPS most of us are quite comfortable to play with custom object (user defined object) and transporting them across different application layer. The real pain comes when we plan to send or retrieve custom object to/from database. More specifically this is really being a challenging task to achieve database communication through C# entity.

In my career I mostly work with Oracle and C#. As we know both of this platform are object oriented so I decided let’s put into practice the OOPS approach for DB communication.

After a long struggle and digging into various available options I found ODP.Net allows interaction to database in terms object passing.

Here in this example I referred to ODP.NET (Oracle Data Provider for .Net, Release – 11.1), Oracle 10g and Visual Studio 2008.

ODP.NET is freely available and one can download the executable from oracle site.

http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

Below I am going to discuss the implementation steps in detail.

Send custom object to Oracle store procedure:

//Person Entity – C# Custom Object
PersonBO objPersonBO 	= new PersonBO();
objPersonBO.Address 	= "Kolkata";
objPersonBO.Age 		= 20;
objPersonBO.Name 		= "Mr.Jhon";

//------ Establish the connection with Oracle-----///

//Insert the Person object into database table
OracleCommand cmd = new OracleCommand("ODP_RND_InsertPerson_Proc", objCon);
cmd.CommandType = CommandType.StoredProcedure; //Database store procedure

//Oracle Paramater
OracleParameter objParam = new OracleParameter();

//Denotes, we are going to pass a custom object
objParam.OracleDbType = OracleDbType.Object;

objParam.Direction = ParameterDirection.Input;

//Note: The UdtTypeName is case-senstive - Should be in upper case
//This is a database object and physically exists in the database as custom // type
objParam.UdtTypeName = "ODP_RND_PERSON_TYPE";

//Attach the C# custom object as input parameter
objParam.Value = objPersonBO;

//Attach parameter to command object
cmd.Parameters.Add(objParam);

//Insert the UDT into the table
cmd.ExecuteNonQuery();

From above code snippet we come across a new keyword “UdtTypeName” which refers to oracle user type. We will explain this later on the discussion.

Receive data as custom object from Oracle store procedure:

This requires few steps to fetch the data from database.

//SQL statement
string strSql = "SELECT c.contact FROM odp_rnd_person_table c"

//------ Establish the connection with Oracle-----///


//Pass the SQL statement
OracleCommand objCmd 		= new OracleCommand(strSql, objCon);
objCmd.CommandType 		= CommandType.Text;

//Isue the statement
OracleDataReader objReader 	= objCmd.ExecuteReader();

//Fetch each row
while (objReader.Read())
{
      //Custom object
      PersonBO objPersonBO 	= new PersonBO();

       //Fetch the objects as a custom type
      objPersonBO 		= (PersonBO)objReader.GetValue(0);
}

We are done with data exchange between C# and oracle which requires nominal steps to be performed. More interesting part we are going to discuss is the custom object creation.

Namespace are required

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Xml.Serialization;
using System.Xml.Schema;

Create the custom class and make it derives from IOracleCustomType. IOracleCustomType is an interface for conversion between C# Custom Type and an Oracle Object Type.

public class PersonBO :  IOracleCustomType

Create following public property underneath PersonBO and make it decorated by OracleObjectMappingAttribute.The OracleObjectMappingAttribute need to be specified on each members of custom type that represent the Oracle object type. This attribute must specify the name or zero-based index of the attribute in the Oracle object that the custom class property maps to. This also allows the custom type to declare field or property names which differ from the Oracle Object type.

[OracleObjectMappingAttribute("PNAME")]
 public virtual string Name{get;set;}

[OracleObjectMappingAttribute("ADDRESS")]
 public virtual string Address{get;set;}

[OracleObjectMappingAttribute("AGE")]
 public virtual decimal Age { get; set; }

Create following method FromCustomObject underneath PersonBO and Overrides it. This interface method creates an Oracle Object by setting the attribute respectively on the specified Oracle UDT.

public virtual void FromCustomObject(OracleConnection objCon, IntPtr objUdt)
{
    //The FromCustomObject method is used to build an Oracle Object or   
    //Collection from a custom object by 
    //setting attribute or element values respectively through the 
    //OracleUdt.SetValue method.

    OracleUdt.SetValue(objCon, objUdt, "PNAME", this.Name);
    OracleUdt.SetValue(objCon, objUdt, "ADDRESS", this.Address);
    if (this.Age > 0) OracleUdt.SetValue(objCon, objUdt, "AGE", this.Age);
 }

Create following method ToCustomObject underneath PersonBO and Overrides it. It provides the Oracle Object with the attribute values to set on the custom type. This interface method initializes a custom object using the specified Oracle UDT.

public virtual void ToCustomObject(OracleConnection objCon, IntPtr objUdt)
{
   //The ToCustomObject method is used to initialize a custom object from the 
   //specified Oracle 
   //Object or Collection by retrieving attribute or element values 
   //respectively through the OracleUdt.GetValue method.
    
    this.Name    = ((string)(OracleUdt.GetValue(objCon, objUdt, "PNAME")));
    this.Address = ((string)(OracleUdt.GetValue(objCon, objUdt, "ADDRESS")));

    bool AgeIsNull = OracleUdt.IsDBNull(objCon, objUdt, "AGE");
    if ((AgeIsNull == false)) this.Age = 
		((decimal)(OracleUdt.GetValue(objCon, objUdt, "AGE")));
}

Prepare the Database Object

CREATE TABLE ODP_RND_PERSON_TABLE
(
  CONTACT  ODP_RND_PERSON_TYPE
)

The field CONTACT in the above script is type of ODP_RND_PERSON_TYPE which is a oracle user define type.

CREATE OR REPLACE type ODP_RND_Person_Type as object 
 (
     pname varchar2(30), 
     address varchar2(60), 
     age number(3)
) NOT FINAL

Here we must remember the structure of C# and oracle user define type should be identically same.

Here is the DB store procedure for inserting data into table.This proceduere accept above type as input parameter.This type encapsulates the actual value passed from UI level.

Within the procedure If we want to access the value of individual property we can do it in the following way.

Person.pname, person.address etc.

CREATE OR REPLACE procedure 
 ODP_RND_InsertPerson_Proc(person IN ODP_RND_Person_Type) as
 begin
    Insert into ODP_RND_Person_Table values (person); 
 end

!! Enjoy coding !!