Oracle sequences and ADO .Net. A few days ago, I twisted my brain on a tricky problem. The point was « the tables in the database have triggers witch inserts a sequence value when inserting a row. The client application uses a dataset and datatables objects with a master-detail relation which requires the primary key to be stored in the datatable objects »

That is not a brand new problem. The sequence mechanism is database oriented. That is a good idea. When there are numerous users, it is better to use a centralized id generator. But, an advanced cache oriented data access API like ADO .NET needs to manage this unique identifier without synchronizes the entire dataset.

So, last week, I desperately try to build a master/detail dataset model that does not need to know the primary keys of new entries. That was not an elegant solution. Neither it is to use the ADO .net autoId system (as it is client side oriented).

I have founded the following solution.

Let us have a table named EMP with the following fields:
ID_EMP Number(6)
EMP_NAME VARCHAR2(100)

..And the following sequence:

CREATE SEQUENCE PSI_ADMIN.S_EMP
START WITH 1
INCREMENT BY 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;



A typical way to retrieve and manipulate values in .Net is to build dataAdapter object with the insert, delete, update and select Command.

For instance:

OleDbDataAdapter da = new OleDbDataAdapter() ;
da.SelectCommand.CommandText= "SELECT ID_EMP,EMP_NAME FROM EMP"
da.UpdateCommand.CommandText= "UPDATE EMP SET EMP_NAME= ? WHERE ID_EMP= ?" ;
da.UpdateCommand.Parameters.Add("EMP_NAME",OleDbType.VarChar,100,"EMP_NAME");
da.UpdateCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");
da.InsertCommand.CommandText= "INSERT INTO EMP(ID_EMP,EMP_NAME) VALUES(?,?)";
da.InsertCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");
da.InsertCommand.Parameters.Add("EMP_NAME",OleDbType.VarChar,100,"EMP_NAME");
da.DeleteCommand.CommandText= "DELETE EMP WHERE ID_EMP= ?" ;
da.DeleteCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");

da.SelectCommand.Connection = cnx ;
da.UpdateCommand.Connection = cnx ;
da.InsertCommand.Connection = cnx ;
da.DeleteCommand.Connection = cnx ;

….where “cnx” is a valid OLEDB connection to the Oracle database.
The "da" object may be use for instance to fill a dataset that can be itself manipulated by a datagrid:
…
da.Fill(ds,"EMP");
…

When synchronizing:
…
da.Update(ds,"EMP");
…
…where "ds" is a valid dataset.

This code will works fine as long as you can provide a valid ID_EMP.
If you want to use the Oracle sequence, and that is my point, you need something more.
You need to handle the “update” event and retrieve the sequence’s next Val from Oracle.

Here is how I implement this:
First, handle the event in the dataAdapter’s construction:
…
da.RowUpdating +=new OleDbRowUpdatingEventHandler(da_RowUpdating);
..
In addition, somewhere else in the class, put the following method:
private void da_RowUpdating(object sender, OleDbRowUpdatingEventArgs e)
{
// Is it an insertion ?
if (e.StatementType == StatementType.Insert)
da.InsertCommand.Parameters["ID_EMP"].Value= (new OleDbCommand("select S_EMP.nextval from Dual",cnx).ExecuteScalar());

}

When the dataAdapter’s Update method is called, this event fire for each new, deleted or updated row, just before the SQL action is performed.
If the action’s type is “insert” the sequence‘s next Val replace the current id_emp parameter.

That’s all folks!

Hope this helps.

Comments are welcome.  ¶ 8:58 AM