Database update

[9727604]

Hi all,
I have an oracle 9i database and from it I fill a dataset using an OracleDataAdapter. I am pulling information from credit card numbers from multiple tables, encrypting them and the I want to write the values back to the various tables. I am having a problem with my update.
The code to read from the database is:

// Connect to Database
OracleConnection oCon = new OracleConnection
("Data source = DATABASE; user id = Username; password = pass");

oCon.Open();

// Command Object
OracleCommand oCmd = new OracleCommand
("PKG_MultiResultset.Read_Write",oCon);

// Stored Procedure
oCmd.CommandType = CommandType.StoredProcedure;

// Create Parameter Object
oCmd.Parameters.Add(new OracleParameter
("Cardcur",OracleType.Cursor)).Direction = ParameterDirection.Output;

oCmd.Parameters.Add(new OracleParameter
("TransactionCur",OracleType.Cursor)).Direction = ParameterDirection.Output;

// Instatiate Dataset
DataSet Ds = new DataSet();

// Instatiate Data Adapter
OracleDataAdapter oAdp = new OracleDataAdapter(oCmd);

OracleCommandBuilder custCB = new OracleCommandBuilder(oAdp);

// Fill Dataset
oAdp.Fill(Ds);

I now modify the data and I have verified that this is correct. The problem is I don't know how to update multiple tables. Thank you

 

[obislavu]

As I can see, your DataSet contains only a DataTable object which is populated with the records returned by a stored procedure.
These records are comming from many tables which can be on the same database or different datbases e.g.
The DataSet contains the result of a select like:
"select t1.col1 as field1, t1.col2 as field2 , t2.col1 as field3, t2.date as expdate, t3.col11 from Table1 t1, Table2 t2, Table3 t3 where ...";
So, the DataSet.Tables[0] contains the all records with the field1, field2, field3, expdate, col11 ...
Now you modify the DataSet object e.g. the Tables[0] object and you want to reflect these changes back in the DB.
If that is right you have few solutions:
1.Implement handlers for the ColumnChanged, RowChanged or RowDeletedIterate and there use call stored procedure to performs the updates in the databse.
That means the database get in sync with every change you do in the DataSet.
2. Iterate through DataSet.tables[0].Rows collection and for each DataRow check the DataRowState ( Added, Modified, Deleted, Unchanged) and take action to update back the databse by calling a stored procedure to do this job.
Using stored procedures is powerful because you can initiate triggers, call other stored procedures, functions etc...

Another way is another design e.g. to create DataRelation object to establish the relationships between the tables loaded in the DataSet (as exist in the database) and at the end you could use the DataAdapter/CommandBuilder and Update() to put back the changes.
-obislavu-