DataGrid基于Access的快速分頁法

2024-09-07 19:04:57

字體：大中小

供稿：網(wǎng)友

datagrid是一個功能非常強大的asp.net web服務器端控件，它除了能夠方便地按各種方式格式化顯示表格中的數(shù)據(jù)，還可以對表格中的數(shù)據(jù)進行動態(tài)的排序、編輯和分頁。使人員從繁瑣的代碼中解放。實現(xiàn)datagrid的分頁功能一直是很多初學asp.net的人感到棘手的問題，特別是自定義分頁功能，實現(xiàn)方法多種多樣，非常靈活。本文將向大家介紹一種datagird控件在access數(shù)據(jù)庫下的快速分頁法，幫助初學者掌握datagrid的分頁技術(shù)。

目前的分頁方法

datagrid內(nèi)建的分頁方法是使用諸如“select * from <table>”的sql語句從數(shù)據(jù)庫表中取出所有的記錄到dataset中，datagrid控件綁定到該dataset之后，它的自動分頁功能會幫你從該dataset中篩選出當前分頁的數(shù)據(jù)并顯示出來，其他沒有用的數(shù)據(jù)將被丟棄。

還有一種方法是使用自定義分頁功能，先將datagrid的allowcustompaging屬性設(shè)置為true，再利用dataadapter的fill方法將數(shù)據(jù)的篩選工作提前到填充dataset時，而不是讓datagrid幫你篩選：

public int fill (

dataset dataset, //要填充的 dataset。

int startrecord, //從其開始的從零開始的記錄號。

int maxrecords, //要檢索的最大記錄數(shù)。

string srctable //用于表映射的源表的名稱。

);

該方法首先將來自查詢處的結(jié)果填充到dataset中，再將不需要顯示的數(shù)據(jù)丟棄。當然，自定義分頁功能需要完成的事情還不止這些，本文將在后面詳細介紹。

以上兩種方法的工作原理都是先從數(shù)據(jù)庫中取出所有的記錄，然后篩選出有用的數(shù)據(jù)顯示出來?？梢姡瑑煞N方法的效率基本上是一致的，因為它們在數(shù)據(jù)訪問階段并沒有采取有效的措施來減少access對磁盤的訪問次數(shù)。對于小數(shù)量的記錄，這種開銷可能是比較小的，如果針對大量數(shù)據(jù)的分頁，開銷將會非常巨大，從而導致分頁的速度非常的慢。換句話說，就算每個datagrid分頁面要顯示的數(shù)據(jù)只是一個擁有幾萬條記錄的數(shù)據(jù)庫表的其中10條，每次datagrid進行分頁時還是要從該表中取出所有的記錄。

很多人已經(jīng)意識到了這個問題，并提出了解決方法：用自定義分頁，每次只從數(shù)據(jù)庫中取出要顯示的數(shù)據(jù)。這樣，我們需要在sql語句上下功夫了。由于access不支持真正的存儲過程，在編寫分頁算法上就沒有sql server那么自由了。sql server可以在存儲過程中利用臨時表來實現(xiàn)高效率的分頁算法，受到了廣泛的采用。而對于access，我們必須想辦法在一條sql語句內(nèi)實現(xiàn)最高效的算法。

用一條sql語句取得某段數(shù)據(jù)的方法有好幾種。算法不同，效率也就不同。我經(jīng)過粗略的測試，發(fā)現(xiàn)效率最差的sql語句執(zhí)行時耗費的時間大概是效率最高的sql語句的3倍！而且這個數(shù)值會隨著記錄總數(shù)的增加而增加。下面將介紹其中兩條常用的sql語句。

為了方便接下來的討論，我們先約定如下：

變量
說明
變量
說明

@pagesize
每頁顯示的記錄總數(shù)
@middleindex
中間頁的索引

@pagecount
分頁總數(shù)
@lastindex
最后一頁的索引

@recordcount
數(shù)據(jù)表的記錄總數(shù)
@tablename
數(shù)據(jù)庫表名稱

@pageindex
當前頁的索引
@primarykey
主鍵字段名稱

@firstindex
第一頁的索引
@queryfields
要查詢的字段集

變量
定義

@pagecount
(int)math.ceiling((double)@recordcount / @pagesize)

@firstindex
0

@lastindex
@pagecount – 1

@middleindex
(int)math.ceiling((double)@pagecount / 2) – 1

先讓我們看看效率最差的sql語句：

select top @pagesize * from @tablename

where @primarykey not in (

select top @pagesize*@pageindex @primarykey from @tablename

order by @primarykey asc

) order by @primarykey asc

