Manipulating Database Record with Delphi Codes

From the previous tutorials for beginners in database with Delphi (see: Very Basic Database Application in Delphi and Most Basic Editing Database in Delphi) I have shown you how easy to build simple database application with Delphi using only its GUI designer. In the demo project of those tutorials we have wrote zero lines of codes! But like I have said before, we won't be able to do anything more advance if we don't write codes to manipulate the database. And this tutorial will introduce you to the basic of manipulating database records using codes.

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 when it's clicked is:

Code:

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:

Code:

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:

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.

And TDataset's properties that are helpful when moving cursors are:

• 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.

In the demo project we have a button to show the number of records shown in the datagrid. Instead of using RecordCount, here we will use First, Next, and EoF to iterate each record.

Code:

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:

Code:

  MyDataset.FieldValues['FieldName'] := 'New Value';

we can use shorter way like this:

Code:

  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:

Code:

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 changes
If 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:

Code:

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 Addition
Similar 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 at 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.

Code:

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;