ASPNet中DataGrid基于Access的快速分頁(yè)法

1、DataGrid基于Access的快速分頁(yè)法DataGrid是一個(gè)功能非常強(qiáng)大的ASP.NET Web服務(wù)器端控件，它除了能夠方便地按各種方式格式化顯示表格中的數(shù)據(jù)，還可以對(duì)表格中的數(shù)據(jù)進(jìn)行動(dòng)態(tài)的排序、編輯和分頁(yè)。使Web開發(fā)人員從繁瑣的代碼中解放。實(shí)現(xiàn)DataGrid的分頁(yè)功能一直是很多初學(xué)ASP.NET的人感到棘手的問(wèn)題，特別是自定義分頁(yè)功能，實(shí)現(xiàn)方法多種多樣，非常靈活。本文將向大家介紹一種DataGird控件在Access數(shù)據(jù)庫(kù)下的快速分頁(yè)法，幫助初學(xué)者掌握DataGrid的分頁(yè)技術(shù)。目前的分頁(yè)方法DataGrid內(nèi)建的分頁(yè)方法是使用諸如“ SELECT * FROM 勺SQL語(yǔ)句從數(shù)據(jù)

2、庫(kù)表中取出所有的記錄到DataSet中，DataGrid控件綁定到該 DataSet之后，它的自動(dòng)分頁(yè)功能會(huì)幫你從該 DataSet中篩選出當(dāng)前分頁(yè)的數(shù)據(jù)并顯示出來(lái)，其他沒(méi)有用的數(shù)據(jù)將被丟 棄。還有一種方法是使用自定義分頁(yè)功能，先將DataGrid的AllowCustomPaging屬性設(shè)置為True ,再利用DataAdapter的Fill方法將數(shù)據(jù)的篩選工作提前到填充 DataSet時(shí)，而不 是讓DataGrid幫你篩選：該方法首先將來(lái)自查詢處的結(jié)果填充到DataSet中，再將不需要顯示的數(shù)據(jù)丟棄。當(dāng)然，自定義分頁(yè)功能需要完成的事情還不止這些，本文將在后面詳細(xì)介紹。以上兩種方法的工作原理都

3、是先從數(shù)據(jù)庫(kù)中取出所有的記錄，然后篩選出有用的數(shù)據(jù)顯示出來(lái)?？梢?jiàn)，兩種方法的效率基本上是一致的，因?yàn)樗鼈冊(cè)跀?shù)據(jù)訪問(wèn)階段并沒(méi)有采取有效的措施來(lái)減少Access對(duì)磁盤的訪問(wèn)次數(shù)。對(duì)于小數(shù)量的記錄，這種開銷可能是比較小的， 如果針對(duì)大量數(shù)據(jù)的分頁(yè)，開銷將會(huì)非常巨大，從而導(dǎo)致分頁(yè)的速度非常的慢。換句話說(shuō)， 就算每個(gè)DataGrid分頁(yè)面要顯示的數(shù)據(jù)只是一個(gè)擁有幾萬(wàn)條記錄的數(shù)據(jù)庫(kù)表的其中10條，每次DataGrid進(jìn)行分頁(yè)時(shí)還是要從該表中取出所有的記錄。很多人已經(jīng)意識(shí)到了這個(gè)問(wèn)題， 并提出了解決方法： 用自定義分頁(yè)，每次只從數(shù)據(jù)庫(kù)中 取出要顯示的數(shù)據(jù)。這樣，我們需要在 SQL語(yǔ)句上下功夫了。由于 Acc

4、ess不支持真正的存 儲(chǔ)過(guò)程，在編寫分頁(yè)算法上就沒(méi)有 SQL Server那么自由了。 SQL Server可以在存儲(chǔ)過(guò)程中 利用臨時(shí)表來(lái)實(shí)現(xiàn)高效率的分頁(yè)算法，受到了廣泛的采用。而對(duì)于 Access ,我們必須想辦 法在一條SQL語(yǔ)句內(nèi)實(shí)現(xiàn)最高效的算法。用一條SQL語(yǔ)句取得某段數(shù)據(jù)的方法有好幾種。算法不同，效率也就不同。我經(jīng)過(guò)粗略的測(cè)試，發(fā)現(xiàn)效率最差的SQL語(yǔ)句執(zhí)行時(shí)耗費(fèi)的時(shí)間大概是效率最高的SQL語(yǔ)句的3倍！而且這個(gè)數(shù)值會(huì)隨著記錄總數(shù)的增加而增加。下面將介紹其中兩條常用的SQL語(yǔ)句。為了方便接下來(lái)的討論，我們先約定如下:變量說(shuō)明變量說(shuō)明PageSize每貝顯不的記錄總數(shù)MiddleIndex

