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