C # executes Oracle stored procedures

1. Does not return the result set, has the input, the output parameter

        /// <summary>        ///Executing stored procedures/// </summary>        /// <param name= "Storedprocname" >Stored Procedure name</param>        /// <param name= "SQL" >The SQL statement executed</param>        /// <param name= "SQL2" >SQL statement executed 2</param>        /// <returns></returns>         Public Static BOOLRunprocedure (stringStoredprocname,stringSqlstringsql2) {            using(OracleConnection connection =NewOracleConnection (connectionString)) {connection.                Open (); OracleCommand SQLCMD=NewOracleCommand (storedprocname, connection); intState =0; Oracleparameter[] Paras={                                NewOracleParameter ("P_sql", SQL),NewOracleParameter ("P_SQL2", SQL2),NewOracleParameter ("p_state", state)//A return value of 0 indicates that failure 1 indicates success                                        }; paras[2]. Direction =ParameterDirection.Output;                SqlCmd.Parameters.AddRange (paras); Sqlcmd.commandtype= CommandType.StoredProcedure;//setting up using Stored proceduresSqlcmd.executenonquery (); Connection.                Close (); State= Convert.ToInt32 (paras[2].                Value); returnstate = =1?true:false; }        }




Stored procedures:

Create or replace procedure Tcz_operation
(
P_sql in VARCHAR2,--sql statement
P_sql2 in VARCHAR2,--sql statement
P_state out integer--The return value 0 is the failure 1 is successful

)
Is

Begin

Execute immediate p_sql; --Execute SQL statement
Execute immediate p_sql2; --Execute SQL statement
Commit
P_state:=1;
Dbms_output.put_line (p_state);
EXCEPTION
When others then
Rollback
p_state:=0;
Dbms_output.put_line (p_state);
End

2. Return result set with input and output parameters

        /// <summary>        ///Executing stored procedures/// </summary>        /// <param name= "Storedprocname" >Stored Procedure name</param>        /// <param name= "TableName" >Table name</param>        /// <param name= "strwhere" >Query Criteria</param>        /// <param name= "Ordercolumn" >sorted Columns</param>        /// <param name= "Orderstyle" >Sorting Method</param>        /// <param name= "PageIndex" >Current Page</param>        /// <param name= "PageSize" >number of record bars per page</param>        /// <param name= "RowCount" >Total Record Count</param>        /// <param name= "PageCount" >Total Pages</param>        /// <returns></returns>         Public StaticDataSet Runprocedure (stringStoredprocname,stringTableName,stringStrwhere,stringOrdercolumn,stringOrderstyle,intPageIndex,intPageSize, out intRowCount, out intPageCount) {            using(OracleConnection connection =NewOracleConnection (connectionString)) {DataSet DataSet=NewDataSet (); Connection.                Open (); OracleDataAdapter SqlDA=NewOracleDataAdapter (storedprocname, connection); RowCount=0; PageCount=0; Oracleparameter[] Paras={                                NewOracleParameter ("P_tablename", TableName),NewOracleParameter ("P_strwhere", strwhere),NewOracleParameter ("P_ordercolumn", Ordercolumn),NewOracleParameter ("P_orderstyle", Orderstyle),NewOracleParameter ("P_curpage", PageIndex),NewOracleParameter ("p_pagesize", pageSize),NewOracleParameter ("P_totalrecords", RowCount),NewOracleParameter ("p_totalpages", PageCount),NewOracleParameter ("V_cur", OracleType.Cursor)//the returned cursor                                                         }; paras[6]. Direction =ParameterDirection.Output; paras[7]. Direction =ParameterDirection.Output; paras[8]. Direction =ParameterDirection.Output;                SqlDA.SelectCommand.Parameters.AddRange (paras); SqlDA.SelectCommand.CommandType= CommandType.StoredProcedure;//setting up using Stored proceduresSqlda.fill (DataSet); Connection.                Close (); RowCount= Convert.ToInt32 (paras[6].                Value); PageCount= Convert.ToInt32 (paras[7].                                Value); returnDataSet; }        }



To create a package:

CREATE OR REPLACE Package pkg_query as TYPE cur_query is REF CURSOR;
END Pkg_query;

Stored procedures:

Create or replace procedure Sp_getpagelist
(P_tablename in Varchar2,--table name
P_strwhere in Varchar2,--Query conditions
P_ordercolumn in Varchar2,--sorted columns
P_orderstyle in Varchar2,--Sort by
P_curpage in Out number,--current page
P_pagesize in Out number,--display of record bars per page
P_totalrecords out number,--Total records
P_totalpages out number,--Total pages
V_cur out Pkg_query.cur_query)--returned result set

Is
V_sql VARCHAR2 (2000): = "; --sql statements
V_startrecord number (4); --Number of record bars to start displaying
V_endrecord number (4); --the number of record bars to end the display
BEGIN
--The total number of records in the record
V_sql: = ' SELECT to_number (COUNT (*)) from ' | | P_tablename | | ' WHERE 1=1 ';
IF P_strwhere is not NULL or P_strwhere <> "then
V_sql: = V_sql | | P_strwhere;
END IF;
EXECUTE IMMEDIATE v_sql into p_totalrecords;

--Verify page record size
IF P_pagesize < 0 Then
P_pagesize: = 0;
END IF;

--Calculates the total number of pages based on page size
IF MOD (p_totalrecords,p_pagesize) = 0 Then
P_totalpages: = p_totalrecords/p_pagesize;
ELSE
P_totalpages: = p_totalrecords/p_pagesize + 1;
END IF;

--Verification page number
IF P_curpage < 1 Then
P_curpage: = 1;
END IF;
IF p_curpage > P_totalpages Then
P_curpage: = p_totalpages;
END IF;

--Realization of paged query
V_startrecord: = (p_curpage-1) * p_pagesize + 1;
V_endrecord: = P_curpage * p_pagesize;
V_sql: = ' select * FROM (select A.*, rownum r from ' | |
' (SELECT * from ' | | | p_tablename;
IF P_strwhere is not NULL or P_strwhere <> "then
V_sql: = V_sql | | ' WHERE 1=1 ' | | P_strwhere;
END IF;
IF P_ordercolumn is not NULL or P_ordercolumn <> "then
V_sql: = V_sql | | ' ORDER by ' | | P_ordercolumn | | "| | P_orderstyle;
END IF;
V_sql: = V_sql | | ') A WHERE rownum <= ' | | V_endrecord | | ') B WHERE R >= '
|| V_startrecord;
Dbms_output.put_line (V_sql);
OPEN v_cur for V_sql;
END sp_getpagelist;

C # executes Oracle stored procedures