C#和Oracle 10g数据库调用存储过程
问题描述:
我是一个C#+ SQL Server数据库开发人员。
I am a C# + SQL server database developer.
现在我使用的甲骨文作为数据库。我有一个Oracle存储过程的重新调整参考光标。
Now I am using Oracle as a database. I have one Oracle stored procedure retuning ref cursor.
CREATE OR REPLACE PROCEDURE Get_Trans_Data ( p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
select * from test_tbl1 ;
END Get_Trans_Data;
使用OLEDB我只怎么可以访问此存储过程并保存导致数据集?
Using Oledb only how can I access this stored proc and save result in dataset?
我没有得到在OLEDB REF CURSOR数据类型的选项。
I am not get the option for ref cursor datatype in Oledb.
答
您可以尝试这样的事。无需添加参数OUT的。了解更多这里
You can try something like this. No need to add parameter for OUT's. Read more here
static void Read()
{
using (var currentConnection = new OleDbConnection(
"provider=MSDAORA;Data Source=orcl;User ID=db_test;Password=db_test;"))
{
currentConnection.Open();
using (var myCommand = new OleDbCommand())
{
myCommand.Connection = currentConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "Get_Data";
myCommand.Parameters.Add(
new OleDbParameter(
"firstParam",
OleDbType.Integer, 0, ParameterDirection.Input,
true, 0, 0, "", DataRowVersion.Default, Convert.DBNull));
myCommand.Parameters[0].Value = 42;
var myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read())
for (int i = 0; i < myDataReader.FieldCount; i++)
Console.WriteLine(myDataReader.GetName(i));
}
}
}
CREATE OR REPLACE PROCEDURE Get_Data (
p_return_cur OUT SYS_REFCURSOR,
firstParam INTEGER)
IS
BEGIN
OPEN p_return_cur FOR select * from test;
END Get_Data;