大数量查询分页显示

微软的解决办法usingSystem;usingSystem.Data;usingSyst运维

微软的解决办法

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