The master / Detail is based on a "one-to-many" relationship, providing detailed information in a database table, and this table is recorded by another database table. Based on the master-slave composite structure, we can give all the record information related to this record when browsing data in a table.
Borland C Builder provides a database control of the TTable and TQuery types that can easily implement the master / detail relationship of the database table. This article uses the example database BcDemos from BCB as an example to explain how to implement data tables in different ways. The master-slave composite structural relationship, with data browsing function as an example: ie browsing the main data table Customer.db (customer information) record, display all records related to it from the data table orders.db (customer order information) information.
The basic properties related to the TTable control are as follows: DatabaseName: Set the database base name or database directory path to open; TABLENAME: Set the database table file name to be associated; active: Set to true, the database table file is turned on, otherwise you need code. Open the data table in the program. The basic properties of the TQuery control: DatabaseName: Set the database base or database directory path you want to open; SQL: TSTRING type, the SQL data query statement you want to execute, you can double-click to open the SQL property directly in the object viewer (Object Inspector). Active: Automatically opens the query database table file when setting to TRUE, otherwise you need to use the code to open the query data table in the program. The two associated with m / d will be interpreted in conjunction with the example below.
I. TTABLE control associated main, from the table to implement Master / Detail Relationship Report
The simplest implementation method of Master / Detail relationship is to use two TTable controls to associate with the primary table and slave tables, respectively. The tableMaster and TableDetail are named TableMaster and TableDetAme, which sets TableMaster's DatabaseName for bcdemos, tablename for custom; set TableMaster's DatabaseName for BcDemos, TableName as Orders.db. This can be associated with the top owner from the data table.
Because you want to browse the display data table log content, you need to place two TDBGRID type control DBGRIDMASTERs on the form, dBGridDetail to display the corresponding record of the M / D relationship master from the table; set two TDataSource type control DataSourceMaster, DatasourceDetail Indicate the data source. Set the DataSourMaster's DataSet property to TableMaster, DataSourceDetail's DataSet property is TableDetail, pointing to the master's watches. Set the DataSource property of DBGRIDMASTER to DataSourceMaster, DBGRIDetail's DataSource property is DataSourceDetail.
The key to implementing the master / detail relationship is the masterSource property from the table related control tableDetail and the masterfields attribute: the former points to a TDataSource control, the control DataSet property should point to the master table of the master / Detail relationship; the latter specifies the primary table and slave table The associated field, you need to double-click to open the Field Link Designer dialog for settings, select the corresponding associated field from the table and the primary table to "Joined Fields" in the "Joined Fields". In this example, the Custom (Customer.db Customer Information) and the Custno field of the slave table (ORDERS.DB order information) are associated fields, so set TableDetail's MASTERSOURCE attribute to DataSourceMaster, point to the primary table; Masterfields property is Custno associated field. If TableMaster and TableDetail's Active property is false, you need to double-click the form form1 blank, add the following two black body code to the TFORM1 :: FormCreate () event handle:
TableMaster-> Active = true; TableDetail-> Active = true;
Run the program, you can browse to the relevant data records of the Master / Detail relationship master from the DBGRIDMASTER, DBGRIDETAIL.
Second, the TQUERY control implements Master / Detail Relationship Report
The main difference between the TQUERY control and TTable controls is that the TQUERY control is dynamically accessed by the SQL instruction statement assigned by the SQL property, and the TTable control is a static and data table. The TQuery control can be associated with multiple database tables simultaneously, and the TTable control can only be associated with a single database table. Compared to TTable controls, the TQUERY control is more suitable for applications in multi-layer, large, network database systems because of the flexibility and relative complexity of SQL language.
2.1, TTable control association primary table, TQUERY control association point implementation Master / Detail relationship report
The TableDetail control is deleted on the form FORM1, place the TQuery Type Control QueryDetail, modify the DataSourceDetail's DataSet property is querydetail, and the remaining control properties are unchanged. The TQUERY control can set a restriction condition on the access range, so that the function of the M / D relationship can be implemented. Set QueryDetail's DatabaseName to BCDemos, SQL properties:
"Select ORDERNO, Custno, Saledate, Empno from Orders where ORDERS.CUSTNO =: Custno".
That is, the same record set as those in the table and the primary table CustNO field are removed and only four qualified field information implement the master / detail relationship, and the other key is the DataSource attribute and params property from the table associated control QueryDetail: former Point to a TDataSource control, the control DataSet property should indicate the data source of the SQL instruction parameter; the latter set the parameters in the SQL instruction, you need to double-click to open the QueryDetail Parameters dialog box for settings, select the corresponding SQL instruction Set it correctly. In this example, the CUSTNO field of the primary table (Customer.db customer information) is used as the parameter of the SQL instruction, so setting the queryDetail's DataSource property is DataSourceMaster, pointing to the primary table; the params property is a Custno associated field for parameters. Note: Before the parameter name in the SQL instruction, must be a prefix as a prefix to distinguish.
Add the following two black body code to the TFORM1 :: FormCreate () event handle:
TableMaster-> Active = true; querydetail-> active = true;
Run the program, you can browse to the relevant data records of the master / detail relationship from the DBGRIDMASTER, DBGRIDetail, and note that only four field information is displayed only from the raster data record from the table.
If QueryDetail's SQL attribute is: "Select * from Orders where ORDERS.CUSTNO =: Custno", the field name removed from the table is not limited.
2.2, TQuery control associated master, from the table to implement Master / Detail Relationship Report
Master / Detail relationship is more flexible to implement the primary and slave tables associated with the TQuery control, namely QueryMaster and QueryDetail. Set QueryMaster and QueryDetail's DatabaseName to bcdemos; QueryMaster's SQL property is: "Select * from customer", DataSource and params properties are empty, so that you can associate with the main data table, get the same effect as TableMaster control; QueryDetail The remaining properties are the same in 2.1. Correspondingly, the DataSourceMaster and DataSourceDetail are changed, respectively, point to QueryMaster and QueryDetail.
Note: Because the TQuery control is dynamically accessed, when only the TQuery control is associated with the main slave table, the Active property from the table associated control QueryDetail must be set to false, otherwise the runtime BDE will report the error message.
Add the following two black body code to the TFORM1 :: FormCreate () event handle:
Querymaster-> Active = true; querydetail-> active = true;
Run the program, you can browse to the MASTER / DETAIL relationship of the same effect as 2.1 in DBGRIDMASTER, DBGRIDETAIL.
Third, the TQuery control is summarized, the classification report is summarized in the TQuery control.
In a flow-water operation system or real-time monitoring system, it is often required to store the current record information to a single data sheet file, and afterwards, it will make a summary, classification, storage, etc. The TTABLE control is only possible to implement the Master / Detail relationship in this single table, which can only be implemented with the TQuery control in this single table. ORDERS.DB (order information table) is still explained how to flexibly use the SQL instruction properties of the TQuery control. Place the TQuery control Querymaster and QueryDetail on the form FORM1, and other control properties are unchanged. Set QueryMaster and QueryDetail's DatabaseName to bcdemos; QueryMaster's SQL property is:
"SELECT DISTINCT CustNo, SUM (ItemsTotal) ItemsTotalAll, SUM (Freight) FreightAll, SUM (AmountPaid) AmoutPaidAll FROM Orders GROUP BY CustNo ORDER BY CustNo", DataSource Params properties are empty and generates M / D relationship main table; QueryDetail's SQL property is set to:
"Select ORDERNO, CUSTNO, PaymentMethod, ItemStotal, Taxrate, FREIGHT, AMOUNTPAID from Order, FRETIDERS.CUSTNO =: Custno", the DataSource property is DataSourceMaster, pointing to the primary table; Params property is parameters as a Custno associated field as a parameter. Corresponding, DataSourceMaster and DataSourceDetail points to QueryMaster and QueryDetail, respectively.
The SQL instruction of the sample program implements the following functions: in the table in the table, the total value, the total freight, the total number of payments is the recorded field, and the corresponding information summarized from the order information table is summarized as the primary table; The order number, customer number, payment method, delivery value, tax rate, freight, payment number of records are recorded in the table, and extract corresponding information from the order information table as a slave table; the associated field of the master from the table Custno (Customer number) passed through the parameters of the SQL instruction. The final result realizes the display of each customer's total order information, which shows the details of the detailed order information record of the corresponding customer's qualifier on the DBGRIDetail control.
Add the following two black body code to the TFORM1 :: FormCreate () event handle:
Querymaster-> Active = true; querydetail-> active = true;
Run the program to browse to the master / detail relationship from the Master / Detail relationship in DBGRIDMASTER, DBGRIDETAIL.
In summary, for a general master / detail relationship application, use TTable controls to cope, and achieve further functions, such as multi-layer M / D relationship, remote network database operation, multi-table interrelated type, single Table m / d relationship database programming, you need to use the TQUERY control to operate the database with the flexibility of the SQL instruction. Master / Detail relational database programming of complex features can be honest in the camogetices of φ φ φ 嘤 嘤 帮 帮 帮
This article debug passes in C Builder 6.0 / WinXP, Win2000, Win2003.
