Question

如何在WinForm中对DataGrid进行分页显示

Answer 1

首先，需要定义一个数据库存储过程，用来获得指定页的数据记录，大致的数据结构和存储过程如下（在SQL Server 2000下）：字段名类型备注EmployeeIDInt自增字段，主键EmployeeNameVarchar(20) SalaryInt CellPhoneVarchar(20) EmailAddressVarchar(20) 存储过程为：CREATE PROCEDURE GetEmployees @EmployeeNum int, @StartNO int, @@TotalCount INT OUTPUT, @@RealNum INT OUTPUT AS-- Get employees from DB through the specific number and the start positionDECLARE @PreRecCount VARCHAR( 10 )DECLARE @CurRecCount VARCHAR( 10 ) SELECT @@TotalCount = COUNT(*) FROM EmployeeInfo IF @@TotalCount > ( @StartNO + 1 ) * @EmployeeNum SET @@RealNum = @EmployeeNumELSE SET @@RealNum = @@TotalCount - @StartNO * @EmployeeNum -- Get employees by the computed numberSET @CurRecCount = CAST( @StartNO * @EmployeeNum + @@RealNum AS VARCHAR( 10 ) )IF @STARTNO = 0 EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo ORDER BY EmployeeID ASC' )ELSE BEGIN SET @PreRecCount = CAST( @StartNO * @EmployeeNumAS VARCHAR( 10 ) ) EXEC( 'SELECT TOP ' + @CurRecCount + ' * FROM EmployeeInfo WHERE EmployeeID NOT IN ' + '(SELECT TOP ' + @PreRecCount + ' EmployeeID FROM EmployeeInfo ORDER BY EmployeeID ASC) ' + 'ORDER BY EmployeeID ASC' ) ENDGO 然后就是调用存储过程来进行显示，比较完整的源代码如下：//------------------------ Multi Page Demo ------------------------------------//-----------------------------------------------------------------------------//---File:frmMultiPagesDemo.cs//---Description:The main form file to show how to use pages in datagrid //---Author:Knight//---Date:Mar.23, 2006//-----------------------------------------------------------------------------//-----------------------{ Multi Page Demo }----------------------------------- using System;using System.Drawing;using System.Collections;using System.ComponentModel;using System.Windows.Forms;using System.Data;using System.Data.SqlClient; namespace CSDataGrid{ /// /// Summary description for frmMultiPages. /// publicclass frmMultiPages : System.Windows.Forms.Form { private System.Windows.Forms.Label lblPageInfo; private System.Windows.Forms.Button btnPrevious; private System.Windows.Forms.Button btnNext; protected SqlConnection sqlConn = new SqlConnection(); protected SqlDataAdapter sqlDAdapter = null; protected DataSet sqlRecordSet = null; privateint nCurPageNum = 0; privateconstint REC_NUM_PER_PAGE = 3; privateint nTotalCount; privateint nRealNum; privateint nTotalPage; private System.Windows.Forms.DataGrid dtgUserInfo; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; public frmMultiPages() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// /// Clean up any resources being used. /// protectedoverridevoid Dispose( bool disposing ) { if( disposing ) { if(components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// privatevoid InitializeComponent() { this.dtgUserInfo = new System.Windows.Forms.DataGrid(); this.lblPageInfo = new System.Windows.Forms.Label(); this.btnPrevious = new System.Windows.Forms.Button(); this.btnNext = new System.Windows.Forms.Button(); ((System.ComponentModel.ISupportInitialize)(this.dtgUserInfo)).BeginInit(); this.SuspendLayout(); // // dtgUserInfo // this.dtgUserInfo.DataMember = ""; this.dtgUserInfo.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dtgUserInfo.Location = new System.Drawing.Point(16, 16); this.dtgUserInfo.Name = "dtgUserInfo"; this.dtgUserInfo.Size = new System.Drawing.Size(552, 416); this.dtgUserInfo.TabIndex = 0; // // lblPageInfo // this.lblPageInfo.AutoSize = true; this.lblPageInfo.Location = new System.Drawing.Point(16, 440); this.lblPageInfo.Name = "lblPageInfo"; this.lblPageInfo.Size = new System.Drawing.Size(83, 16); this.lblPageInfo.TabIndex = 1; this.lblPageInfo.Text = "{0} of {1} Pages"; // // btnPrevious // this.btnPrevious.Location = new System.Drawing.Point(408, 440); this.btnPrevious.Name = "btnPrevious"; this.btnPrevious.TabIndex = 2; this.btnPrevious.Text = "Previous"; this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click); // // btnNext // this.btnNext.Location = new System.Drawing.Point(488, 440); this.btnNext.Name = "btnNext"; this.btnNext.TabIndex = 3; this.btnNext.Text = "Next"; this.btnNext.Click += new System.EventHandler(this.btnNext_Click); // // frmMultiPages // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(584, 469); this.Controls.Add(this.btnNext); this.Controls.Add(this.btnPrevious); this.Controls.Add(this.lblPageInfo); this.Controls.Add(this.dtgUserInfo); this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle; this.MaximizeBox = false; this.Name = "frmMultiPages"; this.Text = "Multi Pages In DataGrid"; this.Load += new System.EventHandler(this.frmMultiPages_Load); ((System.ComponentModel.ISupportInitialize)(this.dtgUserInfo)).EndInit(); this.ResumeLayout(false); } #endregion privatevoid frmMultiPages_Load(object sender, System.EventArgs e) { //Set connection string sqlConn.ConnectionString = yourconnectionstring; //Connect to DB if( ConnectDB() ) { //Bind data to datagrid BindData(); } } /// /// Connect to DB /// ///If connected, return True; else return False privatebool ConnectDB() { //Check current connection's state try { if( sqlConn.State == ConnectionState.Closed || sqlConn.State == ConnectionState.Broken ) { //Connection is not available sqlConn.Close(); } else //Connection is available returntrue; } catch{}; //Re-connect try { sqlConn.Open(); } catch(SqlException e) { //Sql's exception MessageBox.Show( e.Message ); } catch(Exception e) { //Other exception MessageBox.Show( e.Message ); } if( sqlConn.State == ConnectionState.Closed || sqlConn.State == ConnectionState.Broken ) //Connection is not available returnfalse; else //Connection is available returntrue; } privatevoid AddDGStyle() { DataGridTableStyle ts1 = new DataGridTableStyle(); //specify the table from dataset (required step) ts1.MappingName = "EmployeeInfo"; PropertyDescriptorCollection pdc = this.BindingContext [sqlRecordSet, "EmployeeInfo"].GetItemProperties(); DataGridColumnStyle TextCol = new DataGridTextBoxColumn( pdc["EmployeeID"], "i" ); TextCol.MappingName = "EmployeeID"; TextCol.HeaderText = "EmployeeID"; TextCol.Width = 0; TextCol.ReadOnly = true; ts1.GridColumnStyles.Add(TextCol); TextCol = new DataGridTextBoxColumn(); TextCol.MappingName = "EmployeeName"; TextCol.HeaderText = "Employee Name"; TextCol.Width = 100; ts1.GridColumnStyles.Add(TextCol); TextCol = new DataGridTextBoxColumn( pdc["Salary"], "i" ); TextCol.MappingName = "Salary"; TextCol.HeaderText = "Salary"; TextCol.Width = 80; ts1.GridColumnStyles.Add(TextCol); TextCol = new DataGridTextBoxColumn(); TextCol.MappingName = "CellPhone"; TextCol.HeaderText = "Cell Phone"; TextCol.Width = 80; ts1.GridColumnStyles.Add(TextCol); TextCol = new DataGridTextBoxColumn(); TextCol.MappingName = "EmailAddress"; TextCol.HeaderText = "Email Address"; TextCol.Width = 100; ts1.GridColumnStyles.Add(TextCol); dtgUserInfo.TableStyles.Add(ts1); } privatevoid GetEmployeeData() { sqlDAdapter = new SqlDataAdapter( ); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; &nb