一个DataGridView的分页代码(用存储过程来实现)

//from:http://www.ohuo.net/?p=84

if exists(select * from sysobjects where name=’Proc_MyPagination’)     drop procedure Proc_MyPagination go

create procedure Proc_MyPagination /* *************************************************************** ** 分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Fields :字段 7.Filter :过滤语句，不带Where 8.Group :Group语句,不带Group By 9.GetCount :返回记录总数, 非 0 值则返回 ***************************************************************/     @Tables varchar(600),     @PrimaryKey varchar(100),     @Sort varchar(200)=null,     @CurrentPage bigint=1,     @PageSize bigint=10,     @Fields varchar(1000)=’*',     @Filter varchar(1000)=null,     @Group varchar(1000)=null,     @GetCount bit=0 as     if(@GetCount=0)         begin

            /*默认排序*/             if @Sort is null or @Sort=”                 set @Sort=@PrimaryKey                      declare @SortTable varchar(100)             declare @SortName varchar(100)             declare @strSortColumn varchar(200)             declare @operator char(2)             declare @type varchar(100)             declare @prec int                      /*设定排序语句*/             if charindex(’desc’,@Sort)>0                 begin                 set @strSortColumn=replace(@Sort,’desc’,”)                 set @operator=’<=’                 end             else                 begin                 if charindex(’asc’,@Sort)=0                     set @strSortColumn=replace(@Sort,’asc’,”)                 set @operator=’>=’                 end                      if charindex(’.',@strSortColumn)>0                 begin                 set @SortTable=substring(@strSortColumn,0,charindex(’.',@strSortColumn))                 set @SortName=substring(@strSortColumn,charindex(’.',@strSortColumn)+1,len(@strSortColumn))                 end             else                 begin                 set @SortTable=@Tables                 set @SortName=@strSortColumn                 end                      select @type=t.name,@prec=c.prec                 from sysobjects o join syscolumns c on o.id=c.id                 join systypes t on c.xusertype=t.xusertype                 where o.name=@SortTable and c.name=@SortName                      if charindex(’char’,@type)>0                 set @type=@type+’('+cast(@prec as varchar)+’)’                      declare @strPageSize varchar(50)             declare @strStartRow varchar(50)             declare @strFilter varchar(1000)             declare @strSimpleFilter varchar(1000)             declare @strGroup varchar(1000)                      /*默认当前页*/             if @CurrentPage<1                 set @CurrentPage=1                      /*设置分页参数*/             set @strPageSize=cast(@PageSize as varchar(50))             set @strStartRow=cast(((@CurrentPage-1)*@PageSize+1) as varchar(50))                      /*筛选以及分组语句*/             if @Filter is not null and @Filter!=”                 begin                 set @strFilter=’ where ‘+@Filter+’ ‘                 set @strSimpleFilter=’ and ‘+@Filter +’ ‘                 end             else                 begin                 set @strSimpleFilter=”                 set @strFilter=”                 end                      if @Group is not null and @Group!=”                 set @strGroup=’ group by ‘+@Group+’ ‘             else                 set @strGroup=”                      exec(             ’ declare @SortColumn ‘+ @type + ‘ set RowCount ‘ + @strStartRow             + ‘ select @SortColumn=’ + @strSortColumn + ‘ from ‘ + @Tables             + @strFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort             + ‘ set rowcount ‘ + @strPageSize + ‘ select ‘ + @Fields             + ‘ from ‘ + @Tables + ‘ where ‘ + @strSortColumn + @operator             + ‘@SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort + ‘ ‘)         end     else         begin             declare @strSQL varchar(5000)             if @Filter !=”                 set @strSQL = ’select count(’ + @PrimaryKey + ‘) as Total from [’ + @Tables + ‘] where ‘ + @Filter             else                 set @strSQL = ’select count(’ + @PrimaryKey + ‘) as Total from [’ + @Tables + ‘]’             exec(@strSQL)         end go

然后再程序里调用存储过程