ORACLE端建立了一个存储过程
Create Table T_TEST(A CHAR(1), B CHAR(1));
Insert Into T_TEST ('1', '1');
Insert Into T_TEST ('2', '2');
Insert Into T_TEST ('3', '3');
commit;
Create Or Replace Procedure P_TEST (Cursor_Parameter out sys_refcursor) as
begin
open Cursor_Parameter for select * from T_TEST;
end P_TEST;
/
创建好了以后,调用:
declare
res sys_refcursor;
lop t_test%rowtype;
begin
p_test(res);
loop
fetch res into lop;
exit when res%notfound;
dbms_output.put_line(lop.a||' '||lop.b);
end loop;
end;出力结果都没有问题
1 1
2 2
3 3但如果我想用.NET调用,返回记录集和DATAGRID绑定,如何做??
Create Table T_TEST(A CHAR(1), B CHAR(1));
Insert Into T_TEST ('1', '1');
Insert Into T_TEST ('2', '2');
Insert Into T_TEST ('3', '3');
commit;
Create Or Replace Procedure P_TEST (Cursor_Parameter out sys_refcursor) as
begin
open Cursor_Parameter for select * from T_TEST;
end P_TEST;
/
创建好了以后,调用:
declare
res sys_refcursor;
lop t_test%rowtype;
begin
p_test(res);
loop
fetch res into lop;
exit when res%notfound;
dbms_output.put_line(lop.a||' '||lop.b);
end loop;
end;出力结果都没有问题
1 1
2 2
3 3但如果我想用.NET调用,返回记录集和DATAGRID绑定,如何做??
后面的就和SQL Server一样,你也可以直接在设计界面中直接绑定存储过程
我问的是.NET里如何来调用,参数如何写.如何来保存到记录集里.NET里没有sys_refcursor这种工具包.我如何来调用这个动态存储过程?
//Oralce
//using System.Data.OracleClient;public abstract class DataObject
{
protected SqlConnection Connection;
//protected OracleConnection oracleConnection;
public DataObject(string newConnectionString)
{
connectionString = newConnectionString;
//实例化Sql连接
Connection = new SqlConnection(connectionString);
}
protected string ConnectionString
{
get
{
return connectionString;
}
}
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, Connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
} protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
int result;
Connection.Open();
SqlCommand command = BuildIntCommand(storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
Connection.Close();
return result;
} protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
sqlDA.Fill(dataSet,tableName);
Connection.Close();
return dataSet;
}
}
}然后在你的DataGrid页面中绑定DataSet
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)IDataParameter[] parameters是参数值,比如说存储过程的参数是两上字符字符形变量可以这么做,你写的这个是.NET调用存储过程的,但这个存储过程是属于那种DDL的操作,返回的是一个字符串或数值,而不可能返回一个批量记录集吧??
//根据数据库存储过程名称和参数生成.NET中可执行的SqlCommandprotected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
//返回int值的运行程序,用于带返回值的存储过程,如添加删除等protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
//返回记录集,这个是你需要的,中间的
sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
就是调用上面的BuildQueryCommand方法生成存储过程调用程序另外写漏了个,这个是在BuildQueryCommand的参数基础上追加一个返回值参数private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
你在定义参数那行这么写
command.Parameters.Add(New OracleParameter("IO_CURSOR", OracleType.Cursor))试试
http://msdn2.microsoft.com/en-us/ms971506.aspx
http://msdn2.microsoft.com/en-us/ms971488.aspx
OracleCommand cmd=new OracleCommand("包名.p_test",orcn);
cmd.CommandType=CommandType.StoredProcedure;
OracleParameter p=new OracleParameter("Cursor_Parameter",OracleType.Cursor);
p.Direction=ParameterDirection.Output;
cmd.Parameters.Add(p);
OracleDataAdapter da=new OracleDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds,"test");
this.DataGrid1.DataSource=ds.Tables["test"].DefaultView;
Page.DataBind();