Delphi Delete all records from FDTable

Question

Iam trying to delete all records in my FDTable component using ,

mytable.Delete;

but no record is getting deleted.

can any one suggest me a way to delete all records in a FdTable.

EDIT

i modified it in this way,

  for i := mytable.RecordCount - 1 downto 0 do
  begin
    mytable.Delete;
  end;
  mytable.Refresh;

but it is taking lot of time because there a lot rows in my fdtable.

Answer 1

Remove all the records in the underlying database table (the best way to do that will depend on the database) and call refresh just once.

If you must do it via TFdTable, use BeginBatch, do your deletes (without refresh), EndBatch and only then Refresh.

 mytable.BeginBatch;
 for i := mytable.RecordCount - 1 downto 0 do
 begin
    mytable.Delete;
 end;
 mytable.EndBatch;
 mytable.Refresh;

Answer 2

Try

as doing delete for all the rows would hamper the performance.

If that does not work then why don you create the dataset runtime and then free the object.

Answer 3

Try

MyTable.EmptyDataSet;

There may be a bit more to do than that, but you don't say how you are populating MyTable in the first place.

Answer 4

I would suggest you use a stored procedure for the job and not SQL from your application. Stored procedures are simple to make and blazing fast because they operate directly on the server. Just create a stored procedure for your table;

sp_deleteall

and do in it a single line of sql:

delete from mytable

From your application,drop a TFDStoredProc on the form, link it to your stored procedure on the server and just call on the procedure `

sp_deleteall.ExecProc;

To know how many records were deleted just call, after the procedure execution :

ShowMessage(IntToStr(sp_deleteall.RowsAffected) + ' records were deleted.');

Try it ...

Answer 5

Form fastest to slowest. First two are commands you execute server side.

1. Close the table and do a SQL DDL command TRUNCATE TABLENAME; and then reopen. This requires exclusive access to the table.
2. Use a SQL DML command DELETE FROM TABLENAME;. This does not require exclusive access.
3. Use a loop client side. A lot slower and triggers a lot of events client side.

Answer 6

all you need to do for delete all records on a FDMemTable is

MyTable.Active := False;