Winform datagridview 大数量查询分页显示 微软的解决办法（修改版）

 【转自】 http://www.zmke.com/article/182/184/donet/2009/2009070819781.html

 

增加了 首页和末页的显示，直接上代码，只要本地安装了sqlserver2000 就可以直接用。

 using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsApplication1
{
    
    public partial class Form1:Form
    {
        // WinForm上的控件
        Button prevBtn = new Button();
        Button nextBtn = new Button();
        Button firstBtn = new Button();
        Button lastBtn = new Button();
        static DataGrid myGrid = new DataGrid();
        static Label pageLbl = new Label();
        // 分页的变量
        static int pageSize = 4; // 每页显示多少
        static int leftpageSiz; // 分页余数
        static int totalPages = 0; // 总共页数
        static int currentPage = 0; // 当前页数.
        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 Form1()
        {
            InitializeComponent();

            // Initialize controls and add to form.
            this.ClientSize = new Size(360, 274);
            this.Text = "NorthWind Data";
            myGrid.Size = new System.Drawing.Size(729, 240);
            myGrid.Dock=System.Windows.Forms.DockStyle.Top;
            myGrid.AllowSorting = true;
            myGrid.CaptionText = "NorthWind Customers";
            myGrid.ReadOnly = true;
            myGrid.AllowNavigation = false;
            myGrid.PreferredColumnWidth = 150;

            firstBtn.Text = "First";
            firstBtn.Size = new Size(48, 24);
            firstBtn.Location = new Point(22, 240);
            firstBtn.Click += new EventHandler(First_OnClick);

            prevBtn.Text = "Prev";
            prevBtn.Size = new Size(48, 24);
            prevBtn.Location = new Point(92, 240);
            prevBtn.Click += new EventHandler(Prev_OnClick);

            nextBtn.Text = "Next";
            nextBtn.Size = new Size(48, 24);
            nextBtn.Location = new Point(160, 240);
            nextBtn.Click += new EventHandler(Next_OnClick);

            lastBtn.Text = "Last";
            lastBtn.Size = new Size(48, 24);
            lastBtn.Location = new Point(230, 240);
            lastBtn.Click += new EventHandler(Last_OnClick);

            pageLbl.Text = "没有记录";
            pageLbl.Size = new Size(130, 16);
            pageLbl.Location = new Point(300, 244);
            this.Controls.Add(myGrid);
            this.Controls.Add(prevBtn);
            this.Controls.Add(firstBtn);
            this.Controls.Add(nextBtn);
            this.Controls.Add(lastBtn);
            this.Controls.Add(pageLbl);
          
            // 获取第一页数据
            GetData("Default");
            DataView custDV = new DataView(custTable, "", "ID", DataViewRowState.CurrentRows);
            myGrid.SetDataBinding(custDV, "");
 
        }
        public static void First_OnClick(object sender, EventArgs args)
        {
            GetData("First");
        }
        public static void Prev_OnClick(object sender, EventArgs args)
        {
            GetData("Previous");
        }
        public static void Next_OnClick(object sender, EventArgs args)
        {
            GetData("Next");
        }
        public static void Last_OnClick(object sender, EventArgs args)
        {
            GetData("Last");
        }
        private void Form1_Load(object sender, EventArgs e)
        {
           
        }
        public static void GetData(string direction)
        {
            // Create SQL statement to return a page of records.
            selCmd.Parameters.Clear();
            switch (direction)
            {
                case "First":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ";
                    break;

                case "Next":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
                    "WHERE ID > @ID ORDER BY ID";
                    selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
                    break;

                case "Previous":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
                    "WHERE ID < @ID ORDER BY ID DESC";
                    selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
                    break;

                case "Last":
                    selCmd.CommandText = "SELECT TOP " + leftpageSiz + " * FROM Customers ORDER BY ID DESC";
                    break;

                default:
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ORDER BY ID";
                    // 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);
                    if ((totalRecords % pageSize)==0)
                    {
                        leftpageSiz = pageSize;
                    }
                    else
                    {
                        leftpageSiz = 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 "First":
                        currentPage=1;
                        break;
                    case "Next":
                        currentPage++;
                        break;
                    case "Previous":
                        currentPage--;
                        break;
                    case "Last":
                        currentPage = totalPages;
                        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("", "ID ASC");
                firstVisibleCustomer = ordRows[0][0].ToString();
                lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
            }
        }    
    }
}

本文章来源于西盟软件站【www.zmke.com】详细地址：http://www.zmke.com/article/182/184/donet/2009/2009070819781.html