2013年4月11日 星期四

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#----main
private static void TEST()
{
    SimpleTable TestList = new SimpleTable();
    String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;
    using (OracleConnection conn = new OracleConnection(ConnectionString))
    {
        conn.Open();
        using (OracleCommand cmd = conn.CreateCommand())
        {
            try
            {
                //procedure寫法
                cmd.CommandText = "TEST";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("V_Start",1);
                cmd.Parameters.Add("V_Num", 10);
                cmd.Parameters.Add(new OracleParameter()
                {
                    ParameterName = "DtoList",
                    Direction = ParameterDirection.Output,
                    OracleDbType=OracleDbType.Array,
                    UdtTypeName = "TESTTABLE"
                });
                /*function寫法
                cmd.CommandText = "TEST";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter()
                {
                    ParameterName = "DtoList",
                    Direction = ParameterDirection.ReturnValue,
                    OracleDbType=OracleDbType.Array,
                    UdtTypeName = "TESTTABLE"
                });
                cmd.Parameters.Add("V_Start",1);
                cmd.Parameters.Add("V_Num", 10);
                       
                */
                cmd.ExecuteNonQuery();
                TestList = (SimpleTable)cmd.Parameters["DtoList"].Value;
                foreach (OrderDto i in TestList.Value)
                    Console.WriteLine(i.pname );
                }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }
}
ORACLE TYPE---
DTO--
CREATE OR REPLACE TYPE Dto AS OBJECT
(
    pname varchar2(30), 
    address varchar2(60), 
    age number(3)
)NOT FINAL
Collection--
CREATE OR REPLACE TYPE TESTTABLE AS TABLE OF Dto
Procedure--
PROCEDURE TEST(V_Start IN INTEGER ,V_Num IN INTEGER, DtoList OUT TESTTABLE) AS
    Temp_CUR CUR;
    Temp_Dto Dto:=Dto('1','1','1'); --初始化
BEGIN
    DtoList :=TESTTABLE(); --初始化
    DtoList.EXTEND(V_Num);--給予大小
    OPEN Temp_CUR FOR
    SELECT *  FROM pp ;
    FOR K IN 1 .. V_Num
    LOOP
        FETCH Temp_CUR INTO Temp_Dto.pname ,Temp_Dto .address ,Temp_Dto,age  ; --一定要fetch所有欄位
        EXIT WHEN Temp_CUR%NOTFOUND;
        DtoList(K):=Temp_Dto;
    END LOOP;
END;

function --
FUNCTION TEST(V_Start IN INTEGER ,V_Num IN INTEGER) RETURN TESTTABLE AS 
    Temp_CUR CUR;
    Temp_Dto Dto:=Dto('1','1','1'); --初始化
    DtoList TESTTABLE:=TESTTABLE();--初始化
BEGIN
    DtoList.EXTEND(V_Num);--給予大小
    OPEN Temp_CUR FOR
    SELECT *  FROM pp ;
    FOR K IN 1 .. V_Num
    LOOP
        FETCH Temp_CUR INTO Temp_Dto.pname ,Temp_Dto .address ,Temp_Dto,age  ; --一定要fetch所有欄位
        EXIT WHEN Temp_CUR%NOTFOUND;
        DtoList(K):=Temp_Dto;
    END LOOP;
    RETURN DtoList ;
END;