Csharp:oracle Stored Procedure DAL using odp.net

Paging:http://www.codeproject.com/articles/44858/custom-paging-gridview-in-asp-net-oracle

Oracle sql:

--Book category catalogue Kind--geovin Du CREATE TABLE bookkindlist (Bookkindid INT PRIMARY key,bookkindname nvarchar2 ($) Not Null,bookk Indparent INT null,bookkindcode varchar (---);--sequence to create a drop SEQUENCE bookkindlist_seq;            CREATE SEQUENCE bookkindlist_seqincrement by 1--add several start with 1 each--counting from 1 nomaxvalue--not setting the maximum value nocycle           --keep accumulating, not circulating nocache; --Set cache caches sequence, if the system is down or otherwise will cause sequence discontinuity, can also be set to---------nocache--self-growth trigger drop trigger bookkindlist_id_auto; Create or replace trigger Bookkindlist_id_auto before insert on Bookkindlist--bookkindlist is the table name for each rowdeclare n Extid Number;begin if:new. Bookkindid is NULL or:new. Bookkindid=0 then--bookkindid is the column name of select Bookkindlist_seq.    Nextval--bookkindlist_seq was just created into the nextid from dual; : New.  Bookkindid:=nextid;    End If;end; --Add drop PROCEDURE proc_insert_bookkindlist; CREATE OR REPLACE PROCEDURE proc_insert_bookkindlist (temtypename nvarchar2,temparent number) Asncount number; Begin--select CounT (*) into ncount from Bookkindlist fm1 where EXISTS (SELECT bookkindname from Bookkindlist fm2 where FM2. Bookkindname=temtypename);--to determine whether to save select COUNT (*) into the ncount from Bookkindlist where bookkindname=temtypename;if Ncount<=0 Thenbegininsert into Bookkindlist (bookkindname,bookkindparent) VALUES (temtypename,temparent); commit;  End;elsebegin SELECT Bookkindid into ncount from Bookkindlist where bookkindname=temtypename; Dbms_output.put_line (' existence of the same record, add unsuccessful! ' | | ncount); end;end if; Exception when others then Dbms_output.put_line (' There is a problem, add unsuccessful! ' | |       ncount); Rollback;end proc_insert_bookkindlist; --Test Oracle 11g proc_insert_bookkindlist 20150526exec (' Oil paint ', 3); Drop PROCEDURE proc_insert_bookkindout;drop PROCEDURE procinsertbookkindout; --Add a return value of Create OR REPLACE PROCEDURE proc_insert_bookkindout--Add return ID (temtypename nvarchar2,temparent int,temid out int) Asncount Number;reid number;begin--select COUNT (*) into ncount from Bookkindlist fm1 where EXISTS (SELECT BookkindnaMe from Bookkindlist fm2 where FM2. Bookkindname=temtypename);--to determine whether to save select COUNT (*) into the ncount from Bookkindlist where bookkindname=temtypename;if Ncount<=0 Thenbegin--insert into Bookkindlist (bookkindid,bookkindname,bookkindparent) VALUES (BookKindList_ seq.nextval,temtypename,temparent); INSERT into Bookkindlist (bookkindname,bookkindparent) VALUES (Temtypename, temparent); Select Bookkindlist_seq.currval into Reid from Dual;temid:=reid;dbms_output.put_line (' Add success! ' | |  TEMID); Commit;end;elsebegin SELECT bookkindid into ncount from Bookkindlist where bookkindname=temtypename; Dbms_output.put_line (' existence of the same record, add unsuccessful! ' | |  ncount); Temid:=0;end;end if; Exception when others then begin Dbms_output.put_line (' There is a problem, add unsuccessful! ' | |      ncount);       temid:=0;    Rollback; End;end proc_insert_bookkindout;--test Oracle 11g tu poly 20150526declaremid number:=0;nam nvarchar2 (100): = ' black and white ';p ar number: =3;begin--proc_insert_bookkindout (Nam in Nvarchar2,par in Int,mid in out int);p roc_insert_bookkindout (NAM,par, mid); if mid>0 thendbms_output.put_line (' Add success! output parameter: ' | | mid); Elsedbms_output.put_line (' There is the same record, add not successful! output parameter: ' | | End if;end;--Modify Create OR REPLACE PROCEDURE procupdatebookkindlist (p_id in INT,--Bookkindlist.bookkindid%type, P_n Ame in Nvarchar2,--Bookkindlist.bookkindname%type, p_parent in INT,--Bookkindlist.bookkindparent%type,p_code in  Varchar--bookkindlist.bookkindcode%type) Isncount number; BEGIN SELECT Count (*) into ncount from bookkindlist where bookkindname=p_name;if ncount<=0 thenbeginupdate bookkindlis  T SET bookkindname=p_name,bookkindparent=p_parent,bookkindcode=p_code WHERE bookkindid=p_id; COMMIT;  End;elsebegin SELECT Bookkindid into ncount from Bookkindlist where bookkindname=p_name; Dbms_output.put_line (' existence of the same record, modification unsuccessful! ' | | ncount); End End If;  END procupdatebookkindlist; --Test Beginprocupdatebookkindlist (8, ' philosophy ', 1, ' Geovin Du ');  end;--Delete Create OR REPLACE PROCEDURE procdeletebookkindlist (P_bookkindid in Bookkindlist.bookkindid%type) isbegin DELETE Bookkindlist where Bookkindid = P_bookkindid; COMMIT;  END;---A record-create a package: Creates or replace packages Pack_bookkindid is type cur_bookkindid is REF CURSOR; End Pack_bookkindid;          --Creating a stored procedure create or Replace procedure procselectbookkindlist (p_id in Int,p_cur out Pack_bookkindid.cur_bookkindid) is        V_sql varchar2 (+); begin if p_id = 0 Then--0 query all open p_cur for select * from Bookkindlist;            else v_sql: = ' select * from bookkindlist where Bookkindid =: p_id ';          Open p_cur for v_sql using p_id;  End If; End procselectbookkindlist;--Creates a package that returns all of the record result sets of the Bookkindlist table as a cursor drop packages Pkg_select_bookkindlistall;drop Procedure proc_select_bookkindlistall;create or Replace package pkgselectbookkindlistall is--Author:geovindu type Myc    UR is ref CURSOR; Procedure Procselectbookkindlistall (Cur_return out mycur), end pkgselectbookkindlistall;create or replace package body Pkgselectbookkindlistall is--Function and procedure implementations procEdure Procselectbookkindlistall (Cur_return out mycur) is the begin open Cur_return for select * from Bookkindlist; End Procselectbookkindlistall;end pkgselectbookkindlistall;--test package and Stored procedure query table for all content declare--variables that define the cursor type Cur_return  pkgselectbookkindlistall.mycur;--defining row Types Pdtrow bookkindlist%rowtype;begin-Executing stored procedures  Pkgselectbookkindlistall.procselectbookkindlistall (Cur_return);           --Traverse the data loop in the cursor--fetch the current row data into the Pdtrow FETCH Cur_return into Pdtrow;           --End loop If no data is obtained EXIT when Cur_return%notfound; --Dbms_output The data obtained by the output. Put_Line (Pdtrow. bookkindid| | ', ' | | Pdtrow.         Bookkindname);         END LOOP; CLOSE Cur_return;end;

CSharp Code:

<summary>///20160918 tu//Geovin Du//</summary> public class Bookkindlistdal:ibookki ndlist {//private static string connectionString [email protected] "DATA source=oracle11g;        USER Id=geovin;password=geovindu; "; <summary>///Additional records///</summary>///<param name= "Bookkindlistinfo" ></param&gt        ;            <returns></returns> public int insertbookkindlist (Bookkindlistinfo bookkindlist) {            int ret = 0; try {oracleparameter[] par = new Oracleparameter[]{new OracleParameter ("Temtypename", oracledbty Pe.                nvarchar2,1000), New OracleParameter ("Temparent", oracledbtype.int32,4),}; Par[0].                Value = Bookkindlist.bookkindname; Par[0].                Direction = ParameterDirection.Input; PAR[1].                Value = bookkindlist.bookkindparent; PAR[1].                Direction = ParameterDirection.Input; Ret = Oraclehelper.executesql ("Proc_insert_bookkindlist", commandtype.storedprocedure, par);            } catch (OracleException ex) {throw ex;        } return ret; }///<summary>///<param name= "Authorlist" ></ param>//<param name= "Authorid" ></param>///<returns></returns> Public            int Insertbookkindoutput (Bookkindlistinfo bookkindlist, out int bookkindlid) {bookkindlid = 0;            int ret = 0; try {oracleparameter[] par = new Oracleparameter[]{new OracleParameter ("Temtypename", oracledbty Pe. nvarchar2,1000), New OracleParameter ("Temparent", oracledbtype.int32,4), New OracleParameter (                "Temid", oracledbtype.int32,4),}; Par[0].                Value = Bookkindlist.bookkindname; Par[0]. Direction = Parameterdirection.                Input; PAR[1].                Value = bookkindlist.bookkindparent; PAR[1].                Direction = ParameterDirection.Input; PAR[2].                Direction = ParameterDirection.Output;                ret = Oraclehelper.executesql ("Proc_insert_bookkindout", commandtype.storedprocedure, par); if (Ret > 0) {bookkindlid =int. Parse (Par[2].                Value.tostring ());            }} catch (OracleException ex) {throw ex;        } return ret; }///<summary>///Modify record///Tu 20160920///</summary>///<param name= "Boo Kkindlistinfo "></param>///<returns></returns> public int updatebookkindlist (Bookkindli            Stinfo bookkindlist) {int ret = 0; try {oracleparameter[] par = new Oracleparameter[]{new OracleParameter ("p_id", Oracledbtype.int3 2,4),New OracleParameter ("P_name", oracledbtype.nvarchar2,1000), New OracleParameter ("P_parent", oracledbtype.int32,4),                New OracleParameter ("P_code", oracledbtype.varchar2,1000),}; Par[0].                Value = Bookkindlist.bookkindid; Par[0].                Direction = ParameterDirection.Input; PAR[1].                Value = Bookkindlist.bookkindname; PAR[1].                Direction = ParameterDirection.Input; PAR[2].                Value = bookkindlist.bookkindparent; PAR[2].                Direction = ParameterDirection.Input; PAR[3].                Value = Bookkindlist.bookkindcode; PAR[3].                Direction = ParameterDirection.Input;               ret = Oraclehelper.executesql ("Procupdatebookkindlist", commandtype.storedprocedure, par);            ret = 1;            } catch (OracleException ex) {throw ex;        } return ret; }///<summary>///delete record///</summary>//<param name= "Bookkindidinfo" ></param>///<returns></returns> public bool Deletebookk            indlist (int bookkindid) {BOOL ret = false;                try {oracleparameter par = new OracleParameter ("P_bookkindid", Bookkindid); Par.                Direction = ParameterDirection.Input;                int temp = 0;                temp = Oraclehelper.executesql ("Procdeletebookkindlist", commandtype.storedprocedure, par);                if (temp! = 0) {ret = true;            }} catch (OracleException ex) {throw ex;        } return ret; }///<summary>///query record///</summary>///<param name= "Bookkindidinfo" ></p        aram>///<returns></returns> public bookkindlistinfo selectbookkindlist (int bookKindID) {Bookkindlistinfo BookkiNdlist = null; try {oracleparameter[] par = new oracleparameter[]{new OracleParameter ("p_id", O                racledbtype.int32,4), New OracleParameter ("P_cur", Oracledbtype.refcursor),}; Par[0].                Value = Bookkindid; Par[0].                Direction = ParameterDirection.Input; PAR[1].                Direction = ParameterDirection.Output; using (OracleDataReader reader = Oraclehelper.getreader ("Procselectbookkindlist", CommandType.StoredProcedure, par)) Proc_select_bookkindlist hint name is too long for Oracle {if (reader.                        Read ()) {bookkindlist = new bookkindlistinfo (); Bookkindlist.bookkindid = (!object. Equals (reader["Bookkindid"], null))?                        (decimal) reader["Bookkindid"]: 0; Bookkindlist.bookkindname = (!object. Equals (reader["Bookkindname"], null))? (string) reader["Bookkindname"] : ""; Bookkindlist.bookkindparent = (!object. Equals (reader["bookkindparent"], null))?                    (decimal) reader["Bookkindparent"]: 0;            }}} catch (OracleException ex) {throw ex;        } return bookkindlist;        }///<summary>///query all records///</summary>///<returns></returns> Public list<bookkindlistinfo> Selectbookkindlistall () {list<bookkindlistinfo> List = new Li            St<bookkindlistinfo> ();            Bookkindlistinfo bookkindlist = null; try {//define parameters, note that the parameter name must be consistent with the stored procedure definition, and the type is oracletype.cursor oracleparameter cur_set = NE                W OracleParameter ("Cur_return", oracledbtype.refcursor); Set the parameter to the output type Cur_set.                Direction = ParameterDirection.Output; Oraclehelper.executereader (ConnectIonstring, CommandType.StoredProcedure, "Pkg_select_bookkindlistall.proc_select_bookkindlistall", Cur_set) using (OracleDataReader reader = Oraclehelper.getreader ("Pkg_select_bookkindlistall.proc_select_bookkindlistall", CommandType.StoredProcedure, Cur_set)) {while (reader.                        Read ()) {bookkindlist = new bookkindlistinfo (); string s = reader["Bookkindid"].                        ToString (); Bookkindlist.bookkindid = (!object. Equals (reader["Bookkindid"], null))?                        (decimal) reader["Bookkindid"]: 0; Bookkindlist.bookkindname = (!object. Equals (reader["Bookkindname"], null))?                        (string) reader["Bookkindname"]: ""; Bookkindlist.bookkindparent = (!object. Equals (reader["bookkindparent"], null))?                        (decimal) reader["Bookkindparent"]: 0; List.                    ADD (bookkindlist);         }                }            }   catch (OracleException ex) {throw ex;        } return list;        }///<summary>///query all records///</summary>///<returns></returns>            Public DataTable Selectbookkindlistdatatableall () {datatable dt = new DataTable (); try {//define parameters, note that the parameter name must be consistent with the stored procedure definition, and the type is oracletype.cursor oracleparameter cur_set = NE                W OracleParameter ("Cur_return", oracledbtype.refcursor); Set the parameter to the output type Cur_set.                Direction = ParameterDirection.Output; Add the parameter//comm.                Parameters.Add (Cur_set); using (DataTable reader = oraclehelper.gettable ("Pkgselectbookkindlistall.procselectbookkindlistall",                CommandType.StoredProcedure, Cur_set)) {dt = reader;      }} catch (OracleException ex) {throw ex;      } return DT; }///<summary>//Fill DataSet DataSet-oracle Library///</summary>//<param name= "Pindex "> Current page </param>//<param name=" psql "> SQL statement to execute query </param>//<param name=" Psize "> per  The number of records displayed on the page </param>///<returns></returns> private bool Gridbind (int pindex, string psql, int            psize) {OracleConnection conn = new OracleConnection ();            OracleCommand cmd = new OracleCommand ();            OracleDataAdapter dr = new OracleDataAdapter (); Conn. ConnectionString = system.configuration.configurationmanager.connectionstrings["ConnectionString"].            ConnectionString; Cmd.            Connection = conn;            Cmd.commandtype = CommandType.StoredProcedure; Conn.            Open ();            Cmd.commandtext = "Dotnet.dotnetpagerecordscount"; Cmd. Parameters.Add ("Psqlcount", oracledbtype.varchar2).            Value = Psql; Cmd. ParaMeters. ADD ("Prcount", Oracledbtype.int32).            Direction = ParameterDirection.Output; Cmd.            ExecuteNonQuery (); String pcount = cmd. parameters["Prcount"].            Value.tostring (); Cmd.            Parameters.clear ();            Cmd.commandtext = "Dotnet.dotnetpagination"; if (Pindex! = 0) {cmd. Parameters.Add ("Pindex", Oracledbtype.int32).            Value = pindex-1; } else {cmd. Parameters.Add ("Pindex", Oracledbtype.int32).            Value = Pindex; } cmd. Parameters.Add ("Psql", oracledbtype.varchar2).            Value = Psql; Cmd. Parameters.Add ("Psize", Oracledbtype.int32).            Value = psize; Cmd. Parameters.Add ("V_cur", Oracledbtype.refcursor).            Direction = ParameterDirection.Output; Cmd. Parameters.Add ("Pcount", Oracledbtype.int32).            Direction = ParameterDirection.Output; Dr.            SelectCommand = cmd;        try {DataSet ds = new DataSet ();        Dr.                Fill (DS); Displays the status of the page number bar//showstatus (Convert.ToInt32 (cmd). parameters["Pindex"]. Value + 1,//Convert.ToInt32 (CMD). parameters["Pcount"].                Value),//Convert.ToInt32 (Pcount)); for (int i = 0; I < ds. Tables.count; i++) {//Remove the table with the data behavior zero if (ds. Tables[i]. Rows.Count = = 0) ds. Tables.remove (ds. Tables[i].                TableName); }} catch (Exception ex) {Console.WriteLine (ex).                Message);            return false; } conn.            Close ();        return true; }    }

　　

Csharp:oracle Stored Procedure DAL using odp.net