.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Tuesday, February 22, 2005
  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.
 
Comments:
Is'nt there any concurency problem with this solution? I mean .. can not the sequence number be incresed by inserting second row between creating the first one and selecting its primary key value? Sorry if it's complicated .. and for my english :)
 
Ok, I just read this comment, about 2 years after it has been posted.
First if you have a question , try in french.
Second. I'm sure it works fine as long as the idemp is valid. A sequence mechanism is supposed to bring a valid one and will not bring two times the same even to two different and concurrent threads.
regarding your point I understand that you are afraid of concurrency. But You shoudln't because there is no concurrent access whith this mechanism.

Of course if another process try to insert data without using the sequence but with a dirty thing like "select max(ident)+1 from emp"
It won't work but, it is exactly what I'm trying to avoid.
 
Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

Back to work
Code generation and self description
links
Oracle data dictionary
Code generation with .net
Change all querytable in an Excel File
My Wiki contributions
WIKIWIKI
Connect a .Net application to an Oracle Database 1...
Mandelbrot in C# , the sources!
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr