Using Oracle functions and procedures in. NET and outputting parameters (1)

Technorati labels:. net, ADO. net, Oracle, dbhelperora

We know. net in the Process of calling oracle is not so convenient to call the stored procedures in the MS-SQLSERVER, especially when the parameter output is used, but the powerful Oracle function will not be unable to handle this, You know, huh, huh.

Two solutions: one is that functions in Oracle can be output with parameters, which is consistent with the stored procedures in SQL Server. (For personal opinions, use the functions in Oracle to cope with the stored procedures in SQL Server.

.

The second is to use the out parameter of procedure to bring out the results to solve this problem.

Let's test the program.

Qiantian "runat =" server "> button1" runat = "server" onclick = "button#click" text = "test function output"/> button2 "runat =" server "onclick =" button2_click "text =" test proceduce output "/>
Button3 "runat =" server "text =" test function layer 3 "onclick =" button3_click "/> button4" runat = "server" text = "Test Procedure Layer 3" onclick = "button4_click" />

The background code is output for the test function (directly written in the Code)

       protected void Button1_Click(object sender, EventArgs e)        {            OracleConnection conn = new OracleConnection("Data Source=yu;Persist Security Info=True;User ID=$$$$$$;Password=$$$$$$$$;Unicode=True;");            OracleCommand cmd = new OracleCommand();            cmd.Connection = conn;            cmd.CommandText = "F_ACC_CREATEPERMISSION";            cmd.CommandType = CommandType.StoredProcedure;            // add the parameters, including the return parameter to retrieve                        cmd.Parameters.Add("CategoryID", OracleType.Number).Value = 555;            cmd.Parameters.Add("Description", OracleType.VarChar, 50).Value = "zzz1";// the return value             cmd.Parameters.Add("Result", OracleType.Number).Direction = ParameterDirection.ReturnValue;            // execute the function            conn.Open();            cmd.ExecuteNonQuery();            conn.Close();            // output the result            Response.Write("Resultis: " + cmd.Parameters["Result"].Value);        }

The background code is used to test funcation (output in layer-3 mode of the parameter database class)

       protected void Button3_Click(object sender, EventArgs e)        {         //   Accounts.Bus.Permissions myperm = new Accounts.Bus.Permissions();            Accounts.Data.Permission myperm = new Accounts.Data.Permission();            int i = myperm.Create(555, "zzz3");            Response.Write(i.ToString());                              } 
 
 

Code in the permission class

///// Create a permission // public int create (INT categoryid, string description) {int rowsaffected; oracleparameter [] parameters = {New oracleparameter ("categoryid ", oracletype. number), new oracleparameter ("Description", oracletype. varchar, 50)}; Parameters [0]. value = categoryid; Parameters [1]. value = description; return dbhelperora. runprocedure ("f_acc_createpermission", parameters, out rowsaffected );}

Code in dbhelperora

///// Execute the stored procedure and return the number of affected rows corresponding to the return of the function of Oracle //////Stored Procedure name ///Stored procedure parameters ///Number of affected rows // public static int runprocedure (string storedprocname, idataparameter [] parameters, out int rowsaffected) {using (oracleconnection connection = new oracleconnection (connectionstring) {int result; connection. open (); oraclecommand command = buildintcommand (connection, storedprocname, parameters); rowsaffected = command. executenonquery (); Result = int. parse (command. parameters ["returnvalue"]. value. tostring (); // connection. close (); return result ;}}

Code segment called by buildintcommand

///// Create an oraclecommand object instance (used to return an integer )//////Stored Procedure name ///Stored procedure parameters // oraclecommand object instance Private Static oraclecommand buildintcommand (oracleconnection connection, string storedprocname, idataparameter [] parameters) {oraclecommand command = buildquerycommand (connection, storedprocname, parameters ); // command. parameters. add (New oracleparameter ("returnvalue", oracletype. int32, 4, parameterdirection. returnvalue, false, 0, 0, String. empty, datarowversion. default, null); command. parameters. add ("returnvalue", oracletype. number ). direction = parameterdirection. returnvalue; return command ;}

Code segment called by buildquerycommand

///// Construct an oraclecommand object (used to return a result set instead of an integer )//////Database Connection ///Stored Procedure name ///Stored procedure parameters // oraclecommand Private Static oraclecommand buildquerycommand (oracleconnection connection, string storedprocname, idataparameter [] parameters) {oraclecommand command = new oraclecommand (storedprocname, connection); command. commandtype = commandtype. storedprocedure; foreach (oracleparameter parameter in parameters) {command. parameters. add (parameter);} return command ;}