Mega Code Archive
How to retrieve autoinc values with bdp in delphi 2005
<![CDATA[
In this article I’ll use the term “Identity” interchangeably to note Auto generated (from RDMS) values usually for a column representing the primary key of a table. These are called “sequences” in Oracle and “generators” in Interbase.
Actually, there are 3 options to get the identity value back using ADO .NET in general:
Use a batch query (the database obviously has to support this)
Use a stored procedure (as the Insert command) having as an output parameter the “Identity” value (this is the fastest and thus recommended method)
Handle Adapter's RowUpdated /RowUpdating event and issue a "SELECT @@IDENTITY " (in case of SQL Server) query in code in this event handler (slowest)
The first two options rely on UpdatedRowSource property of a Command object so that the new value is propagated back to the DataSet object. Unfortunately, this property has not currently been implemented in bdpCommand. That means that the only viable option is the 3rd one and I’ll build a sample application (against MS SQL Server Northwind database) showing this:
Start Delphi 2005.
Do File | New, and select the "Windows Forms Application - Delphi for .NET" option.
Drag from Data Explorer the dbo.Employees table from Northwind database in MSSQL provider (details on how to setup this connection can be found in this excellent article by Bob Swart). Employees table uses an AutoInc column as primary key called “EmployeeID”
Right click the BDPDataAdapter created and select Configure Adapter. This will bring up the data adapter configuration form for the bdpAdapter component. Modify the SELECT clause as is shown in the following picture and click GenerateSQL:
<!--[if !supportLists]--> <!--[endif]--><!--[if !supportLists]-->Click on the dataset tab and select “New Dataset” and click OK.
<!--[endif]--><!--[if !supportLists]--> Set bdpAdapter Active property to True.
<!--[endif]--><!--[endif]--><!--[if !supportLists]--><!--[endif]--> Click the Tables property of the newly created Dataset (“Dataset1”). This will bring up the Tables collection property editor for the dataset. Select Columns and this will bring up the Columns editor for the employees Table. Select the “EmployeeID” column and change the following properties:
AutoIncrement=True
AutoIncrementSeed=-1
<!--[endif]-->AutoIncrementStep=-1
as is shown in the following picture:
NOTE: Since the values we will set in the DataSet will be discarded anyway during applying updates to the database, then any dummy (unique) value could be used as well. However, it is a good practice to set the AutoIncrement value of the column representing the primary key field as true and make it take unique negative values, because this way we rely on Dataset's properties to get temporary unique values and in addition using negative values as temporary EmployeeID values makes sure that there is no conflict with permanent (positive) database server assigned values.
<!--[if !supportLists]--> <!--[endif]--><!--[if !supportLists]--><!--[endif]-->Drop a datagrid to the form and set its datasource property to Dataset1 and its DataMember property to “employees”.
Drop a Button, rename it as btnSave and set its Text to “Save Changes”. Double click the button and enter the following code:
procedure TWinForm1.btnSave_Click(sender: System.Object; e: System.EventArgs);
begin
BdpDataAdapter1.AutoUpdate(DataSet1, 'Employees', BdpUpdateMode.All, ['EmployeeID'],[]);
end;
The 4thparameter of AutoUpdate method defines EmployeeID as a read only column (that means, that it will not be included in the final INSERT clause) and that is what we actually want since this value will be automatically generated from the database server.
Finally, double click the RowUpdated event of bdpAdapter and add the following handler:
procedure TWinForm1.BdpDataAdapter1_RowUpdated(sender: System.Object; e: Borland.Data.Provider.BdpRowUpdatedEventArgs);
var
Cmd:BdpCommand;
begin
If (e.Status=UpdateStatus.Continue) and
(e.StatementType=StatementType.Insert)
then
begin
Cmd:=BdpCommand.Create('SELECT @@IDENTITY',BdpConnection1);
e.Row['EmployeeID']:=Cmd.ExecuteScalar;
e.Row.AcceptChanges;
end;
end;
At first, I ascertain that an error has not come up during the update of this particular row (UpdateStatus.Continue) and if the row been saved was an inserted one. In that case I create a bdpCommand object issuing the SELECT @@IDENTITY query. I then assign the retrieved value to the current row (e.row) and finally I call e.Row.AcceptChanges to remove the change I made from the change log.
Note that we should use @@SCOPE_IDENTITY instead of @@IDENTITY in case there were Audit tables in the database automatically updated through some trigger (making @@IDENTITY the value inserted in the audit table and not the "real" table we are updating).
Run the application and add a couple of rows:
Click the “Save changes” button and the EmployeeID values will have automatically been updated:
Similar techniques (involving the RowUpdating event) could be used to get a generated value from an Interbase generator prior to applying this new value to the database
]]>