Dragging and Dropping into DBGrids

In this post I demonstrate how to implement advanced drag-and-drop features with a DBGrid as the target using a ClientDataSet and cloned cursors. But first, let me acknowledge the posts of Zarko Gajic on delphi.about.com. I started writing this post five days ago, and he posted a new entry concerning drag-and-drop two days ago. It's entirely a coincidence. Nonetheless, if you are new to drag-and-drop operations, Zarko has posted many excellent articles on the subject, and I recommend that you visit his site to learn more about this and other Delphi-related topics.

Drag-and-drop operations, where the user drags data from one object and drops it into another object, has been a standard feature in Delphi applications for a long time. Is dragging and dropping with a DBGrid as the target more difficult than, say, a ListBox? Well, that depends.

If you are dragging from some source object and simply dropping that value into a DBGrid, without concern for where the dropped data will appear in the DBGrid, then no. From the DBGrid's OnDragDrop event you read the data from the source object and insert that data into the underlying DataSet. If the DataSet is displaying its data using an index, the dropped data will appear in the grid at the position dictated by the index key, otherwise it will appear at the end of the grid.

It's when you actually want to drop the source data into a particular row of the DBGrid that things become more complicated. It is even more complicated if you want to permit the user to be able to re-order the records in a DBGrid by dragging a record from one position in the DBGrid, dropping it into a new position.

Before I demonstrate how to implement sophisticated drag-and-drop operations using a DBGrid, let's first consider what makes this operation complicated. In a word, indexes. In most databases the order of records in a table is dictated by an index, and in most cases, a primary index. For the same reason, the sequence in which the records appear in a DBGrid are either affected by an index, or in the case of a query, an ORDER BY clause.

What this suggests is that you must manipulate the fields involved in the index or ORDER BY clause of your DataSet when implementing drag-and-drop in a DBGrid, which is correct. Following this logic, you might assume that with every drop operation you are going to write to your database. While you can do that, it is something that I personally want to avoid. Instead, I would prefer to implement the drag-and-drop functions on a cached version of the data, writing to the database only after the user is satisfied with the order of the data they are working with.

So, how is this done? With a ClientDataSet, of course.

Here's the basic approach. Associate a DBGrid with a ClientDataSet that makes use of an integer index that dictates the order of records in the grid. In some cases, including the code sample associated with this post, the ClientDataSet might be empty initially. In other cases, the ClientDataSet is first populated from a database.

When the data is loaded from a database, the field associated with the ordering index is most likely not actually a field in the database. Instead, it is an extra field added for ordering purposes. However, if your underlying data has a field that specifies order, you can use that. In my particular implementation it is necessary that this ordering field begin with 1 (the first record) and be sequential. This is just a detail, however, and the code that I will show could be modified to accommodate any kind of ordinal data.

Once you have a ClientDataSet with these features, all you need to do is to enable drag-and-drop and then implement OnDragOver and OnDragDrop event handlers from which you perform some basic operations. These techniques are demonstrated in the DBGridDragDrop project, which you can download from the preceding link. I wrote this project in Delphi XE2, and tested the posted code in Delphi 7. As a result, I assume that it will run in Delphi 7 Professional and later.

This project demonstrates two especially valuable drag-and-drop techniques. The first is dropping to an arbitrary position in the DBGrid. The second is dragging records from one position in the DBGrid to another.

There are two drag sources in this project, a ListBox and a DBGrid, and one target, the DBGrid. For demonstration purposes, the ListBox is loaded from a call to the PopulateListBox method, shown here:

procedure TForm1.PopulateListBox;
begin
  ListBox.Clear;
  ListBox.Items.Add('One');
  ListBox.Items.Add('Two');
  ListBox.Items.Add('Three');
  ListBox.Items.Add('Four');
  ListBox.Items.Add('Five');
  ListBox.Items.Add('Six');
  ListBox.Items.Add('Seven');
  ListBox.Items.Add('Eight');
  ListBox.Items.Add('Nine');
  ListBox.Items.Add('Ten');
