Monday, April 06, 2009

 

Binding TCollections to paginating ListViews


In my previous article, I wrote about a method to paginate data shown in a ListView.
Now, I want to extend its reach by replacing TDataSets by TCollections.

What is a TCollection?

A TCollection is a container, where objects of only one type can be stored in it.
The difference with other containers like TList and TObjectList is they can
contain any kind of pointer, in the case of TList, and heterogenous objects in
a TObjectList, on the other hand, in a TCollection,
only objects of a specific class can be used.

A collection of Customers

To create a collection of Customers, we have to derive a class from TCollectionItem,
for example TCustomer, and another class from TCollection, for example
TCustomers, as follows:


TCustomer = class(TCollectionItem)
private
FCustId: Integer;
FLastName: string;
FFirstName: string;
public
property CustId: Integer read FCustId write FCustId;
property FirstName: string read FFirstName write FFirstName;
property LastName: string read FLastName write FLastName;
end;

TCustomers = class(TCollection)
private
function GetItem(AIndex: Integer): TCustomer;
public
function Add: TCustomer;
property Items[AIndex: Integer]: TCustomer read GetItem; default;
end;


Now lets define the methods Add and GetItem:


function TCustomers.GetItem(AIndex: Integer): TCustomer;
begin
result := TCustomer(inherited GetItem(AIndex));
end;

function TCustomers.Add: TCustomer;
begin
result := TCustomer(inherited Add);
end;


Save the above code in a unit called customers.pas, then create a new application,
and add the newly created unit to the "uses" clause of the main form, then add
the attribute FCustomers: TCustomers; to the private section of the form:


type
TForm1 = class(TForm)
private
FCustomers: TCustomers;
public
{ Public declarations }
end;



Now, override the OnCreate and OnDestroy methods of the main form to
instantiate and free the collection:


procedure TForm1.FormCreate(Sender: TObject);
begin
FCustomers := TCustomers.Create(TCustomer);
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
FCustomers.Free;
end;


From database to collection

Ok, the next step is to fill the collection with data. As I explained in my last
article, place the database connectivity components in a DataModule or just
in the main form (this is just an example) and name the TQuery as IbQuery1.

Good, the next step is to adapt the FormCreate method of the last article
to this:


procedure TForm1.FormCreate(Sender: TObject);
begin
(* Create an instance of the customers collection *)
FCustomers := TCustomers.Create(TCustomer);
(* Get the total amount of customers *)
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;


Now, to fill our TCollection with data from the database, just modify
our GetCurrentPage method as this:


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

begin
(* Do the query *)
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;
(* Fill the collection *)
FCustomers.Clear;
while not IbQuery1.Eof do
begin
with FCustomers.Add do
begin
CustId := IbQuery1.FieldByName('CustId').AsInteger;
FirstName := IbQuery1.FieldByName('FirstName').AsString;
LastName := IbQuery1.FieldByName('LastName').AsString;
end;
IbQuery1.Next;
end;
IbQuery1.Close;
end;


Let me tell you that assigning data by querying with FieldByName is very slow,
but simple enough for the purpouse of this article, in a future post, I'll
show an improved version.

The last step, is to place the TListView in the form, set its properties as
shown in my last article and create the new OnData method,
adapted to our TCollection:


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;
GetCurrentPage(FCurrentPage);
end;

(* Paint the ListView's item with our TCollection's items *)
Item.Caption := FCustomers[lPos].LastName;
Item.SubItems.Add(FCustomers[lPos].FirstName);
end;


That's it.

My next article, will be focused on creating an automated object binding method
using RTTI, to avoid repetitive assignments by hand.
Comments:
Hello Leonardo,

Thank you to share with us these great lines of code. What I can suggest you to improve FieldByName reference is to add something like :

procedure TForm1.GetCurrentPage(ACurrentPage: Integer);
var
lFrom: Integer;
lTo: Integer;
I: Integer;
ACustID, AFirstName, ALastName: TField;

begin
(* Do the query *)
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;
(* Fill the collection *)
FCustomers.Clear;
ACustID := IbQuery1.FieldByName('CustId');
AFirstName := IbQuery1.FieldByName('FirstName');
ALastName := IbQuery1.FieldByName('LastName');
while not IbQuery1.Eof do
begin
with FCustomers.Add do
begin
CustId := ACustId.AsInteger;
FirstName := AFirstName.AsString;
LastName := ALastName.AsString;
end;
IbQuery1.Next;
end;
IbQuery1.Close;
end;

I'm very impatient to read the following of this article.

Regards,

Laurent
 
Hola Leonardo, saludos desde Salta, Argentina.

En la línea 15 del procedure
TForm1.ListView1Data(Sender: ....
donde dice
GetDataPage(FCurrentPage)
debería decir
GetCurrentPage(FCurrentPager)

Lo puedes corregir para el que copia y pega ... :)

Otra cosa. Yo utilizo Firebird 1.5 y la sentencia select no permite para esta versión el uso de "rows x to y" sino hasta la versión 2.0;
en cambio lo solucioné con el uso de "first" y "skip" de la siguiente manera:

IbQuery1.Close;
IbQuery1.SQL.Text :=

'select first ' + IntToStr(lTo - lFrom) + ' skip ' + ntToStr(lFrom) + 'CustId, FirstName, LastName from customers';

IbQuery1.Open;

Esto sería algo así como: Seleccionar los X registros siguientes a partir del registro Y...

Falta revisar si en algunos de los límites se suma o resta uno, pero la idea del uso está.
 
Gracias CAMU, el error está corregido.

Con respecto a la modificación para Firebird 1.5 es correcta, la idea del post es dar a conocer una metodología para paginar datos en un ListView. Luego hay que adaptarlo a las particularidades de cada motor de base de datos.
 
Saludo, Leonardo.

Por desgracia domino el español mal, por eso uso al traductor informático :-).
Si puedo traducir al ruso dos tus artículos instalado en http://leonardorame.blogspot.com/("ListView and Pagination" y "Binding TCollections to paginating ListViews") y colocar las traducciones en el sitio http://www.freepascal.ru para que ellos podían leer de lengua rusa los usuarios FreePascal y Lazarus?
En los códigos de los ejemplos debo aportar el cambio pequeño - sustituir el nombre IBQuery en SQLQuery, porque en IDE Lazarus (el análogo Delphi 7) este componente cumple la función de las interpelaciones a la base de datos.

Agradezco es cordial.
Vadim Isáev
 
Vadim, yes, you can publish the article in Russian, and make all changes you need. Just don't forget to mention that was originally written by me, and add this URL.
 
Thank your, Leonardo!
Certainly, at the very beginning of article-transfer will be written your name as author and a reference to the source.
 
Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?