DataGrid自定义分页存储过程

存储过程代码：

CREATE proc page
@RecordCount int output,
@QueryStr nvarchar(100)=@#table1@#,--表名、视图名、查询语句
@PageSize int=20,  --每页的大小(行数)
@PageCurrent int=1,  --要显示的页
@FdShow nvarchar (1000)=@#*@#, --要显示的字段列表
@IdentityStr nvarchar (100)=@#id@#, --主键
@WhereStr nvarchar (200)=@#1=1 and id % 11111 = 0@#,
@FdOrder nvarchar(100)=@#id@#, --排序
@isReturn bit=0

as
declare
@sql nvarchar(2000)
set @sql = @#@#
if @WhereStr = @#@# begin
 set @WhereStr = @#1=1@#
end

declare @tsql nvarchar(200)

if(@isReturn=1)begin
 set @tsql=N@#select @RecordCount = count(*) from @# + @QueryStr + @# where @# + @WhereStr
 exec sp_executesql @tsql,N@#@RecordCount int output@#,@RecordCount output
end
else begin
 set @RecordCount = @PageSize * @PageCurrent + 1
end

if @PageCurrent = 1 begin
 set @sql = @#select top @# + cast(@PageSize as nvarchar(3)) + @# @# + @FdShow + @# from @# + @QueryStr + @# where @# + @WhereStr + @# order by @# + @IdentityStr
end
else begin
 set @sql = @#select top @# + cast(@PageSize as nvarchar(3)) + @# @# + @FdShow + @# from @# + @QueryStr + @# where @# + @WhereStr + @# and @# + @IdentityStr + @#> ( select max(@# + @IdentityStr + @#) from (select top @# + cast(@PageSize*(@PageCurrent-1) as nvarchar(10)) + @# @# + @IdentityStr + @# from @# + @QueryStr + @# where @# + @WhereStr + @# order by @# + @IdentityStr + @#) as t) order by @# + @IdentityStr
end
if @FdOrder <>@#@# and @FdOrder<>@IdentityStr begin
 set @sql = @#select * from (@# + @sql + @#) as t4867435348493 order by @# + @FdOrder
end
--print @sql
execute(@sql)
GO

后台代码：

  protected System.Web.UI.WebControls.DataGrid DataGrid1;
 
  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!Page.IsPostBack)
   {
    DataGrid1DataBind(1);
   }
  }

  private void DataGrid1DataBind(int page)
  {
   SqlConnection conn = new SqlConnection("server=qq;uid=sa;pwd=***;database=testdb");

   SqlCommand cmd = new SqlCommand("page",conn);
   cmd.CommandType = CommandType.StoredProcedure;

   SqlParameter p1 = cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
   p1.Direction = ParameterDirection.Output;
   SqlParameter p = cmd.Parameters.Add("@PageCurrent",SqlDbType.Int);
   p.Value = page;

   SqlDataAdapter da  = new SqlDataAdapter();
   DataSet ds = new DataSet();
   da.SelectCommand = cmd;
   da.Fill(ds);
   DataGrid1.DataSource = ds.Tables[0].DefaultView;
   int count = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
   if(null == ViewState["page"] || "" == ViewState["page"].ToString())
   {
    ViewState["page"] = count.ToString();
   }
   else
   {
    count = int.Parse(ViewState["page"].ToString());
   }

   DataGrid1.VirtualItemCount = count;

   DataGrid1.DataBind();
  }

  private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
  {
   DataGrid1.CurrentPageIndex = e.NewPageIndex;
   DataGrid1DataBind(e.NewPageIndex + 1);
  }

原文转自：http://www.ltesting.net