這條sql語句慢就慢在not in這里，主select語句遍歷的每個@primarykey的值都要跟子select語句的結(jié)果集中的每一個@primarykey的值進行比較，這樣時間復雜度非常大。這里不得不提醒一下大家，平時編寫sql語句時應該盡量避免使用not in語句，因為它往往會增加整個sql語句的時間復雜度。

另一種是使用了兩個top和三個order by的sql語句，如下所示：

select * from (

select top @pagesize * from (

select top @pagesize*(@pageindex+1) * from @tablename

order by @primarykey asc

) tablea order by @primarykey desc

) tableb order by @primarykey asc

這條sql語句空間復雜度比較大。如果要顯示的分頁面剛好是最后一頁，那么它的效率比直接select出所有的記錄還要低。因此，對于分頁算法，我們還應該具體情況具體分析，不能一概而論。下面將簡單介紹一下相關(guān)概念，如果您對主鍵和索引非常熟悉，可以直接跳過。

有關(guān)主鍵和索引的概念

在 access中，一個表的主鍵（primary key，又稱主索引）必然是唯一索引（unique index），它的值是不會重復的。除此之外，索引依據(jù)索引列的值進行排序，每個索引記錄包含著一個指向它所引用的數(shù)據(jù)行的指針，這對order by的執(zhí)行非常有幫助。我們可以利用主鍵這兩個特點來實現(xiàn)對某條記錄的定位，從而快速地取出某個分頁上要顯示的記錄。

舉個例子，假設(shè)主鍵字段為integer型，在數(shù)據(jù)庫表中，記錄的索引已經(jīng)按主鍵字段的值升序排好（默認情況下），那么主鍵字段值為“11”的記錄的索引，肯定剛好在值為“12”的記錄的索引前面（假設(shè)數(shù)據(jù)庫表中存在主鍵的值為“12”的記錄）。如果主鍵字段不具備unique約束，數(shù)據(jù)庫表中將有可能存在兩個或兩個以上主鍵字段的值為“11”的記錄，這樣就無法確定這些記錄之間的前后位置了。

下面就讓我們看看如何利用主鍵來進行數(shù)據(jù)的分段查詢吧。

快速分頁法的原理

其實該分頁法是從其他方法衍生而來的。本人對原來的方法認真地分析，發(fā)現(xiàn)通過優(yōu)化和改進可以非常有效地提高它的效率。原算法本身效率很高，但缺乏對具體問題的具體分析。同一個分頁算法，可能在取第一頁的數(shù)據(jù)時效率非常高，但是在取最后一頁的數(shù)據(jù)時可能反而效率更低。

經(jīng)過分析，我們可以把分頁算法的效率狀態(tài)分為四種情況：

（1）@pageindex <= @firstindex

（2）@firstindex < @pageindex <= @middleindex

（3）@middleindex < @pageindex < @lastindex

（4）@pageindex >= @lastindex

狀態(tài)（1）和（4）分別表示第一頁和最后一頁。它們屬于特殊情況，我們不必對其使用特殊算法，直接用top就可以解決了，不然會把問題復雜化，反而降低了效率。對于剩下的兩種狀態(tài)，如果分頁總數(shù)為偶數(shù)，我們可以看作是從數(shù)據(jù)庫表中刪掉第一頁和最后一頁的記錄，再把剩下的按前后位置平分為兩部分，即前面的一部分，也就是狀態(tài)（2），后面的為另一部分，也就是狀態(tài)（3）；如果分頁總數(shù)為奇數(shù)，則屬于中間頁面的記錄歸于前面的部分。這四種狀態(tài)分別對應著四組sql語句，每組sql語句由升序和降序兩條sql語句組成。

下面是一個數(shù)據(jù)庫表，左邊第一列是虛擬的，不屬于該數(shù)據(jù)庫表結(jié)構(gòu)的一部分，它表示相應記錄所在的分頁索引。該表將用于接下來的sql語句的舉例中：

pageindex
itemid
productid
price

0
001
0011
$12

002
0011
$13

1
003
0012
$13

004
0012
$11

2
005
0013
$14

006
0013
$12

3
007
0011
$13

008
0012
$15

4
009
0013
$12

010
0013
$11

由表可得：@pagesize = 2，@recordcount = 10，@pagecount = 5

升序的sql語句

（1）@pageindex <= @firstindex

取第一頁的數(shù)據(jù)是再簡單不過了，我們只要用top @pagesize就可以取出第一頁要顯示的記錄了。

select top @pagesize @queryfields

from @tablename

where @condition

order by @primarykey asc

（2）@firstindex < @pageindex <= @middleindex

