C＃和Oracle 10g数据库调用存储过程 [英] C# and Oracle 10g database to call stored procedure

问题描述

我是一个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;

这篇关于C＃和Oracle 10g数据库调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！