end;

The ClientDataSet in this project begins as an empty DataSet. This ClientDataSet is created in the following method.

procedure TForm1.CreateClientDataSet;
begin
  ClientDataSet := TClientDataSet.Create(Self);
  //This example assumes that the field defining the
  //order of records is named Sequence. This field
  //can appear in any position in the table structure.
  ClientDataSet.FieldDefs.Add('Sequence', ftInteger);
  ClientDataSet.FieldDefs.Add('Field', ftString, 30);
  //The ClientDataSet can have any number of fields
  //The following field is just for demonstration
  ClientDataSet.FieldDefs.Add('RandomNumber', ftInteger);
  ClientDataSet.CreateDataSet;
  ClientDataSet.IndexFieldNames := 'Sequence';
  DataSource1.DataSet := ClientDataSet;
end;

Both of these methods are called when the main form is first created, producing the form shown in the following figure.



Figure 1. The DBGridDragDrop project main form

We could set the DragMode property of both the ListBox and DBGrid to dmAutomatic, but I like to avoid that. Instead, I prefer to initiate the drag operation only after the mouse has been dragged some reasonable distance. Otherwise, a drag operation begins with the slightest mouse down drag. As a result, I initiate the drag operation from the OnMouseMove event handler of both drag sources. This event handler is shown here.

procedure TForm1.MouseMove(Sender: TObject; Shift: TShiftState;
  X, Y: Integer);
const //this constant is typically declared with a higher scope
  MouseMovePixels = 15;
begin
  if ssLeft in Shift then
    TListBox(Sender).BeginDrag(False, MouseMovePixels);
end;

We need to add an OnDragOver event handler to the drop target in order to have the mouse cursor change to indicate that dropping is allowed (and to permit the OnDragDrop event handler to trigger). This event handler is shown here, and it is assigned only to the DBGrid.

procedure TForm1.DBGridDragOver(Sender, Source: TObject; X, Y: Integer;
State: TDragState; var Accept: Boolean);
begin
  //Test for acceptable drag origin classes or objects
  Accept := (Source is TListBox) or (Source is TDBGrid);
end;

Finally, the drop operation is performed from the target's OnDragDrop event handler. From here we determine the source of the drag operation (ListBox versus DBGrid) and take the appropriate action. Again, here is the event handler. It is only associated with the DBGrid.

procedure TForm1.DBGridDragDrop(Sender, Source: TObject; X, Y: Integer);
var
  GridRow: Integer;
  OriginalRow: Integer;
begin
  GridRow := DBGrid.MouseCoord(X,Y).Y;
  if GridRow = 0 then
    GridRow := 1;
  if (Source is TListBox) then
  begin
    //An item is being dropped into the DBGrid
    if ClientDataSet.IsEmpty then
    begin
    //The grid is empty. Add the item in the first position
    ClientDataSet.AppendRecord([1, 
                                TListBox(Source).Items[TListBox(Source).ItemIndex],
                                RandomRange(1, 101)]);
    end 
    else
    begin
      //Insert the item at the position of the drop
      if GridRow = -1 then //the drop is at the end of the DBGrid
        GridRow := ClientDataSet.RecordCount + 1
      else //the drop needs to be inserted into the DBGrid. Make room
        ResequenceCDS(ClientDataSet, GridRow);
      //Insert the new item at the drop position
      ClientDataSet.InsertRecord( 
         [GridRow, TListBox(Source).Items[TListBoxSource).ItemIndex], 
            RandomRange(1, 101)]);
    end;
    //Remove the dropped item from the source (optional)
    TListBox(Source).Items.Delete(TListBox(Source).ItemIndex);
  end
  else
  if Source = Sender then
  begin
    //We are dragging within the DBGrid
    if ClientDataSet.IsEmpty then exit;
    OriginalRow := ClientDataSet.RecNo;
    if (OriginalRow = GridRow) or (GridRow = -1) then
      exit
    else
      MoveRecord(ClientDataSet, OriginalRow, GridRow);
  end;
