Leonardo's blog

This is one of those things I needed for ages, but didn't bothered to implement
because I thought it could require an unnecessary amount of work, and allways
opted for a less perfectionist method.

I'm talking about a method to retrieve data in Pages, then browse it in a ListView
transparently for the user.

I know TDbGrid allows a similar behavior when connected to *some* database connectors,
such as ADO, but what about a general method of browsing paged data independently
of the database engine?.

Let's start by creating an application and a data module, containing a database
connection, a dataset and a transaction. I'll assume you know how to create
connections to databases, datasets and how to do queries.

Now, supposse the TDataSet is a TIbQuery component that allows to query an Interbase
or Firebird database, and we have a table called Customers, with one million records.
The table has three fields, CustId, FirstName and LastName.

ListView in Virtual Mode

Place a TListView in the main form, then set this properties:

ViewStyle = vsReport
Columns = (3 columns Id, FirstName, LastName)
OwnerData = True

The OwnerData property setted to True, means the ListView will not be a data repository
by itself, it won't contain any data. To show it on screen,
it will rely on its OnData method, who will be in charge of getting data from
the dataset and paint the rows of the ListView.

First attempt

Let's do a first essay by getting All the data from the database. TListView
in Virtual Mode, must know in advance how much data it will show, so
first of all, override the Form's OnCreate method with this:

procedure TForm1.FormCreate(Sender: TObject);
begin
 IbQuery1.Close;
 IbQuery1.SQL.Text := 'select count(*) from customers';
 IbQuery1.Open;
 ListView1.Items.Count := IbQuery1.Fields[0].Value;
 // Re-Set the query
 IbQuery1.Close;
 IbQuery1.SQL.Text := 'select CustId, FirstName, LastName from customers';
 IbQuery1.Open;
end;

The second step is to override the OnData method of the TListView with
this code:

procedure TForm1.ListView1Data(Sender: TObject; Item: TListItem);
begin
 (* Move the DataSet's pointer to Item.Index, and paint the ListView's item *)
 IbQuery1.RecNo := Item.Index + 1;
 Item.Caption := IbQuery1.Fields[0].Value;
 Item.SubItems.Add(IbQuery1.fields[1].Value);
end;

In small datasets, this code will perform really god, but as the dataset
becomes huge, it will start getting slower, and slower.

Pagination

What I want to accomplish, is to divide the data in pages of say 100 records,
then show each page at a time. Querying only 100 records each time, is
practically instantaneous, so let's show the first page, then, when the user
tries to browse after the 100 nt record, re-query the database for the
101 to 200 and so on.

Almost every database engine has a method to query just a slice of the data,
MySql has "limit nn to mm", Firebird has "Rows nn to mm", MsSql has "Top", etc.
So, I'll add a new method to my program, to let query only a given page:

procedure TForm1.GetCurrentPage(ACurrentPage: Integer);
var
 lFrom: Integer;
 lTo: Integer;

begin
 lFrom := ((ACurrentPage * cPageSize) - cPageSize) + 1;
 lTo := (ACurrentPage * cPageSize) + 1;
 IbQuery1.Close;
 IbQuery1.SQL.Text :=
  'select CustId, FirstName, LastName from customers ' +
  'rows ' + IntToStr(lFrom) + ' to ' + IntToStr(lTo);
 IbQuery1.Open;
end;

Add the constant "const cPageSize = 100;" just after the "implementation" section
of the unit, or simply replace cPageSize by 100. Also add a the internal attribute
FCurrentPage: Integer; in the private section of the form.

Now, go back to the OnCreate method, and change it by this:

procedure TForm1.FormCreate(Sender: TObject);
begin
 IbQuery1.Close;
 IbQuery1.SQL.Text := 'select count(*) from customers';
 IbQuery1.Open;
 ListView1.Items.Count := IbQuery1.Fields[0].Value;
 // query for the first page
 FCurrentPage := 1;
 GetCurrentPage(FCurrentPage);
end;

The last step is to slightly modify the OnData method with this:

procedure TForm1.ListView1Data(Sender: TObject; Item: TListItem);
var
 lCurrPage: Integer;
 lPos: Integer;
begin
 (* Get current page index *)
 lCurrPage := Item.Index div cPageSize;
 (* Get the position in the current page *)
 lPos := Item.Index - (lCurrPage * cPageSize);

 (* Page changed? refresh the data *)
 if FCurrentPage - 1 <> lCurrPage then
 begin
   FCurrentPage := lCurrPage + 1;
   GetDataPage(FCurrentPage);
 end;

 (* Paint the ListView's item *)
 IbQuery1.RecNo := lPos + 1;
 Item.Caption := IbQuery1.Fields[0].Value;
 Item.SubItems.Add(IbQuery1.fields[1].Value);
end;

I hope you enyoyed this as much as I did when I wrote it. When I'll find
time, I'll post a modified version using TCollections instead of
TDataSets, just as I do in my projects.