1,分頁嘛先要有個SQL 程式才能寫下去 先提供下SQL的思路,對於分頁的SQL我之前帖子有介紹,就不一一介紹了 select top pageSize * --顯示數量 from (select row_number() over(order by EG_ID asc) as rownumber, ...
1,分頁嘛先要有個SQL 程式才能寫下去 先提供下SQL的思路,對於分頁的SQL我之前帖子有介紹,就不一一介紹了
select top pageSize * --顯示數量 from (select row_number() over(order by EG_ID asc) as rownumber,* --行數 from ExchangGifts) temp_row where rownumber>((pageIndex-1)*pageSize);--開始頁數 --建議SQL
存儲過程參考下
CREATE proc [dbo].[P_GetCardLevelsPaged] @PageSize int, @PageIndex int, @Count int output, @LevelName varchar(20) as begin select top(@PageSize) * from CardLevels where CL_ID not in( select top(@PageSize*(@PageIndex-1)) CL_ID from CardLevels where CL_LevelName like '%'+@LevelName+'%' ) and CL_LevelName like '%'+@LevelName+'%' select @Count=COUNT(*) from CardLevels where CL_LevelName like '%'+@LevelName+'%' end GO
2,例子中用的分頁SQL:
SELECT * FROM(SELECT ROW_NUMBER() OVER(order BY E.EG_ID) as rows ,E. * FROM ExchangGifts E) AS A WHERE A.rows BETWEEN 1 and 10;
代碼 註釋詳細
public partial class XtraForm2 : DevExpress.XtraEditors.XtraForm { private int pageIndex = 1; //當前頁碼 private int pageSize = 5; //每頁的尺寸 private int pageCount = 0; //總頁數 public XtraForm2() { InitializeComponent(); } //獲取記錄總數 public int GetRecordCount() { int count = 0; string sql = "select count(*) from ExchangGifts"; count = Convert.ToInt32(DBHelper.GetScalar(sql, null, false)); return count; } //獲取當前頁的記錄列表 public DataTable GetListByPage(int startIndex, int endIndex) { DataTable dt = new DataTable(); StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select row_number() over(order by t.[EG_ID]) as rows,t.* from ExchangGifts as t) as tt"); strSql.AppendFormat(" where tt.rows between {0} and {1} ", startIndex, endIndex); dt = DBHelper.Query(strSql.ToString(), null); return dt; } //綁定數據表格控制項 public void BindPageGridList() { //記錄的開始索引 int startIndex = (pageIndex - 1) * pageSize + 1; //記錄的結束索引 int endIndex = pageIndex * pageSize; //總記錄數 int row = GetRecordCount(); if (row % pageSize > 0) { pageCount = row / pageSize + 1; } else { pageCount = row / pageSize; } //如果當前是最後一頁,設置最後一行記錄的索引 if (pageIndex == pageCount) { endIndex = row; } DataTable dt = this.GetListByPage(startIndex, endIndex); gc.DataSource = dt; nvgDataPager.DataSource = dt; nvgDataPager.TextStringFormat = string.Format("第{0}頁,共{1}頁", pageIndex, pageCount); } private void nvgDataPager_ButtonClick(object sender, NavigatorButtonClickEventArgs e) { string type = e.Button.Tag.ToString(); switch (type) { case "首頁": pageIndex = 1; break; case "末頁": pageIndex = pageCount; break; case "下一頁": if (pageIndex < pageCount) { pageIndex++; } break; case "上一頁": if (pageIndex > 1) { pageIndex--; } break; } this.BindPageGridList(); //綁定當前頁到控制項 } }
效果圖