delphi 新版内存表 FDMemTable

四面十方 發表於 2024-11-16 15:17:00

c++builder XE
官方demo最全60多个
http://community.embarcadero.com/blogs?view=entry&id=8761
 
 FireDAC.Comp.Client
 
用好FDMemTable代替之前的ClientDataSet，以前ClientDataSet内存表转换太繁琐了步骤。
 TClientDataSet *cds = new TClientDataSet(this);   DataSetProvider1->DataSet = dm->ADOQueryPub;   cds->ProviderName = "DataSetProvider1";   cds->Open();
 
c++智能指针
 #include <memory>       //For STL auto_ptr class
std::auto_ptr<TFDMemTable> table (new TFDMemTable(NULL));
for (table->First(); !table->Eof; table->Next())
{
table->FieldByName("aa")->AsString;
}
 
一句就可以了
FDMemTable1->CopyDataSet(dm->ADOQueryPub, TFDCopyDataSetOptions() << coStructure << coRestart << coAppend);
 
多用FDMemTable，不再用ClientDataSet\DataSetProvider1做转换了
FDMemTable2->Data = FDMemTable1->Data;
FDMemTable2->CopyDataSet(FDMemTable1, TFDCopyDataSetOptions() << coStructure << coRestart << coAppend);
 
建立缓存表
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><img src="https://assets.cnblogs.com/images/copycode.gif" alt="复制代码"></div>
<pre>FDMemTable1.Close();
FDMemTable1.FieldDefs.Clear();
FDMemTable1.FieldDefs.Add('ID', ftInteger, 0, True);
FDMemTable1.FieldDefs.Add('Name', ftString, 20, false);
FDMemTable1.CreateDataSet();
FDMemTable1.AppendRecord();
FDMemTable1.AppendRecord();
FDMemTable1.AppendRecord();</pre>
<div class="cnblogs_code_toolbar"><img src="https://assets.cnblogs.com/images/copycode.gif" alt="复制代码"></div>
</div>
 
0）FDMemTable1.SourceView遍历各行数据，取任意行数据无需Next移动指针了。TFDDatSView
 for (int i = 0; i < FDMemTable1->SourceView->Rows->Count; i++)  {   Caption = FDMemTable1->SourceView->Rows->ItemsI->GetData(1);  }
FDMemTable1->SourceView->Rows->ItemsI->GetData("fieldName");//取指定行指定字段名的值  }
9行7列的值。
 FDMemTable1.Data.DataView.Rows.ItemsI.ValueI;
 FDMemTable1.Table.Rows.ValueI
 Caption = FDMemTable1->SourceView->Rows->Count;//过滤后1条  Caption = FDMemTable1->Table->Rows->Count;//过滤无效，全部记录3条
 
<div class="cnblogs_code">
<pre>iMax := 0;
for i := 0 to FDQuery1.SourceView.Rows.Count - 1 do
if FDQuery1.SourceView.Rows.GetData('id', @iVal) and (iVal > iMax) then
iMax := iVal</pre>
</div>
 
数据集拷贝，复制数据集，合并数据集 
 FDMemTable1->Filter = "id=102";  FDMemTable1->Filtered = true;
FDMemTable1只有1条记录
 1）Data
FDMemTable2->Data = FDMemTable1->Data;
 
FDQuery->Open("select * from tt");
FDMemTable2->Data = FDQuery->Data;
 
FDMemTable2是全部记录，有3条记录。
FDMemTable1->Delete();后的记录不在Data里。
 2）CopyDataSet
带结构拷贝 
FDMemTable2->CopyDataSet(FDMemTable1, TFDCopyDataSetOptions() << coStructure << coRestart << coAppend);
 FDMemTable2->CommitUpdates();
缓存更新用到changeCount，所以copy完后加上CommitUpdates
 
不带结构，仅拷贝数据，字段个数可以不一致，字段数以目标数据集FDMemTable2为标准。
FDMemTable2->CopyDataSet(FDMemTable1, TFDCopyDataSetOptions() <<  coRestart << coAppend);
//第二个参数默认是coRestart << coAppend，所以下面就更简单了。
FDMemTable2->CopyDataSet(FDMemTable1);
FDMemTable2只有1条记录
 3)CopyRecord\CopyField
copy the current record field values .Only One Record
  FDMemTable2->CopyDataSet(FDMemTable1, TFDCopyDataSetOptions() << coStructure);  FDMemTable2->Edit();  FDMemTable2->CopyRecord(FDMemTable1);
 4)CloneCursor
All Record, ignore filter
CloneCursor，数据共享，一个修改，另一个也修改了，但是FDMemTable1->Close之后FDMemTable2还正常显示。
FDMemTable2->CloneCursor( FDMemTable1);
 2016.3.4 test 不能排序，FDMemTable2->IndexFieldNames = "ID";不起作用，所以尽量不建议用这个方式。
