.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.
 
Thursday, February 10, 2005
  Back to work
Hello everybody!
Waow! I didn't post for about 20 days! Sorry about this. I wasn't able to post because I was here and also there, I've also been to this place and this one.
Oups! I nearly forget this island and this one.

Sorry, that's hard to post code sample today.

 
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

Go to wordpress
When does the next bus pass?
Thanks
googled2cd966929769ab9
two lines in the datagrid header
Context saving with persistent datasets
.net webservice session
Winform, Web Services & credential
back to work
self description
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr