Autoinc retrieve values in the Bdp in Delphi 2005

Autoinc retrieve values in the Bdp in Delphi 2005

Autoinc retrieve values in the Bdp in Delphi 2005 : Multi-thousand tips to make your life easier.

In this article I will use the term-note values of "identity" as a Synonym, that car (from RDMS) generates a rule for a column of the primary key of a table. These are called in Oracle, and "generators" in Interbase "sequences".
There are 3 ways to get the identity value back to ADO NET in General:

1. use a batch query (the database obviously has
2. ) you Use a stored procedure (like Insert-command) has as an Output Parameter the value of the "identity" (This is the fastest and thus recommended method)
3. Treat the adapter's RowUpdated event, /RowUpdating and a "@@IDENTITY select" (SQL Server) query in the Code in this event handler (at the slowest)

The first two options are based on the UpdatedRowSource property of a Command object, so that the new value is returned to the DataSet object. Unfortunately, this feature has not been implemented currently in BdpCommand. This means that the only viable Option of the 3. and I'll application an example (for MS SQL Server Northwind database) to show this build:

• Start Delphi 2005.
• file | New, and select the "Windows Forms application – Delphi for net"Option.
• Drag from the Data Explorer, the Dbo. Employees table from the Northwind database in MSSQL provider (Details of how this connection can be found in this excellent article by Bob Swart). "Employees" table uses an AutoInc column as primary key called EmployeeID
• The BDPDataAdapter is created, right-click and select configure Adapter. This brings up the data form for the configuration of the Adapter for the BdpAdapter component. To change the SELECT clause, as shown in the following figure is displayed, and click on GenerateSQL:

• Click on the tab and select "New Dataset" and click OK.
• you Set BdpAdapter Active property to True.
• Click the Tables property of the newly created Datasets ("Dataset1"). This will bring up the tables collection Editor property for the Dataset. Select columns, and this brings up the columns Editor for the employees table. Select the column "EmployeeID" and change the following properties:
  
  • AutoIncrement = True
  • AutoIncrementSeed =-1
  • auto increment step =-1

As shown in the following figure:

note: Because the values we put in the DataSet anyway when Applying to be discarded an update of the database could be used with any dummy (unique) value. However, it is recommended to set the AutoIncrement value for the column is the primary key field as true and make it to take unique negative values, because in this way, we rely on the dataset properties to obtain temporary unique values and, in addition, with negative values as a temporary EmployeeID values ensures that no permanent (positive) database Server-assigned values conflict.

• you Delete a DataGrid control on the form and set the Datasource property to Dataset1, and the DataMember property to "employees".
• to Delete a button, rename it as Btnsave_onclick and place your Text on the "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 of

The 4^thParameter of the AutoUpdate method defined EmployeeID as a read only column (that is to say, that it is not to be taken into account in the last INSERT clause), and this is what we really want, because this value will be generated by the database server automatically.the

• Finally, double-click on the RowUpdated event of the BdpAdapter and paste the following event handler:

procedure TWinForm1.BdpDataAdapter1_RowUpdated (Sender: System.Object e: Borland.Data.Provider.BdpRowUpdatedEventArgs)
var
Cmd:BdpCommand
begin
If (e. Status=UpdateStatus.Continue)
(e. statement type=statement type.Insert)


Cmd:=BdpCommand start.Create ("SELECT @@IDENTITY', BdpConnection1)
e. Row['EmployeeID']:=Cmd.ExecuteScalar
e. Row.Accept changes
end
end of

First of all, I notice that there is an error in the updating of this particular row (update status.Continue) is not come, and if the line was stored, was added. In this case, I create a BdpCommand object to select @@IDENTITY query is issued. I then assign the retrieved value of the current row (e. row) and finally, I call e. Row.To remove accept changes to the Amendment, I have from the change log.
Note that we should use @@SCOPE_IDENTITY instead of @@IDENTITY, for the case that there are Audit tables in the database by some Trigger (@@IDENTITY inserted the value in the Audit table and not the "right" table, which we update) is automatically updated.

Run the application and insert a few rows:

Click on "save Changes", and the EmployeeID values are updated automatically:

Similar techniques (to be used in the case of the the RowUpdating event), a value generated from an Interbase Generator before Applying this new value in the database

references:

Borland Delphi 2005 RAD for ADO.NET - by Bob Swart
-Microsoft ADO NET (Microsoft Press) by David Sceppa
-use Autoinc fields with DataSnap by Dan Miser