把取數(shù)據(jù)表前半部分記錄和取后半部分記錄的sql語句分開寫，可以有效地改善性能。后面我再詳細解釋這個原因?，F(xiàn)在看看取前半部分記錄的sql語句。先取出當前頁之前的所有記錄的主鍵值，再從中選出最大值，然后取出主鍵值大于該最大值的前@pagesize條記錄。這里@primarykey的數(shù)據(jù)類型可以不是integer類型，char、varchar等其他類型照樣可以。

select top @pagesize @queryfields

from @tablename

where @primarykey > (

select max(@primarykey) from (

select top @pagesize*@pageindex @primarykey

from @tablename

where @condition

order by @primarykey asc

) tablea

) where @condition

order by @primarykey asc

例如：@pageindex=1，紅-->黃-->藍

（3）@middleindex < @pageindex < @lastindex

接下來看看取數(shù)據(jù)庫表中后半部分記錄的sql語句。該語句跟前面的語句算法的原理是一樣的，只是方法稍微不同。先取出當前頁之后的所有記錄的主鍵值，再從中選出最小值，然后取出主鍵值小于該最小值的前@pagesize條記錄。

select * from (

select top @pagesize @queryfields

from @tablename

where @primarykey < (

select min(@primarykey) from (

select top (@[email protected]*(@pageindex+1)) @primarykey

from @tablename

where @condition

order by @primarykey desc

) tablea

) where @condition

order by @primarykey desc

) tableb

order by @primarykey asc

之所以把取數(shù)據(jù)表前半部分記錄和取后半部分記錄的sql語句分開寫，是因為使用取前半部分記錄的sql語句時，當前頁前面的記錄數(shù)目隨頁數(shù)遞增，而我們還要從這些記錄中取出它們的主鍵字段的值再從中選出最大值。這樣一來，分頁速度將隨著頁數(shù)的增加而減慢。因此我沒有這樣做，而是在當前頁索引大于中間頁索引時（@middleindex < @pageindex）選用了分頁速度隨著頁數(shù)的增加而加快的算法。由此可見，假設(shè)把所有分頁面劃分為前面、中間和后面三部分，則最前面和最后面的分頁速度最快，最中間的分頁速度最慢。

例如：@pageindex=3，紅 --> 黃 --> 藍

（4）@pageindex >= @lastindex

取最后一頁的記錄可以簡單地使用類似狀態(tài)（1）的做法：

select * from (

select top @pagesize @queryfields

from @tablename

where @condition

order by @primarykey desc

) tablea order by @primarykey asc

不過，這樣產(chǎn)生的最后一頁不一定是實際意義上的最后一頁。因為最后一頁的記錄數(shù)未必剛好跟@pagesize相等，而上面的sql語句是直接取得倒數(shù)的@pagesize條記錄。如果想要精確地取得最后一頁的記錄，應該在先計算出該頁的記錄數(shù)，作為top語句的條件：

select * from (

select top (@[email protected]*@lastindex) @queryfields

from @tablename where @condition

order by @primarykey desc

) tablea order by @primarykey asc

降序的sql語句

降序的sql語句跟升序的大同小異，這里就不在羅嗦了j

（1）@pageindex <= @firstindex

select top @pagesize @queryfields

from @tablename

where @condition

order by @primarykey desc

（2）@firstindex < @pageindex <= @middleindex

select top @pagesize @queryfields

from @tablename

where @primarykey < (

select min(@primarykey) from (

select top @pagesize*@pageindex @primarykey

from @tablename

where @condition

order by @primarykey desc

) tablea

) where @condition

order by @primarykey desc

（3）@middleindex < @pageindex < @lastindex

select * from (

select top @pagesize @queryfields

from @tablename

where @primarykey > (

select max(@primarykey) from (

select top (@[email protected]*(@pageindex+1)) @primarykey

from @tablename

where @condition

order by @primarykey asc

) tablea

) where @condition

order by @primarykey asc

) tableb order by @primarykey desc

（4）@pageindex >= @lastindex

select * from (

select top (@[email protected]*@lastindex) @queryfields

from @tablename where @condition order by @primarykey asc

) tablea order by @primarykey desc

如何動態(tài)產(chǎn)生上述的sql語句？

看了上面的sql語句之后，相信大家已經(jīng)基本明白該分頁法的原理了。下面，我們將要設(shè)計一個動態(tài)生成sql語句的類fastpaging。該類有一個公有靜態(tài)方法，它根據(jù)您給出的條件動態(tài)生成sql語句，作為方法的返回值。

// 產(chǎn)生根據(jù)指定字段排序并分頁查詢的 select 語句。

