Database.ExecuteNonQuery() doesn't work with ODP.NET (Oracle.DataAccess.dll)???

Topics: Building and extending application blocks, Data Access Application Block
Nov 5, 2007 at 2:14 PM
I have extended the Microsoft Enterprise Library to create a provider for Oracle using ODP.NET (Oracle.DataAccess.dll) and Ent Lib 2.0's abstract class called Database which has ExecuteNonQuery(DbCommand command).

The probem here is when I call ExecuteNonQuery and pass the OracleCommand (ODP.NET in Oracle.DataAccess.dll) it works but NULLs are inserted into the Database. This is a strange behaviour of Database.ExecuteNonQuery(). For this to work I had to override the ExecuteNonQuery method and in that I had to create another new instance of OracleCommand in ExecuteNonQuery and then call OracleCommand.ExecuteNonQuery(). This is nothing different that what the ExecuteNonQuery in Ent Lib the base class (Database).

Can you help me understand whats the issue with Database.ExecuteNonQuery() here why its behaviour is strange with used with OracleCommand in ODP.NET?

Thanks in advance ,
Nilesh
Nov 6, 2007 at 11:45 AM
Hi Nilesh,

Can you describe how you implemented your db class, and be more precise about the unexpected behavior? All inserted values are null, or just some? How do you configure the OracleCommand to make it work (ie how are you handling parameters)?

Fernando
Nov 6, 2007 at 2:49 PM


fsimonazzi wrote:
Hi Nilesh,

Can you describe how you implemented your db class, and be more precise about the unexpected behavior? All inserted values are null, or just some? How do you configure the OracleCommand to make it work (ie how are you handling parameters)?

Fernando



Hi Fernando,

I extened the Ent Lib abstaract class called Database. I can't attach the entire source here since there is no provision fot that but I will give a idea and sample code

public class OracleDatabaseODP : Database
{
// This does not override the ExecuteNonQuery of the base class so it should call Databse.ExecuteNonQuery if user calls OracleDatabaseODP.ExecuteNonQuery()
}

This call (OracleDatabaseODP.ExecuteNonQuery()) successfuly completes but it insert Null's for all the columns values. However if I override the ExecuteNonQuery () and implement it something like this it works and inserts records successfully.

public override int ExecuteNonQuery(DbCommand command) // Here incomming is also an OracleCommand
{
using (OracleCommand objCmd=new OracleCommand()) // This is the weired resolution that I had to implement and create a new OracleCommand even when it's not required
{
// Pass all the properties of the incomming command (OracleCommand) to objCmd
num = objCmd.ExecuteNonQuery();
}
}

Hope this explains. If you can share you mail Id I can send you the src so that you can understand it well in case this doesn't sufficie.

Thnaks,
Nilesh




Nov 6, 2007 at 8:01 PM
Got the same pb, all parameters of string type are null with ExecuteNonQuery() here is the dump from SQL monitor (included in TOAD), it shows that it come from the client for sure
----------------------------------
Timestamp: 21:51:15.828
INSERT INTO HR.TABLE1
(ID,
CHAINE)
VALUES
(:Id,
:Chaine)
:1 = 1
:2 = <NULL>


The code is this one :
database.AddInParameter(commandWrapper, ":Chaine", DbType.String, entity.Chaine );
results = Utility.ExecuteNonQuery(transactionManager, commandWrapper);

The ':' parameters identificator has no effect here

I really dont understand what's happening

By the way I use ODP.NET v2.0.50727

Thx

Axel
Nov 22, 2007 at 1:38 PM
Edited Nov 22, 2007 at 1:51 PM
Hi Nilesh,

First of all, I apologize for the delay in getting back to you.

I tracked down the problem to the default initialization of the parameter. The Database class implements the intialization like this:

protected virtual void ConfigureParameter(DbParameter param, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
param.DbType = dbType;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
param.Direction = direction;
param.IsNullable = nullable;
param.SourceColumn = sourceColumn;
param.SourceVersion = sourceVersion;
}

The default value set for size is zero, but it looks like setting param.Size to zero on an OracleParameter will change the string to null. Now that I think of it, strings with zero chars do equal to null in Oracle...

Anyway, you can override this initialization in your ODP database to avoid setting the size value when it's equal to the current value for the Size property. Note that the value for the Size property on a brand new OracleParameter will read zero; it looks like forcing to zero is what causes the problem.

I really hope this helps,
Fernando
Nov 22, 2007 at 5:13 PM
thx I'll give a try tomorrow