2013年4月3日 星期三

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;