DataGrid基於Access的快速分頁法

來源:互聯網
上載者:User
access|datagrid|分頁 DataGrid是一個功能非常強大的ASP.NET Web伺服器端控制項,它除了能夠方便地按各種方式格式化顯示表格中的資料,還可以對錶格中的資料進行動態排序、編輯和分頁。使Web開發人員從繁瑣的代碼中解放。實現DataGrid的分頁功能一直是很多初學ASP.NET的人感到棘手的問題,特別是自訂分頁功能,實現方法多種多樣,非常靈活。本文將向大家介紹一種DataGird控制項在Access資料庫下的快速分頁法,協助初學者掌握DataGrid的分頁技術。



目前的分頁方法



DataGrid內建的分頁方法是使用諸如“SELECT * FROM <TABLE>”的SQL語句從資料庫表中取出所有的記錄到DataSet中,DataGrid控制項綁定到該DataSet之後,它的自動分頁功能會幫你從該DataSet中篩選出當前分頁的資料並顯示出來,其他沒有用的資料將被丟棄。



還有一種方法是使用自訂分頁功能,先將DataGrid的AllowCustomPaging屬性設定為True,再利用DataAdapter的Fill方法將資料的篩選工作提前到填充DataSet時,而不是讓DataGrid幫你篩選:



public int Fill (

DataSet dataSet, //要填充的 DataSet。

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

int maxRecords, //要檢索的最大記錄數。

string srcTable //用於表映射的源表的名稱。

);




該方法首先將來自查詢處的結果填充到DataSet中,再將不需要顯示的資料丟棄。當然,自訂分頁功能需要完成的事情還不止這些,本文將在後面詳細介紹。



以上兩種方法的工作原理都是先從資料庫中取出所有的記錄,然後篩選出有用的資料顯示出來。可見,兩種方法的效率基本上是一致的,因為它們在資料訪問階段並沒有採取有效措施來減少Access對磁碟的訪問次數。對於小數量的記錄,這種開銷可能是比較小的,如果針對大量資料的分頁,開銷將會非常巨大,從而導致分頁的速度非常的慢。換句話說,就算每個DataGrid分頁面要顯示的資料只是一個擁有幾萬條記錄的資料庫表的其中10條,每次DataGrid進行分頁時還是要從該表中取出所有的記錄。



很多人已經意識到了這個問題,並提出瞭解決方法:用自訂分頁,每次只從資料庫中取出要顯示的資料。這樣,我們需要在SQL語句上下功夫了。由於Access不支援真正的預存程序,在編寫分頁演算法上就沒有SQL Server那麼自由了。SQL Server可以在預存程序中利用暫存資料表來實現高效率的分頁演算法,受到了廣泛的採用。而對於Access,我們必須想辦法在一條SQL語句內實現最高效的演算法。



用一條SQL語句取得某段資料的方法有好幾種。演算法不同,效率也就不同。我經過粗略的測試,發現效率最差的SQL語句執行時耗費的時間大概是效率最高的SQL語句的3倍!而且這個數值會隨著記錄總數的增加而增加。下面將介紹其中兩條常用的SQL語句。



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



變數
說明
變數
說明

@PageSize
每頁顯示的記錄總數
@MiddleIndex
中間頁的索引

@PageCount
分頁總數
@LastIndex
最後一頁的索引

@RecordCount
資料表的記錄總數
@TableName
資料庫表名稱

@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語句的結果集中的每一個@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出所有的記錄還要低。因此,對於分頁演算法,我們還應該具體情況具體分析,不能一概而論。下面將簡單介紹一下相關概念,如果您對主鍵和索引非常熟悉,可以直接跳過。



有關主鍵和索引的概念



在 ACCESS中,一個表的主鍵(PRIMARY KEY,又稱主索引)必然是唯一索引(UNIQUE INDEX),它的值是不會重複的。除此之外,索引依據索引列的值進行排序,每個索引記錄包含著一個指向它所引用的資料行的指標,這對ORDER BY的執行非常有協助。我們可以利用主鍵這兩個特點來實現對某條記錄的定位,從而快速地取出某個分頁上要顯示的記錄。



舉個例子,假設主鍵欄位為INTEGER型,在資料庫表中,記錄的索引已經按主鍵欄位的值升序排好(預設情況下),那麼主鍵欄位值為“11”的記錄的索引,肯定剛好在值為“12”的記錄的索引前面(假設資料庫表中存在主鍵的值為“12”的記錄)。如果主鍵欄位不具備UNIQUE約束,資料庫表中將有可能存在兩個或兩個以上主鍵欄位的值為“11”的記錄,這樣就無法確定這些記錄之間的前後位置了。



下面就讓我們看看如何利用主鍵來進行資料的分段查詢吧。



快速分頁法的原理



其實該分頁法是從其他方法衍生而來的。本人對原來的方法認真地分析,發現通過最佳化和改進可以非常有效地提高它的效率。原演算法本身效率很高,但缺乏對具體問題的具體分析。同一個分頁演算法,可能在取第一頁的資料時效率非常高,但是在取最後一頁的資料時可能反而效率更低。



經過分析,我們可以把分頁演算法的效率狀態分為四種情況:

(1)@PageIndex <= @FirstIndex

(2)@FirstIndex < @PageIndex <= @MiddleIndex

(3)@MiddleIndex < @PageIndex < @LastIndex

(4)@PageIndex >= @LastIndex



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



下面是一個資料庫表,左邊第一列是虛擬,不屬於該資料庫表結構的一部分,它表示相應記錄所在的分頁索引。該表將用於接下來的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



取第一頁的資料是再簡單不過了,我們只要用TOP @PageSize就可以取出第一頁要顯示的記錄了。



SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey ASC




(2)@FirstIndex < @PageIndex <= @MiddleIndex



把取資料表前半部分記錄和取後半部分記錄的SQL語句分開寫,可以有效地改善效能。後面我再詳細解釋這個原因。現在看看取前半部分記錄的SQL語句。先取出當前頁之前的所有記錄的主索引值,再從中選出最大值,然後取出主索引值大於該最大值的前@PageSize條記錄。這裡@PrimaryKey的資料類型可以不是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



接下來看看取資料庫表中後半部分記錄的SQL語句。該語句跟前面的語句演算法的原理是一樣的,只是方法稍微不同。先取出當前頁之後的所有記錄的主索引值,再從中選出最小值,然後取出主索引值小於該最小值的前@PageSize條記錄。



SELECT * FROM (

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @PrimaryKey < (

SELECT MIN(@PrimaryKey) FROM (

SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey DESC

) TableA

) WHERE @Condition

ORDER BY @PrimaryKey DESC

) TableB

ORDER BY @PrimaryKey ASC




之所以把取資料表前半部分記錄和取後半部分記錄的SQL語句分開寫,是因為使用取前半部分記錄的SQL語句時,當前頁前面的記錄數目隨頁數遞增,而我們還要從這些記錄中取出它們的主鍵欄位的值再從中選出最大值。這樣一來,分頁速度將隨著頁數的增加而減慢。因此我沒有這樣做,而是在當前頁索引大於中間頁索引時(@MiddleIndex < @PageIndex)選用了分頁速度隨著頁數的增加而加快的演算法。由此可見,假設把所有分頁面劃分為前面、中間和後面三部分,則最前面和最後面的分頁速度最快,最中間的分頁速度最慢。



例如:@PageIndex=3,紅 --> 黃 --> 藍







(4)@PageIndex >= @LastIndex



取最後一頁的記錄可以簡單地使用類似狀態(1)的做法:



SELECT * FROM (

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey DESC

) TableA ORDER BY @PrimaryKey ASC




不過,這樣產生的最後一頁不一定是實際意義上的最後一頁。因為最後一頁的記錄數未必剛好跟@PageSize相等,而上面的SQL語句是直接取得倒數的@PageSize條記錄。如果想要精確地取得最後一頁的記錄,應該在先計算出該頁的記錄數,作為TOP語句的條件:



SELECT * FROM (

SELECT TOP (@RecordCount-@PageSize*@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 (@RecordCount-@PageSize*(@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 (@RecordCount-@PageSize*@LastIndex) @QueryFields

FROM @TableName WHERE @Condition ORDER BY @PrimaryKey ASC

) TableA ORDER BY @PrimaryKey DESC




如何動態產生上述的SQL語句?



看了上面的SQL語句之後,相信大家已經基本明白該分頁法的原理了。下面,我們將要設計一個動態產生SQL語句的類FastPaging。該類有一個公有靜態方法,它根據您給出的條件動態產生SQL語句,作為方法的傳回值。



// 產生根據指定欄位排序並分頁查詢的 SELECT 語句。

public static String Paging(

int pageSize, //每頁要顯示的記錄的數目。

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

int recordCount, //資料表中的記錄總數。

String tableName, //要查詢的資料表。

String queryFields, //要查詢的欄位。

String primaryKey, //主鍵欄位。

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

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

) {

StringBuilder sb = new StringBuilder();

int pageCount = GetPageCount(recordCount,pageSize); //分頁的總數

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方法還有另外幾個方法:



// 根據記錄總數和分頁大小計算分頁數。

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工作起來



有了上面的類,實現分頁的工作就簡單多了。首先,我們要將DataGrid的AllowPaging屬性和AllowCustomPaging屬性為True,除此之外,為了體現出升序和降序的功能,還需要將AllowSorting屬性也設定為True。然後在每次分頁時,我們需要產生一個OleDbDataReader對象或DataView對象綁定到DataGrid,作為DataGrid的資料來源。這裡需要用FastPaging類的Paging方法根據條件產生一個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應該設定為資料庫表中的記錄總數。DataGrid通過它和PageSize屬性可以虛擬出DataGrid的分頁數。VirtualItemCount的值是在Page的Load事件處理常式中被設定的,而該值的大小需要經過一次資料庫訪問才能得到。為了提高效能,可以只在第一次載入頁面的時候設定該值。



總結



DataGrid基於Access的快速分頁法到這裡就介紹完了。當然,這種方法並不能“包治百病”,可能對於您的要實現的功能,還有其它更好的方法。這就需要大家在平時工作和學習中不斷總結經驗,在解決實際問題時儘可能找到最有效方法。這也是本文的方法中所貫穿的思想。



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。