5)AtrachTable
All Record,ignore filter,TFDDatSTable
 FDMemTable2->AttachTable(FDMemTable1->Table, NULL);
//or
//FDMemTable2->AttachTable(FDMemTable1->Table,FDMemTable1->View);  FDMemTable2->Open();
6)FilteredData
<pre class="Element12">FDQuery1.Filter := 'upper(name) like ''D%''';
FDQuery1.Filtered := True;
// copy to FDMemTable1 all FDQuery1 visible (where name starts from D) records
FDMemTable1.Data := FDQuery1.FilteredData; RecordCount是过滤后的记录数。过滤前有100行，过滤后有5行，那么RecordCount就是5 FDQuery1->Data是100行，FilteredData是5行的数据 7)XMLData All Record ignore filter  Memo1->Text = FDMemTable1->XMLData;  FDMemTable2->XMLData = Memo1->Text; FDMemTable1->ChangeCount， 通过Data赋值，默认全部记录都是修改过的，也就是ChangeCount=RecordCount，有100条记录，获取ChangeCount属性就是100.这样合适还是不合适呢？ FDMemTable1->Data = FDQuery->Data; FDMemTable1->CancelUpdates();或者FDMemTable1->CommitUpdates(); 加上CancelUpdates这句话，ChangeCount就正常了！！反应真实的修改记录数。 Append的记录需要把属性CachedUpdates设为true，ChangeCount就正确了。 8)Delta IFDDataSetReference类型 FDMemTable2->Delta= FDMemTable1->Delta; ADMemTable1.FilterChanges := ; ADMemTable1.Data := ADQuery1.Delta; 9)查看删除过的记录UpdateStatus FDMemTable想要找到并显示删除的记录 FDMemTable1->Delete();    FDMemTable2->FilterChanges << Firedac::Comp::Dataset::rtDeleted;    FDMemTable1->Data = FDMemTable1->Data; while (!FDMemTable1.eof) { if( table->UpdateStatus() == usDeleted) ... } 默认是不显示删除过的记录的，FilterChanges不包括rtDeleted属性。 10)分页及加载全部页 FetchOptions的Mode默认是fmOnDemand表示分页，每页50，改为fmAll表示全部记录。 分页TFDFetchOptions.RowsetSize FetchNext FetchAll FetchOptions.RecordCountMode property FDQuery1.Open; FDQuery1.FetchAll;//必须加这一句，否则数据集不全。 FDMemTable1.Data := FDQuery1.Data; 用了grideh，为何导致分页不灵了？？？全部记录出来了？field设置了ftsum导致，不设置每次就50行记录。 LocateEx </pre>
11）增强的Locate功能LocateEx、LookupEx函数
<pre class="Element12">lxoCheckOnly  If included, then LocateEx does not:</pre>
<pre class="Element12">Change the current position. Fire BeforeScroll / AfterScroll events. Finish editing mode 不改变位置和编辑状态的搜索，强大！    if (ds1->LocateEx("DM", "001", TFDDataSetLocateOptions() << lxoCheckOnly)) 12）刷新数据集 query1.Refresh(); 13）只读字段 select'' as temp,flag=0,sql返回的虚拟字段，以前clientDataSet可以修改，FDMemTable里不能改了。 ClientDataSet1->FieldByName("flag")->AsString="1"; 但是FDMemTable不能改了。怎么办？以前的这种虚拟字段的方式挺好用啊。 解决办法：设置属性TFDMemTable.UpdateOptions.CheckReadonly=true 14）主从表关系</pre>
<pre class="Element12">第一步： fdqueryDetail.MasterSource := DataSource1; 第二步： fdqueryDetail.MasterFields := 'OrderNo'; { 多个字段时用分号隔开 } 或者 fdqueryDetail.sql.text='select * from OrderDetail where OrderMasterKey=:OrderMasterKey'; 两个FDMemTable做主从怎么不起作用呢？ 15）过滤数据FilterChanges 只显示修改后的数据 ClientDataSet1->FilterChanges = TFDUpdateRecordTypes() << Firedac::Comp::Dataset::rtModified; ClientDatSet用Grideh可以排序，添加EhLibCDS.pas文件即可。 TFDMemTable添加EhLibFireDAC.pas怎么不起不能排序，报错TFDMemTable is not SQL based dataset，FDQuery排序可以了 把SortLocal=true，也不能排序,报错 TSQLDatasetFeaturesEh can not sort data in dataset "FDMemTable1" in local mode TFDMemTable不能排序，ClientDatSet替换为TFDMemTable的进程又得延缓. 比较和跟踪源码，Delphi里TFDMemTable排序正常。c++builder新建一个工程添加内存表和数据也排序正常。 最终解决了，原来是旧的ehlib控件卸载不干净，原来的工程里还有路径和ehlib.lib文件的链接，清除后排序全部OK！ 2019.4.1 一个界面A 排序报错TFDMemTable is not SQL based dataset，弹出窗口排序正确，为什么呢，</pre>
<pre class="highlighter-hljs"><code class="highlighter-hljs hljs language-undefined">FDQuery.Fields.DataSet</code> ?这也是数据集？ Data.DB.TFields.DataSet Identifies the dataset to which a TFields object belongs.</pre>
<pre class="Element12"> A DataTable or a DataView must be supplied. Hint: if that is TFDMemTable, use CreateDataSet or CloneCursor to open dataset TFDDataSet 基类 fdquery,dbgrideh控件的ftsum字段求和影响 C++Builder 返回数据集 _di_IFDDataSetReference 16)已有数据的FDMemTable添加列，动态添加列 原有数据集,现有数据集,现有字段,原有字段,添加新增选择列   FDMemTable2.FieldDefs := FDMemTable1.FieldDefs;   FDMemTable2.FieldDefs.Add('Test', ftString, 20 { , False } ); // default parameter   FDMemTable2.FieldDefs.Find('Test').Index := 0;   FDMemTable2.CreateDataSet; // or just Open that sets Active to true;   FDMemTable2.CopyDataSet(FDMemTable1); 17)缓存更新 FDMemTable1->ChangeCount 执行了ApplyUpdates或CommitUpdates后ChangeCount变为0 FDMemTable1->ApplyUpdates(0); C++builder Berlin//Berlin 排序无效 2017.3.12 delphi正常 升序 self.FDMemTable1.IndexFieldNames := 'ID:A'; 降序 self.FDMemTable1.IndexFieldNames := 'ID:D'; FDMemTable1->IndexFieldNames = "ID:D"; FDMemTable1->CloneCursor(m->dsModule, true, true);</pre>
<pre class="Element12">CloneCurso的数据集均无法用IndexFieldNames排序。用CreateDataSet创建的数据集排序正常！ Data赋值、CopyDataSet后的数据集均排序可以正常。建议不用CloneCursor。 18.修改只读字段 query1.UpdateOptions.AssignedValues =     FDStoredProc1->UpdateOptions->CheckReadOnly = false; </pre>
<pre class="prettyprint">TSQLTimeStampField ClientDataSet1: Type mismatch for field 'ffdatetimie', expecting: DateTime actual: TimeStamp TDateTimeField TDateTime 替换为 TSQLTimeStampField FMTBcd ds1: Type mismatch for field 'UNITPRICE', expecting: Float actual: FMTBcd. TFloatField *ds1UNITPRICE; TBCDField 替换为 TFMTBCDField numeric(18,0)   TFMTBCDField decimal(18,4)  TBCDField ff3 decimal(18,3) TFMTBCDField 可能fdquery认为4位小数就设计器添加到字段列表是TBCDField，非4位小数就是TFMTBCDField，包括0 1 2 3 位小数。 19.<code class="has-numbering">建立索引</code></pre>
<pre>   FDQueryRelation.AddIndex('index1','ITEMTYPE;ITEMNOHIS','', []);   FDQueryRelation.IndexName := 'index1'; 经过试验证明，filter没有使用索引，支持like表达式   locate和findkey使用了索引，精确定位，要求检索速度快，检验使用 </pre>
<pre class="Element12">Caching_Updates_</pre>
<pre class="Element12"> http://docwiki.embarcadero.com/RADStudio/Rio/en/Caching_Updates_(FireDAC)</pre>
<pre>FDQuery1.CachedUpdates := True;
iSavePoint := FDQuery1.SavePoint;
try
FDQuery1.Append;
...
FDQuery1.Post;
FDQuery1.Append;
...
FDQuery1.Post;
FDQuery1.Append;
...
FDQuery1.Post;
except
FDQuery.SavePoint := iSavePoint;
end; </pre>
<pre>FDQuery1.CachedUpdates := True;
FDQuery1.Append;
...
FDQuery1.Post;
FDQuery1.Append;
...
FDQuery1.Post;
FDQuery1.Append;
...
FDQuery1.Post;
FDConnection1.StartTransaction;
iErrors := FDQuery1.ApplyUpdates;
if iErrors = 0 then begin
FDQuery1.CommitUpdates;
FDConnection1.Commit;
end
else
FDConnection1.Rollback; </pre>
<pre>var
oErr: EFDException;
...
if FDQuery1.ApplyUpdates > 0 then begin
FDQuery1.FilterChanges := [rtModified, rtInserted, rtDeleted, rtHasErrors];
try
FDQuery1.First;
while not FDQuery1.Eof do begin
 oErr := FDQuery1.RowError;
 if oErr <> nil then begin
 // process exception object
 ...
 end;
 FDQuery1.Next;
