Delphi：检查DataSet的Record是否可见或过滤 [英] Delphi: check if Record of DataSet is visible or filtered
问题描述
在工作中，我们有一个称为ClientdatasetGrid的组件，
，它允许用户通过点击一个或多个列标题对网格的记录进行排序。
我已经做了一个工作的组件，TEdit的后代，我称之为TDBFilterEdit。
一旦你分配了一个DataSet或DBGrid，它为DataSet创建一个OnFilterRecord事件，并且在您停止更改执行该事件的文本之后。
只要数据集已被过滤并且用户对网格进行排序，就会出现问题。
grid-component将IndexDefs添加到Clientdataset中，首先删除当前的IndexDef，更新，添加新索引并再次更新。
每当索引被删除或添加时，我的OnFilterRecord事件被触发。
我通过禁用控件和从网格内部调用OnFilterRecord事件来缓解这一点，直到添加新的索引为止。
cds.DisableControls（）;
try
extProc：= nil;
if（TMethod（cds.OnFilterRecord）.Code<> nil）和（TMethod（cds.OnFilterRecord）.Data<> nil）然后
begin
TMethod（extProc） TMethod（cds.OnFilterRecord）;
cds.OnFilterRecord：= nil;
结束
...
... //< - 删除索引&创建新索引
...
finally
cds.OnFilterRecord：= extProc;
cds.EnableControls（）;
结束
一旦事件被再次分配，它就是不可思议的调用，并且正在遍历所有X记录，即使用户可能只会看到5。
现在我正在寻找一种方法来查看记录是否已被过滤出来，以便我可以在我的过滤器方法中跳过它，如果文本没有改变。
编辑：由于已经要求MVCE，我将发布一个简短版本的OnFilterRecord过程
- 每当组件没有收到1秒的输入时，执行以下过程
-
- fStringTypes：= [ftString，ftMemo，ftFMTMemo，ftFixedChar，ftWideString];
- fTimeTypes：= [ftDate，ftTime，ftDateTime，ftTimeStamp];
-
在程序完成后，定时器被禁用，控件再次启用。
程序TDBEditFilter.FilterRecords（DataSet：TDa taSet var Accept：Boolean）;
var
...
begin
// initiliaztion //
s：= FilterText; // Filtertext =用户输入到TDBEditFilters Textfield
TestFloat：= 0;
接受：= False;
///////////////
for i：= 0 to fDBGrid.Columns.Count-1 do // for all DBGrid-Columns
begin
如果fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.DataType在fStringTypes然后
begin
Strvalue：= fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.AsString;
接受：= AnsiContainsText（Strvalue，s）; //< - 忽略大写/小写
end
如果fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.DataType在fTimeTypes然后
开始
StrValue：= DateTimeToStr（fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.As DateTime，Local_Form_Settings）;
接受：= Pos（StrValue，s）<> 0;
end
else if fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.DataType = ftBlob then
begin
// ignore Blob
end
else //任何字段类型必须是一个数字字段类型，如整数或浮点数
begin
如果TryStrToFloat（s，TestFloat）= True然后
begin
接受：= （TestFloat = fDataSet.FieldByName（fDBGrid.Columns [i] .FieldName）.AsFloat）;
结束
结束
如果Accept = True然后break; //停止检查此记录并检查下一条记录
end;
结束
我以为我会把这个作为一个单独的答案，因为我一直在尝试
与一个过滤器TEdit的工作原理与我猜你的相似的方式，它不
似乎表现出任何特殊的性能问题。我的主要假设是，您对感兴趣的每个数据字段使用一个过滤器TEdit，而是单个用户键入复合Sql样表达式的一个过滤器，包括字段名称，比较运算符等。
我必须做的猜测是为什么我说这将有助于你包括一个MCVE。
我们把它写成自包含的，即它生成自己的数据，而不需要
一个外部数据库。
正如你看到的，如果你尝试有一个CDS包含3000个记录，
更新过滤器的时间是几十毫秒（在笔记本电脑下不到20秒）。
如果CDS包含30000条记录，则过滤器更新时间大致线性地增加
到大约200 ms，这似乎完全可以从gui-responsivenes pov。
（传统上，当记录数达到数万），传统上，TCDS被认为是一种砖墙性能表现。
请注意，为了简单起见， a）由于处理用户输入的部分日期的复杂性，我没有为BirthDate或其他任何东西使用DateTime的邮箱。$ /
b）在 OnFilterRecord 事件中，LastName，FirstName和Age比较
是通过将字段比较为字符串与相应的过滤器表达式。
c）过滤器表达式（如果为非空白）以星号
左右填充，并且值比较使用Masks单元中的 MatchesMask 函数完成。
请参阅 FilterExpr 。
d）IndexDef的FieldNames由其中的字段的名称组成
过滤器编辑的文本不为空。
e）如果用户快速键入几个
字符，则gui更新速度太慢继续进入TEdits，你可以通过
来解决这个问题，通过它们的KeyUp事件
中的代码来替换TEdits的OnChange事件代码，它启用一个间隔为150 ms的TTimer。然后，在其OnTimer中，调用 UpdateFilter 。
代码：
TForm1 = class（TForm）
DBGrid1：TDBGrid;
CDS1：TClientDataSet;
DataSource1：TDataSource;
Memo1：TMemo;
CDS1ID：TIntegerField;
CDS1Age：TIntegerField;
CDS1LastName：TStringField;
CDS1FirstName：TStringField;
edLastNameFilter：TEdit;
edFirstNameFilter：TEdit;
edAgeFilter：TEdit;
procedure CDS1FilterRecord（DataSet：TDataSet; var Accept：Boolean）;
procedure edLastNameFilterChange（Sender：TObject）; //将
// FirstName和Age TEdits的OnChange事件设置为此，
过程FormCreate（Sender：TObject）;
private
程序日志（const Title，Msg：String）;
函数FilterExpr（const Input：String）：String;
protected
public
LastNameFilter，
FirstNameFilter，
AgeFilter：String;
IndexFields：String;
IndexDef：TIndexDef;
procedure UpdateFilterExprsAndIndex;
procedure UpdateFilter;
结束
[...]
rocedure TForm1.FormCreate（Sender：TObject）;
var
i：整数;
Ch1，
Ch2：Char;
LastName，
FirstName：String;
年龄：整数;
begin
CDS1.CreateDataSet;
CDS1.DisableControls;
尝试
为i：= 1到30000 do begin
Ch1：= Chr（Ord（'a'）+ random（26））;
Ch2：= Chr（Ord（'a'）+ random（26））;
LastName：= StringOfChar（Ch1，1 + Random（10））;
FirstName：= StringOfChar（Ch2,1 + Random（10））;
年龄：= Trunc（随机（71））;
CDS1.InsertRecord（[i，LastName，FirstName，Age]）;
结束
finally
CDS1.First;
CDS1.EnableControls;
结束
结束
procedure TForm1.Log（const Title，Msg：String）;
begin
Memo1.Lines.Add（Title +'：'+ Msg）;
结束
程序TForm1.CDS1FilterRecord（DataSet：TDataSet; var Accept：Boolean）;
begin
接受：= True;
如果LastNameFilter<> ''然后
接受：= MatchesMask（CDS1LastName.AsString，LastNameFilter）;
如果不接受则退出;
如果FirstNameFilter<> ''然后
接受：=接受和MatchesMask（CDS1FirstName.AsString，FirstNameFilter）;
如果不接受则退出;
如果AgeFilter<> ''然后
接受：=接受和MatchesMask（CDS1Age.AsString，AgeFilter）;
结束
procedure TForm1.edLastNameFilterChange（Sender：TObject）;
begin
UpdateFilter;
结束
程序TForm1.UpdateFilter;
var
T1：整数;
begin
T1：= GetTickCount;
UpdateFilterExprsAndIndex;
CDS1.DisableControls;
try
CDS1.Filtered：= False;
如果（edLastNameFilter.Text）或（edFirstNameFilter.Text;'）或（edAgeFilter.Text;'）然后开始
CDS1.Filtered： =真;
结束
如果IndexFields<> ''然后
CDS1.IndexDefs [0] .Fields：= IndexFields; //警告：此IndexDef需要存在
Log（'过滤器更新时间'，IntToStr（GetTickCount - T1）+'ms'）;
finally
CDS1.EnableControls;
结束
结束
函数TForm1.FilterExpr（const Input：String）：String;
begin
结果：= Input;
如果Result<> ''then
Result：='*'+ Result +'*';
结束
procedure TForm1.UpdateFilterExprsAndIndex;
begin
LastNameFilter：= FilterExpr（edLastNameFilter.Text）;
FirstNameFilter：= FilterExpr（edFirstNameFilter.Text）;
AgeFilter：= FilterExpr（edAgeFilter.Text）;
IndexFields = ='';
如果LastNameFilter<> ''然后
IndexFields：='LastName';
如果FirstNameFilter<> ''然后开始
如果IndexFields<> ''然后
IndexFields：= IndexFields +';';
IndexFields：= IndexFields +'FirstName';
结束
如果AgeFilter<> ''然后开始
如果IndexFields<> ''然后
IndexFields：= IndexFields +';';
IndexFields：= IndexFields +'Age';
结束
结束
我希望这至少为您提供与您自己的
代码进行比较的基础，以便您可以确定任何瓶颈。
更新相反，令我惊讶的是，我发现使用复合过滤器表达式我用于测试， strong>很多更快地将CDS的 Filter 设置为表达式，并使用 OnFilterRecord ，具有30000条记录， UpdateFilter2 需要20ms以下，而使用`UpdateFilter'的类似表达式为200 ms。
程序TForm1.btnSetFilterExprClick（Sender：TObject）;
begin
edFilter.Text：='LastName =''aaa''和FirstName =''zz''和Age> 30';
UpdateFilter2;
结束
程序TForm1.UpdateFilter2;
var
T1：整数;
begin
CDS1.OnFilterRecord：= Nil;
T1：= GetTickCount;
CDS1.DisableControls;
try
CDS1.Filtered：= False;
CDS1.Filter：= edFilter.Text;
如果CDS1.Filter<> ''然后开始
CDS1.Filtered：= True;
结束
日志（'过滤器更新时间'，IntToStr（GetTickCount - T1）+'ms'）;
finally
CDS1.EnableControls;
结束
结束
At work we have a component called a "ClientdatasetGrid", which allows the user to sort the records of the grid by clicking on one or multiple column-titles.
I have made a component for work also, a descendant from TEdit, which I call TDBFilterEdit.
once you assign a DataSet or DBGrid to it, it creates an OnFilterRecord event for the DataSet and after you stop changing the text that Event is executed.
the problem arises whenever the Dataset is already filtered and the user sorts the grid.
the grid-component adds IndexDefs to the Clientdataset by first deleteing the current IndexDef, Updating, Adding the new Index and updating again.
whenever an index is deleted or added my OnFilterRecord event is triggered. I mitigated this by disableing controls and NIL-ing the OnFilterRecord event from inside the grid until the new index is added.
cds.DisableControls();
try
extProc:=nil;
if (TMethod(cds.OnFilterRecord).Code<>nil) and (TMethod(cds.OnFilterRecord).Data<>nil) then
begin
TMethod(extProc):=TMethod(cds.OnFilterRecord);
cds.OnFilterRecord:=nil;
end;
...
... //<-- Delete Index & create new Index
...
finally
cds.OnFilterRecord:=extProc;
cds.EnableControls();
end;
Once the Event is assigned again, it is immeadeately called and is iterating through all X records even though the user may only see 5.
Now I am searching for a way to see if a record is already filtered out so I can skip it inside my filter-method if the text hasn't changed.
Edit: Since a MVCE has been demanded I'll post a short version of my OnFilterRecord procedure.
- the following procedure is executed everytime the component hasn't recieved an input for 1 second
- fStringtypes and fTimeTypes are both a set of TFieldType
- fStringTypes:=[ftString,ftMemo,ftFMTMemo,ftFixedChar,ftWideString];
- fTimeTypes:=[ftDate,ftTime,ftDateTime,ftTimeStamp];
after the procedure is completely finished the timer is disabled and controls are enabled again.
procedure TDBEditFilter.FilterRecords(DataSet:TDataSet; var Accept:Boolean); var ... begin //initiliaztion// s:=FilterText; //Filtertext=User Input into the TDBEditFilters Textfield TestFloat:=0; Accept:=False; ///////////////// for i:=0 to fDBGrid.Columns.Count-1 do //for all DBGrid-Columns begin if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType in fStringTypes then begin Strvalue:=fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).AsString; Accept:=AnsiContainsText(Strvalue,s); //<--to ignore Upper/lowercase end else if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType in fTimeTypes then begin StrValue:=DateTimeToStr(fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).As DateTime,Local_Form_Settings); Accept:=Pos(StrValue,s)<>0; end else if fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).DataType=ftBlob then begin //ignore Blob end else //whatever fieldtype is left must be a numeric Field-type like integer or float begin if TryStrToFloat(s,TestFloat)=True then begin Accept:=(TestFloat=fDataSet.FieldByName(fDBGrid.Columns[i].FieldName).AsFloat); end; end; if Accept=True then break; //stop checking this record and check next record end; end;
I thought I would post this as a separate answer because I've been experimenting with a "Filter TEdit" that works in a similar way as I'm guessing yours does, and it doesn't seem to exhibit any particular performance problems. My main assumption is that you are using one filter TEdit per datafield of interest, rather that a single one into which the user types a compound Sql-like expression including the field names, comparison operators, etc.
The number of guesses I've had to make is why I said it would have been helpful for you to include an MCVE.
I've written it to be self-contained, i.e. it generates its own data instead of needing an external database.
As you'll see if you try it, with a CDS containing, say, 3000 records, the time to update the filters is a few tens of milliseconds (under 20 on my laptop). If the CDS contains 30000 records, the filter update time increases roughly linearly to about 200 ms which seems perfectly acceptable from a gui-responsivenes pov.
(Traditionally, TCDSs have been regarded as hitting a brick wall performance-wise when the number of records gets into the tens of thousands)
Note that for simplicity
a) I haven't used a DateTime fiield for BirthDate or whatever, because of the complications of dealing with partial dates inputted by the user.
b) In the OnFilterRecord event, the LastName, FirstName and Age comparisons
are done by comparing the field as a string with the corresponding filter expression.
c) The Filter expressions, if non-blank are left- and right-padded with asterisks
and the value comparisons are done using the MatchesMask function from the Masks unit.
See FilterExpr.
d) The IndexDef's FieldNames are composed of the names of the fields for which the filter edit's text is non-blank.
e) If the gui-updating is too slow if the user rapidly types several
characters in succession into the TEdits, you can work around this by
replacing the TEdits' OnChange event code by code in their KeyUp event
which enables a TTimer which has an interval of, say, 150 ms. Then, in its OnTimer, call UpdateFilter.
Code:
TForm1 = class(TForm)
DBGrid1: TDBGrid;
CDS1: TClientDataSet;
DataSource1: TDataSource;
Memo1: TMemo;
CDS1ID: TIntegerField;
CDS1Age: TIntegerField;
CDS1LastName: TStringField;
CDS1FirstName: TStringField;
edLastNameFilter: TEdit;
edFirstNameFilter: TEdit;
edAgeFilter: TEdit;
procedure CDS1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
procedure edLastNameFilterChange(Sender: TObject); // Set the OnChange events for the
// FirstName and Age TEdits to this, too
procedure FormCreate(Sender: TObject);
private
procedure Log(const Title, Msg: String);
function FilterExpr(const Input: String): String;
protected
public
LastNameFilter,
FirstNameFilter,
AgeFilter : String;
IndexFields : String;
IndexDef : TIndexDef;
procedure UpdateFilterExprsAndIndex;
procedure UpdateFilter;
end;
[...]
rocedure TForm1.FormCreate(Sender: TObject);
var
i : Integer;
Ch1,
Ch2 : Char;
LastName,
FirstName : String;
Age : Integer;
begin
CDS1.CreateDataSet;
CDS1.DisableControls;
try
for i := 1 to 30000 do begin
Ch1 := Chr(Ord('a') + random(26));
Ch2 := Chr(Ord('a') + random(26));
LastName:= StringOfChar(Ch1, 1 + Random(10));
FirstName := StringOfChar(Ch2, 1 + Random(10));
Age := Trunc(Random(71));
CDS1.InsertRecord([i, LastName, FirstName, Age]);
end;
finally
CDS1.First;
CDS1.EnableControls;
end;
end;
procedure TForm1.Log(const Title, Msg : String);
begin
Memo1.Lines.Add(Title + ' : ' + Msg);
end;
procedure TForm1.CDS1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
Accept := True;
if LastNameFilter <> '' then
Accept := MatchesMask(CDS1LastName.AsString, LastNameFilter);
if not Accept then exit;
if FirstNameFilter <> '' then
Accept := Accept and MatchesMask(CDS1FirstName.AsString, FirstNameFilter);
if not Accept then exit;
if AgeFilter <> '' then
Accept := Accept and MatchesMask(CDS1Age.AsString, AgeFilter);
end;
procedure TForm1.edLastNameFilterChange(Sender: TObject);
begin
UpdateFilter;
end;
procedure TForm1.UpdateFilter;
var
T1 : Integer;
begin
T1 := GetTickCount;
UpdateFilterExprsAndIndex;
CDS1.DisableControls;
try
CDS1.Filtered := False;
if (edLastNameFilter.Text <> '') or (edFirstNameFilter.Text <> '') or (edAgeFilter.Text <> '') then begin
CDS1.Filtered := True;
end;
if IndexFields <> '' then
CDS1.IndexDefs[0].Fields := IndexFields; // Warning: This IndexDef needs to exist
Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms');
finally
CDS1.EnableControls;
end;
end;
function TForm1.FilterExpr(const Input : String) : String;
begin
Result := Input;
if Result <> '' then
Result := '*' + Result + '*';
end;
procedure TForm1.UpdateFilterExprsAndIndex;
begin
LastNameFilter := FilterExpr(edLastNameFilter.Text);
FirstNameFilter := FilterExpr(edFirstNameFilter.Text);
AgeFilter := FilterExpr(edAgeFilter.Text);
IndexFields := '';
if LastNameFilter <> '' then
IndexFields := 'LastName';
if FirstNameFilter <> '' then begin
if IndexFields <> '' then
IndexFields := IndexFields + ';';
IndexFields := IndexFields + 'FirstName';
end;
if AgeFilter <> '' then begin
if IndexFields <> '' then
IndexFields := IndexFields + ';';
IndexFields := IndexFields + 'Age';
end;
end;
I hope this at least gives you a basis for comparison with your own code so that you can identify any bottlenecks.
Update Rather to my surprise, I found that with the compound filter expression I used for testing, it is much faster to set the CDS's Filter to the expression and leave it do to the filtering using OnFilterRecord, With 30000 records, UpdateFilter2 takes under 20 ms, compared with 200 ms for a similar expression set using the `UpdateFilter'.
procedure TForm1.btnSetFilterExprClick(Sender: TObject);
begin
edFilter.Text := 'LastName=''aaa'' and FirstName = ''zz'' and Age > 30 ';
UpdateFilter2;
end;
procedure TForm1.UpdateFilter2;
var
T1 : Integer;
begin
CDS1.OnFilterRecord := Nil;
T1 := GetTickCount;
CDS1.DisableControls;
try
CDS1.Filtered := False;
CDS1.Filter := edFilter.Text;
if CDS1.Filter <> '' then begin
CDS1.Filtered := True;
end;
Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms');
finally
CDS1.EnableControls;
end;
end;
这篇关于Delphi：检查DataSet的Record是否可见或过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
