Oracle custom types Invoke examples in C #

1.c# Code:

  Public BOOLUseramountchange (dictionary<string,Long> Fee, dictionary<string,Long>Recharge) {            varf =Getrechargetypearray (fee); varR =Getrechargetypearray (recharge); using(OracleConnection conn = This. CreateConnection ())using(OracleCommand cmd =Conn. CreateCommand ()) {Conn.                Open (); Cmd.commandtext="Proc_hotstandbyquotachange"; Cmd.commandtype=CommandType.StoredProcedure; varOP =NewOracleParameter {parametername ="result", Oracledbtype = oracledbtype.int32, Direction = parameterdirection.output,value=NULL }; Cmd. Parameters.addrange (Neworacleparameter[] {NewOracleParameter {parametername="feededuction", oracledbtype=oracledbtype.array,direction=parameterdirection.input,udttypename="Hotstandby_recharge_array", value=F.toarray ()},NewOracleParameter {parametername="Recharge", Oracledbtype = Oracledbtype.array, Direction = ParameterDirection.Input, UdtTypeName ="Hotstandby_recharge_array", Value =R.toarray ()}, op});//Hotstandby_recharge_arraycmd.                ExecuteNonQuery (); intnum =Convert.ToInt32 (Op.                Value.tostring ()); returnnum = =0; }        }

2. Define the type that corresponds to the database:

 Public classHotstandby_rechargetype:ioraclecustomtype { Public voidfromcustomobject (oracleconnection con, IntPtr pudt) {Oracleudt.setvalue (con, Pudt,"USERNAME", This.            UserName); Oracleudt.setvalue (Con, Pudt,"Msgtype", This.            Msgtype); Oracleudt.setvalue (Con, Pudt,"VAL", This.        Val); }         Public voidtocustomobject (oracleconnection con, IntPtr pudt) { This. UserName = (string) Oracleudt.getvalue (Con, Pudt,"USERNAME");  This. Msgtype = (int) Oracleudt.getvalue (Con, Pudt,"Msgtype");  This. Val = (int) Oracleudt.getvalue (Con, Pudt,"VAL"); } [Oracleobjectmapping ("USERNAME")]         Public stringUserName {Get;Set; } [Oracleobjectmapping ("Msgtype")]         Public intMsgtype {Get;Set; } [Oracleobjectmapping ("VAL")]         Public intVal {Get;Set; } } [Oraclecustomtypemappingattribute ("Hotstandby_recharge")]     Public classHotstandby_rechargefactory:ioraclecustomtypefactory { PublicIoraclecustomtype CreateObject () {return NewHotstandby_rechargetype (); }} [Oraclecustomtypemapping ("Hotstandby_recharge_array")]     Public classHotstandby_rechargearrayfactory:ioraclearraytypefactory { PublicArray Createarray (intNumelems) {            return NewHotstandby_rechargefactory[numelems]; }         PublicArray Createstatusarray (intNumelems) {            return NULL; }    }

3. Database type definition and table type definition

--as-- creating type Create OR REPLACE type Hotstandby_rechargeisobject (   UserName varchar2 (),   msgtype Integer,   Val     integer)

Stored procedure called by 4.c#

Create or Replace procedure Proc_hotstandbyquotachange (feedeductioninchHotstandby_recharge_array, RechargeinchHotstandby_recharge_array, result outinteger) ascursor F_cursor is Select* fromtable (feededuction); cursor R_cursor is Select* fromtable (recharge); V_userid integer:=0; F_row f_cursor%RowType, R_row r_cursor%Rowtype;begin open f_cursor;--Open Cursor--fetch f_cursor into F_row;        Loop fetch f_cursor into F_row; --let the cursor pointer move down exit when F_cursor%NotFound; Update User_amountinfo TSett.amount=t.amount-(F_row. Val)whereT.userid= (SelectC.userid fromBase_userinfo CwhereC.username=f_row. UserName) and T.msgtype=F_row. Msgtype;--fetch f_cursor into F_row;      End Loop;   Close f_cursor;     Open r_cursor;       Loop fetch r_cursor into R_row; Exit when R_cursor%NotFound; SelectT.userid into V_userid fromUser_amountinfo TwhereT.userid = (SelectU.userid fromBase_userinfo uwhereU.username =r_row. UserName) and T.msgtype =R_row.      Msgtype; if(v_userid>0) then update User_amountinfo TSett.amount=t.amount-(R_row. Val), T.addtime=sysdate (), t.rechargeamount=t.rechargeamount-(R_row. Val)whereT.userid=v_userid and T.msgtype=R_row.        Msgtype; elsif (V_userid<=0) then inserts into User_amountinfo (Userid,amount,msgtype,addtime,rechargeamount) VALUES (v_userid,r_ Row. Val,r_row. Msgtype,sysdate (), R_row.        Val); Endif;     End Loop;     Close r_cursor;     Commit Result:=sqlcode;end Proc_hotstandbyquotachange;

Oracle custom types Invoke examples in C #