18th Chapter-delphi Client Server application development (d) (4)

Another tstoredproc part that uses stored procedures in the Csdemo application is Deleteemployeeproc. It accomplishes the task of deleting records from the employee table and modifying all related tables to maintain data consistency. The properties are as follows:

Table 18.20 Deleteemployeeproc The value of the main property of the part

━━━━━━━━━━━━━━━━━━━━━━━━━━

Property Name property value

──────────────────────────

DataBaseName Employeedemodb

Parambindmode Pbbyname

Params emp_num (input parameter, integral type)

Storedprocname Delete_employee

━━━━━━━━━━━━━━━━━━━━━━━━━━

Stored procedures Delete_employee The following procedures:

PROCEDURE Delete_employee

DECLARE VARIABLE any_sales INTEGER;

BEGIN

any_sales = 0;

SELECT count (Po_number)

From sales

WHERE Sales_rep =: emp_num

Into:any_sales;

IF (Any_sales > 0) THEN

BEGIN

EXCEPTION Reassign_sales;

SUSPEND;

End

UPDATE Department

SET mngr_no = NULL

WHERE mngr_no =: emp_num;

UPDATE Project

SET Team_leader = NULL

WHERE Team_leader =: emp_num;

DELETE from Employee_project

WHERE emp_no =: emp_num;

DELETE from Salary_history

WHERE emp_no =: emp_num;

DELETE from Employee

WHERE emp_no =: emp_num;

SUSPEND;

End

Parameters:

Emp_num INPUT INTEGER

From the example of the stored procedure described above, we see that stored procedures have a strong ability to maintain data consistency on the server, which saves overhead and improves client performance.

18.4.2.5 Transaction Control Programming

In a client/server application, transaction control is an important technology. It is of great significance to improve the reliability of the system and maintain the data consistency.

Delphi provides both implicit and explicit control methods for transactions. The performance of explicit control is high, the following introduces the programming method of Delphi transaction explicit control.

The part that Delphi serves as the transaction control task is tdatabase. Tdatabase properties used for transaction control are transisolation, with Starttranstion, commit, and rollback. See Client/server transaction management for these properties and how they work and how to use them.

In Csdemo, the tdatabase part is employeedatabase and its Transisolation property value is tireadcommitted, meaning that if there are multiple simultaneous transaction Access databases, either transaction can only read data submitted by other transactions.

The form that demonstrates transaction control in Csdemo is Tfrmtransdemo.

The contents of the employeetable are displayed in the DBGRID1. When the window is displayed, Employeedatabase starts a transaction control and activates the employeetable:

Procedure Tfrmtransdemo.formshow (Sender:tobject);

Begin

DmEmployee.EmployeeDatabase.StartTransaction;

DmEmployee.EmployeeTable.Open;

End

Employeedatabase commits a transaction when the window is closed or hidden:

Procedure Tfrmtransdemo.formhide (Sender:tobject);

Begin

DmEmployee.EmployeeDatabase.Commit;

End

There are two buttons in the window btncommitedits and btnundoedits. Pressing the Btncommitedits button commits the current transaction and starts a new transaction control and refreshes the data.

Procedure Tfrmtransdemo.btncommiteditsclick (Sender:tobject);

Begin

If DmEmployee.EmployeeDatabase.InTransaction and

(Messagedlg (' Are you sure your want to commit your changes? ')

Mtconfirmation, [Mbyes, mbno], 0 = mryes) Then

Begin

DmEmployee.EmployeeDatabase.Commit;

DmEmployee.EmployeeDatabase.StartTransaction;

DmEmployee.EmployeeTable.Refresh;

End Else

Messagedlg (' Can ' t Commit changes:no Transaction Active ', Mterror, [Mbok], 0);

End

Pressing the Btnundoedits button will return to the current object, restore the original data, start a new transaction control, and refresh the display of the data.

Procedure Tfrmtransdemo.btnundoeditsclick (Sender:tobject);

Begin

If DmEmployee.EmployeeDatabase.InTransaction and

(Messagedlg (' Are you sure for want to undo all changes made the ' +

' Current transaction ', Mtconfirmation, [Mbyes, mbno], 0 = mryes) Then

Begin

DmEmployee.EmployeeDatabase.Rollback;

DmEmployee.EmployeeDatabase.StartTransaction;

DmEmployee.EmployeeTable.Refresh;

End Else

Messagedlg (' Can ' t Undo edits:no Transaction Active ', Mterror, [Mbok], 0);

End