Using OleDbDataAdapter Update with InsertCommands and getting blocking locks on Oracle table

the following code snippet shows use of oledbdataadapter insertcommands.  code is producing many inserts on oracle table , suffering contention... on same table.  how oledbdataadapter produce inserts dataset... characteristics these inserts inherent in terms of batch behavior... or naturally contend same resource. 

oc.open();
(int = 0; < ximageid.count; i++)
{

...

// create oracle adapter using sql not return actual rows structure
oledbdataadapter da =
   new oledbdataadapter("select business_unit, invoice, assignment_id, end_dt, ri_timecard_id, image_id, filename, barcode_label_id, " +
   "direct_invoicing, exclude_flg, dttm_created, dttm_modified, image_data, process_instance sysadm.ps_ri_inv_pdf_merg 1 = 2", oc);

// create data set
dataset ds = new dataset("documents");
da.fill(ds, "documents");

// loop through invoices , write oracle
string[] sinvoices = invoicenumber.split(',');
foreach (string sinvoice in sinvoices)
{
    // create data set row
    datarow dr = ds.tables["documents"].newrow();

    ... map data

    // populate dataset
    ds.tables["documents"].rows.add(dr);
}

// create insert command

string insertcommandtext =
    "insert /*+ append */ ps_table " +
    "(seq_nbr, business_unit, invoice, assignment_id, end_dt, ri_timecard_id, image_id, filename, barcode_label_id, direct_invoicing, " +
    "exclude_flg, dttm_created, dttm_modified, image_data, process_instance) " +
    "values (inv.nextval, :business_unit, :invoice, :assignment_id, :end_dt, :ri_timecard_id, :image_id, :filename,  " +
    ":barcode_label_id, :direct_invoicing, :exclude_flg, :dttm_created, :dttm_modified, :image_data, :process_instance)";

// add insert command data adapter
da.insertcommand = new oledbcommand(insertcommandtext);
da.insertcommand.connection = oc;

// add params insert
da.insertcommand.parameters.add(":business_unit", oledbtype.varchar, 5, "business_unit");
da.insertcommand.parameters.add(":invoice", oledbtype.varchar, 22, "invoice");
da.insertcommand.parameters.add(":assignment_id", oledbtype.varchar, 15, "assignment_id");
da.insertcommand.parameters.add(":end_dt", oledbtype.date, 0, "end_dt");
da.insertcommand.parameters.add(":ri_timecard_id", oledbtype.varchar, 10, "ri_timecard_id");
da.insertcommand.parameters.add(":image_id", oledbtype.varchar, 8, "image_id");
da.insertcommand.parameters.add(":filename", oledbtype.varchar, 80, "filename");
da.insertcommand.parameters.add(":barcode_label_id", oledbtype.varchar, 18, "barcode_label_id");
da.insertcommand.parameters.add(":direct_invoicing", oledbtype.varchar, 1, "direct_invoicing");
da.insertcommand.parameters.add(":exclude_flg", oledbtype.varchar, 1, "exclude_flg");
da.insertcommand.parameters.add(":dttm_created", oledbtype.date, 0, "dttm_created");
da.insertcommand.parameters.add(":dttm_modified", oledbtype.date, 0, "dttm_modified");
da.insertcommand.parameters.add(":image_data", oledbtype.binary, system.convert.toint32(filedata.length), "image_data");
da.insertcommand.parameters.add(":process_instance", oledbtype.varchar, 10, "process_instance");

// update table
da.update(ds, "documents");

...

}

every datarow has state property.  when add new row gets marked added, when update a value in existing row gets marked modified. when changes sent db enumerates rows , calls insert or update commands accordingly.  each command executed based upon row ordering in table , not done batch. default if row fails subsequent rows not updated.

order matter in update.  example if update existing row fk row going adding can run issues update applied before insert.  work around in cases important need retrieve rows add (based upon state) , pass them update first.  can apply remaining updates.  msdn has more information on process.

beyond that, in general shouldn't have issues inserting or updating multiple rows @ once.  if running blocking issues around triggers or joined tables causing problem.  you'll need profile database determine blocking.

michael taylor
http://blogs.msmvps.com/p3net

Visual Studio Languages  ,  .NET Framework  >  Visual C#