oracle 从 DbCommand 对象转换为 OracleCommand 对象

Converting from DbCommand object to OracleCommand object

提问by zohair

I have inherited a function in an ASP.NET (C#) application where the author used the Microsoft.Practices.EnterpriseLibrary.Data library, but I have been asked to change it so that it uses System.Data.OracleClient. This function uses a stored procedure form a database. itemName, and openDateare string parameters that the function takes in. PKG_AUCTION_ITEMS.IsAuctionItemis the stored procedure function name.

我在 ASP.NET (C#) 应用程序中继承了一个函数，作者在Microsoft.Practices.EnterpriseLibrary.Data library其中使用了System.Data.OracleClient. 此函数使用存储过程形成数据库。itemName, 和openDate是函数接受的字符串参数。PKG_AUCTION_ITEMS.IsAuctionItem是存储过程函数名。

Here is the code that I received:

这是我收到的代码：

    string result = String.Empty;

    Database db = DatabaseFactory.CreateDatabase("OraData");
    using (DbCommand cmdDB = db.GetStoredProcCommand("PKG_AUCTION_ITEMS.IsAuctionItem"))
    {
        db.AddInParameter(cmdDB, "vItemName", DbType.String, itemName);
        db.AddInParameter(cmdDB, "vOpenDate", DbType.String, openDate);
        db.AddParameter(cmdDB, "ret", DbType.String, 2, ParameterDirection.ReturnValue, false, 0, 0, null, DataRowVersion.Current, null);


        db.ExecuteNonQuery(cmdDB);
        result = cmdDB.Parameters["ret"].Value.ToString();
    }

Here is my code:(connstris the connection string)

这是我的代码：（connstr是连接字符串）

    string result = String.Empty;
    OracleConnection conn = new OracleConnection(connstr);
    OracleCommand cmd = new OracleCommand("PKG_AUCTION_ITEMS.IsAuctionItem",conn);
    myCmd.CommandType = CommandType.StoredProcedure;

    using (myCmd)
    {
        myCmd.Parameters.AddWithValue("vItemName", itemName);
        myCmd.Parameters.AddWithValue("vOpenDate", openDate);
        myCmd.Parameters.AddWithValue("ret", ???);
        myCmd.ExecuteNonQuery();
        result = myCmd.Parameters["ret"].Value.ToString();
    }

I do not understand what the difference between AddInParameter and AddParameter is, and what this line does:

我不明白 AddInParameter 和 AddParameter 之间的区别是什么，以及这一行的作用：

db.AddParameter(cmdDB, "ret", DbType.String, 2, ParameterDirection.ReturnValue, false, 0, 0, null, DataRowVersion.Current, null);

Am I on the right track? Can anyone please help? Thanks

我在正确的轨道上吗？有人可以帮忙吗？谢谢

采纳答案by Justin Grant

db.AddParameter adds an output parameter in this case. You need to let the db client library know that you're looking to get the return value back from the sproc call. Hence the call to AddParameter. db.AddInParameter adds a parameter which is only an in-parameter. In the It's a shortcut for db.AddParameter using ParameterDirection.Input. See http://blogs.x2line.com/al/archive/2006/03/25/1579.aspxfor a discussion of AddInParameter vs. AddParameter.

在这种情况下，db.AddParameter 添加了一个输出参数。您需要让 db 客户端库知道您希望从 sproc 调用中获取返回值。因此调用 AddParameter。db.AddInParameter 添加一个参数，它只是一个内参数。在它是使用 ParameterDirection.Input 的 db.AddParameter 的快捷方式。有关AddInParameter 与 AddParameter 的讨论，请参阅http://blogs.x2line.com/al/archive/2006/03/25/1579.aspx。

Similarly, using OracleClient, AddWithValue is like AddInParameter-- a shortcut to use for input params when you already know the value. Since the return value is, by definition, an output parameter, you can't use AddWithValue. You need to use Parameters.Add() instead.

类似地，使用 OracleClient，AddWithValue 就像 AddInParameter--当您已经知道值时用于输入参数的快捷方式。由于根据定义，返回值是一个输出参数，因此您不能使用 AddWithValue。您需要改用 Parameters.Add()。

Now, back to your main question: what's the equivalent code using OracleClient. It's something like this:

现在，回到您的主要问题：使用 OracleClient 的等效代码是什么。它是这样的：

string result = String.Empty;
OracleConnection conn = new OracleConnection(connstr);
OracleCommand cmd = new OracleCommand("PKG_AUCTION_ITEMS.IsAuctionItem",conn);
myCmd.CommandType = CommandType.StoredProcedure;

using (myCmd)
{
    myCmd.Parameters.AddWithValue("vItemName", itemName);
    myCmd.Parameters.AddWithValue("vOpenDate", openDate);

    // depending on whether you're using Microsoft's or Oracle's ODP, you 
    // may need to use OracleType.Varchar instead of OracleDbType.Varchar2.
    // See http://forums.asp.net/t/1002097.aspx for more details.
    OracleParameter retval = new OracleParameter("ret",OracleDbType.Varchar2,2);
    retval.Direction = ParameterDirection.ReturnValue;
    myCmd.Parameters.Add(retval);

    myCmd.ExecuteNonQuery();
    result = myCmd.Parameters["ret"].Value.ToString();
}

回答by Juri

We actually do the configuration of the parameters more explicitly, something like this

我们实际上更明确地做参数的配置，像这样

System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand("PACKAGE_NAME.STORED_NAME");
command.CommandType = System.Data.CommandType.StoredProcedure;

System.Data.OracleClient.OracleParameter param;
param = new System.Data.OracleClient.OracleParameter("PARAM_NAME_ID", System.Data.OracleClient.OracleType.Number);
param.Value = id;
command.Parameters.Add(param);

param = new System.Data.OracleClient.OracleParameter("PARAM_NAME_RETURN_COUNT", System.Data.OracleClient.OracleType.Number);
param.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(param);
...

You see, there is a property direction which we explicitly assign for the parameter that is being returned. The first gets the value of a variable "id" and is a parameter that gets passed TO the stored procedure.

您会看到，我们为正在返回的参数明确分配了一个属性方向。第一个获取变量“id”的值，并且是传递给存储过程的参数。

The 2nd one is being returned by the stored procedure, therefore no value is assigned to that parameter value and the direction is set to "System.Data.ParameterDirection.Output"

第二个正在由存储过程返回，因此没有值分配给该参数值，方向设置为 "System.Data.ParameterDirection.Output"