DataGrid自訂分頁預存程序

來源:互聯網
上載者:User
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);
  }

 



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

11.11 Big Sale for Cloud

Get Unbeatable Offers with up to 90% Off,Oct.24-Nov.13 (UTC+8)

Get It Now >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。