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

asdeclare@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 outputendelse begin set @RecordCount = @PageSize * @PageCurrent + 1end

if @PageCurrent = 1 begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStrendelse 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 ' + @IdentityStrendif @FdOrder <>'' and @FdOrder<>@IdentityStr begin set @sql = 'select * from (' + @sql + ') as t4867435348493 order by ' + @FdOrderend--print @sqlexecute(@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);  } 

转载于:https://www.cnblogs.com/weipengyang/archive/2006/02/28/339798.html

相关资源：垃圾分类数据集及代码