通过查询结果进行分页(C#)_成都软件开发公司

通过查询结果进行分页(C#)

作者：xiaoxiao
发表时间：2020-12-23 10:37
来源：未知

作者：孟宪会出自：【孟宪会之精彩世界】发布日期：2003年7月7日 6点57分19秒

通过查询结果进行分页就是以结果集的子集处理查询结果的过程，这样，每次返回给用户的只是当前页面的数据大小。

DataAdapter对象通过重载Fill方法提供了返回当前页面数据的功能。然而，这种方法对大数据量的查询结果并不是最好的选择，这是因为：当DataAdapter用请求的结果填充DataTable或者DataSet时，数据库返回的资源仍是全部的查询结果，只是在返回时附加了额外的限定条件才返回了少量的记录集的。

要使用Fill方法返回当前一页的记录，需要指定开始记录startRecord，和当前页的最大记录数maxRecords。

下面的例子用来返回一页为5条记录的第一页的查询结果：

[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从句即可。例如：

[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，我们必须象下面那样：

[C#] currentIndex += pageSize; myDS.Tables["Orders"].Rows.Clear(); myDA.Fill(myDS, currentIndex, pageSize, "Orders");

下面是完整的代码：

[C#]

using System; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms; public class PagingSample: Form { // Form 控件. Button prevBtn = new Button(); Button nextBtn = new Button(); static DataGrid myGrid = new DataGrid(); static Label pageLbl = new Label(); // 分页变量 static int pageSize = 10; // 要显示的页数 static int totalPages = 0; // 总页数 static int currentPage = 0; // 当前页 static string firstVisibleCustomer = ""; // 当前页的第一条记录，用来进行移动&#8220;前一页&#8221;的定位。 static string lastVisibleCustomer = ""; //当前页的最后条记录，用来进行移动&#8220;下一页&#8221;的定位。 // DataSet用来绑定到DataGrid. static DataTable custTable; //初始化连接和DataAdapter. static SqlConnection nwindConn = new SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind"); static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn); static SqlCommand selCmd = custDA.SelectCommand; public static void GetData(string direction) { // 创建返回一页记录的SQL语句 selCmd.Parameters.Clear(); switch (direction) { case "下一页": selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " + "WHERE CustomerID > @CustomerId ORDER BY CustomerID"; selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer; break; case "前一页": selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " + "WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC"; selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer; break; default: selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID"; // 计算总页数 SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn); nwindConn.Open(); int totalRecords = (int)totCMD.ExecuteScalar(); nwindConn.Close(); totalPages = (int)Math.Ceiling((double)totalRecords / pageSize); break; } // 用查询结果填充临时表 DataTable tmpTable = new DataTable("Customers"); int recordsAffected = custDA.Fill(tmpTable); // 如果表不存在，就创建 if (custTable == null) custTable = tmpTable.Clone(); // 如果有记录返回，就刷新表 if (recordsAffected > 0) { switch (direction) { case "下一页": currentPage++; break; case "上一页": currentPage--; break; default: currentPage = 1; break; } pageLbl.Text = "第" + currentPage + "/ " + totalPages + "页"; // 清除行集，添加新记录 custTable.Rows.Clear(); foreach (DataRow myRow in tmpTable.Rows) custTable.ImportRow(myRow); // 保存first 和 last 关键值 DataRow[] ordRows = custTable.Select("", "CustomerID ASC"); firstVisibleCustomer = ordRows[0][0].ToString(); lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString(); } } public PagingSample() { // 初始化控件并添加到Form this.ClientSize = new Size(360, 274); this.Text = "NorthWind 数据表"; myGrid.Location = new Point(10,10); myGrid.Size = new Size(340, 220); myGrid.AllowSorting = true; myGrid.CaptionText = "NorthWind 客户信息"; myGrid.ReadOnly = true; myGrid.AllowNavigation = false; myGrid.PreferredColumnWidth = 150; prevBtn.Text = "前一页"; prevBtn.Size = new Size(60, 24); prevBtn.Location = new Point(50, 240); prevBtn.Click += new EventHandler(Prev_OnClick); nextBtn.Text = "下一页"; nextBtn.Size = new Size(60, 24); nextBtn.Location = new Point(120, 240); pageLbl.Text = "没有记录返回"; pageLbl.Size = new Size(130, 16); pageLbl.Location = new Point(200, 244); this.Controls.Add(myGrid); this.Controls.Add(prevBtn); this.Controls.Add(nextBtn); this.Controls.Add(pageLbl); nextBtn.Click += new EventHandler(Next_OnClick); // 计算默认的第一页，并进行绑定 GetData("Default"); DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows); myGrid.SetDataBinding(custDV, ""); } public static void Prev_OnClick(object sender, EventArgs args) { GetData("前一页"); } public static void Next_OnClick(object sender, EventArgs args) { GetData("下一页"); } } public class Sample { static void Main() { Application.Run(new PagingSample()); } }

Public Shared Sub Prev_OnClick(sender As Object, args As EventArgs) GetData("Previous") End Sub Public Shared Sub Next_OnClick(sender As Object, args As EventArgs) GetData("Next") End SubEnd ClassPublic Class Sample Shared Sub Main() Application.Run(New PagingSample()) End SubEnd Class