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

2013年7月19日 星期五

Static Constructor vs Constructor 靜態建構式

參考:MSDN 靜態建構式
靜態建構函式可以用來初始化任何靜態資料,或執行只需執行一次的特定動作。
在建立第一個執行個體或參考任何靜態成員之前,會自動呼叫靜態建構函式。

C# Code
class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("了解Static Constructor 與Constructor");
            ForTestConstractor x = new ForTestConstractor();
            Console.ReadLine();
        }
     }

class ForTestConstractor
    {
         //static Constrator
        static ForTestConstractor()
        {
            Console.WriteLine("static Constructor");
        }
        public ForTestConstractor()
        {
            Console.WriteLine("normal Constructor");
        }
    }
執行結果:

了解Static Constructor 與Constructor
static Constructor
normal Constructor

2013年6月6日 星期四

MVC 4 AjaxBeginForm 用多重按鈕

參考文章:Handling multiple submit buttons on the same form - MVC Razor

一般使用Ajax.BeginForm會搭配Submit button觸發,
要使用多重按鈕
1.必須要指定所有按鈕都有相同的name屬性(name="btn")
2.每個按鈕賦予不同的值(value = "Create" or value = "Update" )
3.在Controller中,除了model的參數外(ModelDto XDto),
還要加上從View傳到Control 的值,參數的命名必須跟name屬性相同(string btn )
4.利用btn當作條件做判斷

--View-----------------------------------------------------------------------------------------------------
@using (Ajax.BeginForm("Create", "DIM", new AjaxOptions { HttpMethod = "POST", Confirm = "確定要變更?", UpdateTargetId = "ajaxBody" }))
{
    @Html.EditorFor(model => model.id)
    

}
--Controller------------------------------------------------------------------------------------------------
// POST: /DIM/Create
[HttpPost]
public ActionResult Create(ModelDto XDto, string btn)
{        
    if (ModelState.IsValid)
    {
        if (btn == "Create")
        {
            db.ModelDto .Add(XDto);
            db.SaveChanges();
        }
        else
        {
            db.Entry(XDto).State = EntityState.Modified;
            db.SaveChanges();
        }
    }
    return RedirectToAction("List");
}

2013年5月20日 星期一

C# .NET 修改系統時間

依賴系統時間執行不同方法的程式,
測試時可能就需要改變系統時間來確定程式是否有問題。

2013年5月14日 星期二

Oracle UT 自製Assert

參考Oracle PL/SQL 5th

Oracle沒有提供UT的元件,
所以必須要自己撰寫類似Assert的檢核機制。
ex.
PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN VARCHAR2, actual_value  IN VARCHAR2) --驗證用
IS
BEGIN
    DBMS_OUTPUT.PUT(description || ': ');
    IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
        THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
        ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
    END IF;
END;
PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN NUMBER, actual_value IN NUMBER) --驗證用
IS
BEGIN
    DBMS_OUTPUT.PUT(description || ': ');
    IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
        THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
        ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
    END IF;
END;
-------------------------------
<!--more>
所以UT的撰寫時,可以獨立的把上述兩個Procedure撰寫在oracle中,
或者可以直接寫在Declare裡。
ex.
Declare
    A number;--預計資料筆數
    B number;--實際資料筆數(從table撈)
    PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN VARCHAR2, actual_value  IN VARCHAR2) --驗證用
    IS
    BEGIN
        DBMS_OUTPUT.PUT(description || ': ');
        IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
            THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
            ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
        END IF;
    END;
    PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN NUMBER, actual_value IN NUMBER) --驗證用
    IS
    BEGIN
        DBMS_OUTPUT.PUT(description || ': ');
        IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
            THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
            ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
        END IF;
    END;

BEGIN
       A : =10;(預計10筆)
       SELECT Count(*) INTO B FROM table;
       ASSERT('筆數檢核:',A,B) ;
END;
------------------------------------------------------------------------------------------------
結果(DBMS_OUTPUT):
通過→
筆數檢核:PASSED
失敗→
筆數檢核:FAILED. Expected : 10 ;got 9

2013年5月7日 星期二

2013年5月3日 星期五