首页 > 代码库 > WinForm DataGridView分页功能
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 = "http://www.mamicode.com/TableName";//对应的数据表名 64 command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "http://www.mamicode.com/Index";//主键ID 65 command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "http://www.mamicode.com/*";//要查询的字段，*为全部字段 66 command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = "http://www.mamicode.com/1=1";//查询条件 67 command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = http://www.mamicode.com/pageIndex.ToString();//当前页码"@PageSize", SqlDbType.Int, 8).Value = http://www.mamicode.com/pageSize.ToString();//每一页显示的行数"@Sort", SqlDbType.NVarChar, 200).Value = "http://www.mamicode.com/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 AS14 15 16 17 DECLARE @strWhere VARCHAR(2000)18 DECLARE @strsql NVARCHAR(3900)19 IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>020 BEGIN21 SET @strWhere = ‘ WHERE ‘ + @Sql + ‘ ‘22 END23 ELSE24 BEGIN25 SET @strWhere = ‘‘26 END27 28 IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)29 BEGIN30 IF(@Sort=‘‘)31 SET @Sort = @TIndex + ‘ DESC ‘32 ELSE33 SET @Sort = @Sort+ ‘ , ‘+@TIndex + ‘ DESC ‘34 END35 IF @PageIndex < 136 SET @PageIndex = 137 38 IF @PageIndex = 1 39 BEGIN 40 SET @strsql = ‘SELECT TOP ‘ + str(@PageSize) +‘ ‘+@Column+ ‘ FROM ‘ + @Table + ‘ ‘ + @strWhere + ‘ ORDER BY ‘+ @Sort41 END 42 ELSE43 BEGIN44 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 D53 WHERE RowNum BETWEEN ‘+@START_ID+‘ AND ‘ +@END_ID +‘ ORDER BY ‘+@Sort54 END55 EXEC(@strsql)56 PRINT @strsql57 SET @strsql = ‘SELECT Count(1) as TotalRecords FROM ‘ + @Table +‘ WITH(NOLOCK) ‘ + @strWhere 58 PRINT @strsql59 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+112 declare @lastRow int--结束行13 set @lastRow=@PageSize*@PageIndex14 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 output19 set @ExceSQL=‘select * from(‘+@SQL+‘) as tmp where RowNumber between‘+ Convert(nvarchar,@startRow)20 +‘ and ‘+Convert(nvarchar,@lastRow)21 execute(@ExceSQL)22 23 GO
分页的控件是自己写的一个用户控件，生成之后是一个DLL文件，直接引用在项目里面即可，有需要的可以留下邮箱。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。