Oracle UDTs _Object with Collection

C#
--------Object with Collection---------

class LeagueDto : IOracleCustomType
{
    [OracleObjectMappingAttribute("NAME")]
    public virtual string Name { get; set; }
    [OracleObjectMappingAttribute("MEMBERS")]
    public virtual TeamWithPlayer Members { get; set; }

    public virtual void FromCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "NAME", this.Name);
        OracleUdt.SetValue(con, pUdt, "MEMBERS", this.Members);
    }
    public virtual void ToCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        this.Name = ((string)(OracleUdt.GetValue(con, pUdt, "NAME")));
        this.Members = (TeamWithPlayer)OracleUdt.GetValue(con, pUdt, "MEMBERS");
    }
    [OracleCustomTypeMapping("LEAGUE")]
    public class LeagueFactory : IOracleCustomTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            return new LeagueDto();
        }
    }
}

--------collection---------

class TeamWithPlayer : IOracleCustomType
{
    //Dto是自己的DTO
    public PlayerDto[] Value;
    public virtual void FromCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, this.Value);
    }
    public virtual void ToCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        Value = (PlayerDto[])OracleUdt.GetValue(con, pUdt, 0);
    }
    [OracleCustomTypeMapping("TEAMWITHPLAYER")]
    public class TeamWithPlayerFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            return new TeamWithPlayer();
        }
        public virtual Array CreateArray(int length)
        {
            return new PlayerDto[length];
        }
        public virtual Array CreateStatusArray(int length)
        {
            return null;
        }
    }
}

--------DTO---------

class PlayerDto : IOracleCustomType
{
    [OracleObjectMappingAttribute("NAME")]
    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, "NAME", this.Name);
        OracleUdt.SetValue(objCon, objUdt, "ADDRESS", this.Address);
        OracleUdt.SetValue(objCon, objUdt, "AGE", this.Age);
    }
    public virtual void ToCustomObject(OracleConnection objCon, IntPtr objUdt)
    {
        this.Name = ((string)(OracleUdt.GetValue(objCon, objUdt, "NAME")));
        this.Address = ((string)(OracleUdt.GetValue(objCon, objUdt, "ADDRESS")));
        this.Age = ((decimal)(OracleUdt.GetValue(objCon, objUdt, "AGE")));
    }
    [OracleCustomTypeMappingAttribute("PLAYER")]
    public class PlayerFactory : IOracleCustomTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            PlayerDto obj = new PlayerDto();
            return obj;
        }
    }
}

-------Main---------

