从C＃调用Oracle存储过程？

I just started reading about stored procedures. Can anyone please help me call a stored procedure in oracle from C#?

我刚开始阅读有关存储过程的内容。有人可以帮我从C＃调用oracle中的存储过程吗？

5 个解决方案

#1

Please visit this ODP site set up by oracle for Microsoft OracleClient Developers: http://www.oracle.com/technetwork/topics/dotnet/index-085703.html

请访问oracle为Microsoft OracleClient开发人员设置的ODP站点：http：//www.oracle.com/technetwork/topics/dotnet/index-085703.html

Also below is a sample code that can get you started to call a stored procedure from C# to Oracle. PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT is the stored procedure built on Oracle accepting parameters PUNIT, POFFICE, PRECEIPT_NBR and returning the result in T_CURSOR.

下面是一个示例代码，可以帮助您开始从C＃调用存储过程到Oracle。 PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT是在Oracle上构建的存储过程，它接受参数PUNIT，POFFICE，PRECEIPT_NBR并将结果返回到T_CURSOR。

using Oracle.DataAccess;
using Oracle.DataAccess.Client;

public DataTable GetHeader_BySproc(string unit, string office, string receiptno)
{
    using (OracleConnection cn = new OracleConnection(DatabaseHelper.GetConnectionString()))
    {
        OracleDataAdapter da = new OracleDataAdapter();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = cn;
        cmd.InitialLONGFetchSize = 1000;
        cmd.CommandText = DatabaseHelper.GetDBOwner() + "PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("PUNIT", OracleDbType.Char).Value = unit;
        cmd.Parameters.Add("POFFICE", OracleDbType.Char).Value = office;
        cmd.Parameters.Add("PRECEIPT_NBR", OracleDbType.Int32).Value = receiptno;
        cmd.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
}

#2

I have now got the steps needed to call procedure from C#

我现在已经完成了从C＃调用过程所需的步骤

   //GIVE PROCEDURE NAME
   cmd = new OracleCommand("PROCEDURE_NAME", con);
   cmd.CommandType = CommandType.StoredProcedure;

   //ASSIGN PARAMETERS TO BE PASSED
   cmd.Parameters.Add("PARAM1",OracleDbType.Varchar2).Value = VAL1;
   cmd.Parameters.Add("PARAM2",OracleDbType.Varchar2).Value = VAL2;

   //THIS PARAMETER MAY BE USED TO RETURN RESULT OF PROCEDURE CALL
   cmd.Parameters.Add("vSUCCESS", OracleDbType.Varchar2, 1);
   cmd.Parameters["vSUCCESS"].Direction = ParameterDirection.Output;

   //USE THIS PARAMETER CASE CURSOR IS RETURNED FROM PROCEDURE
   cmd.Parameters.Add("vCHASSIS_RESULT",OracleDbType.RefCursor,ParameterDirection.InputOutput); 

   //CALL PROCEDURE
   con.Open();
   OracleDataAdapter da = new OracleDataAdapter(cmd);
   cmd.ExecuteNonQuery();

   //RETURN VALUE
   if (cmd.Parameters["vSUCCESS"].Value.ToString().Equals("T"))
   {
      //YOUR CODE
   }
   //OR
   //IN CASE CURSOR IS TO BE USED, STORE IT IN DATATABLE
   con.Open();
   OracleDataAdapter da = new OracleDataAdapter(cmd);
   da.Fill(dt);

Hope this helps

希望这可以帮助

#3

It's basically the same mechanism as for a non query command with:

它基本上与非查询命令的机制相同：

command.CommandText = the name of the stored procedure
command.CommandText =存储过程的名称
command.CommandType = CommandType.StoredProcedure
command.CommandType = CommandType.StoredProcedure
As many calls to command.Parameters.Add as the number of parameters the sp requires
尽可能多地调用command.Parameters.Add作为sp所需的参数个数
command.ExecuteNonQuery
command.ExecuteNonQuery

There are plenty of examples out there, the first one returned by Google is this one

有很多例子，谷歌返回的第一个就是这个例子

There's also a little trap you might fall into, if your SP is a function, your return value parameter must be first in the parameters collection

如果你的SP是一个函数，你可能会陷入一个小陷阱，你的返回值参数必须是参数集合中的第一个

#4

This Code works well for me calling oracle stored procedure

这段代码适用于我调用oracle存储过程

Add references by right clicking on your project name in solution explorer >Add Reference >.Net then Add namespaces.

通过右键单击解决方案资源管理器中的项目名称>添加引用> .Net然后添加命名空间来添加引用。

using System.Data.OracleClient;
using System.Data;

then paste this code in event Handler

然后将此代码粘贴到事件处理程序中

        string str = "User ID=username;Password=password;Data Source=Test";
        OracleConnection conn = new OracleConnection(str);
        OracleCommand cmd = new OracleCommand("stored_procedure_name", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        --Ad parameter list--
        cmd.Parameters.Add("parameter_name", "varchar2").Value = value;
        ....
        conn.Open();
        cmd.ExecuteNonQuery();

And its Done...Happy Coding with C#

并完成...用C＃编写快乐的编码

#5

In .Net through version 4 this can be done the same way as for SQL Server Stored Procs but note that you need:

在.Net到版本4中，这可以与SQL Server存储过程相同的方式完成，但请注意，您需要：

using System.Data.OracleClient;

There are some system requirements here that you should verify are OK in your scenario.

这里有一些系统要求，您应该验证在您的方案中是否正常。

Microsoft is deprecating this namespace as of .Net 4 so third-party providers will be needed in the future. With this in mind, you may be better off using Oracle Data Provider for .Net (ODP.NET) from the word go - this has optimizations that are not in the Microsoft classes. There are other third-party options, but Oracle has a strong vested interest in keeping .Net developers on board so theirs should be good.

Microsoft从.Net 4开始弃用此命名空间，因此将来需要第三方提供商。考虑到这一点，您可能最好从单词go使用Oracle数据提供程序.Net（ODP.NET） - 这具有不在Microsoft类中的优化。还有其他第三方选择，但甲骨文对保持.Net开发人员的强大既得利益，所以他们应该是好的。

#1