Spring DAO Equivalent of Using ArrayBinding Feature in ODP.NET Provider

[vsarathy]

Hi,

I have a .NET/C# application that tries to insert close to 3 Milliion records into a database. We are using Oracle 9i. The data access component I use for the inserts has been implemented using the AdoTemplate and the ICommandCallback classes in Spring Framework.

While using this approach, there does not seem to be a way to insert a whole array of data in a single execution of command.ExecuteNonQuery() statement. You can do this if you are directly using the classes provided by ODP.NET provider using the "array binding" feature as follows:

String[] arrayOfValues = ..
OracleParameter param = new OracleParameter ();
param.Type = OracleDbType.VarChar2;
param.Value = arrayOfValues;
OracleConnention connection = ..
OracleCommand command = ..
command.Connection = connection;
command.CommandType = CommandType.Text;
command.ArrayBindCount = arrayOfValues.Length;
command.ExecuteNonQuery ();


Is there an equivalent to the above while using the DAO abstractions provided by Spring Framework. I only seem to be able to set a scalar value to the IDbDataParameter.Value property.
As far as I can see, even the classes in System.Data.OracleClient in .NET SDK do not seem to support this.

Any help is greatly apppreciated.

Viji

[Mark Pollack]

Hi,

Yes, the Microsoft data provider doesn't support that feature. It seems you are down the right path, using the ICommandCallback. I'm not sure what you mean by the question "Is there an equivalent to the above while using the DAO abstractions provided by Spring Framework". I guess you mean a high level 'one-liner' method? I don't see a good match because you need to call the oracle specific method ArrayBindCount on the command. In your callback implementation you should downcast to the specific oracle command type, i.e.


public object DoInCommand(IDbCommand command)
{
OracleCommand cmd = command as OracleCommand;

// use oracle specific methods.

return cmd.ExecuteNonQuery();
}


This still gives you the benefits of particpation in delcarative transactions, not having to worry about any resource management code, exception translation, etc. If you want to encapsulate more of these special settings with a resuable code artifact, you can create an instance of IDbCommandCreator and use the corresponding execute method.

Hope this helps.
Cheers,
Mark