end;
finally
FDQuery1.FilterChanges := [rtUnmodified, rtModified, rtInserted];
end;
end; 19)AppendData 2个完全一样的数据集合并 AppendData ADOQuery1->AppendData(ADOQuery2->Data ); 不用循环遍历了，一行命令搞定 void __fastcall Tfrm::ds1UpdateError(TDataSet *ASender, EFDException *AException,           TFDDatSRow *ARow, TFDUpdateRequest ARequest, TFDErrorAction &AAction) { throw new Exception(  AException->Message ); } 20.fdmemtable批量编辑性能优化 http://docwiki.embarcadero.com/RADStudio/Rio/en/TFDMemTable_Questions 例如 Set the following properties LogChanges, FetchOptions, ResourceOptions, and UpdateOptions. </pre>
<pre>FDMemTable1.LogChanges := False;
FDMemTable1.FetchOptions.RecsMax := 300000;//Sample value
FDMemTable1.ResourceOptions.SilentMode := True;
FDMemTable1.UpdateOptions.LockMode := lmNone;
FDMemTable1.UpdateOptions.LockPoint := lpDeferred;
FDMemTable1.UpdateOptions.FetchGeneratorsPoint := gpImmediate;</pre>
Then surround massive edition operations into BeginBatch / EndBatch method calls:
<div class="mw-highlight mw-content-ltr" dir="ltr">
<pre>FDMemTable1.BeginBatch;
try
for i := 1 to 1000 do begin
 FDMemTable1.Append;
 // ...
 FDMemTable1.Post;