end;

As you can see, one of the tricks to this technique is getting the row over which the drop operation occurred. This is done using the DBGrid's public MouseCoord method, to which we pass the X and Y coordinates, which we obtain from the event handler's parameters. We then use the Y property of the returned TGridCoord object to discover the record being dropped on. (MouseCoord is public in Delphi 7 and later. If you are using an earlier version where this method is protected, refer to Zarko's THackDBGrid to expose this method to your code.)

Dragging and Dropping to a Specific DBGrid Position

What happens next depends on the source of the drag operation. If the drag operation originated from a ListBox, we need to ensure that it is dropped into the proper position within the DBGrid. To do this, we have to check for three possible conditions.

If the ClientDataSet is empty, we simply insert a record, assigning a sequence of 1. If the integer Y property of the TGridCoord instance returned by MouseCoord evaluates to -1, the user has dropped at the end of the DBGrid. This, too, is a simple matter of inserting the record with a sequence of TClientDataSet.RecordCount + 1.

It is when the drop occurs somewhere in the middle of the DBGrid that the approach is more complicated. In short, we need to re-sequence all of the records appearing in the drop location, adding 1 to their sequence field value. After that we can insert the record using the drop position record number as the value for the sequence field. In the OnDragDrop event handler listed previously this task is handled first by a call to ResequenceCDS, after which the TClientDataSet's InsertRecord method is invoked.

It is in the ResequenceCDS method that things get fun. As you can see in the code listing that follows, a ClientDataSet cloned cursor is used to perform the record resequencing. This has the effect of changing the underlying indexed field values with a minimal impact on the user interface.

procedure TForm1.ResequenceCDS(cds: TClientDataSet; FromRow: Integer);
var
  clone: TClientDataSet;
  SequenceFld: TField;
begin
  clone := TClientDataSet.Create(nil);
  try
    clone.CloneCursor(cds, True);
    SequenceFld := clone.FieldByName('Sequence');
    begin
      //Shift all records down to make room in the sequence
      //for the record being inserted
      clone.Last;
      while (SequenceFld.AsInteger >= FromRow) and not clone.bof do
      begin
        clone.Edit;
        SequenceFld.AsInteger := SequenceFld.AsInteger + 1;
        clone.Post;
        clone.Prior;
      end;
    end
  finally
    clone.Free;
  end;
end;

The following three figures demonstrate this drag-and-drop in action. Figure 2 depicts the DBGrid after three records have been added, while Figure 3 shows a fourth record being dropped into the second position of the DBGrid. Figure 4 shows how the DBGrid appears following the drop operation.



Figure 2. Three records have been dropped onto the DBGrid from the ListBox.



Figure 3. A value from the ListBox is being dropped into the second position of the DBGrid



Figure 4. The drag-and-drop operation has dropped the new value in position 2

Dragging and Dropping Existing Records in a DBGrid

Dragging an existing record from its current position to a new position within the DBGrid is a bit more involved. This process is handled by the MoveRecord method shown here.

procedure TForm1.MoveRecord(cds: TClientDataSet; OldPos, NewPos: Integer);
var
  clone: TClientDataSet;
  SequenceFld: TField;
begin
  clone := TClientDataSet.Create(nil);
  try
    clone.CloneCursor(cds, True);
    SequenceFld := clone.FieldByName('Sequence');
    clone.RecNo := OldPos;
    clone.Edit;
    //Move the record being moved to the end of the sequence
    SequenceFld.AsInteger := cds.RecordCount + 1;
    clone.Post;
    if OldPos < NewPos then
    begin
      //Shift the records after the original old position up one position
      clone.RecNo := OldPos;
      while (clone.RecNo < NewPos) do
      begin
        clone.Edit;
        SequenceFld.AsInteger := SequenceFld.AsInteger - 1;
        clone.Post;
        clone.Next;
      end;
    end
    else
    begin
      //Shift the record before the original position down one position
      clone.IndexFieldNames := 'Sequence';
      clone.RecNo := OldPos - 1;
      while (clone.RecNo >= NewPos) and (not clone.bof) do
      begin
        clone.Edit;
        SequenceFld.AsInteger := SequenceFld.AsInteger + 1;
        clone.Post;
        clone.Prior;
      end;
    end;
    //Move the record being moved to its new position
    clone.RecNo := cds.RecordCount;
    clone.Edit;
    SequenceFld.AsInteger := NewPos;
    clone.Post;
  finally
    clone.Free;
  end;
  cds.RecNo := NewPos;
end;

As you can see from this code, we begin by repositioning the record that is being moved to the end of the index order, assigning to it RecordCount + 1 (in reality this could be a any value outside the current range of sequence numbers, just so long as we get the record we are moving out of the way. Next, depending on whether the move is upwards or downwards, we resequence each of the records that need to be repositioned before we perform the final movement. Finally, we reposition the record we are moving into its new position.

This operation is demonstrated in the following two figures. In Figure 5 the record at position 4 is being dragged to position 2. Figure 6 shows the DBGrid after the drop operation has been completed.



Figure 5. The record in position 4 is being dragged to position 2.



Figure 6. The dragged record has now been moved to position 2.

Deleting Records

We also have to update the sequence when a record is removed. Since the ReadOnly property is set to True on the DBGrid, record removal is something that we do from a custom popup menu. This popup menu has a single item, whose caption is Remove. This menu is surpressed when there are no records in the grid by the OnPopup event handler, shown here.

procedure TForm1.Remove1Click(Sender: TObject);
var
  cds: TClientDataSet;
begin
  cds := TClientDataSet(DBGrid.DataSource.DataSet);
  RemoveFromSequence(cds, cds.RecNo);
end;

Removal is performed by the RemoveFromSequence method, shown here.

procedure TForm1.RemoveFromSequence(cds: TClientDataSet; Position: Integer);
var
   clone: TClientDataSet;
   SeqFld: TField;
begin
  clone := TClientDataSet.Create(nil);
  try
    clone.CloneCursor(cds, True);
    SeqFld := clone.FieldByName('Sequence');
    clone.RecNo := Position;
    // Edit - Added this to next line: and (clone.RecordCount = 1)
    if (Position = 1) and (clone.RecordCount = 1) then
    begin
      //There is just one record. Delete it,
      //but do not try to set a new record position
      clone.Delete;
    end
    else
    begin
      if clone.RecNo = clone.RecordCount then
      begin
        clone.Delete;
        cds.RecNo := cds.RecordCount;
      end
      else
      begin
        clone.Delete;
        while not clone.eof do
        begin
          clone.Edit;
          SeqFld.AsInteger := SeqFld.AsInteger - 1;
          clone.Post;
          clone.Next;
        end;
        cds.RecNo := Position;
      end;
    end;
  finally
    clone.Free;
  end;
end;

To Clone or Not To Clone

All of the methods that I have shown here make use of cloned cursors, which are very handy for working with a ClientDataSet in the background. However, cloned cursors are not essential for these techniques to work. In fact, the code sample in the download for this post includes two versions of the three workhorse methods (ResequenceCDS, MoveRecord, and RemoveFromSequence). The version that I have shown here that employes cloned cursors, and another version that does not. You might find that one version works better than the other, depending on the circumstances of your data.

Summary

Dragging and Dropping is a common feature in Delphi applications, and a great technique for productive user interface design. Dragging and dropping within DBGrids, however, is more involved, especially if you want to drop somewhere other than the end of the DBGrid. In this posting, I have demonstrated how you can use a ClientDataSet and a cloned ClientDataSet cursor to implement sophisticated drag-and-drop operations involving a DBGrid as a target.

If you want to learn more about ClientDataSets, including an entire chapter on cloned cursors, please take a look at my most recent book Delphi in Depth: ClientDataSets.
Copyright © 2012 Cary Jensen. All Rights Reserved.