Wednesday, August 7, 2013

Dragging and Dropping in DBGrids Revisited

Please note: a bug was found in the original code upload. It was reported in a comment below.

I have uploaded a code example that fixes the problem (see link at the of this post). However, that code can only handle drag/drop operations in a DBGrid so long as the Options property of that DBGrid includes the dgTitles flag.

I am leaving this post, and a link to the updated code, live on this blog, as I know that the feature described here is a valuable one. I will continue to work on addressing the remaining issue, and will update this blog and the code upload once I have accomplished that.

Once again, thank you for your patience.

About a year ago I posted a blog that described in detail how to implement drag and drop operations with a DBGrid. The technique that I described employed a ClientDataSet as a key element in the drag and drop process, and it is one that I have used extensively in the year since.

There was one glich, however. During this past spring's Delphi Developer Days tour with my colleague Bob Swart (a.k.a Dr.Bob) one of the attendees asked what would happen if the DBGrid displayed less than all records in the underlying ClientDataSet. It turns out that this is an issue with my original code example (and it has since been pointed out elegantly in a comment posted to the blog post I linked to above). If the first record displayed in the DBGrid does not correspond to the first record in the underlying ClientDataSet, my original code would incorrectly place a record dropped into the top position of the DBGrid in the top-most position of the ClientDataSet, instead of above the record onto which the drop operation targeted.

Initially I did not worry about this discrepancy too much, selfishly I will admit. In my real world usage, the number of records being dragged or dropped never exceeded the visual display capacity of the DBGrid. But it is a problem, and Michael Riley's posted comment on my earlier blog motivated me once again to look for a solution.

As that last paragraph implied, I did try to find a solution earlier, in response to the question posed by the Delphi Developer Days attendee. However, I could not see a way to determine the record number of the underlying DataSet when the visible rows of the DBGrid were greater than the number of records in the DataSet, and the DBGrid was scrolled down.

Having taken up the challenge again, I turned to every coder's best friend, Stack Overflow. There I posted my question, hoping that someone had discovered an appropriate mechanism. Even though I posted the question on a Saturday, long-time Stack Overflow contributor Uwe Raabe was quick to post an answer. With this information in hand, I was able to quickly adapt my earlier drag and drop example to correctly perform drag and drop into and within a DBGrid under all relevant conditions.

What Uwe proposed was the creation of a class helper for the TDBGrid class. His class helper looked like the following:

TDBGridHelper = class helper for TDBGrid
public
    function RecNoFromVisibleRow(Value: Integer): Integer;
end;

His implementation of the RecNoFromVisibleRow method is shown here:

function TDbGridHelper.RecNoFromVisibleRow(Value: Integer): Integer;
begin
  Result := DataSource.DataSet.RecNo - Row + TopRow + Value;
  if dgTitles in Options then
    Dec(Result);
end;

This worked like a charm, and solve the problem that I specifically asked about in my Stack Overflow post, which concerned the first visible record in the DBGrid. Testing my updated drag and drop example revealed another issue that I had not anticipated. Specifically, if the DBGrid contained more records than the number visible in the DBGrid, and the user dropped a new record past the end of the last visible record, that record would be placed at the end of the underlying ClientDataSet, rather than after the last visible record.

This problem was easy to solve by modifying Uwe's code in the following manner.

function TDBGridHelper.RecNoFromVisibleRow(Value: Integer): Integer;
begin
  if Value = -1 then
  begin
    Result := DataSource.DataSet.RecNo - Row + TopRow + VisibleRowCount
  end
  else
  begin
    Result := DataSource.DataSet.RecNo - Row + TopRow + Value;
    if dgTitles in Options then
      Dec(Result);
  end;
end;

Over the past several days I have tested this code pretty hard, and it appears to work under all conditions. A link to the download for the final code example is located at the end of this blog post.

I will leave it up to you to follow the link I provided at the outset of this post to read the details about how this dragging and dropping in DBGrids works. I will, however, point out the minor changes that the introduction of this class helper has on the original code. In addition, I will discuss the use of class helpers, and offer an alternative solution, in the form of an interceptor class.

My original OnDragDrop event handler on the DBGrid began like the following:

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

It now looks like this, where the initial adjustment of the GridRow variable is replaced by a call to the RecNoFromVisibleRow method:

procedure TForm1.DBGridDragDrop(Sender, Source: TObject; X, Y: Integer);
var
  GridRow: Integer;
  OriginalRow: Integer;
begin
  GridRow := DBGrid.MouseCoord(X,Y).Y;
  GridRow := DBGrid.RecNoFromVisibleRow((*ClientDataSet.RecNo, *)GridRow);
  if (Source is TListBox) then

However, when dragging and dropping within the DBGrid itself, GridRow values of 0 do need to be adjusted to 1, so that happens later in this method method, once a within grid drag/drop is confirmed, as shown here:

  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
    if GridRow = 0 then GridRow := 1;
    MoveRecord(ClientDataSet, OriginalRow, GridRow);
  end;

I also added an additional line of code to reposition the current record in the underlying ClientDataSet at the end of the segment associated with a drop from the ListBox (dropping from outside of the DBGrid). This line, and the several above it, are shown here:

  //Insert the new item at the drop position
  ClientDataSet.InsertRecord( [GridRow,  TListBox(Source).Items[TListBox(Source).ItemIndex], RandomRange(1, 101)]);
  //Make the dropped record the current record
  ClientDataSet.RecNo := GridRow;

Other than the declaration of the class helper and the implementation of RecNoFromVisibleRow, all of the remaining original code is untouched.

Now, let me address the use of the class helper. First let me say that using a class helper is a perfectly sound solution. It does, however, have one drawback. You can have only one class helper for a given class. If you already have a class helper for TDBGrid (or if Embarcadero introduces one), this new class helper will make that one unavailble.

An alternative solution is to use an interceptor class. An interceptor class is one that has the same class name as the existing class, and extends that existing class, but which has a scope closer to the code that uses it than the original class. When those conditions exist, the interceptor class takes precedence, and its overridden or introduced methods, properties, fields, and such, are available to your code. In addition, since the interceptor class is a descendant of the original, any protected (other than strict protected) members of the original class are visible to it. This is important, since Row and TopRow, which are protected members of the Vcl.DBGrids.TDBGrid class, need to be access from within the RecNoFromVisibleRow method.

In my code sample, the interceptor class is declared prior to the TForm class, which results in any TDBGrid instances appearing on the form being instances of my interceptor class. This class declaration looks like the following (in which I have included a little bit of the TForm declaration as well):

type
  TDBGrid = class(DBGrids.TDBGrid)
  public
    function RecNoFromVisibleRow(Value: Integer): Integer;
  end;
  TForm1 = class(TForm)
    ListBox: TListBox;
    DBGrid: TDBGrid;

Of course, you must implement the RecNoFromVisibleRow method in the interceptor class. In this case, since the interceptor class is named TDBGrid, the only difference between the implementation of the interceptor method and class helper method is the class name (TDBGrid versus TDBGridHelper). As a result, I will not repeat that code here. Otherwise, the remaining code is exactly the same. A helper class adds the RecNoFromVisibleRecord method to the Vcl.DBGrids.TDBGrid class, while the interceptor class introduces this method, and TDBGrid instances on the form are instances of the interceptor, instead of the interceptor's ancestor.

One final note. While I declared the interceptor class in the same unit as my form declaration, I could have just as easily declared it in some other unit. The only requirement for the use of the interceptor class in my form is that the unit in which the interceptor class is declared must appear in the interface section uses clause, and later in that uses clause than the Vcl.DBGrids unit.

Here is the link to the updated code. Please enjoy.