static void Main(string[] args)
{
    Console.WriteLine("Welcome to using C# Custom Object and Oracle User-defined Types:");
    Console.WriteLine("Using DTO & UDTs with procedure to change the Player's age ");
    LeagueDto objLeague = new LeagueDto();
    PlayerDto objPlayer = new PlayerDto();
    objPlayer.Name = "Mr. Syscom";
    objPlayer.Address = "台北市108萬華區峨眉街115號";
    objPlayer.Age = 20;
    PlayerDto objPlayer2 = new PlayerDto();
    objPlayer2.Name = "Mr. Syscom2";
    objPlayer2.Address = "台北市108萬華區峨眉街115號";
    objPlayer2.Age = 30;

    objLeague.Name = "Group 1";
    objLeague.Members = new TeamWithPlayer();
    objLeague.Members.Value = new PlayerDto[2] { objPlayer, objPlayer2 };

    Console.WriteLine("Player1 Age was " + objLeague.Members.Value[0].Age);
    Console.WriteLine("Player2 Age was " + objLeague.Members.Value[1].Age);
    String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;

    // Establish the connection with Oracle
    using (OracleConnection objCon = new OracleConnection(ConnectionString))
    {
        // Open the connection
        objCon.Open();
        // Insert the Person object into database table
        using (OracleCommand cmd = new OracleCommand("AlterLeague", 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 = "LEAGUE";
            // Attach custom object as input parameter
            objParam.Value = objLeague;

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

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

            objLeague = (LeagueDto)cmd.Parameters[0].Value;
        }
    }
    Console.WriteLine("After alter the age:");
    Console.WriteLine("Player1 Age is " + objLeague.Members.Value[0].Age);
    Console.WriteLine("Player2 Age is " + objLeague.Members.Value[1].Age);
    Console.ReadLine();        
}

-----Procedure---------

CREATE OR REPLACE procedure
                EEP.AlterLeague(L_LEAGUE IN OUT LEAGUE) as
begin
    L_LEAGUE.MEMBERS(1).age:=22;
    L_LEAGUE.MEMBERS(2).age:=32;
end AlterLeague;

Object with collection in Oracle

CREATE OR REPLACE TYPE EEP.LEAGUE AS OBJECT
(
    name varchar2(30),
    MEMBERS TEAMWITHPLAYER
)NOT FINAL

--------Collection in Oracle---------

CREATE OR REPLACE TYPE EEP.TEAMWITHPLAYER AS TABLE OF PLAYER

----------Object---------

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

----------Result---------

C#DTO collection & Oracle UDTs Collection

C#--- class

class SimpleTable : IOracleCustomType
{

    [OracleArrayMapping()]
   //Dto是自己的DTO
    public Dto[] Value;
    public virtual void FromCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
            OracleUdt.SetValue(con, pUdt, 0, this.Value);
    }

    public virtual void ToCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        Value = (Dto[])OracleUdt.GetValue(con, pUdt, 0);
        //this.Value = ((Dto[])(OracleUdt.GetValue(con, pUdt, 0)));
    }
    [OracleCustomTypeMapping("TESTTABLE")]
    public class SimpleTableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            return new SimpleTable();
        }

        public virtual Array CreateArray(int length)
        {
            return new Dto[length];
        }

        public virtual Array CreateStatusArray(int length)
        {
            return null;
        }
    }
}

C# ARRAY n Oracle UDTs VARRAY

參考網址：http://www.codeproject.com/Articles/33829/How-to-use-Oracle-11g-ODP-NET-UDT-in-an-Oracle-Sto

C#--- class

public class SimpleVarray : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public string[] Array;
    private bool m_bIsNull;
    public bool IsNull
    {
        get
        {
            return m_bIsNull;
        }
    }
    public static SimpleVarray Null
    {
        get
        {
            SimpleVarray obj = new SimpleVarray();
            obj.m_bIsNull = true;
            return obj;
        }
    }
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
    }
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array);
    }
}

[OracleCustomTypeMapping("EEP.TESTVARRAY")]
public class SimpleVarrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new SimpleVarray();
    }
    public Array CreateArray(int numElems)
    {
        return new string[numElems];
    }
    public Array CreateStatusArray(int numElems)
    {
        return null;
    }
}

C#----main

private static void OrderReport()
{  
    SimpleVarray test = new SimpleVarray();
    test.Array = new string[]
    {
        "A","B","C","D", "E","F","G","H",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
    };
    String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;
    using (OracleConnection conn = new OracleConnection(ConnectionString))
    {
        conn.Open();
        using (OracleCommand cmd = conn.CreateCommand())
        {
            try
            {
                cmd.CommandText = "TEST";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("V_List", OracleDbType.Array).Direction = ParameterDirection.Input;
                cmd.Parameters[0].Value = test;
                cmd.Parameters[0].UdtTypeName = "TESTVARRAY";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("1");
            Console.ReadLine();
        }
    }
}

ORACLE TYPE---

TYPE "TESTVARRAY" AS VARRAY(3000) OF VARCHAR(30)

ORACLE STORED PROCEDURE---

PROCEDURE TEST(V_List IN TESTVARRAY) AS
    BEGIN
        INSERT INTO AAAA (A, B,C, D, E, F, G, H, DATETIME) VALUES(V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), V_List(8), to_timestamp(V_List(9), 'yyyy-mm-dd hh24:mi:ss.ff3'));
    END;

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;
        }
    }
}