Chapter 13 Patients Database Programming Technology of Delphi 4 in front of several database applications. In order to enable the reader to thoroughly understand the programming technology and flexibly use, we take the Delphi 4's demonstration procedures to analyze, these demonstration procedures have a very technical. To illustrate, we may ignore some details that are unrelated to topics when analyzing procedures. 13.1 Demo Procedure for a background query This section detailedly analyzes the demonstration program of the background query, the project name is BKQuery, which can be found in the C: / Program Files / Borland / Delphi4 / DEMOS / DB / BKQUERY directory. Its main form is shown in Figure 13.1. Figure 13.1 BKQuery's main form begins with the handle of the handle of the oncreate event of the form, because it is the starting point of the application.
Procedure TAdhocForm FormCreate (Sender: TObject); Procedure CreateInitialIni; Const VeryInefficientName =. 'IB: Very Inefficient Query'; VeryInefficientQuery = 'select EMP_NO, Avg (Salary) as Salary / n' 'from employee, employee, employee / n' 'group by EMP_NO'; AmountDueName = 'DB: Amount Due by Customer'; AmountDueByCustomer = 'select Company, Sum (ItemsTotal) - Sum (AmountPaid) as AmountDue / n' 'from customer, orders / n' 'where Customer.CustNo = Orders.CustNo / n ' ' group by Company '; Begin With SavedQueries Do Begin WriteString (VeryInefficientName,' Query ', VeryInefficientQuery); WriteString (VeryInefficientName,' Alias', 'IBLOCAL'); WriteString (VeryInefficientName, 'Name', 'SYSDBA'); SavedQueryCombo.Items.Add (VeryInefficientName); WriteString (AmountDueName, 'Query', AmountDueByCustomer); WriteString (AmountDueName, 'Alias', 'DBDEMOS'); WriteString (AmountDueName, 'Name', ''); SavedQuerycombo.items.add (amouNTduename); end; end; begin session.getalia sNames (AliasCombo.Items); SavedQueries: = TIniFile.Create ( 'BKQUERY.INI'); SavedQueries.ReadSections (SavedQueryCombo.Items); If SavedQueryCombo.Items.Count <= 0 then CreateInitialIni; SavedQueryCombo.ItemIndex: = 0; QueryName : = SavedQuerycombo.items [0]; unmodify; readQuery; end; formCreate mainly doing such things: First, it calls Tsession GetAliasNames function put all defined BDE alias in a string list, actually Fill in the "Database Alias" box in Figure 13.1. Next, a TINIFILE type object instance is created and the file name is bkquery.ini. If this file does not exist now, you need to call CreateInitialIni to create a file.
As for how to write .ini files, this is not the subject of this chapter to discuss. Finally, call ReadQuery to read the relevant parameters saved in the file. ReadQuery function is defined as: Procedure TAdhocForm.ReadQuery; BeginIf not CheckModified then Exit; With SavedQueries DoBeginQueryName: = SavedQueryCombo.Items [SavedQueryCombo.ItemIndex]; QueryEdit.Text: = IniStrToStr (ReadString (QueryName, 'Query', '') ); Aliascombo.text: = readstring (queryname, 'alias', ''); nameEdit.text: = readstring (queryname, 'name', ''); end; unmodify; if shwing kil nameEdit.text <> '' The passwordedit.setfocus elsequeeryedit.setfocus; end; When the user clicks the "Execute" button, the program calls BackgroundQuery to execute the query in the background. Procedure TAdhocForm.ExecuteBtnClick (Sender: TObject); BeginBackgroundQuery (QueryName, AliasCombo.Text, NameEdit.Text, PasswordEdit.Text, QueryEdit.Text); BringToFront; End; BackgroundQuery is defined in the unit called ResItFrm in another, later Key to introduce this process. When the user clicks on the "New" button, the program reinitializes some of the windows on the form. Procedure TAdhocForm.NewBtnClick (Sender: TObject); Function UniqueName: string; varI: Integer; BeginI: = 1; RepeatResult: = Format ( 'Query% d', [I]); Until SavedQueryCombo.Items.IndexOf (Result) < 0; End; BeginAliasCombo.Text: = 'DBDEMOS'; NameEdit.Text: = ''; PasswordEdit.Text: = ''; QueryEdit.Text: = ''; QueryEdit.SetFocus; QueryName: = UniqueName; SavedQueryCombo.ItemIndex: = -1; unnamed: = true; end; When the user clicks the "Save" button, the program calls the SaveQuery function to save the current parameters to the .ini file.
Procedure TAdhocForm.SaveBtnClick (Sender: TObject); BeginSaveQuery; End; SaveQuery and is defined as: Procedure TAdhocForm.SaveQuery; BeginIf Unnamed then SaveQueryAsElse With SavedQueries DoBeginWriteString (QueryName, 'Query', StrToIniStr (QueryEdit.Text)); WriteString ( QueryName, 'alias', aliascombo.text); WritestRing (queryname, 'name', nameEdit.text); unmodify; end; end; When the user clicks the "Save AS" button, the program call SaveQueryas function is saved in another name parameter. Procedure TAdhocForm.SaveAsBtnClick (Sender: TObject); BeginSaveQueryAs; End; SaveQueryAs and is defined as: Procedure TAdhocForm.SaveQueryAs; BeginIf GetNewName (QueryName) then BeginUnnamed: = False; SaveQuery; With SavedQueryCombo, Items DoBeginIf IndexOf (QueryName) <0 Then add (queryName); itemindex: = indexof (queryName); end; end; end; where getnewname is defined in a unit called SaveQas, it will open the dialog shown in Figure 13.2 to allow users to enter one file name. Figure 13.2 Specify another file name In addition, the program also processes the onchange event of the SavedQueryCombo box: procedure tadhocform.savedQueryCombochange (sender: TOBJECT); beginreadQuery; end; so-called background query, actually use multi-thread programming technology to make the query A special thread is performed. To do this, first base TThread to a thread object class declaration: TypeTQueryThread = Class (TThread) PrivateQueryForm: TQueryForm; MessageText: string; Procedure ConnectQuery; Procedure DisplayMessage; ProtectedProcedure Execute; override; PublicConstructor Create (AQueryForm: TQueryForm); End; Let's take a look at the thread object: constructor tQuerythread.create (AQueryform: tQueryform); beginQueryform: = AQueryform; FreeOnterminate: = true; inherited create (false); end; When the user clicks the "Execute" button, the program is called The BackgroundQuery function executes the query in the background.
BackgroundQuery is defined as: Procedure BackgroundQuery (const QueryName, Alias, User, Password, QueryText: string); varQueryForm: TQueryForm; BeginQueryForm: = TQueryForm.Create (Application); With QueryForm, Database DoBeginCaption: = QueryName; QueryLabel.Caption: = Querytext; show; aliasname: = alias; params.values ['user']: = user; params.values ['password']: = password; query.sql.text: = querytext; end; tQuerythread.create (queryform ); END; BackgroundQuery mainly do three things, one is dynamically created and displaying a form (TQueryform) because you want to display query results in this form. The second is to assign the passing parameters to the SQL attribute of the AliasName, Params, and TQuery components of the TDADABASE component, respectively. The third is an example of creating a thread object. Since the freeOnterminate attribute of the thread object is set to True, it is not necessary to delete thread objects specifically. OK, now let's look at the most critical program code that is Execute function thread object: Procedure TQueryThread.Execute; varUniqueNumber: Integer; BeginTryWith QueryForm DoBeginUniqueNumber: = GetUniqueNumber; Session.SessionName: = Format ( '% s% x' [Session.name, uniqueenumber]); Database.SessionName: = session.sessionname; Database.DatabaseName: = Format ('% s% x', [database.name, uniqueenumber]); query.sessionname: = dataBase.SessionName ; Query.DatabaseName: = Database.DatabaseName; Query.Open; Synchronize (ConnectQuery); MessageText: = 'Query openned'; Synchronize (DisplayMessage); End; Except On E: Exception DoBeginMessageText: = Format ( '% s:% s . ', [E.classname, e.Message]); SYNCHRONIZE (DISPLAYMESSAGE); END; end; end; due to this is a multi-threaded database application, there is a need to explicitly use Tsession components, and to ensure each The BDE session target used by threads is unique. Therefore, the program first calls getUniquenumber to get a unique serial number. Similarly, there are similar problems for the TDATABASE component. Execute allows the main thread to execute ConnectQuery, DisplayMessage by Synchronize, because ConnectQuery, DISPLAYMESSAGE needs to be deal with VCL and must be used in Synchronize.
13.2 Demonstration Program for a Cache Update This section analyzes a buffer update to the demonstration program, the project name is Cache, which can be found in the C: / Program Files / Borland / Delphi4 / DEMOS / DB / CACHEUP directory. Its main form is shown in Figure 13.3. Figure 13.3 There is a "cached updates" check box on the main form of Cache, if this check box is selected, indicating using the cache update technology. Otherwise, it means that the cache update technology is not used. When the user modifies the data, the data is directly written to the data set. There is also a "Update SQL" check box on the main form, if this check box is selected, indicating the use of the TUPDATESQL component to make a cache update. When the user clicks the "Apply Updates" button, apply to the database to update the data. When the user clicks the "Cancel Updates" button, all unresolved modifications will be canceled. When the user clicks on the "Revert Record" button, the modification of the current record will be canceled. There are several check boxes in the "Show Records" packet box, which are used to select which records you want to display in the grid, including unmodified records, modified records, insert records, and delete records. Re-execute the query when the user clicks on the "RE-EXECUTE Query" button. In addition, this exemplary program also uses a calculation field to express the current update status. Let's take a look at how to achieve the above functions. Before introducing the program code, we must introduce the data module Cachedata, because several key components are placed on this data module, as shown in Figure 13.4. Figure 13.4 Data Module Data Module There are four components on the data module: a TDataSource component, named cacheds, a TDATABASE component name cachedb, a TQuery component name cacheQuery, a TupdateSQL component name UpdateSQL. OnCalcFields member TQuery event is handled: Procedure TCacheData.CacheQueryCalcFields (DataSet: TDataSet); ConstUpdateStatusStr: array [TUpdateStatus] of string = ( 'Unmodified', 'Modified', 'Inserted', 'Deleted'); BeginIf CacheQuery. CachedUpdates Then cacheQueryUpdateStatus.Value: = updatestatusStr [cacheQuery.Updatestatus]; END; The above code is used to assign a value to the calculation field cacheQueryUpdateStatus to display the current update status. OnUpdateError member TQuery event is handled: Procedure TCacheData.UpdateErrorHandler (DataSet: TDataSet; E: EDatabaseError; UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction); BeginUpdateAction: = UpdateErrorForm.HandleError (DataSet, E, UpdateKind); End; now Let's go back to the main form, starting from the handle of the oncreate event of the main form.
Procedure TCacheDemoForm FormCreate (Sender: TObject); BeginFDataSet:. = CacheData.CacheDS.DataSet as TDBDataSet; FDataSet.CachedUpdates: = CachedUpdates.Checked; SetControlStates (FDataSet.CachedUpdates); FDataSet.Open; End; TDataSource member from a first line of code The DataSet property takes out the current data set. The second line code is based on the check box cachedupdates to determine the cachedupdates property of the data set, and then call the SETCONTROLSTATES function setting the status on the control on the control, and finally call the Open to execute the query. SetControlStates is defined as: Procedure TCacheDemoForm.SetControlStates (Enabled: Boolean); BeginApplyUpdatesBtn.Enabled: = True; CancelUpdatesBtn.Enabled: = True; RevertRecordBtn.Enabled: = True; UnmodifiedCB.Enabled: = True; ModifiedCB.Enabled: = True Insertedcb.enabled: = true; deletedcb.enabled: = true; useUpdateSQL.Enabled: = true; end; the following is an event to process some controls. First check box CachedUpdates OnClick event: Procedure TCacheDemoForm.ToggleUpdateMode (Sender: TObject); BeginFDataSet.CachedUpdates: = not FDataSet.CachedUpdates; SetControlStates (FDataSet.CachedUpdates); End; checkbox UseUpdateSQL OnClick event is handled : Procedure TCacheDemoForm.UseUpdateSQLClick (Sender: TObject); BeginFDataSet.Close; if UseUpdateSQL.Checked then FDataSet.UpdateObject: = CacheData.UpdateSQLElseFDataSet.UpdateObject: = nil; FDataSet.Open; End; when the user clicks the "Apply Updates" button, Apply to the database to the database. Procedure tcachedemoform.applyupdatesbtnclick (sender: Tobject); beginfdataset.database.ApplyUpdates; end; all universal modifications will be canceled when the user clicks on the "Cancel Updates" button. Procedure tcachedemoform.cancelupdatesbtnclick (sender: TOBJECT); beginfdataset.cancelupdates; end; When the user clicks the "Revert Record" button, the modification of the current record will be canceled.
Procedure TCacheDemoForm.RevertRecordBtnClick (Sender: TObject); BeginFDataSet.RevertRecord; End; in a few checkbox "Show Records" group box, their OnClick event is handled this way: Procedure TCacheDemoForm.UpdateRecordsToShow (Sender: TObject); varUpdRecTypes: TUpdateRecordTypes; BeginUpdRecTypes: = []; If UnModifiedCB.Checked then Include (UpdRecTypes, rtUnModified); If ModifiedCB.Checked then Include (UpdRecTypes, rtModified); If InsertedCB.Checked then Include (UpdRecTypes, rtInserted); If DeletedCB.Checked ThenInClude (UpdRectypes, RTDeleted); fdataSet.UpdateRecordtypes: = UPDRECTYPES; END; UpdateRecordstoshow function first declares that a TupdateRecordtypes type variable UPDRECTYPES, and initializes it into an empty collection. Then determine if the four check boxes are selected, if selected, include the corresponding elements into this collection, as the UpdateRecordtypes property of the data set. Re-execute the query when the user clicks on the "RE-EXECUTE Query" button. Procedure tcachedemoform.reexecuteButtonClick (Sender: TOBJECT); beginfdata.com.close; fdataSet.Open; end; In addition, on the main form, there is a menu command to call About, this command will call SHOWABOUTDIALOG to open a dialog. ShowAboutDialog is defined as: Procedure ShowAboutDialog; BeginWith TAboutDialog.Create (Application) DoTryAboutMemo.Lines.LoadFromFile (ExtractFilePath (ParamStr (0)) 'ABOUT.TXT'); ShowModal; FinallyFree; End; End; 13.3 a Client / Server Demo Procedure This section analyzes a Client / Server Demonstration program, the project name is CSDemos, which can be found in the C: / Program Files / Borland / Delphi4 / Demos / DB / CSDemos directory. Its main form is shown in Figure 13.5. Figure 13.5 CSDemos main form When the user clicks the "Show a View In Action" button, open the FRMViewDemo window. Procedure tfrmlauncher.btnviewsclick (sender: TOBJECT); beginfrmviewDemo.showmodal; end; Open the FRMTRIGGGERDEMO window when the user clicks the "SALARY CHANGE TRIGGER DEMO" button. Procedure tfrmlauncher.btntriggclick (sender: Tobject); beginfrmtriggerDemo.showModal; end; open the FRMQueryProc window when the user clicks on the Query Stored Procedure Demo button.
Procedure tfrmlauncher.btnqryspclick (sender: TOBJECT); beginfrmQueryProc.showModal; end; Open the FRMEXECPROC window when the user clicks on the "Executable Stored Procedure" button. Procedure tfrmlauncher.btnexecspclick (sender: Tobject); beginfrmexecproc.showmodal; end; open the FRMTRANSDEMO window when the user clicks on the "Transaction Editing Demo" button. Procedure tfrmlauncher.btntransclick (sender: TOBJECT); BeginfrMTransDemo.showModal; END; Let's introduce these windows in detail. The FRMViewDemo window is shown in Figure 13.6. Figure 13.6 FRMVIEWDEMO window When this window is popped up, first call the Open function of the TTable component open the data set. Procedure TfrmViewDemo.formshow (sender: TOBJECT); beginvaryingTable.Open; end; program uses two shortcuts to switch the table name, where the left button corresponds to the EMPLOYEE table. Procedure TfrmViewDemo.BtnshowemPloyeeclick (Sender: Tobject); beginshowTable ('Employee'); END; one button on the right corresponds to the Phone_List table. Procedure TFrmViewDemo.BtnShowPhoneListClick (Sender: TObject); BeginShowTable ( 'PHONE_LIST'); End; ShowTable is defined as: Procedure TFrmViewDemo.ShowTable (ATable: string); BeginScreen.Cursor: = crHourglass; VaryingTable.DisableControls; VaryingTable.Active: = FALSE; VaryingTable.TableName: = aTable; VaryingTable.Open; VaryingTable.EnableControls; Screen.Cursor: = crDefault; End; FrmTriggerDemo window shown in FIG 13.7: 13.7 FrmTriggerDemo FIG window when the window pops up, two first call TTable The OPEN of the component opens the data set. Procedure tfrmtriggerDemo.formshow (sender: Tobject); begindMemPloyee.employEetable.Open; DMemPloyee.salaryHistoryTable.Open; end; where DMemPloyee is the name of the data module. The FRMQueryProc window is shown in Figure 13.7. Figure 13.7 FRMQueryProc When this window is popped up, the ONSHOW event will be triggered.
This event is handled this way: Procedure TFrmQueryProc.FormShow (Sender: TObject); BeginDmEmployee.EmployeeTable.Open; EmployeeSource.Enabled: = True; With EmployeeProjectsQuery Do If not Active then Prepare; End; first call the Open EmployeeTable of open data sets, Then set the Enabled property of the data source Employeesource to True, then call Prepare to prepare queries. To perform a query, the program processing OnDataChange the event data source EmployeeSource: Procedure TFrmQueryProc.EmployeeDataChange (Sender: TObject; Field: TField); BeginEmployeeProjectsQuery.Close; EmployeeProjectsQuery.Params [0] .AsInteger: = DmEmployee.EmployeeTableEmp_No.Value; EmployeeProjectsQuery. Open; WriteMsg ( 'Employee' DmEmployee.EmployeeTableEmp_No.AsString 'is assigned to' IntToStr (EmployeeProjectsQuery.RecordCount) 'project (s).'); End; WriteMsg object is invoked to display a message in the status bar . WriteMsg is defined as: Procedure TFrmQueryProc.WriteMsg (StrWrite: String); BeginStatusBar1.SimpleText: = StrWrite; End; Finally, when the window is temporarily withheld, the data source should EmployeeSource Enabled property to False: Procedure TFrmQueryProc. FORMHIDE (Sender: TOBJECT); BegineMPloyeesource.Nabled: = false; End; FrMexecProc window is shown in Figure 13.8. Figure 13.8 FRMEXECPROC When this window is popped up, the ONSHOW event will be triggered. This event is processed: Procedure TFRMEXECPROC.FORMSHOW (Sender: Tobject); BegindMemPloyee.SalesTable.Open; DMemPloyee.Customertable.Open; Salesource.Enabled: = true; end; When the user browses records in the grid, it will trigger Salesource's Ondatachange Event. In handling this event's handle, it is necessary to determine if the value of the ORDER_STATUS field is shipped, if yes, make the "ship order" button valid.
Procedure TFrmExecProc.SalesSourceDataChange (Sender: TObject; Field: TField); BeginIf DmEmployee.SalesTable [ 'ORDER_STATUS'] <> NULL then BtnShipOrder.Enabled: = AnsiCompareText (DmEmployee.SalesTable [ 'ORDER_STATUS'], 'SHIPPED') <> 0 END; When the user clicks on the "Ship Order" button, execute the stored procedure, the parameters of the stored procedure are taken from the PO_NUMBER field. Procedure TFrmExecProc.BtnShipOrderClick (Sender: TObject); BeginWith DmEmployee DoBeginShipOrderProc.Params [0] .AsString: = SalesTable [ 'PO_NUMBER']; ShipOrderProc.ExecProc; SalesTable.Refresh; End; End; FrmTransDemo 13.9 window shown in FIG. This window demonstrates how to handle transactions. First, you want to call the STARTTRANSACTION of the Employeedatabase (TDatabase member) to start a new transaction. Thereafter, all modifications to the database are temporarily kept in the cache until the program is called COMMIT or ROLLBACK. Procedure tfrmtransdemo.formshow (sender: Tobject); begindMemPloyee.employeedatabase.startTransaction; DMemPloyee.employEetable.Open; End; When the user clicks the "Commit Edits" button, submit data to the server. First access the IntraSection property of the TDATABASE component to see if the current is being processed. If so, a dialog is also popped up so that the user confirms whether to submit the data. Program code is as follows: Procedure TFrmTransDemo.BtnCommitEditsClick (Sender: TObject); BeginIf DmEmployee.EmployeeDatabase.InTransaction and (MessageDlg ( 'Are you sure you want to commit your changes?', MtConfirmation, [mbYes, mbNo], 0) = mrYes) thenBeginDmEmployee.EmployeeDatabase.Commit; DmEmployee.EmployeeDatabase.StartTransaction; DmEmployee.EmployeeTable.Refresh; End ElseMessageDlg ( 'Can Yang Commit Changes: No Transaction Active', mtError, [mbOk], 0); End; If the user answers Yes, then, call Commits Submit the data to the server. When the user clicks the "undo edits" button, call the Rollback to cancel all modifications.
Procedure TFrmTransDemo.BtnUndoEditsClick (Sender: TObject); BeginIf DmEmployee.EmployeeDatabase.InTransaction and (MessageDlg ( 'Are you sure you want to undo all changes made during the' , mtConfirmation, [mbYes, mbNo], 0 'current transaction?' ) = mrYes) then BeginDmEmployee.EmployeeDatabase.Rollback; DmEmployee.EmployeeDatabase.StartTransaction; DmEmployee.EmployeeTable.Refresh; EndElseMessageDlg ( 'Can Yang Undo Edits: No Transaction Active', mtError, [mbOk], 0); End; the window is about When you go, you should also call the commit to submit data to the server because the user may not click the "Commit Edits" button. Procedure TFrmTransDemo.FormHide (Sender: TObject); BeginDmEmployee.EmployeeDatabase.Commit; End; 13.4 demonstration program TDBCtrlGrid a detailed analysis of this section member of a demonstration program TDBCtrlGrid member, project name called Ctrlgrid, it can be C: / Program Files The / Borland / Delphi4 / Demos / DB / CtrlGrid directory is found. Its main form is shown in Figure 13.10. We will introduce the data module first, because several key components are on the data module, as can be seen in Figure 13.11, there are three TTable components and three TDataSource components on DM1, these three TTable components have access the master table, Industry table And Holdings table. There are two grids on the main form, one is the raster established with the TDBGRID component, the other is the raster established with the TDBCTRLGRID component, which uses the same TDBNAVIGATOR component to navigate. This program uses such a programming skill. When the user moves the input focus to the grid established by the TDBGRID component, the navigator is established for the TDBGRID component; when the user moves the input focus to the grid established by the TDBCTRLGRID component In time, the navigator is established for the TDBCTRLGRID component. Program code is as follows: Procedure TFmCtrlGrid.DBGrid1Enter (Sender: TObject); BeginDBNavigator1.DataSource: = DM1.DSMaster; End; Procedure TFmCtrlGrid.DBCtrlGrid1Enter (Sender: TObject); BeginDBNavigator1.DataSource: = DM1.DSHoldings; End; pop-up window when the main When you will trigger the ONSHOW event. The program is to handle the onshow event: procedure tfmctrlgrid.formshow (sender: Tobject); begindm1.calculateTotals (sender, nil); end; where CalculateTotal is used to calculate several values, these values will be displayed in the "InvestmentValue" box.
CalculateTotals is defined in the data module DM1 unit: Procedure TDM1.CalculateTotals (Sender: TObject; Field: TField); varflTotalCost, flTotalShares, flTotalValue, flDifference: Real; strFormatSpec: string; Begin {shows the number of stock trading} FmCtrlGrid. lPurchase.Caption: = IntToStr (tblHoldings.RecordCount); {0 if the stock transactions, put the value "Investment value" box cleared} If tblHoldings.recordCount = 0 thenBegin FmCtrlGrid.lTotalCost.Caption: = ''; Fmctrlgrid.ltotalshares.caption: = ''; fmctrlgrid.ldiference.caption: = '; endelsebegin {Set the cursor to an hourglass, because the calculation value can be longer} Screen.cursor: = cr Hourglass; {initialize the value 0.0} flTotalCost: = 0.0; flTotalShares: = 0.0; {calculating the amount of shares held for later} tblHoldings.DisableControls; tblHoldings.First; While not tblHoldings.eof DoBeginflTotalCost: = flTotalCost tblHoldingsPUR_COST.AsFloat; flTotalShares: = flTotalShares tblHoldingsSHARES.AsFloat ; tblHoldings.Next; End; tblHoldings.First; tblHoldings.EnableControls; {calculating the market value of shares and the profit and loss} flTotalValue: = flTotalShares * tblMasterCUR_PRICE.AsFloat; flDifference: = flTotalValue - flTotalCost; strFormatSpec: = tblMasterCUR _PRICE.DisplayFormat; {displaying the data} FmCtrlGrid.lTotalCost.Caption: = FormatFloat (strFormatSpec, flTotalCost); FmCtrlGrid.lTotalShares.Caption: = FormatFloat (strFormatSpec, flTotalValue); FmCtrlGrid.lDifference.Caption: = FormatFloat (strFormatSpec, flDifference) {If it is earned, it will be displayed in green. If a loss, it is displayed in red} If flDifference> 0 then FmCtrlGrid.lDifference.Font.Color: = clGreenElse FmCtrlGrid.lDifference.Font.Color: = clRed; FmCtrlGrid.lDifference.Update; {cursor restitution} Screen. Cursor: = crdefault; end; END; Further, when the user selects the "About" command, the About box will open.
Program code is as follows: Procedure TFmCtrlGrid.About1Click (Sender: TObject); BeginWith TFMAboutBox.Create (nil) DoTryShowModal; Finally Free; End; End; Holdings table displayed when the data set is opened, a dynamically assigned CalculateTotals OnDataChange the event processing dsMaster Handle. Procedure TDM1.TBLHOLDINGSAFTEROPEN (Dataset: TDataSet); begindsmaster.ondataChange: = CalculaTotals; END; in addition, this program also demonstrates the bookmark usage. Procedure TDM1.tblHoldingsAfterPost (DataSet: TDataSet); varbmCurrent: TBookmark; BeginWith tblHoldings DoBeginbmCurrent: = GetBookmark; TryCalculateTotals (nil, nil); GotoBookmark (bmCurrent); Finally; FreeBookmark (bmCurrent); End; End; End; 13.5 capture a database Error Demonstration Procedure This section analyzes a demonstration program that captures database error, and the project name is DBERRORS, which can be found in the C: / Program Files / Borland / Delphi4 / Demos / DB / DBERRORS directory. Its main form is shown in Figure 13.11. This program demonstrates how to capture the database error. Delphi 4 captures errors with OnPOSTERROR, ONEDITELETEEEEEEEError event, which is generated on the user's operation, such as modify, delete, and insert records. First start from its data module. Its data module is called DM, as shown in Figure 13.12. Figure 13.12 Data Module It can be seen that there are three TTable components and three TDataSorce components on the data module, which accesses the Customer table, Orders table, and Items tables, respectively. It should be noted that these three tables are not parallel relationships, but a couple of master / detail relationships. For example, the MASTERSOURCE attribute of the Orders table specifies that the itemsource attribute must be specified as Customersource, and the MASTERSOURCE attribute of the Items table must be specified as OrdersSource. Therefore, these TTABLE components and Creation Order are important, and they cannot be mistaken. The main form of this program is simple, there are three grids (TDBGRID components), display the data of the Customer table, the Orders table, and the Items table, respectively. This program uses the same TDBNAVigator component to navigate through these three grids. Therefore, this program uses a small programming skill to dynamically switch the DataSource properties of the TDBNAVigator component.
Program code is as follows: Procedure TFmMain.GridOrdersEnter (Sender: TObject); BeginDBNavigator1.DataSource: = Dm.OrdersSource; End; Procedure TFmMain.GridCustomersEnter (Sender: TObject); BeginDBNavigator1.DataSource: = Dm.CustomerSource; End; Procedure TFmMain.GridItemsEnter (Sender: TOBJECT); begindbnavigator1.datasource: = DM.Itemssource; END; if the user modifies, inserts or deletes records in the Customer table, when the user wants to move the input focus to other grids, POST should call the user. Write the editing of the data to the database. Procedure tfmain.gridCustomerSexit (Sender: Tobject); Beginif DM.Customer.State In [dsedit, dsinsert] Then DM.Customer.post; End; In addition, an Abut box will appear when the user selects the "About" command. Code is as follows: Procedure TFmMain.About1Click (Sender: TObject); var fmAboutBox: TFmAboutBox; BeginFmAboutBox: = TFmAboutBox.Create (self); TryFmAboutBox.showModal; FinallyFmAboutBox.free; End; End; below focuses on how to catch errors. Any code to capture the wrong mistake is implemented in the unit of the data module, which is also one of the benefits of using the data module. When the program calls POST or the user clicks on the POST button on the navigator, the user is written to the database, if an error (may be because there is a repetitive customer number), the ONPOSTERROR event will be triggered. Let's take a look at how the Customer Table is to handle the onposterror event: procedure tdm.customerposterror (DataSet: tdataserror; var action: tdataAction); beginif (e is edbeneerror) Then IF (e as edbengineerror) .errors [0 ] .Rrorcode = ekeyviol dam ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Errors array to get the current error code. If the error code is ekeyviol, a dialog is displayed to tell the user that you cannot write the data to the database because there are duplicate customer numbers. Then call Abort to abandon this operation. There is also an error while deleting a record in the Customer table, because the deleted customers also have records in the OrderS table and the Items table, which triggerates the OndleteError event.
Let's take a look at how the Customer Table is to handle an OnDeleteError event: Procedure Tdm.customerDeleteError (DataSet: TDataSet; E: EDATABASEERROR; var Action: tdataAction); beginif (e is edbengineerror). EDBENGINEERROR). Errors [0] .Rrorcode = edetailsexist kilinMessagedlg ('to Delete Record, First Delete Related ORDERS AND ITEMS.', MTWARNING, [MBOK], 0); Abort; end; end; readers may discover how to handle ONDeleteError events with processing onposteerror events The way is similar, first determined if the error code is edetailsexist, if yes, the deleted client also has a record in the OrderS table and the items table, showing a dialog to tell the user: To delete this record, first delete the Orders table And related records in the Items table. Then call Abort to abandon this operation. Since the CustNo field is a key field of the Customer table, when the user modifies the value of the CustNo field but not before POST, in order to prevent chaos from the raster of the OrderS table and the Items table, it is best to call the DisableControls function temporarily disabled Data, etc. Call the POST or the user after clicking the POST button on the navigator, then call the EnableControls function. Procedure Tdm.customerCustNochange (Sender: Tfield); beginorder :.disablecontrols; items.disablecontrols; end; After program call POST or user Click the POST button on the navigator, the AfterPost event will be triggered.
The program is the AFTERPOST event for the Customer table: procedure tdm.customerafterpost (DataSet: TDataSt); begindm.orders.refresh; DM.Items.Refresh; DM.OrDers.EnableControls; DM.Items.EnableControls; End; Items Table For the way to handle the ONPOSTERROR event to process the ONPOSTERROR event in the Customer table is substantially the same: Procedure Tdm.ItemSposteerror (DataSet: TDataSet; E: EDATABASEERROR; VAR Action: TDataAction); Beginif (e as edbeneerror) .errors [ 0] .rrorcode = EFOREIGNKEY THENBEGINMESSAGED ', MTWARNING, [Mbok], 0); Abort; end; end; OrderS Table is to handle ONPOSTERROR events: procedure tdm.ordersposteerror (DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction); var iDBIError: Integer; BeginIf (E is EDBEngineError) thenBeginiDBIError: = (E as EDBEngineError) .Errors [0] .Errorcode; Case iDBIError ofeRequiredFieldMissing: {EmpNo field must have a value} BeginMessageDlg ( 'Please Provide An Employee ID ', MTWARNING, [Mbok], 0); Abort; End; EKEYVIOL: {For the ORDERS table, the key field is ORDERNO} BeginMessagedlg (' Unable to post. duplicate Order Number ', MtWarning, [Mbok ], 0); Abort; end; end; end; due to Items In the ORDERS table, there is also an error when it is deleted records in the ORDERS table. Therefore, the program handles the ORDELETEERROR event of the OrderS table. However, unlike the OndleteError event of the Customer Table, here is used to make the user choose whether to delete this "problem" record, if the user answers Yes, delete all the records of the items table, and then put The Action parameter is set to DareTry, indicating that after exiting this event handle, will re-try the record. If the user answers NO, call Abort to discard the operation.
Procedure TDM.OrdersDeleteError (DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction); BeginIf E is EDBEngineError thenIf (E as EDBEngineError) .Errors [0] .Errorcode = eDetailsExist thenBeginIf MessageDlg ( 'Delete this order and related items?' , MTConfirmation, [mbyes, mbno], 0) = mryes thenbeginwhile items.recordcount> 0 do items.delete; action: = DareTry; end; 13.6 A Demonstration program for filtering data set An exemplary program that is filtered by the data set, the project name is Filter, which can be found in the C: / Program Files / Borland / Delphi4 / Demos / DB / Filter directory. Its main form is shown in Figure 13.13. This demonstration program demonstrates how to dynamically set filter conditions by modifying the Filter property, how to change the filtering criteria in the handle of the ONFILTERRecord event, how to get parameters from another data set from another data set, and how to switch one grid data set. We still start from the data module because several critical components are placed on the data module. The data module of this program is called DM1, as shown in Figure 13.14. There is a TTable component called Customer on the data module to access the Customer table. There is a tQuery component called SqlCustomer, accesses the Customer table through the SQL statement, the SQL statement is as follows: select * from "customer.db" data module has a TDataSource component called Customersource, which can be set to Customer, or Set to Sqlcustomer. There is also a TQuery component called Sqlorders on the data module, which is used to query the orders table. . There are two grids on the main form, and this grid called dbgrid1, below this grid called DBGRID2. The DataSource property of DBGRID1 is set to Customersource, and the Customersource's DataSet property can be set to Customer, or it can be set to SQLCustomer, which is switched by two radio buttons in the "DataSet" box.
Procedure TfmCustView.rgDataSetClick (Sender: TObject); varst: string; BeginWith DM1, CustomerSource DoBeginIf Dataset.Filtered then st: = Dataset.Filter; Case rgDataset.ItemIndex of0: If Dataset <> SQLCustomer then Dataset: = SQLCustomer; 1: If Customersource.DataSet <> Customer The DataSet: = Customer; end; if st <> '' Then BegindataSet.filter: = st; dataset.filtered: = true; end; end; end; When the user clicks the Filter Customers button Open a window to allow users to set filter conditions. About this window is later. Procedure tfmcustview.speedButton1Click (Sender: TOBJECT); beginfmfilterfrm.show; end; dbgrid2 Displays data of the OrderS table. The user can select whether to filter the data set through a checkbox, which is actually modifying the Filtered property of SqlORDERS. Procedure TfmCustView.cbFilterOrdersClick (Sender: TObject); BeginDM1.SQLOrders.Filtered: = cbFilterOrders.Checked; If cbFilterOrders.Checked thenEdit1Change (nil); End; If you check this box, then calls Edit1Change the "Amount Paid" box The input value assigns one of the common variables in the DM1 unit called Ordersfilteramount, as for this variable, which will be introduced later when introducing the DM1 unit. Calling Refresh will trigger SQLORDERS's ONFILTERRECORD event. If the user typed non-digital characters in the "AmountPAID" box before calling Refresh, calling Refresh will trigger the EconvertError exception, so the program is protected by the TRY 匛 XCEPT structure. Procedure TfmCustView.Edit1Change (Sender: TObject); BeginIf (cbFilterOrders.checked) and (Edit1.Text <> '') thenTryDM1.OrdersFilterAmount: = StrToFloat (fmCustView.Edit1.Text); DM1.SQLOrders.Refresh; ExceptOn EConvertError DoRaise Exception .Create ('Threshold Amount Must Be a Number') Endend; previously introduced such a programming skill, when a navigator is navigating a few data sets, the raster should be handled to dynamically switch the TDBNAVIGATOR component. DataSource properties.
Procedure TfmCustView.DBGrid1Enter (Sender: TObject); BeginDBNavigator1.DataSource: = DBGrid1.DataSource; End; Procedure TfmCustView.DBGrid2Enter (Sender: TObject); BeginDBNavigator1.DataSource: = DBGrid2.DataSource; End; Further, when the user selects "About" When the command is displayed, the About box will appear. Code is as follows: Procedure TfmCustView.About1Click (Sender: TObject); BeginWith TFMAboutBox.Create (nil) do TryShowModal; FinallyFree; End; End; This program also demonstrates how to handle OnFilterRecord event: Procedure TDM1.SQLOrdersFilterRecord (DataSet: TDataSet; var Accept : Boolean; beginaccept: = SQLORDERSAMOUNTPAID.VALUE> = ORDERSFILTERAMOUNT; END; Please reader pay attention, because ORDERSFILTERAMOUNT is a variable, this means that the user can dynamically change the filter condition as long as the value of this variable can dynamically change the filter condition. When the user clicks on the Filter Customers button, open a dialog to set the filter criteria. This dialog is shown in Figure 13.15. The top "List" box is a combo box for listing the filtering conditions expressions that have been entered in the past. The "Condition" box is a multi-line text editor for entering filter conditional expression. The "fields" box is a list box that lists all fields in the Customer table because the fields are required in the filter conditional expression. Therefore, the program first populates this list box in the handle of the oncreate event in which this window is handled. In addition, the program is also added to the "List" box to join two filter conditions. Procedure TfmFilterFrm FormCreate (Sender: TObject); varI: Integer; BeginFor I:. = 0 to DM1.CustomerSource.Dataset.FieldCount - 1 doListBox1.Items.Add (DM1.Customer.Fields [I] .FieldName); ComboBox1.Items .Add ('LastInvoiceDATE> =' ' DateTostr (Encodedate (1994, 09, 30)) ' ''); ComboBoX1.Items.Add ('country =' US '' and lastinvoicedate> '' DateTostr (Encodedate (1994, 06, 30)) '' '); END; When the user selects or enters a filter expression from the "List" box, first empty the following "condition" box, then use the user Select or entered filter expressions are added to the "CONDition" box. Procedure tfmfilterfrm.comBobox1change (sender: Tobject); beginMemo1.Lines.clear; memo1.lines.add (comboBoX1.text); END; When the user doubles a field in the "Fields" box, add this field to "condition" In the box.
Procedure TfmFilterFrm.AddFieldName (Sender: TObject); BeginIf Memo1.Text <> '' thenMemo1.Text: = Memo1.Text ''; Memo1.Text: = Memo1.Text ListBox1.Items [ListBox1.ItemIndex]; End; When the user doubles an operator in the "Operators" box, add the operator to the "Condition" box. Procedure TfmFilterFrm.ListBox2DblClick (Sender: TObject); BeginIf Memo1.Text <> '' thenMemo1.Text: = Memo1.Text '' ListBox2.Items [ListBox2.ItemIndex]; End; because the user it is possible to filter the conditional expression Divided into a few lines, therefore, the program needs to convert a string of strings in behavior into a string list because the Filter property is a TSTRINGS object. Procedure TfmFilterFrm.Memo1Change (Sender: TObject); var I: Integer; BeginComboBox1.Text: = Memo1.Lines [0]; For I: = 1 to Memo1.Lines.Count - 1 doComboBox1.Text: = ComboBox1.Text ' ' Memo1.lines [i]; end; the program uses two checkbox to allow the user to set the filtering option. One is a "case sensitive" box, if this box is selected, the FILterOptions property will contain the FocaseInSensitive element. The other is the "NOPArtial Compare" box, if this box is selected, the FonopArtialCompare element will be included in the FilterOptions property.
Procedure TfmFilterFrm.cbCaseSensitiveClick (Sender: TObject); BeginWith DM1.CustomerSource.Dataset DoIf cbCaseSensitive.checked thenFilterOptions: = FilterOptions - [foCaseInSensitive] ElseFilterOptions: = FilterOptions [foCaseInsensitive]; End; Procedure TfmFilterFrm.cbNoPartialCompareClick (Sender: TObject); BeginWith DM1.CustomerSource.Dataset DoIf cbNoPartialCompare.checked thenFilterOptions: = FilterOptions [foNoPartialCompare] ElseFilterOptions: = FilterOptions - [foNoPartialCompare]; End; when the user enters the filter conditional expressions and set filtering options, you can click the "Apply" button assigned to the filter condition expression filter property: Procedure TfmFilterFrm.ApplyFilter (Sender: TObject); BeginWith DM1.CustomerSource.Dataset DoBeginIf ComboBox1.Text <> '' thenBeginFilter: = ComboBox1.Text; filtered: = True; fmCustView.Caption: = 'Customers - Filtered'; endelse beginfilter: = '; Filtered: = false; fly; filtered: = false: =' Customers - UNFILTERED'ENEND; END; END; if the user clicks the "Clear" button, put the "condition" box Clear and add the input filtration condition expression to the "List" box. Procedure tfmfilterfrm.sbtnclearClick (Sender: Tobject); Var st: string; begin memine;, st: = combobox1.text; comboBox1.text: = '; if ComboBoX1.Items.Indexof (st) = -1 Then ComboBox1.Items.Add (st); end; turn this window when the user clicks the "Close" button. Procedure tfmfilterfrm.sbtncloseclick (Sender: TOBJECT); Beginclose; End; 13.9 A complex database application This section describes a complex database application, the project name is MastApp, it can be in C: / Program files / Borland / Delphi4 / It is found in the Demos / DB / MastApp directory. Its main form is shown in Figure 13.18. Figure 13.18 MastApp's main form This program is more complicated, and the reader must know its program structure. Let's introduce the main form. We still start from the handle of the handling oncreate event, because this is the starting point of the application.
Procedure TMainForm.FormCreate (Sender: TObject); BeginClientWidth: = CloseBtn.Left CloseBtn.Width 1; ClientHeight: = CloseBtn.Top CloseBtn.Height; MainPanel.Align: = alClient; Left: = 0; Top: = 0 InitrsRun; END; two lines of code for setting the width and height of the main window. Setting the LEFT attribute and TOP attribute to 0 will display the main window to the top left corner of the screen. Note: This exemplary program has an error that ReportSmith has been canceled from Delphi 3, so the UpdatersConnect called initrsRun and the initrsConnect in the initrsRun is redundant. When the user uses the "New Order" command on the "File" menu or click the "Neworder" button on the toolbar, the program will open the "Order Form" window, the code is as follows: Procedure Tmainform.Neworder (Sender: TOBJECT); BeGinedOrderform. ENTER; END; When the user uses the "Print Report" command on the "File" menu, select "Customer List" to print the PrintCustomerReport function Print the customer report. Procedure TMainForm.CustomerReport (Sender: TObject); BeginPrintCustomerReport (False); End; wherein, PrintCustomerReport is defined as: Procedure TMainForm.PrintCustomerReport (Preview: Boolean); BeginWith MastData.CustByLastInvQuery DoBeginOpen; If Preview then CustomerByInvoiceReport.PreviewElse CustomerByInvoiceReport.Print , Close; end; end; because the value passed to the Preview parameter is False, therefore, this will be printed instead of preview reports. When the user uses the "Print Report" command on the File menu, select "Order History" and call the printorderReport function Print order report.
Procedure TMainForm.OrderReport (Sender: TObject); BeginPrintOrderReport (False); End; wherein, PrintOrderReport is defined as: Procedure TMainForm.PrintOrderReport (Preview: Boolean); Const FromToHeading = 'From' '% s'' To ''% s' ''; BeginWith QueryCustDlg DoBeginMsgLab.Caption: = 'Print all orders ranging:'; If FromDate = 0 then FromDate: = EncodeDate (95, 01, 01); If ToDate = 0 then ToDate: = Now; If ShowModal = mrOk thenWith MastData.OrdersByDateQuery DoBeginClose; Params.ParamByName ( 'FromDate') AsDate:. = FromDate; Params.ParamByName ( 'ToDate') AsDate:. = ToDate; Open; OrdersByDateReport.FromToHeading.Caption: = Format (FromToHeading, [DateToStr (FromDate), DateTostr (TODATE)]); if preview thenorder otdatereport.previewElse ORDERSBYDATEREPORT.PRINT; Close; end; end; end; printOrderReport function first pop up a dialog shown in Figure 13.19, allowing users to select the first date. Figure 13.19 Selecting the first date When the user selects the first date and click the OK button, preview the report because the preview parameter is false. When the user uses the "Print Report" command on the "File" menu, select "Invoice" and call the PrintInvoiceReport function to print the delivery single report. Procedure TMainForm.InvoiceReport (Sender: TObject); BeginPrintInvoiceReport (False); End; wherein, PrintInvoiceReport is defined as: Procedure TMainForm.PrintInvoiceReport (Preview: Boolean); BeginIf PickOrderNoDlg.ShowModal = mrOk then If Preview thenInvoiceByOrderNoReport.PreviewElseInvoiceByOrderNoReport.Print; End; PrintInvoiceReport function first pops up the dialog shown in Figure 13.20, so that the user selects the order number. Figure 13.20 Selecting the order number When the user uses the "Printer Setup" command on the "File" menu, the Print Settings dialog opens.
Procedure TMAINFORM.PRINTERSETUPCLICK (Sender: TOBJECT); Begin PrinterSetup.execute; END; When the user uses the "ORDERS" command on the "View" menu or click the "Browse" button on the toolbar, the program will open "Order By Customer" window, as follows: Procedure TMainForm.BrowseCustOrd (Sender: TObject); BeginCase GetDateOrder (ShortDateFormat) OfdoYMD: ShortDateFormat: = 'yy / mm / dd'; doMDY: ShortDateFormat: = 'mm / dd / yy'; doDMY: ShortDateFormat: = 'DD / MM / YY'; end; browSecustord first calls the getDateORDER function to return the date of the date and pop up the "Orderby Customer" window. The GetDateOrder function is defined: function getdateOrder (const dateformat): TDATEORDER; var i: integer; beginResult: = Domdy; i: = 1; While i <= length (DateFormat) Do Begincase CHR (ORD (DateFormat [i ]) and $ df) of'Y ': result: = DOYMD;' M ': Result: = Domdy;' D ': Result: = DODMY; Else Inc (i); Continue; End; End; End; Result: = Domdy; END; When the user uses the "Parts / Inventory" command on the "View" menu or click the "Parts" button on the toolbar, the program will open the "Browse Parts" window, the code is as follows: Procedure Tmainform.Browseparts (Sender : TOBJECT); Beginbrpartsform.Show; END; When the user uses the "Stay on Top" command on the "View" menu, the main window is always on the front of the screen. Procedure TMainForm.ToggleStayonTop (Sender: TObject); BeginWith Sender as TMenuItem DoBeginChecked: = not Checked; If Checked then MainForm.FormStyle: = fsStayOnTopElse MainForm.FormStyle: = fsNormal; End; End; Note to the reader a programming technique, namely how to make The window is always on the front end of the screen. This program allows users to choose a local database or a remote database. When the user selects the Local Data (PARADOX DATA "command on the" VIEW "menu, use the local database. When the user selects the "Remote Data" command on the "View" menu, use the InterBase database. Note: When you select the latter, you must ensure that the Interbase server is installed and is running, otherwise an exception is triggered.
