ODP.NET

All,

I keep getting a concurrency issue when attempting to delete using an OracleDataAdapter and a Stored Procedure on the Oracle side.

I'm using ODP to batch updates to my database. I have stored procedures for update, insert, and deleting tables.

Since I have related data-tables in my ADO.Net dataset, I'm micro-managing the order of adds, updates, and deletes (starting with deletes first).

Here is my code on the Oracle Server

<h3>[Package]</h3>
create or replace
package body pk_update as
...
procedure sp_del_act_MyTable
(
MyTableID in number
);
---
end pk_update;
<h3>[Package]</h3>

<h3>[Package Body]</h3>
create or replace
package body pk_update as
...
procedure sp_del_MyTable
(
MyTableID in number
) as
Begin
DELETE FROM
MYSCHEMA.MYTABLE WHERE MyID = MyTableID;
end sp_del_Actheader;
...
<h3>[Package Body]</h3>

<h3>[C# Code]</h3>
using System;
using System.Data;
using Oracle.DataAccess.Types;

public void UpdateData(ref DataTable dtMyTable)
{
DataView dvMyTable = new DataView(dtMyTable);
dvMyTable.RowStateFilter = DataViewRowState.Deleted //using dvMyTable.Count to determine if there are records before calling GetChanges below.
if (dvMyTable.Count>0)
{
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.DataSource = CustomOracleDataObjectsLib.Properties.Settings.Default.ConnDataSource;
ocsb.UserID = CustomOracleDataObjectsLib.Properties.Settings.Default.ConnUsrID;
ocsb.Password = CustomOracleDataObjectsLib.Properties.Settings.Default.ConnPW;
OracleConnection oc = new OracleConnection(ocsb.ConnectionString);
oc.Open();

ocmDel = new OracleCommand("pk_update.sp_del_MyTable", oc);
ocmDel.CommandType = System.Data.CommandType.StoredProcedure;
ocmDel.Parameters.Add("MyTableID", OracleDbType.Int32, ParameterDirection.Input);
ocmDel.UpdatedRowSource = UpdateRowSource.None; //no output parameters anyway
ocmDel.CommandTimeout = 30; //nice round number, eh
OracleDataAdapter OrAdapter = new OracleDataAdapter();
OrAdapter.DeleteCommand = _ocmDel;
OrAdapter.UpdateBatchSize = dvMyTable.Count;
OrAdapter.Update(dtMyTable.GetChanges(DataRowState.Deleted)); //ERROR. Note that I've also tried using DataRow[] Objects, but when doing so, I still get a concurrency error, and when exploring the DataRow[] Object, I get a DeletedRowInaccessable error.
...
}
}
<h3>[C# Code]</h3>
So my question is what would be the best way to tackle this issue. I'm thinking that the concurrencery issue seems to be related to OracleDataAdapter's handling of any rows with DataRowState.Deleted (i.e. related to the DeletedRowInaccessable exception), but if that's the case, then will I still be able to use OracleDataAdapters or will I need to simply execute the delete command using ocmDel.executenonquery() explicitly after fetching an array of MyTableID parameters (which I hope I don't have to do, as this would mean rewriting some of my code and having to manually iterate through dataviews, etc., not fun).

I should also point out that the SP works when executed from SQL Developer, so I know it's not an issue with the SP itself.

Thanks in advance.

Edited by: 929933 on Apr 24, 2012 12:05 PM