Title: Duplicating a Database Record
Question: Duplicating a database record is occasionally quite useful. I have a report based data table (using ReportBuilder) and needed to give users the ability to duplicate reports easily without having to open the report / save under a new name.
The main issue when duplicating a record is that a Dataset only maintains one cursor, so we can't copy field data directly.
The component I have written copies the source data to a temporary store. Then the Dataset can be put into Append or Insert mode and the data copied in. Two special types of field needed to be handled: BLOBs and AutoInc. AutoInc fields are maintained by the database engine and shouldn't be copied. BLOBs are handled in various ways by database engines, so I have used the top level interface provided by a TBlobField to save / load from a memorystream.
So far, I have used this with MS Access tables (under ADO), the BDE and Memory Tables with no problems.
Answer:
The core component is TFieldCopyList and is used within a program thus:
i) Navigate to the desired record
ii) Create a TFieldCopyList object
iii) Save the data using TFieldCopyList.CopyFromDataSet
iv) Append / Insert a new record, leaving the database in dsEdit/ dsInsert mode
v) Copy the saved data to the new record using TFieldCopyList.SaveToDataSet
vi) Post to the dataset to save the changes.
vii) Cleanup by free-ing the TFieldCopyList object
{-----------------------------------------------------------------------------
Unit Name: DataCopy
Author: Andrew Baylis
Purpose: Holds two classes assisting in duplicating a record within a database
History: ver 1.0
-----------------------------------------------------------------------------}
unit DataCopy;
interface
uses Classes, db, SysUtils;
type
TFieldData = class(TObject)
private
FMemory: TMemoryStream; //for holding BLOB data
FValue: Variant; //variants allow the database engine to deal with the raw data as it wishes
public
constructor Create;
destructor Destroy; override;
procedure LoadField(fld: TField);
procedure SaveField(fld: TField);
end;
TFieldCopyList = class(TObject)
private
FDataSet: TDataSet;
FList: array of TFieldData; //dynamic array used to save memory and allow for flexibility
protected
procedure Clear;
public
constructor Create;
destructor Destroy; override;
procedure CopyFromDataSet(DataSet: TDataSet);
procedure SaveToDataSet(DataSet: TDataSet);
end;
implementation
constructor TFieldCopyList.Create;
begin
inherited;
FDataSet := nil;
SetLength(FList, 0);
end;
destructor TFieldCopyList.Destroy;
begin
Clear;
inherited;
end;
procedure TFieldCopyList.Clear;
var
i: Integer;
begin
for i := 0 to Length(FList) - 1 do
FList[i].Free;
SetLength(Flist, 0);
end;
procedure TFieldCopyList.CopyFromDataSet(DataSet: TDataSet);
var
i: Integer;
begin
SetLength(Flist, Dataset.Fields.Count);
for i := 0 to Dataset.Fields.Count - 1 do
begin
FList[i] := TFieldData.Create;
FList[i].SaveField(DataSet.Fields[i]);
end;
FDataSet := DataSet;
end;
procedure TFieldCopyList.SaveToDataSet(DataSet: TDataSet);
var
i: Integer;
begin
if DataSet FDataSet then
Exit; //stored data is not from the same table
for i := 0 to Length(FList) - 1 do
FList[i].LoadField(DataSet.Fields[i]);
end;
// A TFieldData object is created for each field in the dataset. Its purpose is to hold the data from the desired record
constructor TFieldData.Create;
begin
inherited;
FMemory := TMemoryStream.Create; //holds contents of BLOB streams
end;
destructor TFieldData.Destroy;
begin
FMemory.Free;
inherited;
end;
procedure TFieldData.LoadField(fld: TField);
begin
if fld.IsBlob then
begin
FMemory.Position := 0;
TBlobField(fld).LoadFromStream(FMemory);
end
else
if fld.DataType ftAutoInc then //don't try to copy this type of field
fld.Value := FValue;
end;
procedure TFieldData.SaveField(fld: TField);
begin
if fld.IsBlob then
begin
FMemory.Clear;
TBlobField(fld).SaveToStream(FMemory);
end
else
FValue := fld.Value;
end;
end.