none
Working with ID Columns, AutoIncrement and Oracle Sequences RRS feed

  • Question

  • Dear all,

    I have been wondering about this for quite a while now.
    please imagine you have the following DataSet :

    Table1
    *id (Int32)------------\
    *name |
    |
    Table2 8
    *id (Int32) |
    *name |
    *fk_tbl1_id
    (Int32)----/

    Each Table is filled by a custom DAO, which also does the updates,
    so there's no TableAdapters or anything like that voodoo.

    The GUI Part
    In the GUI, a BindingSource connects a grid (Grid1) to Table1, a second one connects
    another one (Grid2) to Table2. That is - to be more precise : In the Data Sources dialogue,
    there's a child node under Table1 for each Column and one which - as far as i have
    understood - represents a view on Table2 containing only rows that are linked to the current
    position of the Table1BindingSource. This is what grid2 is bound to, so, when
    i change my selection in Grid 1, the entries in Grid2 change accordingly.

    The Database Part
    In Oracle, each table gets its ID-values from a sequence of its own. This is done by an
    insert-trigger.

    My Problem starts in the DataSet : Adding new Rows to each table is only possible if I
    set the ID column to AutoIncrement (or use a crappy default value, which has other drawbacks),
    since the Int32-Column will throw an Exception on NULL Values.
    If I were working with a single table, this would do the trick for me, since Oracle overwrites the
    IDs anyway.
    But now I insert a row in Table1 and get an ID generated by the DataSet, which will be used as fk_tbl1_id
    in Table2 for all related rows. Sending this to the Oracle-Server will result in another id for each
    Table1-row inserted and the DataSet-given Values in Table2.
    fk_tbl1_id.

    I really wonder how I can set either the DataSet or the Table1BindingSource to retrieving the ID
    externally...

    Currently I worked around the problem by removing the insert trigger from the tables and retrieving the next sequence value from within the application:

    this.dataSet1.Table1.TableNewRow += new DataTableNewRowEventHandler(Table1_TableNewRow);

    private void ACL_APPLICATIONS_TableNewRow(object sender, DataTableNewRowEventArgs e)
    {
    e.Row["id"] = myDAO.NextID; // this gets the next value from oracle.
    }

    But there has to be a better way...Is there? Can you please help me improve this architecture? Is there a best practice to do this?

    thanks a lot in advance, best wishes, have a nice weekend Big Smile

    Sebi
    Friday, November 21, 2008 3:09 PM

Answers

  • Hey Sebi,

     

    In the book Programming Microsoft Ado.net 2.0, Core Reference by David Sceppa, the book discusses a way to deal with server generated keys and DataSet.

     

    It describes how oracle admins use sequences to generate key values. The approach described  is to query the sequence for the new values prior to submitting the new rows. Then use them as they are put in.

     

    I was looking around the web for other people and how they solved this problem as well and I found a good blog post describing a solution that is slightly different than yours as well. But since you are not using the DataAdapters I'm not sure it will work

     

    http://oraclevsmicrosoft.blogspot.com/2005/02/oracle-sequences-and-ado-net.html

     

    There is also a complete article on msdn describing a solution using Oracle, DataAdapters and Dataset here

    http://msdn.microsoft.com/en-us/library/ms971506.aspx

     

    Let me know if any of this information helps.

     

    Thanks

    Chris Robinson

    Program Manager - DataSet

     

    Friday, November 21, 2008 7:34 PM

All replies

  • Hey Sebi,

     

    In the book Programming Microsoft Ado.net 2.0, Core Reference by David Sceppa, the book discusses a way to deal with server generated keys and DataSet.

     

    It describes how oracle admins use sequences to generate key values. The approach described  is to query the sequence for the new values prior to submitting the new rows. Then use them as they are put in.

     

    I was looking around the web for other people and how they solved this problem as well and I found a good blog post describing a solution that is slightly different than yours as well. But since you are not using the DataAdapters I'm not sure it will work

     

    http://oraclevsmicrosoft.blogspot.com/2005/02/oracle-sequences-and-ado-net.html

     

    There is also a complete article on msdn describing a solution using Oracle, DataAdapters and Dataset here

    http://msdn.microsoft.com/en-us/library/ms971506.aspx

     

    Let me know if any of this information helps.

     

    Thanks

    Chris Robinson

    Program Manager - DataSet

     

    Friday, November 21, 2008 7:34 PM
  • Hi Chris,

    thanks for your response

     Chris Robinson- MSFT wrote:

    The approach described  is to query the sequence for the new values prior to submitting the new rows. Then use them as they are put in.



    That's what I was trying to express with the snippet I pasted : On TableNewRow event, I fetch a new ID from Oracle in order to be consistent. I guess that's the price I'll have to pay for servergenerated keys...

    thanks anyway!
    Monday, November 24, 2008 7:24 AM