VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > C#教程 >
  • c#通过查询结果进行分页

制作者:剑锋冷月 单位:无忧统计网,www.51stat.net
 

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

  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 += 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 = ""; // 当前页的第一条记录,用来进行移动“前一页”的定位。
  static string lastVisibleCustomer = ""; //当前页的最后条记录,用来进行移动“下一页”的定位。
 
  // 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());
  }
 }
 [VB.NET]
 
 Imports System
 Imports System.Data
 Imports System.Data.SqlClient
 Imports System.Drawing
 Imports System.Windows.Forms
 
 Public Class PagingSample
  Inherits Form
 
  ' Form controls.
  Dim prevBtn As Button = New Button()
  Dim nextBtn As Button = New Button()
 
  Shared myGrid As DataGrid = New DataGrid()
  Shared pageLbl As Label = New Label()
 
  ' Paging variables.
  Shared pageSize As Integer = 10 ' Size of viewed page.
  Shared totalPages As Integer = 0 ' Total pages.
  Shared currentPage As Integer = 0 ' Current page.
  Shared firstVisibleCustomer As String = "" ' First customer on page to determine location for move previous.
  Shared lastVisibleCustomer As String = "" ' Last customer on page to determine location for move next.
 
  ' DataSet to bind to DataGrid.
  Shared custTable As DataTable
 
  ' Initialize connection to database and DataAdapter.
  Shared nwindConn As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind")
  Shared custDA As SqlDataAdapter = New SqlDataAdapter("", nwindConn)
  Shared selCmd As SqlCommand = custDA.SelectCommand()
 
 
  Public Shared Sub GetData(direction As String)
 
  ' Create SQL statement to return a page of records.
  selCmd.Parameters.Clear()
 
  Select Case direction
  Case "Next"
  selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
  "WHERE CustomerID > @CustomerId ORDER BY CustomerID"
  selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer
  Case "Previous"
  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
  Case Else
  selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers ORDER BY CustomerID"
 
  ' Determine total pages.
  Dim totCMD As SqlCommand = New SqlCommand("SELECT Count(*) FROM Customers", nwindConn)
  nwindConn.Open()
  Dim totalRecords As Integer = CInt(totCMD.ExecuteScalar())
  nwindConn.Close()
  totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize))
  End Select
 
  ' Fill a temporary table with query results.
  Dim tmpTable As DataTable = New DataTable("Customers")
  Dim recordsAffected As Integer = custDA.Fill(tmpTable)
 
  ' If table does not exist, create it.
  If custTable Is Nothing Then custTable = tmpTable.Clone()
 
  ' Refresh table if at least one record returned.
  If recordsAffected > 0 Then
  Select Case direction
  Case "Next"
  currentPage += 1
  Case "Previous"
  currentPage += -1
  Case Else
  currentPage = 1
  End Select
 
  pageLbl.Text = "Page " & currentPage & " of " & totalPages
 
  ' Clear rows and add New results.
  custTable.Rows.Clear()
 
  Dim myRow As DataRow
  For Each myRow In tmpTable.Rows
  custTable.ImportRow(myRow)
  Next
 
  ' Preserve first and last primary key values.
  Dim ordRows() As DataRow = custTable.Select("", "CustomerID ASC")
  firstVisibleCustomer = ordRows(0)(0).ToString()
  lastVisibleCustomer = ordRows(custTable.Rows.Count - 1)(0).ToString()
  End If
  End Sub
 
 
  Public Sub New()
  MyBase.New
 
  ' Initialize controls and add to form.
  Me.ClientSize = New Size(360, 274)
  Me.Text = "NorthWind Data"
 
  myGrid.Location = New Point(10,10)
  myGrid.Size = New Size(340, 220)
  myGrid.AllowSorting = true
  myGrid.CaptionText = "NorthWind Customers"
  myGrid.ReadOnly = true
  myGrid.AllowNavigation = false
  myGrid.PreferredColumnWidth = 150
 
  prevBtn.Text = "<<"
  prevBtn.Size = New Size(48, 24)
  prevBtn.Location = New Point(92, 240)
  AddHandler prevBtn.Click, New EventHandler(AddressOf Prev_OnClick)
 
  nextBtn.Text = ">>"
  nextBtn.Size = New Size(48, 24)
  nextBtn.Location = New Point(160, 240)
 
  pageLbl.Text = "No Records Returned."
  pageLbl.Size = New Size(130, 16)
  pageLbl.Location = New Point(218, 244)
 
  Me.Controls.Add(myGrid)
  Me.Controls.Add(prevBtn)
  Me.Controls.Add(nextBtn)
  Me.Controls.Add(pageLbl)
  AddHandler nextBtn.Click, New EventHandler(AddressOf Next_OnClick)
 
 
  ' Populate DataSet with first page of records and bind to grid.
  GetData("Default")
  Dim custDV As DataView = New DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows)
  myGrid.SetDataBinding(custDV, "")
  End Sub
  
  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 Sub
 End Class
 
 
 Public Class Sample
  Shared Sub Main()
  Application.Run(New PagingSample())
  End Sub
 End Class



相关教程