5、中間蟲的索引PageCount分頁(yè)總數(shù)LastIndex最后-頁(yè)的索引RecordCount數(shù)據(jù)表的記錄總數(shù)TableName數(shù)據(jù)庫(kù)表名稱PageIndex當(dāng)前頁(yè)的索引PrimaryKey主鍵字段名稱FirstIndexA頁(yè)的索引QueryFields要查詢的字段集變量定義PageCount(int)Math.Ceiling(double)RecordCount / PageSize)FirstIndex0LastIndexPageCount - 1MiddleIndex(int)Math.Ceiling(double)PageCount / 2)- 1先讓我們看看效率最差的SQL語(yǔ)句：SEL

6、ECT TOP PageSize * FROM TableNameWHERE PrimaryKey NOT IN (SELECT TOP PageSize*PageIndex PrimaryKey FROM TableNameORDER BY PrimaryKey ASC)ORDER BY PrimaryKey ASC這條SQL語(yǔ)句慢就慢在 NOTIN這里，主SELECTS句遍歷的每個(gè) PrimaryKey的值都要 跟子SELEC砒句的結(jié)果集中的每一個(gè) PrimaryKey的值進(jìn)行比較，這樣時(shí)間復(fù)雜度非常大。 這里不得不提醒一下大家，平時(shí)編寫SQL語(yǔ)句時(shí)應(yīng)該盡量避免使用NOT IN語(yǔ)句，因?yàn)樗?/p>

7、往往會(huì)增加整個(gè)SQL語(yǔ)句的時(shí)間復(fù)雜度。另一種是使用了兩個(gè) TO刖三個(gè)ORDER BY勺SQL語(yǔ)句，如下所示：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語(yǔ)句空間復(fù)雜度比較大。如果要顯示的分頁(yè)面剛好是最后一頁(yè)，那么它的效率比直接SELEC礎(chǔ)所有的記錄還要低。因此，對(duì)于分頁(yè)算法

8、，我們還應(yīng)該具體情況具體分析， 不能一概而論。 下面將簡(jiǎn)單介紹一下相關(guān)概念， 如果您對(duì)主鍵和索引非常熟悉， 可以直接跳 過(guò)。有關(guān)主鍵和索引的概念在ACCESS中，一個(gè)表的主鍵（PRIMARY KEY又稱主索引）必然是唯一索引（ UNIQUE INDEX）,它的值是不會(huì)重復(fù)的。除此之外，索引依據(jù)索引列的值進(jìn)行排序，每個(gè)索引記錄 包含著一個(gè)指向它所引用的數(shù)據(jù)行的指針，這對(duì)ORDER BY勺執(zhí)行非常有幫助。我們可以利用主鍵這兩個(gè)特點(diǎn)來(lái)實(shí)現(xiàn)對(duì)某條記錄的定位，從而快速地取出某個(gè)分頁(yè)上要顯示的記錄。舉個(gè)例子，假設(shè)主鍵字段為INTEGER1,在數(shù)據(jù)庫(kù)表中，記錄的索引已經(jīng)按主鍵字段的 值升序排好 （默認(rèn)情況下

9、） ， 那么主鍵字段值為“11”的記錄的索引，肯定剛好在值為“12”的記錄的索引前面 （假設(shè)數(shù)據(jù)庫(kù)表中存在主鍵的值為“12 ”的記錄）。 如果主鍵字段不具備UNIQUE約束，數(shù)據(jù)庫(kù)表中將有可能存在兩個(gè)或兩個(gè)以上主鍵字段的值為“11”的記錄，這樣就無(wú)法確定這些記錄之間的前后位置了。下面就讓我們看看如何利用主鍵來(lái)進(jìn)行數(shù)據(jù)的分段查詢吧?？焖俜猪?yè)法的原理其實(shí)該分頁(yè)法是從其他方法衍生而來(lái)的。 本人對(duì)原來(lái)的方法認(rèn)真地分析， 發(fā)現(xiàn)通過(guò)優(yōu)化和改進(jìn)可以非常有效地提高它的效率。 原算法本身效率很高， 但缺乏對(duì)具體問(wèn)題的具體分析。同一個(gè)分頁(yè)算法， 可能在取第一頁(yè)的數(shù)據(jù)時(shí)效率非常高， 但是在取最后一頁(yè)的數(shù)據(jù)時(shí)可能反而

