C # Calling Stored Procedure Methods with parameters-Oracle

1Because the eight reports of the crosstab chart are written inSQL.SQLStatement has300The view cannot be defined because there are too many rows. Because a parameter must be passed, the stored procedure is used.

 

First, define the package, and then define the package body.. NetCall to generate a dataset

. NetCallOracleThe stored procedure with parameters returns the dataset.

OracleThe steps are as follows:

InPL/SQLFirst write the definition file of the package

Create or replace package zhbb
Type t_cursor is ref cursor;
Procedure zh11 (RQ in char, io_cursor in out t_cursor );

Procedure zh12 (RQ in char, io_cursor in out t_cursor );

End zhbb;

Next, write the definition file of the package body.

Create or replace package body zhbb

Procedure zh11 (RQ in char, io_cursor in out t_cursor) is

V_cursor t_cursor;

Begin

Open v_cursor

Select * from AA where Ny = RQ;

Io_cursor: = v_cursor;

End zh11;

Procedure zh12 (RQ in char, io_cursor in out t_cursor) is

.....

End zh12;

. NetCall method:

Public StaticDatatable getdatatable (StringPackagename,StringProname,StringConnstring,StringRQ)

{

Connstring ="Provider = msdaora.1; user id = xx; Data Source = xx; Password = xx;"

 //Define the new link method, because the original driver"Oraoledb. Oracle"Cannot meet requirements, set"Msdaora.1"

Oledbconnection oraclecon =NewOledbconnection (connstring );

Oraclecon. open ();

Oledbcommand cmd =NewOledbcommand ("{call" + packagename + "." + proname + "(?, {Resultset 0, io_cursor})} ", oraclecon );

Cmd. Connection = oraclecon;

Cmd. Parameters. Add ("RQ", oledbtype. varchar, 8). value = "" + RQ + "";

Oledbdataadapter da =NewOledbdataadapter (CMD );

Datatable dt =NewDatatable ();

// Dataset DS = new dataset ();

Da. Fill (DT );

Oraclecon. Close ();

Oraclecon. Dispose ();

ReturnDT;

}

}