通过查询结果进行分页就是以结果集的子集处理查询结果的过程,这样,每次返回给用户的只是当前页面的数据大小。
DataAdapter对象通过重载Fill方法提供了返回当前页面数据的功能。然而,这种方法对大数据量的查询结果并不是最好的选择,这是因为:当DataAdapter用请求的结果填充DataTable或者DataSet时,数据库返回的资源仍是全部的查询结果,只是在返回时附加了额外的限定条件才返回了少量的记录集的。
要使用Fill方法返回当前一页的记录,需要指定开始记录startRecord,和当前页的最大记录数maxRecords。
下面的例子用来返回一页为5条记录的第一页的查询结果:
[VB.NET]
Dim currentIndex As Integer = 0 Dim pageSize As Integer = 5 Dim orderSQL As String = "SELECT * FROM Orders ORDER BY OrderID" Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn) Dim myDS As DataSet = New DataSet() myDA.Fill(myDS, currentIndex, pageSize, "Orders")
[C#]
int currentIndex = 0; int pageSize = 5; string orderSQL = "SELECT * FROM Orders ORDER BY OrderID"; SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn); DataSet myDS = new DataSet(); myDA.Fill(myDS, currentIndex, pageSize, "Orders");
在上面的例子中,DataSet只填充了5条记录,但返回的仍是整个Orders表。如果要达到填充几条返回几天的目的,在SQL语句中使用TOP和WHERE从句即可。例如:
[VB.NET]
Dim pageSize As Integer = 5 Dim orderSQL As String = "SELECT TOP " & pageSize & " * FROM Orders ORDER BY OrderID" Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn) Dim myDS As DataSet = New DataSet() myDA.Fill(myDS, "Orders")
[C#]
int pageSize = 5; string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID"; SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn); DataSet myDS = new DataSet(); myDA.Fill(myDS, "Orders");
此时需要注意的是:用这种方法进行的分页,必须自己维护记录排序的唯一标识,为了向下一页请求传递唯一的ID,我们必须象下面那样:
[VB.NET] Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString() [C#] string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();
下面的代码在Table填充之前进行了清空:
[VB.NET] currentIndex = currentIndex + pageSize myDS.Tables("Orders").Rows.Clear() myDA.Fill(myDS, currentIndex, pageSize, "Orders") [C#] currentIndex += pag