2013年8月2日 星期五

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