Converting from DbCommand object to OracleCommand object

Question

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

Why were you asked to change it? You should've been able tojust change the config file to use Oracle. — CAbbott
– CAbbott, Commented Sep 18, 2009 at 20:37
The Microsoft.Practice library is not a standard library used here, so the build engine does not have it.Because of this, the project cannot be built on the server. — zohair
– zohair, Commented Sep 18, 2009 at 20:50
You probably have to install Enterprise Library on your web-server to make it work. — Juri
– Juri, Commented Sep 19, 2009 at 16:48

Justin Grant · Accepted Answer · 2009-09-19 17:04:06Z

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

edited Sep 19, 2009 at 17:04

answered Sep 18, 2009 at 20:55

Justin Grant

47.2k17 gold badges135 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zohair Over a year ago

So what would be the equivalent of AddInParameter if I am using OracleClient?

Justin Grant Over a year ago

I'd create the parameter separately, set ParameterDirection.ReturnValue, and then and use Parameters.Add(). See above-- I edited the answer to show a code sample.

Juri · Accepted Answer · 2009-09-19 16:47:36Z

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"

Collectives™ on Stack Overflow

Converting from DbCommand object to OracleCommand object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related