前两天有一个简单的C/S项目用到分页，因为是Winform下,没有现成的，自己也懒得写，就找了下，看到了ycmoon的一个控件
参考后，做了简化，只保留了分页的部分，主要是点击事件的Delegate，未做过多测试，有兴趣的朋友可以下载源码自行修改，如有好的建议，也可以给我反馈。3w@live.cn,效果如下：
控件设计界面：
设计时：
运行时：
附带一个取分页数据的存储过程：
View Code Create DataBase Db_TonyPaging go use Db_TonyPaging go if exists ( select 1 from sysobjects where id = object_id ( ' DepartDemo ' ) and type = ' U ' ) drop table DepartDemo go /* ============================================================== */ /* Table: DepartDemo */ /* ============================================================== */ create table DepartDemo ( PKID int identity ( 1 , 1 ), DName nvarchar ( 200 ) null , DCode nvarchar ( 500 ) null , Manager nvarchar ( 50 ) null , ParentID int null default 0 , AddUser nvarchar ( 50 ) null , AddTime datetime null , ModUser nvarchar ( 50 ) null , ModTime datetime null , CurState smallint not null default 0 , Remark nvarchar ( 500 ) null , F1 int not null default 0 , F2 nvarchar ( 300 ) null , constraint PK_DEPARTDEMO primary key (PKID)) go truncate table DepartDemo go /* **************创建54 条测试数据*************************************downmoo 3w@live.cn ************** */ declare @d datetime set @d = getdate () declare @i int set @i = 1 while @i <= 54 begin -- 插入一条测试数据 insert into DepartDemo select ' 国家统计局房产审计 ' + Cast ( @i as Nvarchar ( 10 )) + ' 科 ' , ' 0 ' , ' 胡不归 ' , 0 , ' DemoUser ' , getdate (), '' , ' 1900-01-01 ' , 1 , ' 专业评估全国房价，为老百姓谋福祉 ' , 0 , '' set @i = @i + 1 end go -- ***********分页存储过程用于SQL server2005/2008、2008R2**************************** SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [ dbo ] . [ ZJF_CPP_GetPagedRecordFor2005_2008 ] ( @Table varchar ( 1000 ), -- 表名,多表是请使用 tA a inner join tB b On a.AID = b.AID @TIndex nvarchar ( 100 ), -- 主键，可以带表头 a.AID @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 /**//**//**//* Execute dynamic query */ 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 )
在WinForm项目中，需要设置控件的总记录数RecordCount（由分页存储过程计算得出），和翻页事件winFormPager1_PageIndexChanged。
测试源码如下：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace DemoPager{ public partial class frmMain : Form { public frmMain() { InitializeComponent(); } #region Members // 总记录数 public int RecordCount = 0 ; private string strConn = @" Data Source=ap2\vegnet;Initial Catalog=Db_TonyPaging;Integrated Security=SSPI; " ; // "Server=localhost;database=Db_TonyPaging;uid=sa;pwd=sa;"; private string strProcedure = " ZJF_CPP_GetPagedRecordFor2005_2008 " ; #endregion #region Methods /// <summary> /// 绑定第Index页的数据 /// </summary> /// <param name="Index"></param> private void BindDataWithPage( int Index) { winFormPager1.PageIndex = Index; // winFormPager1.PageSize = 10;; ; dgvList.DataSource = GetData(strConn, strProcedure, Index, winFormPager1.PageSize); // 获取并设置总记录数 winFormPager1.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 = " DepartDemo " ; command.Parameters.Add( " @TIndex " , SqlDbType.NVarChar, 100 ).Value = " PKID " ; 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 = " PKID desc " ; // 打开连接 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(); } } } #endregion #region Events private void frmMain_Load( object sender, EventArgs e) { // 不自动生成列 dgvList.AutoGenerateColumns = false ; // 绑定数据 BindDataWithPage( 1 ); } /// <summary> /// 翻页事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void winFormPager1_PageIndexChanged( object sender, EventArgs e) { BindDataWithPage(winFormPager1.PageIndex); } #endregion }}
下载控件源码及演示程序(含SQL)
邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。助人等于自助!