Issue Calling Oracle Function

Topics: Data Access Application Block
Dec 24, 2010 at 9:11 AM
Edited Dec 24, 2010 at 9:13 AM

When I call function with the below mentioned code always the return parameter value is Null.

Method#1

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("TestDB");
string sql = "Get_User_Name";
DbCommand _sp1 = db.GetStoredProcCommand(sql);
DbParameter _p1 = _sp1.CreateParameter();
_p1.DbType = DbType.String;
_p1.Direction = ParameterDirection.Input;
_p1.Value = "102";
_sp1.Parameters.Add(_p1);
DbParameter _p2 = _sp1.CreateParameter();
_p2.DbType = DbType.String;
_p2.Direction = ParameterDirection.ReturnValue;
_sp1.Parameters.Add(_p2);

db.ExecuteNonQuery(_sp1);
Console.WriteLine("Value Returned is : " + _p2.Value.ToString());
Method #2

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("TestDB");
string sql = "Get_User_Name";
DbCommand _sp1 = db.GetStoredProcCommand(sql);
db.AddInParameter(_sp1, "IN_PARAM", DbType.Int32, 10);
Oracle.DataAccess.Client.OracleParameter _rv = new Oracle.DataAccess.Client.OracleParameter();
_rv.ParameterName = "Rvalue";
_rv.Direction = ParameterDirection.ReturnValue;
_sp1.Parameters.Add(_rv);
db.ExecuteNonQuery(_sp1);
Console.WriteLine("Value Returned is : " + _rv.Value.ToString());

If I call the same function through ExecuteScalar method it return appropriate value.

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("TestDB");
db.ExecuteScalar(CommandType.Text, "Select Get_User_Name('1') from dual").ToString())

Could please help me out why the return parameter value is Null

 

Using EL 5.0 with  Oracle DB.

Dec 26, 2010 at 12:36 AM
Edited Dec 26, 2010 at 12:40 AM

The ExecuteNonQuery method only returns the number of rows affected not the value you specified as the return value, the ExecuteScalar does that.  That is the behavior using plain ADO.NET.

By the way, the EntLib way of adding parameters is to use the AddInParameter method of the Database object instead of adding it to the stored procedure's Parameters property.

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Dec 27, 2010 at 3:35 AM

Can you share the EL 5.0 sample to call a function with return parameter?

Dec 27, 2010 at 9:46 AM
Edited Dec 27, 2010 at 9:48 AM

Issue got resolved after adding the Return parameter as first parameter..!

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("TestDB");
string sql = "Get_User_Name";
DbCommand _sp1 = db.GetStoredProcCommand(sql);
DbParameter _p2 = _sp1.CreateParameter();
_p2.DbType = DbType.String;
_p2.Direction = ParameterDirection.ReturnValue;
_p2.Size = 3000;
_sp1.Parameters.Add(_p2);


DbParameter _p1 = _sp1.CreateParameter();
_p1.DbType = DbType.String;
_p1.Direction = ParameterDirection.Input;
_p1.Value = "102";
_sp1.Parameters.Add(_p1);