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

[b]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[/b]