ODP.NET

I've been searching all over the net for an answer to my question, but can't seem to find an answer. I'm trying to use an OracleDataAdapter to insert a row from a DataTable and return the primary key value generated by a sequence. Any suggestions on getting this to work would be greatly appreciated.

Here's my Table Definition (DESC STMT_MSG)

Name Null? Type
----------------------------------------- ---------- -----------------
STMT_MSG_ID NOT NULL NUMBER(38)
CREATE_DT NOT NULL DATE
CREATE_PROG_NM NOT NULL VARCHAR2(30)
BLOCK_ID NUMBER(38)
APPL_ID_TYPE_CD VARCHAR2(1)
SVC_ID VARCHAR2(2)
LOGICAL_TERMNL_ADDR VARCHAR2(12)
SESSION_NBR VARCHAR2(4)
HEADER_SEQ_NBR VARCHAR2(6)
APP_BLOCK_ID VARCHAR2(2)
APPL_INPUT_OUTPUT_IND VARCHAR2(1)
MSG_TYPE_CD VARCHAR2(3)
INPUT_TM NUMBER(38)
MSG_INPUT_REF VARCHAR2(28)
OUTPUT_DT DATE
OUTPUT_TM NUMBER(38)
MSG_PRIORITY_TYPE_CD VARCHAR2(1)
USER_BLOCK_ID VARCHAR2(2)
BANK_PRIORITY_TYPE_CD VARCHAR2(8)
MSG_USER_REF VARCHAR2(20)
BODY_BLOCK_ID VARCHAR2(2)
TXN_REF_NBR VARCHAR2(16)
TXN_REF_RELATED_NBR VARCHAR2(16)
ACCT_ID VARCHAR2(35)
STMT_NBR NUMBER(38)
SEQ_NBR VARCHAR2(5)
OPEN_FINAL_INTRMT_IND VARCHAR2(1)
OPEN_BAL_AMT NUMBER(15,2)
OPEN_BAL_DEBT_CREDT_IND VARCHAR2(1)
OPEN_BAL_DT DATE
OPEN_BAL_CURNCY_TYPE_CD VARCHAR2(3)
CLOSE_FINAL_INTRMT_IND VARCHAR2(1)
CLOSE_BAL_DEBT_CREDT_IND VARCHAR2(1)
CLOSE_BAL_DT DATE
CLOSE_BAL_CURNCY_TYPE_CD VARCHAR2(3)
CLOSE_BAL_AMT NUMBER(15,2)
AVAIL_BAL_DEBT_CREDT_IND VARCHAR2(1)
AVAIL_BAL_DT DATE
AVAIL_BAL_CURNCY_TYPE_CD VARCHAR2(3)
AVAIL_BAL_AMT NUMBER(15,2)
FORWD_AVAIL_DEBT_CREDT_IND VARCHAR2(1)
FORWD_AVAIL_BAL_DT DATE
FORWD_AVAIL_BAL_CURNCY_TYPE_CD VARCHAR2(3)
FORWD_AVAIL_BAL_AMT NUMBER(15,2)
SUMRY_INFO_TXT VARCHAR2(400)
SWIFT_TRAILER_BLOCK VARCHAR2(100)
MSG_FILE_NM VARCHAR2(128)

Here's my code (This is a proof of concept, so I'm filling a datatable with an existing record, the zeroing out the primary key so that I can test out the code. Once I have this working I'll work it into my application code).

class Program
{
static void Main(string[] args)
{

//Get a data row that we can use to test our inserts with
DataTable dt = OracleUpdater2.load_it();

//Set the primary key to 0 so we can insert as if it were a new row (I've also tried DBNull.Value here)
dt.Rows[0]["stmt_msg_id"] = 0;
dt = OracleUpdater2.update_datasets_return(dt);
}
}



public class OracleUpdater2
{
private static OracleConnection cn;
private static OracleDataAdapter da = new OracleDataAdapter();
private static OracleConnection conn = new OracleConnection();
private static OracleTransaction transaction = null;

public static DataTable load_it()
{

string connectionString = "Data Source=dwdx;User Id=CAMS;Password=camsdev$10";
string selectSql = "select * from swift_stmt_msg where stmt_msg_id = 5425";
string tableName = "swift_stmt_msg";

conn = new OracleConnection(connectionString);
conn.Open();
da = new OracleDataAdapter(selectSql, conn);
OracleCommandBuilder bldr = new OracleCommandBuilder(da);
DataTable dt = new DataTable(tableName);
da.Fill(dt);
conn.Dispose();
return dt;
}

public static DataTable update_datasets_return(DataTable dt)
{
string selectSql = null;
cn = new OracleConnection("Data Source=dwdx;User Id=CAMS;Password=camsdev$10");
try
{
cn.Open();
}
catch (Exception ex)
{
throw new ArgumentException(" Error: connection lost." + ex.Message);
}

selectSql = "select * from stmt_msg where stmt_msg_id = 0";
da = new OracleDataAdapter(selectSql, cn);
OracleCommandBuilder bldr = new OracleCommandBuilder(da);
da.RowUpdated += new Oracle.DataAccess.Client.OracleRowUpdatedEventHandler(da_RowUpdated);

OracleCommand insertCommand = null;
try
{
insertCommand = (OracleCommand)(bldr.GetInsertCommand());
}
catch (Exception ex)
{
throw new Exception("" + ex.Message);
}

//Get rid of the primary key's parameter since we're going to be using an output parameter to get that back from the sequence.
string keyColumn = insertCommand.Parameters[0].ParameterName;
string sourceColumn = insertCommand.Parameters[0].SourceColumn;
insertCommand.CommandText = insertCommand.CommandText.Replace(keyColumn, "stmt_msg_s01.nextval");
insertCommand.Parameters.Remove(insertCommand.Parameters[0]);

//Add a RETURNING clause to get the sequence generated for our primary key (stmt_msg_id)
insertCommand.CommandText += " RETURNING " + sourceColumn + " INTO :seqno";

//Add the parameter to get the sequence generated for us
OracleParameter p = new OracleParameter();
p.ParameterName = ":seqno";
p.SourceColumn = "STMT_MSG_ID";
p.OracleDbType = OracleDbType.Int32;
p.Direction = ParameterDirection.Output;
insertCommand.Parameters.Add(p);

da.InsertCommand = insertCommand;

//If the primary key initial value is 0 I get ORA-02292: integrity constraint. If it's null, I get a ORA-01407 cannot update to NULL error.
try
{
transaction = cn.BeginTransaction();
da.Update(dt);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
cn.Close();
cn.Dispose();
throw new ArgumentException(" Error: update_datasets_return " + ex.Message);
}

cn.Close();
cn.Dispose();
return dt;
}

public static void da_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
{
for (int i = 0; i < e.Row.Table.Columns.Count; i++)
{
if(e.Row[i] != DBNull.Value)
System.Diagnostics.Debug.WriteLine(string.Format("{0} - {1}", e.Row.Table.Columns.ColumnName, e.Row[i].ToString()));
else
System.Diagnostics.Debug.WriteLine(string.Format("{0} - DBNull", e.Row.Table.Columns[i].ColumnName));
}
}
}


If I remove the primary key column from the data table altogether the insert appears to work, but nothing comes back in the :seqno parameter, and my row doesn't exist in the database table.

Any suggestions? BTW, code very similar to this works just fine with System.Data.OracleClient. In that scenario I'm not using the command builder, as I understand it won't work on an empty table.