Exploring Implementation of paging in XtraGrid

This article from: http://www.cnblogs.com/godwar/archive/2008/01/18/1044502.html

 

 

Exploring Implementation of paging in XtraGrid

To reduce the usage of servers, networks, and other resources by the client when using tables in a large amount of data, we need to implement the paging operation function in the XtraGrid table. Based on this functional requirement and the basic functions of XtraGrid, the following two paging operations solutions are proposed, which are studied and demonstrated one by one.
1. Use the Server Mode of XtraGrid itself
To meet the performance requirements for access to large data volumes, XtraGrid provides a working Mode called Server Mode. In this Mode, xtraGrid will not read all data from the data source to the memory at a time, but will read a small amount of data from the data source and display it to the screen as needed. However, XtraGrid has some limitations when working in this mode:
1) data cannot be edited, added, or deleted;
2) You cannot sort and filter data by displaying values (but you can edit values for these operations );
3) The ColumnView. CustomColumnSort event is not triggered during user sorting;
4) The user group does not trigger the GridView. CustomColumnGroup event;
5) The GridView. CustomSummaryCalculate event is not triggered when the user calculates the summary;
6) in Server Mode, the primary key of a data table cannot be composed of multiple columns.

In addition, because eXpress Persistent Objects library (XPO) can be used to connect data sources in XtraGrid Server Mode, XPO is also required when XPO is used to connect data sources, the databases and versions that support XPO are listed below:

Database Engine	Version (s)
Advantage	Advantage Data effecect v7.1
Asa	SQL Anywhere 8 and SQL Anywhere 9
Ase	Sybase Adaptive Server 12
DB2	DB2 8.1.9
Firebird	Firebird 1.5, Firebird 2.0
MSAccess	Microsoft Jet
MSSqlServer	Microsoft SqlServer 7.0, Microsoft SqlServer 2000, ms SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SqlServer 2005, SQL Server 2005 Express Edition
MSSqlServerCE	Microsoft sqlserver 2005 mobile, Microsoft sqlserver 2005 everywhere edition CTP
MySQL	MySQL Server 4.1, MySQL Server 5.0
Oracle	Oracle 9i, Oracle 10g
Pervasive	Pervasive. SQL 9
Postgres	PostgreSQL 8.1
SQLite	SQLite 3
Vistadb	Vistadb 2.1

Advantages: 1) easy to implement. 2) high efficiency.
Disadvantages: 1) because the xtragrid server mode does limit the data editing operations, the table can only be viewed and cannot be written, so it cannot meet the requirements for data editing in the table.
Conclusion: Because the lookupedit control only reads the option for selection, this mode can be used to increase the speed. For general tables, you can only use this method to view pages, but cannot edit them.

2. Use Database keywords and Algorithms
In SQL Server and Oracle, you can use some special keywords and algorithms (SQL) to implement paging.
1) In sqlserver, you can use the top keyword to implement the paging function. For example, the following SQL statement can implement the paging algorithm:
Select top [pagesize] *
From table
Where id not in
(Select top [pagesize * (currentpage-1)] ID from table [query condition] Order by ID)
And [query conditions]
Order by ID

2) The rownum keyword can be used in Oracle to implement the paging algorithm, for example, the following paging algorithm:
Select *
From (select row _. *, rownum _
From (...) Row _
Where rownum <= ?)
Where rownum _>?

3) use the LIMIT keyword in MYSQL to implement the paging algorithm, for example, the following paging algorithm:

Select *
From table [query condition]
Order by id limit ?,?

Advantages: 1) Fully implement functional requirements;
2) high efficiency and low resource usage.
Disadvantages: 1) the implementation is complicated and requires designing different SQL statements for different databases.
Conclusion: Although Table paging editing needs to increase programming workload, this implementation scheme is the most suitable.