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

       在网上大多数对DataGrid进行分页的例子都是Asp.net环境下的,其实在WinForm中也很简单。以下就是简单举一个例子。

 

       首先,需要定义一个数据库存储过程,用来获得指定页的数据记录,大致的数据结构和存储过程如下(在SQL Server 2000下):

字段名

类型

备注

EmployeeID

Int

自增字段,主键

EmployeeName

Varchar(20)

 

Salary

Int

 

CellPhone

Varchar(20)

 

EmailAddress

Varchar(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 position

DECLARE @PreRecCount VARCHAR( 10 )

DECLARE @CurRecCount VARCHAR( 10 )

 

SELECT @@TotalCount = COUNT(*) FROM EmployeeInfo

 

IF @@TotalCount > ( @StartNO + 1 ) * @EmployeeNum

    SET @@RealNum = @EmployeeNum

ELSE

    SET @@RealNum = @@TotalCount - @StartNO * @EmployeeNum

 

-- Get employees by the computed number

SET @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 * @ EmployeeNum AS 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'

                   )

   END

GO

 

然后就是调用存储过程来进行显示,比较完整的源代码如下:

//------------------------ 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>

    /// Summary description for frmMultiPages.

    /// </summary>

    public class 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;

 

        private int nCurPageNum = 0;

        private const int REC_NUM_PER_PAGE = 3;

        private int nTotalCount;

        private int nRealNum;

        private int nTotalPage;

 

        private System.Windows.Forms.DataGrid dtgUserInfo;

 

        /// <summary>

        /// Required designer variable.

        /// </summary>

        private System.ComponentModel.Container components = null;

 

        public frmMultiPages()

        {

            //

            // Required for Windows Form Designer support

            //

            InitializeComponent();

 

            //

            // TODO: Add any constructor code after InitializeComponent call

            //

        }

 

        /// <summary>

        /// Clean up any resources being used.

        /// </summary>

        protected override void Dispose( bool disposing )

        {

            if( disposing )

            {

                if(components != null)

                {

                    components.Dispose();

                }

            }

            base.Dispose( disposing );

        }

 

        #region Windows Form Designer generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void 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

 

        private void frmMultiPages_Load(object sender, System.EventArgs e)

        {

            //Set connection string

            sqlConn.ConnectionString = yourconnectionstring;

 

            //Connect to DB

            if( ConnectDB() )

            {

                //Bind data to datagrid

                BindData();

            }

       

        }

 

        /// <summary>

        /// Connect to DB

        /// </summary>

        /// <returns>If connected, return True; else return False</returns>

        private bool 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

                    return true;

            }

            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

                return false;

            else

                //Connection is available

                return true;

        }

 

        private void 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);

 

        }

 

        private void GetEmployeeData()

        {

            sqlDAdapter = new SqlDataAdapter( );

            SqlCommand sqlComm = new SqlCommand();

            sqlComm.Connection = sqlConn;

 

            sqlComm.CommandText = "GetEmployees";

            sqlComm.CommandType = CommandType.StoredProcedure;

            sqlComm.Parameters.Add ( "@EmployeeNum", REC_NUM_PER_PAGE );

            sqlComm.Parameters.Add ( "@StartNO", nCurPageNum );

            SqlParameter sqlPar = sqlComm.Parameters.Add ( "@@TotalCount", SqlDbType.Int );

            sqlPar.Direction = ParameterDirection.Output;

            sqlPar = sqlComm.Parameters.Add ( "@@RealNum", SqlDbType.Int );

            sqlPar.Direction = ParameterDirection.Output;

 

            sqlDAdapter.SelectCommand = sqlComm;

 

            //Fill dataset

            sqlRecordSet = new DataSet();

            sqlDAdapter.Fill( sqlRecordSet, "EmployeeInfo" );

            nTotalCount = Convert.ToInt32( sqlComm.Parameters["@@TotalCount"].Value.ToString() );

            nRealNum = Convert.ToInt32( sqlComm.Parameters["@@RealNum"].Value.ToString() );

           

            sqlComm.Dispose();

 

            nTotalPage = nTotalCount - nTotalCount % REC_NUM_PER_PAGE;

            nTotalPage /= REC_NUM_PER_PAGE;

            nTotalPage++;

 

            lblPageInfo.Text = string.Format( "{0} of {1} pages", nCurPageNum + 1, nTotalPage );

            if( nCurPageNum + 1 >= nTotalPage )

                btnNext.Enabled = false;

            else

                btnNext.Enabled = true;

 

            if( nCurPageNum == 0 )

                btnPrevious.Enabled = false;

            else

                btnPrevious.Enabled = true;

               

        }

 

        private void BindData()

        {

            GetEmployeeData();

 

            //Bind datagrid with dataset

            dtgUserInfo.SetDataBinding( sqlRecordSet, "EmployeeInfo");

 

            //Add datagrid style

            AddDGStyle();

        }

 

        private void ReBindData()

        {

            GetEmployeeData();

 

            //Bind datagrid with dataset

            dtgUserInfo.SetDataBinding( sqlRecordSet, "EmployeeInfo");

        }

 

        private void btnPrevious_Click(object sender, System.EventArgs e)

        {

            nCurPageNum--;

            sqlRecordSet.Tables["EmployeeInfo"].Rows.Clear();

            ReBindData();

        }

 

        private void btnNext_Click(object sender, System.EventArgs e)

        {

            nCurPageNum++;

            sqlRecordSet.Tables["EmployeeInfo"].Rows.Clear();

            ReBindData();

        }

 

    }

}

 

评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值