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