Delphi Database controls

Settext and gettext event processing functions of the tfield object

The settext and gettext event processing functions of the tfield object can be used to conveniently display the corresponding values of the field code and code.

Aggregation of the tsimpledataset/tclientdataset object

The aggregate attribute of the tdataset object can be used to calculate the total value, average value, or maximum and minimum values of the data in the client dataset.

Use tbookmark to mark and record bookmarks and access data in the dataset

By using the bookmark attributes of the tbookmark and tdataset objects, you must set bookmarks for the current record,
Example
VaR
BK: tbookmark;
Begin
BK: = Ds. getbookmark; // sets a bookmark.
....
DS. gotobookmark (BK); // return to the original record
DS. freebookmark (BK); // release memory
End;

 

Difference between locate and lookup: After locate finds the searched data, it will move the current record location to the data it finds.
After lookup finds the searched data, it will return the specific field value of the retrieved data, but will not move the current record location.

The third parameter of the lookup method is to specify the Field Values of the data to be returned after lookup finds the data to be searched. If a developer wants to return multiple field values through lookup, each field is separated by a semicolon.

The value returned by the lookup method is the value specified by the third field. If the lookup method returns multiple fields, the return value is a variant array, each returned field is stored in the element of the variant array.

Example:

 

VaR
Sfields: string;
Vresult: variant;
Icount: integer;
Begin
Vresult: = Ds. Lookup ('fieldnamea, fieldnameb', vararraycreate ([valuea, valueb], varvariant), 'fieldname1, fieldname2 ');
If (varisarray (vresult) then
Begin
Sfields: = '';
For icount: = vararraylowbound (vresult, 1) to vararrayhighbound (vresult, 1) do
Begin
Sfields: = sfields + ';' + vresult [icount];
End;
End
Else
Edtreturn. Text: = vresult;
End;

Be careful when using devexpress

1. Setting datacontroller. Filter. autodatasetfilter = true can cause the filter to directly affect the background dataset.
2. Usage of cxformatcontroller. When the system's default data format definition is modified, cxformatcontroller can refresh by getformats and policylisteners.
Example:
Currencystring: = 'meta ';
Sysutils. currencyformat: = 3;
Sysutils. currencydecimals: = 4;
Cxformatcontroller. getformats;
Cxformatcontroller. policylisteners;
3. Update the values of a column in the selected row as follows,
Column. editvalue: = avalue. If you can use record. Values [aindex]: = avalue, you cannot directly update the values in the dataset corresponding to the value specified in the row. You can use column. editvalue.

Tdatasetprovider. updatemode attribute;
Upwhereall is the most restrictive, but guarantees the maximum consistency of records. If two users edit the same record, the first user can update the record, and the second user will receive an error message "the other user has modified this record. If you want to improve the field for this check, you can remove the pfinwhere item in the corresponding tfield. providerflags attribute.

Upwherechanged actually allows two users to edit a record at the same time. conflicts are not checked when two users edit different fields of the same record. For example, if user a modifies the address field and updates the record, user B can still modify the birthdate field and successfully update the record.

Upwherekeyonly: maximum width. Every user can modify and update a record as long as it exists in the database. In this way, the subsequent modifications always overwrite the previous ones.
The default mode is upwhereall. Modify the mode that you think is appropriate!

Process multiple returned result sets in ADO
When using SQL statements, multiple result sets are often returned. For example, exec sp_helpconstraint [tablename] returns two result sets. If we need to process multiple result sets. The nextrecordset method of ADO is available. Usage: VaR
R: integer
Begin
With adoquery do
Begin
Close;
SQL. Text: = 'exec sp_helpconstraint' + ablename;
Open;
R: = 0;
Recordset: = nextrecordset (R );
{Do something}

End

Experiences and skills in using clientdataset

One factor affecting the processing speed of clientdataset
Tclientdataset is a good control when developing a database in Delphi. It has powerful functions.
I often use clientdataset for memorydataset. You can also save the clientdataset data as XML, so that you can use a simple local database. There are many more features. In the process of using clientdataset, I will share my personal experience with you on how to increase the processing speed.

Generally, we use
... Clientdataset --> datasource --> dbcomponent
This structure directly operates clientdataset when processing data. However, most dbcomponet immediately responds to changes in clientdataset. If you insert a lot of data into clientdataset, dbcomponent will respond several times, and the response process varies depending on the control, speed, and number of processes. This affects the execution efficiency of the program. Therefore, in the process of clientdataset, I used the clientdataset. disablecontrols and clientdataset. enablecontrols methods to enable and disable the data display relationship between dbcomponent and clientdataset.
For example:
Clientdataset .. disablecontrols;
...
For I: = 0 to 10000 do
Begin
Clientdataset. append;
...
Clientdataset. post;
End;
...
Clientdataset. enablecontrols
...
After doing so, you will find that the processing speed is doubled from the time when no method was previously used.

Clientdataset data query.
The experiences and skills I 've introduced use clientdataset as an example and can be applied to other dataset. I will not talk much about it. Let's take a look at the code first and then summarize it.
1. Scanning scan data search
This is the simplest, most direct, and slowest way to traverse all data:

Procedure tform1.scanbtnclick (Sender: tobject );
VaR
Found: Boolean;
Begin
Found: = false;
Clientdataset1.disablecontrols;
Start;
Try
Clientdataset1.first;
While not clientdataset1.eof do
Begin
If clientdataset1.fields [fieldlistcombobox. itemindex]. value =
Searchtext then
Begin
Found: = true;
Break;
End;
Clientdataset1.next;
End;
Done;
Finally
Clientdataset1.enablecontrols;
End;
If found then showmessage (searchtext +
'Found at record '+ inttostr (clientdataset1.recno ))
Else
Showmessage (scanforedit. Text + 'not found ');
End;

2. Finding for Data
The oldest But fastest way to search.
Use findkey/findnearest to find one or more qualified data records. Of course, the field to be searched must be an indexfield. We can see that this index-based search speed is very fast.

Procedure tform1.findkeybtnclick (Sender: tobject );
Begin
Start;
If clientdataset1.findkey ([searchtext]) then
Begin
Done;
Statusbar1.panels [3]. Text: = searchtext +
'Found at record '+
Inttostr (clientdataset1.recno );
End
Else
Begin
Done;
Statusbar1.panels [3]. Text: =
Searchtext + 'not found ';
End;
End;

Procedure tform1.findnearestbtnclick (Sender: tobject );
Begin
Start;
Clientdataset1.findnearest ([searchtext]);
Done;
Statusbar1.panels [3]. Text: = 'the nearest match to '+
Searchtext + 'found at record '+
Inttostr (clientdataset1.recno );
End

3. Going Positioning
There is basically no difference between gotokey/gotonearest and findkey/findnearest. It is also an index-based search. The only difference is how you define your search. There are also differences in code:
Clientdataset1.setkey;
Clientdataset1.fieldbyname (indexfieldname). Value: = searchtext;
Clientdataset1.gotokey;
It is equivalent
Clientdataset1.findkey ([searchtext]);
To make good use of these two index-based searches, you also need to understand the clientdataset and index mechanisms. The index mechanism is not detailed here. A basic principle is that indexes are required for search.

4. Locating
Two or three search methods are based on indexes. However, in actual applications, fields other than indexfield may be searched. Then we can use locate. However, the search speed is less than 2 or 3. For example, if you search for a record of 9000/10000, the locate needs 500 ms, scanning needs to be greater than 2 s, and findkey only needs 10 ms (but when you open clientdata, it takes 1 s to create an index ). Procedure tform1.locatebtnclick (Sender: tobject );
Begin
Start;
If clientdataset1.locate ('field1, field2.. ', vararrayof ['value1, value2..'], []) then
Begin
Done;
Statusbar1.panels [3]. Text: =
'Match located at record '+
Inttostr (clientdataset1.recno );
End
Else
Begin
Done;
Statusbar1.panels [3]. Text: = 'no match located ';
End;
End;

Clientdataset provides multiple data search methods. However, they have their own advantages and disadvantages.
The above examples include start; and done. If you are interested, you can join the timing point for speed testing.
Scanning is the simplest, but the slowest. Because it is slow, you have to use the clientdataset. disablecontrols and clientdataset. enablecontrols methods (as I mentioned in the previous article ).
There are many findkey/findnearest (gotokey/gotonearest) codes, but they are very fast. The index must be used. The difference is that the index required by find must be set up, while goto can create an index for the first time.
Locate is the most convenient to use and does not require an index, but the speed is not fast.