WinForm DataGridView分页功能

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

.CS:

  1 using System;

  2 using System.Collections.Generic;

  3 using System.ComponentModel;

  4 using System.Data;

  5 using System.Drawing;

  6 using System.Linq;

  7 using System.Text;

  8 using System.Windows.Forms;

  9 using System.Data.SqlClient;

 10

 11 namespace allenPageTest

 12 {

 13     public partial class Form2 : Form

 14     {

 15         public Form2()

 16         {

 17             InitializeComponent();

 18         }

 19

 20         private void Form2_Load(object sender, EventArgs e)

 21         {

 22             BindDataWithPage(1);

 23         }

 24

 25         //总记录数

 26         public int RecordCount = 0;

 27         private string strConn = @"server=.;database=test;uid=sa;pwd=1234";

 28         private string strProcedure = "PageTest ";

 29

 30

 31         /// <summary>

 32         /// 绑定第Index页的数据

 33         /// </summary>

 34         /// <param name="Index"></param>

 35         private void BindDataWithPage(int Index)

 36         {

 37             allenPage1.PageIndex = Index;

 38             //winFormPager1.PageSize = 10;

 39             DataTable dt = GetData(strConn, strProcedure, Index, allenPage1.PageSize);

 40

 41             dataGridView1.DataSource = dt;

 42

 43             //获取并设置总记录数

 44             allenPage1.RecordCount = RecordCount;

 45         }

 46

 47

 48         /// <summary>

 49         /// 获取数据源

 50         /// </summary>

 51         /// <param name="conn">连接对象</param>

 52         /// <param name="strProcedure">存储过程名称</param>

 53         /// <param name="pageIndex">页码</param>

 54         /// <param name="pageSize">每一页显示的行数</param>

 55         /// <returns></returns>

 56         private DataTable GetData(string conn, string strProcedure, int pageIndex, int pageSize)

 57         {

 58

 59             using (SqlConnection connection = new SqlConnection(conn))

 60             {

 61                 SqlCommand command = new SqlCommand(strProcedure, connection);

 62                 command.CommandType = CommandType.StoredProcedure;//采用存储过程

 63                 command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = "TableName";//对应的数据表名

 64                 command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "Index";//主键ID

 65                 command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "*";//要查询的字段，*为全部字段

 66                 command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = " 1=1 ";//查询条件

 67                 command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();//当前页码

 68                 command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();//每一页显示的行数

 69                 command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = " Column Name asc";//排序的字段

 70                 //打开连接

 71                 if (connection.State != ConnectionState.Open)

 72                 {

 73                     connection.Open();

 74                 }

 75                 try

 76                 {

 77                     //填充数据

 78                     SqlDataAdapter da = new SqlDataAdapter(command);

 79                     DataSet ds = new DataSet();

 80                     da.Fill(ds);

 81                     //获取总记录数

 82                     RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

 83                     //返回数据集

 84                     return ds.Tables[0];

 85

 86                 }

 87                 catch (SqlException err)

 88                 {

 89                     MessageBox.Show(err.Message);

 90                     return null; ;

 91                 }

 92                 finally

 93                 {

 94                     connection.Close();

 95                 }

 96             }

 97         }

 98

 99         private void allenPage1_PageIndexChanged(object sender, EventArgs e)

100         {

101             BindDataWithPage(allenPage1.PageIndex);

102         }

103     }

104 }

存储过程：

 1 IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PageTest')

 2 DROP PROC PageTest

 3 GO

 4 CREATE PROCEDURE [dbo].[PageTest]

 5     @Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID

 6     @TIndex NVARCHAR(100),    --主键

 7     @Column NVARCHAR(2000) = '*',--要查询的字段，全部字段就为*

 8     @Sql NVARCHAR(3000) = '',--Where条件

 9     @PageIndex INT = 1,    --开始页码

10     @PageSize INT = 10,        --每页查询数据的行数

11     @Sort NVARCHAR(200) = '' --排序的字段

12

13 AS

14

15

16

17 DECLARE @strWhere VARCHAR(2000)

18 DECLARE @strsql NVARCHAR(3900)

19 IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0

20   BEGIN

21    SET @strWhere = ' WHERE ' + @Sql + ' '

22   END

23 ELSE

24   BEGIN

25    SET @strWhere = ''

26   END

27

28 IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)

29 BEGIN

30     IF(@Sort='')

31         SET @Sort = @TIndex + ' DESC '

32     ELSE

33         SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '

34 END

35 IF @PageIndex < 1

36   SET @PageIndex = 1

37

38         IF @PageIndex = 1

39         BEGIN

40           SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ '  FROM ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort

41         END

42         ELSE

43           BEGIN

44

45             DECLARE @START_ID NVARCHAR(50)

46             DECLARE @END_ID NVARCHAR(50)

47             SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)

48             SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)

49             SET @strsql =  ' SELECT '+@Column+ '

50            FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum,

51              '+@Column+ '

52               FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D

53            WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort

54           END

55 EXEC(@strsql)

56 PRINT @strsql

57     SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere

58 PRINT @strsql

59 EXEC(@strsql)

还有一种存储过程写法，仅供参考：

 1 CREATE PROCEDURE pro_DataPageRowNumber

 2 @SQL nvarchar(2000),--主句

 3 @Order nvarchar(20),--排序

 4 @PageIndex int,--当前页

 5 @PageSize int,--每页显示数

 6 @TotalRow int output--记录总数

 7 AS

 8     SET NOCOUNT ON;

 9     declare @ExceSQL nvarchar(4000)--主句

10     declare @startRow as int--开始行

11     set @startRow=(@PageIndex-1)*@PageSize+1

12     declare @lastRow int--结束行

13     set @lastRow=@PageSize*@PageIndex

14     declare @RowNumber nvarchar(100)

15     set @RowNumber=',Row_NUMBER() OVER(ORDER BY '+@Order+') as RowNumber from '

16     set @SQL=Replace(@SQL,' from ',@RowNumber)

17     set @ExceSQL='select @TotalRow=max(RowNumber) from ('+@SQL+') as tmp'

18     execute  sp_executesql @ExceSQl,N'@TotalRow in output',@TotalRow output

19     set @ExceSQL='select * from('+@SQL+') as tmp where RowNumber  between'+ Convert(nvarchar,@startRow)

20     +' and '+Convert(nvarchar,@lastRow)

21     execute(@ExceSQL)

22

23 GO