C#中如何调用oracle存储过程 最简单的实例

C#调用oracle存储过程 最简单的实例

Oracle方面
１．创建Oracle过程存储
create or replace procedure proce_test
(
paramin in varchar2,
paramout out varchar2,
paraminout in out varchar2
)
as
  varparam varchar2(28);
begin
  varparam:=paramin;
  paramout:=varparam|| paraminout; 
end;
２．测试过程存储
declare
  param_out varchar2(28);
  param_inout varchar2(28);
begin
  param_inout:='ff';  
  proce_test('dd',param_out,param_inout);  
  dbms_output.put_line(param_out);
end;
C#方面
引用Oracle组件
namespace OraWinApp
{
    public class Procedure
    {
        public string SpExeFor(string ID,string UserName)
        {
                //declare the values
            OracleParameter[] parameters={
                                    new OracleParameter("paramin",OracleType.VarChar,20),
                                    new OracleParameter("paramout",OracleType.VarChar,20),
                                    new OracleParameter("paraminout",OracleType.VarChar,20)
                                    };
            parameters[0].Value=ID;
            parameters[2].Value=UserName;
            parameters[0].Direction=ParameterDirection.Input;
            parameters[1].Direction=ParameterDirection.Output;
            parameters[2].Direction=ParameterDirection.InputOutput;
            try
            {
                RunProcedure("proce_test",parameters);
                return parameters[1].Value.ToString();
            }
            catch(Exception e)
            {
                throw e;
            }
         }

        private void RunProcedure(string storedProcName, OracleParameter[] parameters)
        {
            // Get a connection to the db
            // context connection is used in a stored procedure
            String ConnectionString = "Data Source=Pubs;Persist Security Info=True;User ID=sys;Password=123;Unicod" + "e=True";
            OracleConnection conn = new OracleConnection(ConnectionString);
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.CommandText = storedProcName;                   //declare the stored name
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (OracleParameter parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
            cmd.ExecuteNonQuery();                              //execute the stored
        }
测试结果：ddff