将oracle的sys_refcursor返回到C# [英] Return sys_refcursor of the oracle to c#
本文介绍了将oracle的sys_refcursor返回到C#的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
C#:
public DataSet ListaClientes()
{
DataSet ds = new DataSet();
try
{
System.Data.OracleClient.OracleConnection conexion = Conexion.GetConnection();
if (conexion.State == System.Data.ConnectionState.Open)
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
cmd.Connection = conexion;
cmd.CommandText = "ListadoClientes";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("resul", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
conexion.Close();
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd);
da.Fill(ds);
}
return ds;
}
catch(Exception e)
{
throw e;
}
}
Oracle存储过程:
CREATE OR REPLACE PROCEDURE ListadoClientes(resul OUT sys_refcursor)
IS
BEGIN
OPEN resul for select ID ,NOMBRES ,APELLIDOS ,CEDULA ,DIRECCION ,TELEFONO
from cliente;
END ListadoClientes;
在C#CATCH块中可见错误:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LISTADOCLIENTES'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored
推荐答案
是否存在不使用函数而不是过程的原因?
Is there a reason why you do not use a function instead of procedure?
CREATE OR REPLACE FUNCTION ListadoClientes() RETURN sys_refcursor
IS
resul Sys_refcursor;
BEGIN
OPEN resul for select ID ,NOMBRES ,APELLIDOS ,CEDULA ,DIRECCION ,TELEFONO
from cliente;
RETURN resul;
END ListadoClientes;
然后在C#中,您必须将其更改为此:
Then in C# you must change it to this:
cmd.Parameters.Add("resul", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
当您运行da.Fill(ds);
时,该功能将被执行,即使用cmd.ExecuteNonQuery();
执行该功能两次.
When you run da.Fill(ds);
then the function is executed, i.e. using cmd.ExecuteNonQuery();
executes the function twice.
无论如何,对于一种程序而言,正确的方法应该是这种方式:
Anyway, for a procedure the right way should be this one:
cmd.CommandText = "ListadoClientes(:resul)";
这篇关于将oracle的sys_refcursor返回到C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文