In manipulating database records we interested in loading the records, moving cursor (i.e. the changing active record), finding record, editing the record, adding new record, and deleting record.
1. Loading Records
Of course before we could do anything with the records we need to load them into a dataset. To load records into a dataset we need to activate it by
calling its Open method, or by setting its Active property to True. If the dataset is "table" based, such as TADOTable, TTable, or TIBTable, then prior to activating you have to supply the table name. Most "table" based dataset will try to load the whole table when activating. So it might not interesting for those who have to work with tables with large content.
Beside "table" based datasets we also have query based dataset. These datasets execute SQL command to load the records. With these, we can specify which records to load by customizing the "WHERE" clause of the SQL, if the SQL is SELECT query, or adjusting parameters if the SQL refers to stored procedures. SQL based dataset examples are: TQuery, TADODataset, TADOQuery, and TIBQuery.
In our demo project we have a button with caption "Use Table". This button will make the datagrid to show the whole content of "Table11" using table-based dataset TADOTable. And the code for when it got clicked is:
procedure TFormMain.btnUseTableClick(Sender: TObject); begin // close whatever dataset currently shown in the datagrid DBGrid1.DataSource.DataSet.Close; // Set the table name to our TADOTable Table1.TableName := 'Table11'; // Activate/open the table Table1.Open; // Assign the table to datasource used by our datagrid DBGrid1.DataSource.DataSet := Table1; end;Button with caption "Use Query" will make the datagrid to show the content of "Table11" using SQL SELECT command. And to make it a bit different with when we use table-based dataset, we will add filter to the SQL SELECT to make it only show records with FirstName's field started with 'R'. And the code is:
procedure TFormMain.btnUseQueryClick(Sender: TObject); begin // close whatever dataset currently shown in the datagrid DBGrid1.DataSource.DataSet.Close; // Set the SQL SELECT command ADOQuery1.SQL.Text := 'SELECT * FROM Table11 WHERE FirstName LIKE ''R%'''; // Activate/open the query ADOQuery1.Open; // Assign the query-based dataset to datasource used by our datagrid DBGrid1.DataSource.DataSet := ADOQuery1; end;2. Moving Cursor
When working with a dataset, we can only work with the active record, i.e. the cursor. For example, if we have a dataset with name of "Table1" then this code:
WriteLn(Table1.FieldByName('FirstName').AsString);
means to write the content of field with the name of 'FirstName' from the currently active record of Table1. Table1 may contain a lot of records, therefore making sure that we are working with the correct record is important.Dataset classes in Delphi's VCL are all descendants from TDataset. And TDataset already provided methods and properties we need related with moving the cursor. The methods are:
- First, to move cursor to the first record
- Next, to move cursor to the record next of the current one
- Prior, to move cursor to the record prior to the current one
- Last, to move cursor to the last record
- MoveBy, to attempt to move the cursor x number of records from the current one. When x is positive the cursor will move forward, and cursor moves backward when x is negative. This function will return the number of records it's actually skipped which could be the same or less than x.
- IsEmpty, to detect whether the dataset has any record or not. When it's empty we can't move anything, right?
- GetBookmark, to get a bookmark of the current record so we can easily go back to it later.
- GoToBookmark, to make the record associated with the supplied bookmark active.
- FreeBookmark, to release the memory occupied by a bookmark.
- BoF, short for Beginning of File. Indicates whether the cursor is at the first record or not.
- EoF, short for End of File. Indicates whether the cursor is at the last record or not.
- IsUnidirectional, indicates whether the dataset is unidirectional. Unidirectional datasets allows forward-only cursor movement. So it only allows First and Next methods when moving cursors.
- RecordCount, indicates how many records are there in the dataset. Note that the reliability of this property depends on the implementation in the descendant class. Some dataset might have chosen not to implement this property reliably.
procedure TFormMain.btnCountRecordsClick(Sender: TObject);
var
vCount: Integer;
vBookmark: TBookmark;
begin
vCount := 0;
// we use whatever dataset currently used by the
// datagrid
with DBGrid1.DataSource.DataSet do
begin
// get bookmark of the current record so we can
// go back to it later
vBookmark := GetBookmark;
First; // move to the first record
// do the following block of codes while we
// haven't passed last record
while not EoF do
begin
vCount := vCount + 1; // increment our record counter
Next; // move to next record
end;
// we are done with counting the records,
// time to activate the priorly active
// record that we've bookmarked earlier
GotoBookmark(vBookmark);
FreeBookmark(vBookmark);
end;
// Display our finding
ShowMessage('We found ' + IntToStr(vCount) + ' records');
end;
Note that without the bookmarking codes, each time we record-counting we will end activating the last record. Using bookmarking feature, we can make our cursor movements seems transparent, since to our user it looks like the cursor was never moved.3. Editing A Record
Editing a record means we change the content of some (or all) of its fields. Before we edit a record, we have to make it enters "editable" mode. After we have done editing, we have to "save" the modifications by posting the modifications and thus make the record enters "browsing" mode. To enter "editable" mode, just call the dataset's Edit method. And to post the modification, you can either call Post or CheckBrowseMode method.
After the record enters editable mode, we can change its field(s) with new values. To access a field value we can use FieldByName method or FieldValues property of the Dataset. Since FieldValues is default property with array type, instead of using it like this:
MyDataset.FieldValues['FieldName'] := 'New Value';we can use shorter way like this:
MyDataset['FieldName'] := 'New Value';There are other ways to access the fields beside these two. Like using persistent field, or using TField objects contained in Fields property. But I will save them for later.
In our demo project there is a button with 'Change Name' caption. The button will change the FirstName and LastName fields of the current record. The code is:
procedure TFormMain.btnChangeNameClick(Sender: TObject);
var
vNewFirstName: string;
vNewLastName: string;
begin
if DBGrid1.DataSource.DataSet.IsEmpty then
raise Exception.Create('There is no record available '
+ 'for editing');
vNewFirstName := DBGrid1.DataSource.DataSet['FirstName'];
vNewLastName := DBGrid1.DataSource.DataSet['LastName'];
if not InputQuery('Change FirstName'
, 'New FirstName'
, vNewFirstName) then
Exit;
if not InputQuery('Change LastName'
, 'New LastName'
, vNewLastName) then
Exit;
with DBGrid1.DataSource.DataSet do
begin
Edit; // enter edit mode
// change the content of field "FirstName"
FieldByName('FirstName').Value := vNewFirstName;
// change the content of field "LastName"
FieldByName('LastName').Value := vNewLastName;
Post;
end;
end;
Cancelling changesIf for any reason we have to cancel any changes we have made to the fields, we should call Cancel instead of Post or CheckBrowseMode. Cancel will discard any changes we have made and make the dataset back to "browsing" mode.
For example, in our demo project there is a button 'Change Name (Dont Post)'. When we click that button we will change the FirstName and LastName fields. But the code in that button's OnClick will not automatically post/save the changes, although we can still inspect the changes from the datagrid. If we click the Cancel button, the changes will be discarded and datagrid will show back the old values.
4. Adding/Inserting New Record
The process of adding or inserting new records actually is very similar with editing a record. The only difference is that we need to call Append or Insert instead of Edit when we make the dataset entering editable mode. Beside that the rest are the same.
For example, in our demo project we have 'Add Name' name button which will add a new record with code of:
procedure TFormMain.btnAddNameClick(Sender: TObject);
var
vFirstName: string;
vLastName: string;
begin
if not InputQuery('FirstName'
, 'FirstName'
, vFirstName) then
Exit;
if not InputQuery('LastName'
, 'LastName'
, vLastName) then
Exit;
with DBGrid1.DataSource.DataSet do
begin
Insert; // add new record (****)
// change the content of field "FirstName"
FieldByName('FirstName').Value := vFirstName;
// change the content of field "LastName"
FieldByName('LastName').Value := vLastName;
Post;
end;
end;
Cancelling Record AdditionSimilar with when editing existing record, we can call Cancel method instead of Post or CheckBrowseMode. This time, however, the new record will be removed (since it's actually non-existent in the first place).
5. Deleting Record
To delete a record we only need to go to the record and call Delete method of the dataset. Note that we can not cancel this operation. So I really suggest to add some verification prior calling Delete.
In the demo project, this is a sample of deleting current record.
procedure TFormMain.btnDeleteClick(Sender: TObject);
begin
with DBGrid1.DataSource.DataSet do
begin
// no need to delete if dataset is empty
if IsEmpty then Exit;
// verify the user action is not accidental
if MessageDlg('Are you sure you want to delete '
+ 'this record?'
, mtConfirmation
, [mbYes, mbNo], 0)=mrNo then
Exit;
Delete; // delete the current record
end;
end;
Full source code of the demo project is attached. Note that the database used in the demo project and also how to setup the database connection can be found in the first tutorial in this series.
Attached Files
-
FirstDb-1.zip 388.09KB
1454 downloads
Edited by LuthfiHakim, 26 February 2013 - 06:49 PM.
.
