C# DTO n Oracle UDTs

參考網址：
http://www.codeproject.com/Articles/141728/Interaction-between-C-Application-and-Oracle-throu
C# DTO

class PersonDto : IOracleCustomType
    {
    [OracleObjectMappingAttribute("PNAME")]
    public virtual string Name { get; set; }
    [OracleObjectMappingAttribute("ADDRESS")]
    public virtual string Address { get; set; }
    [OracleObjectMappingAttribute("AGE")]
    public virtual decimal Age { get; set; }
    public virtual void FromCustomObject(OracleConnection objCon, IntPtr objUdt)
    {
        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);
    }
    public virtual void ToCustomObject(OracleConnection objCon, IntPtr objUdt)
    {
        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")));
    }
    [OracleCustomTypeMappingAttribute("PERSON")]
    public class PersonFactory : IOracleCustomTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            PersonDto obj = new PersonDto();
            return obj;
        }
    }
}

C# Main

PersonDto objPersonBO = new PersonDto();
objPersonBO.Address = "Kolkata";
objPersonBO.Age = 20;
objPersonBO.Name = "Mr.Jhon";
String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;

// Establish the connection with Oracle
OracleConnection objCon = new OracleConnection(ConnectionString);
objCon.Open(); // Open the connection

// Insert the Person object into database table
OracleCommand cmd = new OracleCommand("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.InputOutput;
// Note: The UdtTypeName is case-senstive - Should be in upper case
objParam.UdtTypeName = "PERSON";
// Attach 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();
PersonDto temp = (PersonDto)cmd.Parameters[0].Value;
Console.WriteLine(temp.Age);
Console.ReadLine();

Oracle Type

CREATE OR REPLACE TYPE EEP.PERSON AS OBJECT
(
     pname varchar2(30), 
     address varchar2(60), 
     age number(3)
)NOT FINAL
/

Oracle ProcedureS

CREATE OR REPLACE procedure 
         EEP.InsertPerson_Proc(V_PERSON IN OUT PERSON) as
 begin
    Insert into Person_Table values (V_PERSON); 
    V_PERSON.AGE:=1;
 end InsertPerson_Proc;
/