Converting from DbCommand object to OracleCommand object

Question

Hi, 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 openDate are string parameters that the function takes in. PKG_AUCTION_ITEMS.IsAuctionItem is the stored procedure function name.

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:(connstr is the connection string)

    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:

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

Answer 1

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.aspx for a discussion of AddInParameter vs. AddParameter.

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.

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

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();
}

Answer 2

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.

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"