DataGrid自定义分页存储过程

2022-07-15 80酷酷网 80kuku.com

　　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);
  }