WinForm DataGridView分页功能

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

 32         /// 绑定第Index页的数据
 33         /// 
 34         /// 
 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         /// 

 49         /// 获取数据源
 50         /// 
 51         /// 连接对象
 52         /// 存储过程名称
 53         /// 页码
 54         /// 每一页显示的行数
 55         /// 
 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 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