10、效率更低。經(jīng)過(guò)分析，我們可以把分頁(yè)算法的效率狀態(tài)分為四種情況：1） PageIndex = FirstIndex2） FirstIndex PageIndex = MiddleIndex3） 3） MiddleIndex PageIndex = LastIndex狀態(tài)（ 1 ）和（4）分別表示第一頁(yè)和最后一頁(yè)。它們屬于特殊情況，我們不必對(duì)其使用特殊算法，直接用TOP就可以解決了，不然會(huì)把問(wèn)題復(fù)雜化，反而降低了效率。對(duì)于剩下的兩種狀態(tài)， 如果分頁(yè)總數(shù)為偶數(shù)， 我們可以看作是從數(shù)據(jù)庫(kù)表中刪掉第一頁(yè)和最后一頁(yè)的記錄，再把剩下的按前后位置平分為兩部分，即前面的一部分，也就是狀態(tài)（2 ），后面的為另一部

11、分，也就是狀態(tài)（3 ）；如果分頁(yè)總數(shù)為奇數(shù)，則屬于中間頁(yè)面的記錄歸于前面的部分。這四種狀態(tài)分別對(duì)應(yīng)著四組SQL語(yǔ)句，每組SQL語(yǔ)句由升序和降序兩條SQL語(yǔ)句組成。下面是一個(gè)數(shù)據(jù)庫(kù)表， 左邊第一列是虛擬的， 不屬于該數(shù)據(jù)庫(kù)表結(jié)構(gòu)的一部分， 它表示相應(yīng)記錄所在的分頁(yè)索引。該表將用于接下來(lái)的SQL語(yǔ)句的舉例中：PageIndexItemIdProductIdPrice00010011$120020011$1310030012$130040012$1120050013$140060013$1230070011$130080012$1540090013$120100013$11由表可得： PageSiz

12、e = 2 , RecordCount = 10 , PageCount = 5升序的SQL語(yǔ)句(1) PageIndex = FirstIndex取第一頁(yè)的數(shù)據(jù)是再簡(jiǎn)單不過(guò)了，我們只要用TOPPageSize就可以取出第一頁(yè)要顯示的記錄了。SELECT TOP PageSize QueryFields FROM TableName WHERE Condition ORDER BY PrimaryKey ASC(2) FirstIndex PageIndex (SELECT MAX(PrimaryKey) FROM (SELECT TOP PageSize*PageIndex PrimaryK

13、eyFROM TableNameWHERE ConditionORDER BY PrimaryKey ASC)TableA)WHERE ConditionORDER BY PrimaryKey ASC例如：PageIndex=1,紅- 黃- 藍(lán)PageindexIte midProductldPrice I0uni0011隼121020011$131030012ii300400122105oai3$14060013$12300700111$13OB001Z$154090013f 12aio0013$11(3) MiddleIndex PageIndex LastIndex接下來(lái)看看取數(shù)據(jù)庫(kù)表中

14、后半部分記錄的SQL語(yǔ)句。該語(yǔ)句跟前面的語(yǔ)句算法的原理是一樣的，只是方法稍微不同。先取出當(dāng)前頁(yè)之后的所有記錄的主鍵值，再?gòu)闹羞x出最小值， 然后取出主鍵值小于該最小值的前PageSize條記錄。SELECT * FROM (SELECT TOP PageSize QueryFieldsFROM TableNameWHERE PrimaryKey (SELECT MIN(PrimaryKey) FROM (FROM TableNameWHERE ConditionORDER BY PrimaryKey DESC)TableA)WHERE ConditionORDER BY PrimaryKey D

15、ESC)TableBORDER BY PrimaryKey ASC之所以把取數(shù)據(jù)表前半部分記錄和取后半部分記錄的SQL語(yǔ)句分開寫，是因?yàn)槭褂萌∏鞍氩糠钟涗浀腟QL語(yǔ)句時(shí)，當(dāng)前頁(yè)前面的記錄數(shù)目隨頁(yè)數(shù)遞增，而我們還要從這些記錄中取出它們的主鍵字段的值再?gòu)闹羞x出最大值。這樣一來(lái)，分頁(yè)速度將隨著頁(yè)數(shù)的增加而減慢。因此我沒(méi)有這樣做，而是在當(dāng)前頁(yè)索引大于中間頁(yè)索引時(shí)( MiddleIndex 黃- 藍(lán)PageindexIte midProductldPrice0lioi0011$12020011$1310300121$13040012$11工0013$14060013$123CI070011$130840

16、90013f 12 100013Qi(4) PageIndex = LastIndex取最后一頁(yè)的記錄可以簡(jiǎn)單地使用類似狀態(tài)(1)的做法:SELECT * FROM (SELECT TOP PageSize QueryFieldsFROM TableNameWHERE ConditionORDER BY PrimaryKey DESC)TableA ORDER BY PrimaryKey ASC不過(guò)，這樣產(chǎn)生的最后一頁(yè)不一定是實(shí)際意義上的最后一頁(yè)。因?yàn)樽詈笠豁?yè)的記錄數(shù)未必剛好跟PageSize相等，而上面的 SQL語(yǔ)句是直接取得倒數(shù)的 PageSize條記錄。如果想 要精確地取得最后一頁(yè)的記錄

