WinForm DataGridView分页功能

WinForm 里面的DataGridView不像WebForm里面的GridView那样有自带的分页功能，需要自己写代码来实现分页，效果如下图：

.CS:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace allenPageTest
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            BindDataWithPage(1);
        }

        //总记录数
        public int RecordCount = 0;
        private string strConn = @"server=.;database=test;uid=sa;pwd=1234";         
        private string strProcedure = "PageTest ";


        /// <summary>
        /// 绑定第Index页的数据
        /// </summary>
        /// <param name="Index"></param>
        private void BindDataWithPage(int Index)
        {
            allenPage1.PageIndex = Index;
            //winFormPager1.PageSize = 10;
            DataTable dt = GetData(strConn, strProcedure, Index, allenPage1.PageSize);

            dataGridView1.DataSource = dt;

            //获取并设置总记录数
            allenPage1.RecordCount = RecordCount;
        }


        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="strProcedure">存储过程名称</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每一页显示的行数</param>
        /// <returns></returns>
        private DataTable GetData(string conn, string strProcedure, int pageIndex, int pageSize)
        {

            using (SqlConnection connection = new SqlConnection(conn))
            {
                SqlCommand command = new SqlCommand(strProcedure, connection);
                command.CommandType = CommandType.StoredProcedure;//采用存储过程                 
                command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = "TableName";//对应的数据表名
                command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "Index";//主键ID
                command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "*";//要查询的字段，*为全部字段
                command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = " 1=1 ";//查询条件
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();//当前页码
                command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();//每一页显示的行数
                command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = " Column Name asc";//排序的字段
                //打开连接
                if (connection.State != ConnectionState.Open) 
                { 
                    connection.Open(); 
                }
                try
                {
                    //填充数据
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    //获取总记录数
                    RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                    //返回数据集
                    return ds.Tables[0];

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                    return null; ;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private void allenPage1_PageIndexChanged(object sender, EventArgs e)
        {
            BindDataWithPage(allenPage1.PageIndex);
        }
    }
}

存储过程：

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PageTest')
DROP PROC PageTest
GO
CREATE PROCEDURE [dbo].[PageTest]
    @Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
    @TIndex NVARCHAR(100),    --主键 
    @Column NVARCHAR(2000) = '*',--要查询的字段，全部字段就为*
    @Sql NVARCHAR(3000) = '',--Where条件
    @PageIndex INT = 1,    --开始页码
    @PageSize INT = 10,        --每页查询数据的行数
    @Sort NVARCHAR(200) = '' --排序的字段

AS

DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
  BEGIN
   SET @strWhere = ' WHERE ' + @Sql + ' '
  END
ELSE
  BEGIN
   SET @strWhere = ''
  END
        
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
    IF(@Sort='')
        SET @Sort = @TIndex + ' DESC '
    ELSE
        SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
  SET @PageIndex = 1

        IF @PageIndex = 1  
        BEGIN 
          SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ '  FROM ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort
        END 
        ELSE
          BEGIN
         
            DECLARE @START_ID NVARCHAR(50)
            DECLARE @END_ID NVARCHAR(50)
            SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
            SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
            SET @strsql =  ' SELECT '+@Column+ '
           FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum, 
             '+@Column+ '
              FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D
           WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
          END
EXEC(@strsql)
PRINT @strsql
    SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere  
PRINT @strsql
EXEC(@strsql)

另一种存储过程写法：

CREATE PROCEDURE pro_DataPageRowNumber
@SQL nvarchar(2000),--主句
@Order nvarchar(20),--排序
@PageIndex int,--当前页
@PageSize int,--每页显示数
@TotalRow int output--记录总数
AS
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)--主句
    declare @startRow as int--开始行
    set @startRow=(@PageIndex-1)*@PageSize+1
    declare @lastRow int--结束行
    set @lastRow=@PageSize*@PageIndex
    declare @RowNumber nvarchar(100)
    set @RowNumber=',Row_NUMBER() OVER(ORDER BY '+@Order+') as RowNumber from '
    set @SQL=Replace(@SQL,' from ',@RowNumber)   
    set @ExceSQL='select @TotalRow=max(RowNumber) from ('+@SQL+') as tmp'    
    execute  sp_executesql @ExceSQl,N'@TotalRow in output',@TotalRow output
    set @ExceSQL='select * from('+@SQL+') as tmp where RowNumber  between'+ Convert(nvarchar,@startRow)
    +' and '+Convert(nvarchar,@lastRow)
    execute(@ExceSQL)

GO

 

转载于:https://www.cnblogs.com/qtiger/p/5692558.html