public static string paging(

int pagesize, //每頁要顯示的記錄的數(shù)目。

int pageindex, //要顯示的頁的索引。

int recordcount, //數(shù)據(jù)表中的記錄總數(shù)。

string tablename, //要查詢的數(shù)據(jù)表。

string queryfields, //要查詢的字段。

string primarykey, //主鍵字段。

bool ascending, //是否為升序排列。

string condition //查詢的篩選條件。

) {

stringbuilder sb = new stringbuilder();

int pagecount = getpagecount(recordcount,pagesize); //分頁的總數(shù)

int middleindex = getmidpageindex(pagecount); //中間頁的索引

int firstindex = 0; //第一頁的索引

int lastindex = pagecount - 1; //最后一頁的索引

if (pageindex <= firstindex) {

// 代碼略

} else if (pageindex > firstindex && pageindex <= middleindex) {

sb.append("select top ").append(pagesize).append(" ")

.append(queryfields).append(" from ").append(tablename)

.append(" where ").append(primarykey);

if (ascending)

sb.append(" > (").append(" select max(");

else

sb.append(" < (").append(" select min(");

sb.append(primarykey).append(") from ( select top ")

.append(pagesize*pageindex).append(" ").append(primarykey)

.append(" from ").append(tablename);

if (condition != string.empty)

sb.append(" where ").append(condition);

sb.append(" order by ").append(primarykey).append(" ")

.append(getsorttype(ascending)).append(" ) tablea )");

if (condition != string.empty)

sb.append(" and ").append(condition);

sb.append(" order by ").append(primarykey).append(" ")

.append(getsorttype(ascending));

}

else if (pageindex > middleindex && pageindex < lastindex) {

// 代碼略

} else if (pageindex >= lastindex) {

// 代碼略

}

return sb.tostring();

}

除了paging方法還有另外幾個方法：

// 根據(jù)記錄總數(shù)和分頁大小計算分頁數(shù)。

public static int getpagecount(int recordcount, int pagesize) {

return (int)math.ceiling((double)recordcount/pagesize);

}

// 計算中間頁的頁索引。

public static int getmidpageindex(int pagecount) {

return (int)math.ceiling((double)pagecount/2) - 1;

}

// 獲取排序的方式（"asc"表示升序，"desc"表示降序）

public static string getsorttype(bool ascending) {

return (ascending ? "asc" : "desc");

}

// 獲取一個布爾值，該值指示排序的方式是否為升序。

public static bool isascending(string ordertype) {

return ((ordertype.toupper() == "desc") ? false : true);

}

讓datagrid工作起來

有了上面的類，實現(xiàn)分頁的工作就簡單多了。首先，我們要將datagrid的allowpaging屬性和allowcustompaging屬性為true，除此之外，為了體現(xiàn)出升序和降序的功能，還需要將allowsorting屬性也設(shè)置為true。然后在每次分頁時，我們需要產(chǎn)生一個oledbdatareader對象或dataview對象綁定到datagrid，作為datagrid的數(shù)據(jù)源。這里需要用fastpaging類的paging方法根據(jù)條件產(chǎn)生一個sql語句，并賦給oledbcommand對象的commandtext屬性：

cmd.commandtext = fastpaging.paging(

datagrid1.pagesize,

(int)viewstate["currentpageindex"],

datagrid1.virtualitemcount,

"items",

"itemid, productid, price",

"itemid",

fastpaging.isascending(ordertype),

""

);

在上面的程序段中，viewstate["currentpageindex"]的值在datagrid的page事件處理程序中被更新為e.newpageindex。為了方便處理viewstate的空值，最好把對viewstate["currentpageindex"]的存取操作和空值判斷封裝在一個屬性里。datagrid1. virtualitemcount應該設(shè)置為數(shù)據(jù)庫表中的記錄總數(shù)。datagrid通過它和pagesize屬性可以虛擬出datagrid的分頁數(shù)。virtualitemcount的值是在page的load事件處理程序中被設(shè)置的，而該值的大小需要經(jīng)過一次數(shù)據(jù)庫訪問才能得到。為了提高性能，可以只在第一次加載頁面的時候設(shè)置該值。

總結(jié)

datagrid基于access的快速分頁法到這里就介紹完了。當然，這種方法并不能“包治百病”，可能對于您的要實現(xiàn)的功能，還有其它更好的方法。這就需要大家在平時工作和學習中不斷總結(jié)經(jīng)驗，在解決實際問題時盡可能找到最有效的方法。這也是本文的方法中所貫穿的思想。

上一篇：ACCESS數(shù)據(jù)庫訪問組件（一）

下一篇：使用ADOX創(chuàng)建Access數(shù)據(jù)庫和表