7 Replies Latest reply: Oct 11, 2006 12:12 PM by 519674 RSS

    HOW TO GET AUTO GENERATED PRIMARY ID KEY BACK FROM AN INSERT STATEMENT IN .

    526520
      Just recently I ran into a problem with what seems to be a deficiency in the Oracle Database. When trying to return an auto-generated key as is done in Microsoft's SQL database, it seems Oracle for whatever reason didn't add this capability, the key couldn't be passed back to the .Net call. After tinkering with the .Net software and talking with the Oracle techs, a decent work around to the problem (presented here) comes fairly close. Two things have to be done first before this will work. A sequence has to be created on the Oracle DB. Also a trigger has to be created against the table the keys are going to be returned from.

      The class works by passing to the function 'update_datasets_return' a DataSet with as many tables as you want. The function spins through the tables and put the keys in each row inserted. It's assumed the first row will either be the primary key or some numeric value. This can be changed by modifying the: dt.Columns(0).ColumnName

      Notice the word Inserted. On Updates and Deletes the key value is ignored because it's already present. The routine just updates the database as usual.

      So in other words you could send a table to the function with rows inserted, deleted, or updated and it will take care of them all. One routine for all.

      ' ======================================================================
      ' Created by SEF and Oracle SR: 5607364.993 This is a complete Redo
      ' of the initial concept. SEF...
      ' Sample of sequence and trigger at bottom.
      '
      ' ----------------------------------------------------------------------
      ' Uses the ODP.NET data provider.
      '
      ' update_datasets_return: Goes thru each table in the dataset and
      ' updates, deletes, or inserts as needed.
      '
      ' If inserting, a 'sequence counter' and a 'trigger'
      ' using the 'before insert' must be present. The sequence
      ' counter is set to auto increment by 1 starting at 1.
      ' The trigger is specific to the table.
      '
      ' Create the trigger and sequence in the database or run the samples
      ' below in PL/SQL. Be sure the logon with a user that has enough rights.
      '
      ' Routine assumes the first column is going to hold the sequence
      ' number. Actually any column could be used. Just change the
      ' dt.Columns(0).ColumnName to whatever you want or leave as default.
      '
      ' The da_RowUpdated sub is where the 'sequence number' gets returned
      ' on each row that is inserted into a table. Routine is ignored on
      ' deletes and updates. SEF...

      ' =======================================================================
      Imports System
      Imports System.Data
      Imports Oracle.DataAccess.Client
      Imports Oracle.DataAccess.Types

      Public Class OracleUpdate
      Private Shared m_conn As OracleConnection
      Private Shared da As New OracleDataAdapter
      Private Shared dt As DataTable
      Private Shared conn As New OracleConnection
      Private Shared dr As DataRow
      Private Shared astep As Long
      Private Shared rwIndex As Integer = 0
      Private Shared tblIndex As Integer = 0

      Public Shared Function update_datasets_return(ByVal constr As String, ByVal ds As DataSet) As DataSet ''ByRef ds As DataSet)
      Dim selectstmt As String
      m_conn = New OracleConnection(constr)
      Try
      m_conn.Open()
      Catch ex As Exception
      Throw New ArgumentException(" Error: connection lost." & ex.Message)
      End Try
      For Each dt In ds.Tables
      ''Uncomment the code only if auto numbering
      ''is NOT turned on for the table being updated. SEF...
      ''
      ''rwIndex = 0
      ''For Each dr In dt.Rows
      '' Try
      '' Select Case dr.RowState
      '' Case DataRowState.Added
      '' astep += 1
      '' ' =======================================================
      '' ' This "Try Catch" section created only if auto numbering
      '' ' is NOT turned on for the table being updated. SEF...
      '' ' It's a crude attempt at creating a unique number.
      '' ' A more serious approach would be to use a GUID.
      '' ' Use only if you decide not to have a sequence and a
      '' ' trigger for the table.
      '' ' =======================================================
      '' Try
      '' 'ds.Tables(tblIndex).Rows(rwIndex).Item(0) = astep
      '' Catch
      '' ' ignore the error corrected integer identity so don't randomize it
      '' End Try
      '' dr.Item("createdDate") = Now
      '' dr.Item("changedDate") = Now
      '' Case DataRowState.Modified
      '' dr.Item("changedDate") = Now
      '' End Select
      '' Catch ex As Exception
      '' conn.Close()
      '' conn.Dispose()
      '' Throw New ArgumentException(" Error: update_datasets " & ex.Message)
      '' End Try
      '' rwIndex += 1
      ''Next

      selectstmt = "SELECT * From " & dt.TableName & " Where " & dt.Columns(0).ColumnName & " = " & 0
      da = New OracleDataAdapter(selectstmt, m_conn)
      Dim bldr As OracleCommandBuilder = New OracleCommandBuilder(da)
      AddHandler da.RowUpdated, New Oracle.DataAccess.Client.OracleRowUpdatedEventHandler(AddressOf da_RowUpdated)

      Dim insCmd As OracleCommand = Nothing
      Try
      insCmd = CType(bldr.GetInsertCommand(), OracleCommand)
      Catch ex As Exception
      Throw New Exception("")
      End Try
      insCmd.CommandText += " returning " + dt.Columns(0).ColumnName + " into :seqno"
      insCmd.Parameters.Add(New OracleParameter("seqno", OracleDbType.Int16, _
      4, ParameterDirection.Output, False, CType(0, System.Byte), CType(0, _
      System.Byte), dt.Columns(0).ColumnName, DataRowVersion.Current, Nothing))
      da.InsertCommand = insCmd
      Try
      ' ===========================
      da.Update(ds, dt.TableName)
      ' ===========================
      Catch ex As Exception
      Throw New ArgumentException(" Error: update_datasets_return " & ex.Message)
      End Try
      Next
      m_conn.Close()
      m_conn.Dispose()
      Return ds
      End Function

      Friend Shared Sub da_RowUpdated(ByVal sender As Object, ByVal e As OracleRowUpdatedEventArgs)
      If e.StatementType = StatementType.Insert Then
      e.Row(0) = Int64.Parse(e.Command.Parameters("seqno").Value.ToString())
      End If
      End Sub

      ' ================================================================================
      ' Notes:
      '
      '
      '
      ' =================== How To -- Sample section for PL/SQL ==================================
      ' myTrigger, myTable, mySequence, and myColumn are values you need to supply.
      ' Note: A trigger needs to be created for each table.
      ' A sequence needs to be created only once and referenced each time
      ' in the trigger(s). Or you could create a new sequence each time you
      ' create a trigger. Sort of a waste of effort.
      '
      ' Explanation:
      ' myTrigger = The name you are giving this trigger.
      ' If a trigger with same name already
      ' exist, it will be overwritten.
      ' myTable = Table you want to add the sequence numbers to.
      ' mySequence = Sequence counter you created. Whatever name you called it.
      ' myColumn = The column to update with the sequence number.
      '
      ' =================================================================================
      ' -- Run in PL/SQL or create at DB. --
      ' create or replace trigger myTrigger
      ' before insert on myTable for each row
      ' begin
      ' select mySequence.nextval into :new.myColumn from dual ;
      ' end;
      ' /

      ' -- Run in PL/SQL or create at DB. --
      ' create sequence mySequence
      ' MINVALUE 1
      ' START WITH 1
      ' INCREMENT BY 1
      ' NOCACHE; can be set to CACHE but sequence may contain gaps.

      ' -------------------------------------------------------------------------------
      ' Explanation of CACHE from: http://www.techonthenet.com/oracle/sequences.php
      '
      ' With respect to a sequence, the CACHE option specifies how many sequence
      ' values will be stored in memory for faster access. The downside of creating
      ' a sequence with a CACHE is that if a system failure occurs, all cached
      ' sequence values that have not be used, will be "lost". This results in
      ' a "gap" in the assigned sequence values. When the system comes back up,
      ' Oracle will CACHE new numbers from where it left off in the sequence,
      ' ignoring the so called "lost" sequence values.

      ' Note: To recover the lost sequence values, you can always execute an
      ' ALTER SEQUENCE command to reset the counter to the correct value.

      ' NOCACHE means that none of the sequence values are stored in memory.
      ' This option may sacrifice some performance, however, you should not encounter
      ' a gap in the assigned sequence values.
      ' --------------------------------------------------------------------------------
      End Class


      C#:

      using System;
      using System.Data;
      using Oracle.DataAccess.Client;
      using Oracle.DataAccess.Types;

      public class OracleUpdater2
      {
      private static OracleConnection m_conn;
      private static OracleDataAdapter da = new OracleDataAdapter();
      private static OracleConnection conn = new OracleConnection();

      public static DataTable load_it(string constr, string strqry, string tblName)
      {
      // =====================================================================
      // constr = User Id=myUser;Password=myPass";Data Source=myDataSource
      // strqry = Select * from something?
      // tblName = The table name to fill.
      // =====================================================================
      conn = new OracleConnection(constr);
      conn.Open();
      da = new OracleDataAdapter(strqry, conn);
      OracleCommandBuilder bldr = new OracleCommandBuilder(da);
      DataTable dt = new DataTable(tblName);
      da.Fill(dt);
      conn.Dispose();
      return dt;
      }

      public static DataSet update_datasets_return(string constr, DataSet ds)
      //'ByRef ds As DataSet)
      {
      string selectstmt = null;
      m_conn = new OracleConnection(constr);
      try
      {
      m_conn.Open();
      }
      catch (Exception ex)
      {
      throw new ArgumentException(" Error: connection lost." + ex.Message);
      }

      foreach (DataTable dt in ds.Tables)
      {
      selectstmt = "SELECT * From " + dt.TableName + " Where " +
      dt.Columns[0].ColumnName + " = " + 0;
      da = new OracleDataAdapter(selectstmt, m_conn);
      OracleCommandBuilder bldr = new OracleCommandBuilder(da);
      da.RowUpdated += new
      Oracle.DataAccess.Client.OracleRowUpdatedEventHandler(da_RowUpdated);

      OracleCommand insCmd = null;
      try
      {
      insCmd = (OracleCommand)(bldr.GetInsertCommand());
      }
      catch (Exception ex)
      {
      throw new Exception("" + ex.Message);
      }
      insCmd.CommandText += " returning " + dt.Columns[0].ColumnName + " into
      :seqno";
      insCmd.Parameters.Add(new OracleParameter("seqno", OracleDbType.Int16, 4,
      ParameterDirection.Output, false, System.Convert.ToByte(0),
      System.Convert.ToByte(0), dt.Columns[0].ColumnName, DataRowVersion.Current,
      null));
      da.InsertCommand = insCmd;
      try
      {
      // ===========================
      da.Update(ds, dt.TableName);
      // ===========================
      }
      catch (Exception ex)
      {
      throw new ArgumentException(" Error: update_datasets_return " +
      ex.Message);
      }
      }
      m_conn.Close();
      m_conn.Dispose();
      return ds;
      }

      If you need a working program of how this works, let me know.