17、，應(yīng)該在先計(jì)算出該頁(yè)的記錄數(shù)，作為TOP語(yǔ)句的條件：SELECT * FROM (SELECT TOP (RecordCount-PageSize*LastIndex) QueryFieldsFROM TableName WHERE ConditionORDER BY PrimaryKey DESC)TableA ORDER BY PrimaryKey ASC降序的SQL語(yǔ)句降序的SQL語(yǔ)句跟升序的大同小異，這里就不在羅嗦了J(1) PageIndex = FirstIndexSELECT TOP PageSize QueryFields FROM TableName WHERE Condit

18、ion ORDER BY PrimaryKey DESC(2) FirstIndex PageIndex = MiddleIndexSELECT TOP PageSize QueryFieldsFROM TableNameWHERE PrimaryKey (SELECT MIN(PrimaryKey) FROM (SELECT TOP PageSize*PageIndex PrimaryKeyFROM TableNameWHERE ConditionORDER BY PrimaryKey DESC)TableA)WHERE ConditionORDER BY PrimaryKey DESC(3

19、) MiddleIndex PageIndex (SELECT MAX(PrimaryKey) FROM (SELECT TOP (RecordCount-PageSize*(PageIndex+1) PrimaryKeyFROM TableNameWHERE ConditionORDER BY PrimaryKey ASC)TableA)WHERE ConditionORDER BY PrimaryKey ASC)TableB ORDER BY PrimaryKey DESCSELECT * FROM ( SELECT TOP (RecordCount-PageSize*LastIndex)

20、 QueryFieldsFROM TableName WHERE Condition ORDER BY PrimaryKey ASC)TableA ORDER BY PrimaryKey DESC如何動(dòng)態(tài)產(chǎn)生上述的 SQL語(yǔ)句?看了上面的SQL語(yǔ)句之后，相信大家已經(jīng)基本明白該分頁(yè)法的原理了。下面，我們將要設(shè)計(jì)一個(gè)動(dòng)態(tài)生成 SQL語(yǔ)句的類FastPaging 。該類有一個(gè)公有靜態(tài)方法，它根據(jù)您給出的 條件動(dòng)態(tài)生成SQL語(yǔ)句，作為方法的返回值。/產(chǎn)生根據(jù)指定字段排序并分頁(yè)查詢的SELECT語(yǔ)句。public static String Paging(int pageSize,/每頁(yè)要顯示的記錄的數(shù)

21、目。int pageIndex,/要顯示的頁(yè)的索引。int recordCount, String tableName, /String queryFields, /String primaryKey, / bool ascending, /String condition /)數(shù)據(jù)表中的記錄總數(shù)。要查詢的數(shù)據(jù)表。要查詢的字段。主鍵字段。是否為升序排列。查詢的篩選條件。StringBuilder sb = new StringBuilder();int pageCount = GetPageCount(recordCount,pageSize); /分頁(yè)的總數(shù)int middleIndex =

22、 GetMidPageIndex(pageCount); /中間頁(yè)的索引int firstIndex = 0; / 第一頁(yè)的索引int lastIndex = pageCount - 1; /最后一頁(yè)的索引if (pageIndex firstIndex & pageindex ().Append( SELECT MAX();elsesb.Append( = lastindex) /代碼略return sb.ToString();除了 Paging方法還有另外幾個(gè)方法：/根據(jù)記錄總數(shù)和分頁(yè)大小計(jì)算分頁(yè)數(shù)。public static int GetPageCount(int recordCoun

23、t, int pageSize) return (int)Math.Ceiling(double)recordCount/pageSize);/計(jì)算中間頁(yè)的頁(yè)索引。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);/獲取一個(gè)布爾值，該值指示排序的方式是否為升序。public static bool IsAscending(String orderType) return (orderType.ToUpper() = DESC) ? false : true);讓DataGrid工作起來(lái)有了上面的類，實(shí)現(xiàn)分頁(yè)的工作就簡(jiǎn)單多了。首先，我們要將DataGrid的AllowPaging屬性和AllowCustomPaging屬性為True ,除此之外，為了體現(xiàn)出升序和降序的功能，還需 要將AllowSorting 屬性也設(shè)置為True