end;
finally
FDMemTable1.EndBatch;
end;
</pre>
</div>
<pre> c# DataTable dataFee; DataRow[] drRerr =   dataFee.Select("目录类别=''"); 复制表有两种方法 
DataTable.Clone()只克隆表结构 
DataTable.Copy() 克隆表结构及数据 </pre>
<div class="chat-gpt-quick-query-container">
<div class="css-c8nnzp ant-app h-full">
<div class="sider-code-explain-button-wrapper-common">
<div class="absolute right-0 top-2 pr-2 h-">
<div class="sider-code-explain-button">
<div class="button explain-button">
<div>解释</div>
</div>
<div class="inline-flex-center cursor-pointer text-[--gpts-primary-content-text-color] button copy-button"> </div>
<div class="vertical-divider"> </div>
</div>
</div>
</div>
</div>
</div>
<pre class="prettyprint highlighter-hljs"><code class="has-numbering highlighter-hljs hljs language-csharp"> DataTable dt2 = dt.Clone();
 var rows = dataFee.Select("col3>=30", "col3 desc");

if (rows != null && rows.Length > 0)
  {
   foreach (DataRow row in rows)
    {
       dt2.ImportRow(row);
    }
   }

</code></pre>
<pre>FDConnection1一句话执行SQL</pre>
<div class="cnblogs_code">
<pre>FDConnection1.ExecSQL('truncate table tab1');
FDConnection1.ExecSQL('drop table tab2', True);
FDConnection1.ExecSQL('delete from mytab where id > :p1', );
FDConnection1.ExecSQL('update mytab where id = :p1 set blobfld = :blb',
, );</pre>
</div>
<pre class="prettyprint highlighter-hljs"><code class="has-numbering highlighter-hljs hljs language-undefined"></code></pre>
多表查询更新
<div class="chat-gpt-quick-query-container">
<div class="css-c8nnzp ant-app h-full">
<div class="sider-code-explain-button-wrapper-common">
<div class="absolute right-0 top-2 pr-2 h-">
<div class="sider-code-explain-button">
<div class="button explain-button">
<div>解释</div>
</div>
<div class="inline-flex-center cursor-pointer text-[--gpts-primary-content-text-color] button copy-button"> </div>
<div class="vertical-divider"> </div>
</div>
</div>
</div>
</div>
</div>
<pre class="prettyprint highlighter-hljs"><code class="has-numbering highlighter-hljs hljs language-go"> FDQuery1->UpdateOptions->UpdateTableName="T_USER_B";//联合查询更新指定的表
FDQuery1->FieldByName("NAME")->ProviderFlags.Clear();//不更新该字段

克隆
      FClonedDataSet := Table.FDataSet.GetClonedDataSet(True);
    if FClonedDataSet = nil then begin
      FClonedDataSet := TFDMemTable.Create(nil);
      TFDMemTable(FClonedDataSet).CopyDataSet(Table.FDataSet, [coStructure, coCalcFields,
        coRestart, coAppend, coIndexesCopy, coConstraintsCopy, coAggregatesCopy]);</code></pre> 
来源：https://www.cnblogs.com/Thenext/p/18549376

頁: [1]

琼殿技术论坛's Archiver

delphi 新版内存表 FDMemTable