大数量查询分页显示 微软的解决办法

     微软的解决办法
  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Drawing;
  using System.Windows.Forms;
  
  public class PagingSample: Form
  {
  // Form controls.
  Button prevBtn = new Button();
  Button nextBtn = new Button();
  
  static DataGrid myGrid = new DataGrid();
  static Label pageLbl = new Label();
  
  // Paging variables.
  static int pageSize = 10; // Size of viewed page.
  static int totalPages = 0; // Total pages.
  static int currentPage = 0; // Current page.
  static string firstVisibleCustomer = ""; // First customer on page to determine location for move previous.
  static string lastVisibleCustomer = ""; // Last customer on page to determine location for move next.
  
  // DataSet to bind to DataGrid.
  static DataTable custTable;
  
  // Initialize connection to database and DataAdapter.
  static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
  static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
  static SqlCommand selCmd = custDA.SelectCommand;
  
  public static void GetData(string direction)
  {
  // Create SQL statement to return a page of records.
  selCmd.Parameters.Clear();
  
  switch (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;
  break;
  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;
  break;
  default:
  selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
  
  // Determine total pages.
  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;
  }
  
  // Fill a temporary table with query results.
  DataTable tmpTable = new DataTable("Customers");
  int recordsAffected = custDA.Fill(tmpTable);
  
  // If table does not exist, create it.
  if (custTable == null)
  custTable = tmpTable.Clone();
  
  // Refresh table if at least one record returned.
  if (recordsAffected > 0)
  {
  switch (direction)
  {
  case "Next":
  currentPage++;
  break;
  case "Previous":
  currentPage--;
  break;
  default:
  currentPage = 1;
  break;
  }
  
  pageLbl.Text = "Page " + currentPage + " of " + totalPages;
  
  // Clear rows and add new results.
  custTable.Rows.Clear();
  
  foreach (DataRow myRow in tmpTable.Rows)
  custTable.ImportRow(myRow);
  
  // Preserve first and last primary key values.
  DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
  firstVisibleCustomer = ordRows[0][0].ToString();
  lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
  }
  }
  
  
  
  public PagingSample()
  {
  // Initialize controls and add to form.
  this.ClientSize = new Size(360, 274);
  this.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);
  prevBtn.Click += new EventHandler(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);
  
  this.Controls.Add(myGrid);
  this.Controls.Add(prevBtn);
  this.Controls.Add(nextBtn);
  this.Controls.Add(pageLbl);
  nextBtn.Click += new EventHandler(Next_OnClick);
  
  
  // Populate DataSet with first page of records and bind to grid.
  GetData("Default");
  DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
  myGrid.SetDataBinding(custDV, "");
  }
  
  
  
  public static void Prev_OnClick(object sender, EventArgs args)
  {
  GetData("Previous");
  }
  
  public static void Next_OnClick(object sender, EventArgs args)
  {
  GetData("Next");
  }
  }
  
  
  
  public class Sample
  {
  static void Main()
  {
  Application.Run(new PagingSample());
  }
  }
  
  
  
  
  
  
  

原文转自：http://www.ltesting.net