CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名 @keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开 @pageIndex INT=1,-- 要显示的页码 @pageSize INT=10,-- 每页的大小(记录数) @fieldName NVARCHAR(1000)='',-- 要显示的字段列表 @sortName NVARCHAR(1000)='',-- 以逗号分隔的排序字段列表(如:id asc,Name asc)用于指定排序顺序 @strWhere NVARCHAR(1000)='',-- 查询条件 @recordCount INT=0 OUTPUT,-- 总记录数 @pageCount INT=0 OUTPUT -- 总页数AS SET NOCOUNT ON --检查对象是否有效 IF Object_id(@tableName) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tableName) RETURN END IF Objectproperty(Object_id(@tableName), N'IsTable') = 0 AND Objectproperty(Object_id(@tableName), N'IsView') = 0 AND Objectproperty(Object_id(@tableName), N'IsTableFunction') = 0 BEGIN RAISERROR(N'"%s"不是表,视图或者表值函数',1,16,@tableName) RETURN END --分页字段检查 IF Isnull(@keyField, N'') = '' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END --?其它参数检查及规范 IF Isnull(@pageIndex, 0) < 1 SET @pageIndex=1 IF Isnull(@pageSize, 0) < 1 SET @pageSize=10 IF Isnull(@fieldName, N'') = N'' SET @fieldName=N'*' IF Isnull(@sortName, N'') = N'' SET @sortName=N'' ELSE SET @sortName=N'ORDER BY ' + Ltrim(@sortName) IF Isnull(@strWhere, N'') = N'' SET @strWhere=N'' ELSE SET @strWhere=N'WHERE (' + @strWhere + N')' --如果为NULL值,则计算总页数(可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算,对于不想计算总页数,可以给@pageCount赋值) IF @pageCount IS NULL BEGIN DECLARE @sql NVARCHAR(4000) SET @sql=N'SELECT @recordCount=COUNT(*)' + N' FROM ' + @tableName + N' ' + @strWhere EXEC Sp_executesql @sql, N'@recordCount int OUTPUT', @recordCount OUTPUT SET @pageCount=Ceiling(@recordCount * 1.0 / @pageSize) END --计算分页显示 top N 值 DECLARE @TopN VARCHAR(20), @TopN1 VARCHAR(20) SELECT @TopN = @pageSize, @TopN1 = @pageIndex * @pageSize --第一页直接显示 IF @pageIndex = 1 EXEC(N'SELECT TOP '+@TopN +N' '+@fieldName +N' FROM '+@tableName +N' '+@strWhere +N' '+@sortName) ELSE BEGIN --生成主键(唯一键)处理条件 DECLARE @Where1 NVARCHAR(4000), @s NVARCHAR(1000) SELECT @Where1 = N'', @s = @keyField WHILE Charindex(N',', @s) > 0 SELECT @s = Stuff(@s, 1, Charindex(N',', @s), N''), @Where1 = @Where1 + N' AND a.' + LEFT(@s, Charindex(N',', @s)-1) + N'=' + LEFT(@s, Charindex(N',', @s)-1) SELECT @Where1 = Stuff(@Where1 + N' AND a.' + @s + N'=' + @s, 1, 5, N''), @TopN = @TopN1 - @pageSize EXEC(N'SET ROWCOUNT '+@TopN1 +N' SELECT '+@keyField +N' INTO # FROM '+@tableName +N' '+@strWhere +N' '+@sortName +N' SET ROWCOUNT '+@TopN +N' DELETE FROM #' +N' SELECT '+@fieldName +N' FROM '+@tableName +N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1 +N') '+@sortName) END protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindGridViewData(); } } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { #region 如果是绑定数据行,鼠标滑过变色 if (e.Row.RowType == DataControlRowType.DataRow) { //鼠标经过时，行背景色变 e.Row.Attributes["onmouseover"] = "ItemOver(this)"; //鼠标经过行变成手形 //e.Row.Style.Add("cursor", "hand"); } #endregion #region 鼠标单击行，打开窗口同时传出参数ID //string ID = String.Empty; //for (int i = 0; i < grvTestTable.Rows.Count; i++) //{ // ID = grvTestTable.DataKeys[i].Value.ToString(); // grvTestTable.Rows[i].Attributes.Add("onclick", "window.open('Detail.aspx?PKID="+ID+" ')"); //} #endregion } private void BindGridViewData() { string strCondition = ""; MSCL.PageHelper wp = new MSCL.PageHelper(); wp.TableName = "AFM_EmailMessage"; wp.KeyField = "EID"; wp.SortName = "EID"; wp.Condition = strCondition; wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex; wp.PageSize = AspNetPager1.PageSize;//=PageSize; DataTable dt = wp.GetDataTableMyPage(); AspNetPager1.RecordCount = wp.RecordCount; grvAFM_EmailMessage.DataSource = dt; grvAFM_EmailMessage.DataBind(); AspNetPager1.CustomInfoHTML = " 共<font color='#FF8000'><b>" + wp.RecordCount.ToString() + "</b></font>条记录/"; AspNetPager1.CustomInfoHTML += " <font color=#FF8000'><b>" + wp.PageCount.ToString() + "</b></font>页"; AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页"; /* MSCL.PageHelper p = new PageHelper(); p.CurrentPageIndex = AspNetPager1.CurrentPageIndex; p.FieldsName = "*"; p.KeyField = "d_id"; p.SortName = "d_id asc"; p.TableName = "testtable"; p.EndCondition = "count(*)"; p.PageSize = AspNetPager1.PageSize; DataTable dt = p.QueryPagination(); AspNetPager1.RecordCount = p.RecordCount; Repeater1.DataSource = dt; Repeater1.DataBind(); AspNetPager1.CustomInfoHTML = " 共<b>" + p.RecordCount + "</b>条记录/"; AspNetPager1.CustomInfoHTML += "<b>" + p.PageCount + "</b>页"; AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + p.CurrentPageIndex + "</b></font>页"; */ } protected void AspNetPager1_PageChanged(object sender, EventArgs e) { BindGridViewData(); }CREATE PROC P_viewPage /* 敬告：适用于单一主键或存在唯一值列的表或视图 */ @TableName VARCHAR(200), --表名 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为* @PrimaryKey VARCHAR(100), --单一主键或唯一值键 @Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @TotalCount INT OUTPUT, --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数AS SET NOCOUNT ON IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0 SET @Order = RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','') WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0 BEGIN SET @Order = REPLACE(@Order,', ',',') SET @Order = REPLACE(@Order,' ,',',') END IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' OR ISNULL(@PrimaryKey,'') = '' OR @SortType < 1 OR @SortType >3 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 BEGIN PRINT('ERR_00') RETURN END IF @SortType = 3 BEGIN IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC') BEGIN PRINT('ERR_02') RETURN END END DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000) DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000) DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE @SqlCount NVARCHAR(4000) IF ISNULL(@where,'') = '' BEGIN SET @new_where1 = ' ' SET @new_where2 = ' WHERE ' END ELSE BEGIN SET @new_where1 = ' WHERE ' + @where SET @new_where2 = ' WHERE ' + @where + ' AND ' END IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 BEGIN IF @SortType = 1 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC' END IF @SortType = 2 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC' END END ELSE BEGIN SET @new_order1 = ' ORDER BY ' + @Order END IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0 BEGIN SET @new_order1 = ' ORDER BY ' + @Order SET @new_order2 = @Order + ',' SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},') SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,') SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList <> '*' BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') SET @FieldList = ',' + @FieldList WHILE CHARINDEX(',',@new_order3)>0 BEGIN IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0 BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) END SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3)) END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END END SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1 IF @RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN SELECT @TotalCount = @RecorderCount END IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) END IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1 --返回第一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 END IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 END END ELSE BEGIN IF @SortType = 1 --仅主键正序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 2 --仅主键反序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey +' FROM '+ @TableName + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理 BEGIN IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 BEGIN PRINT('ERR_02') RETURN END IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order2 + ' ) AS TMP ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 + ' ) AS TMP ' + @new_order1 END END END PRINT(@Sql) EXEC(@Sql)GO--利用ROW_NUMBER快速分页 ALTER PROCEDURE [dbo].[pr_Pagination] @TableName varchar(500), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id) @Fields varchar(500), --表中的字段，可以使用*代替 @OrderField varchar(500), --要排序的字段 @SqlWhere varchar(max), --WHERE子句 @PageSize int, --分页的大小 @PageIndex int, --要显示的页的索引 @TotalRecord int output,--记录总数 @TotalPage int output, --页的总数 @WholeContion varchar (1000) --存储过程中的条件 as begin --Begin Tran Declare @sql nvarchar(4000); Declare @Record int; --记录总数 --利用WHERE子句进行过滤 if (isnumeric(@WholeContion)=1 ) begin set @sql = 'select @Record = count(*) from ' + @TableName + ' where 1=1 ' + @SqlWhere EXEC sp_executesql @sql,N'@Record int OUTPUT',@Record OUTPUT if (CAST(@WholeContion as int ) < @Record ) begin set @Record = CAST(@WholeContion as int ) end end else begin set @sql = 'select @Record = ' + @WholeContion + ' from ' + @TableName + ' where 1=1 ' + @SqlWhere --执行sql语句得到记录总数 EXEC sp_executesql @sql,N'@Record int OUTPUT',@Record OUTPUT end select @TotalPage=CEILING((@Record+0.0)/@PageSize) select @TotalRecord=@Record --select @TotalRecord --select @TotalPage --根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序 set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where 1=1 ' + @SqlWhere --确保当前页的索引在合理的范围之内 if @PageIndex<=0 Set @PageIndex = 1 --得到当前页在整个结果集中准确的ROW_NUMBER值 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 --输出当前页中的数据 set @sql = @sql + ') as t' + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) print @sql Exec(@sql) --If @@Error <> 0 --Begin --RollBack Tran --End --Else --Begin --Commit Tran --End end
