分页处理大量数据microsoft解决方案

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());
 }
}