DevExpress中如何實現GridControl的分頁功能？

加入兩個組件：BindingNavigator和BindingSource

技術分享

技術分享

代碼：

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Linq;

using System.Threading.Tasks;

using System.Windows.Forms;

using DevExpress.XtraEditors;

using DZAMS.DBUtility;

namespace DZAMS.Demo

{

public partial class GridPage_Frm : DevExpress.XtraEditors.XtraForm

{

public DataTable dt = new DataTable();

StoreProcedure sp;

private int pageSize = 10; //每頁顯示行數

private int nMax = 0; //總記錄數

private int pageCount = 0; //頁數＝總記錄數/每頁顯示行數

private int pageCurrent = 0; //當前頁號

private DataSet ds = new DataSet();

private DataTable dtInfo = new DataTable();

public GridPage_Frm()

{

InitializeComponent();

}

private void GridPage_Frm_Load(object sender, EventArgs e)

{

string strQuery = string.Format("SELECT Id, UserCode, UserName, RoleName, Ip, Mac, LoginTime FROM DZ_LoginLog");

dt = SqlHelper.ExecuteDataset(SqlHelper.conn, CommandType.Text, strQuery.ToString()).Tables[0];

gridControl1.DataSource = dt;

string strConn = "SERVER=(local);DATABASE=DZ;UID=sa;PWD=XXXX"; //數據庫連接字符串

SqlConnection conn = new SqlConnection(strConn);

conn.Open();

string strSql = "SELECT count(*) as num FROM DZ_LoginLog";

SqlDataAdapter sda = new SqlDataAdapter(strSql, conn);

sda.Fill(ds, "ds");

conn.Close();

nMax = Convert.ToInt32(ds.Tables[0].Rows[0]["num"].ToString());

lblTotalCount.Text = nMax.ToString();

lblPageSize.Text = pageSize.ToString();

sp = new StoreProcedure("Pr_Monitor_Pagination", strConn);

dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent++, pageSize);

InitDataSet();

}

private void InitDataSet()

{

pageCount = (nMax / pageSize); //計算出總頁數

if ((nMax % pageSize) > 0) pageCount++;

pageCurrent = 1; //當前頁數從1開始

LoadData();

}

private void LoadData()

{

lblPageCount.Text = "/"+pageCount.ToString();

txtCurrentPage.Text = Convert.ToString(pageCurrent);

this.bdsInfo.DataSource = dtInfo;

this.bdnInfo.BindingSource = bdsInfo;

this.gridControl1.DataSource = bdsInfo;

}

private void bdnInfo_ItemClicked(object sender, ToolStripItemClickedEventArgs e)

{

if (e.ClickedItem.Text == "導出當前頁")

{

SaveFileDialog saveFileDialog = new SaveFileDialog();

saveFileDialog.Title = "導出Excel";

saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";

DialogResult dialogResult = saveFileDialog.ShowDialog(this);

if (dialogResult == DialogResult.OK)

{

DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();

gridControl1.ExportToXls(saveFileDialog.FileName, options);

// gridControl1.ExportToExcelOld(saveFileDialog.FileName);

DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

}

if (e.ClickedItem.Text == "關閉")

{

this.Close();

}

if (e.ClickedItem.Text == "首頁")

{

pageCurrent--;

if (pageCurrent <= 0)

{

MessageBox.Show("已經是首頁。請點擊“下一頁”查看！");

return;

}

else

{

pageCurrent = 1;

dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);

}

}

if (e.ClickedItem.Text == "上一頁")

{

pageCurrent--;

if (pageCurrent <= 0)

{

MessageBox.Show("已經是第一頁，請點擊“下一頁”查看！");

return;

}

else

{

dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);

}

}

if (e.ClickedItem.Text == "下一頁")

{

pageCurrent++;

if (pageCurrent > pageCount)

{

MessageBox.Show("已經是最后一頁。請點擊“上一頁”查看。");

return;

}

else

{

dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);

}

}

if (e.ClickedItem.Text == "尾頁")

{

pageCurrent++;

if (pageCurrent > pageCount)

{

MessageBox.Show("已經是尾頁，請點擊“上一頁”查看。");

return;

}

else

{

pageCurrent = pageCount;

dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCount, pageSize);

}

}

LoadData();

}

}

}

StoreProcedure類：

public class StoreProcedure

{

// 存儲過程名稱。

private string _name;

// 數據庫連接字符串。

private string _conStr;

// 構造函數

// sprocName: 存儲過程名稱；

// conStr: 數據庫連接字符串。

public StoreProcedure(string sprocName, string conStr)

{

_conStr = conStr;

_name = sprocName;

}

// 運行存儲過程，不返回值。

// paraValues: 參數值列表。

// return: void

public void ExecuteNoQuery(params object[] paraValues)

{

using (SqlConnection con = new SqlConnection(_conStr))

{

SqlCommand comm = new SqlCommand(_name, con);

comm.CommandType = CommandType.StoredProcedure;

AddInParaValues(comm, paraValues);

con.Open();

comm.ExecuteNonQuery();

con.Close();

}

}

// 運行存儲過程返回一個表。

// paraValues: 參數值列表。

// return: DataTable

public DataTable ExecuteDataTable(params object[] paraValues)

{

SqlCommand comm = new SqlCommand(_name, new SqlConnection(_conStr));

comm.CommandType = CommandType.StoredProcedure;

AddInParaValues(comm, paraValues);

SqlDataAdapter sda = new SqlDataAdapter(comm);

DataTable dt = new DataTable();

sda.Fill(dt);

return dt;

}

// 運行存儲過程。返回SqlDataReader對象。

// 在SqlDataReader對象關閉的同一時候。數據庫連接自己主動關閉。

// paraValues: 要傳遞給給存儲過程的參數值類表。

// return: SqlDataReader

public SqlDataReader ExecuteDataReader(params object[] paraValues)

{

SqlConnection con = new SqlConnection(_conStr);

SqlCommand comm = new SqlCommand(_name, con);

comm.CommandType = CommandType.StoredProcedure;

AddInParaValues(comm, paraValues);

con.Open();

return comm.ExecuteReader(CommandBehavior.CloseConnection);

}

// 獲取存儲過程的參數列表。

private ArrayList GetParas()

{

SqlCommand comm = new SqlCommand("dbo.sp_sproc_columns_90",

new SqlConnection(_conStr));

comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.AddWithValue("@procedure_name", (object)_name);

SqlDataAdapter sda = new SqlDataAdapter(comm);

DataTable dt = new DataTable();

sda.Fill(dt);

ArrayList al = new ArrayList();

for (int i = 0; i < dt.Rows.Count; i++)

{

al.Add(dt.Rows[i][3].ToString());

}

return al;

}

// 為 SqlCommand 加入參數及賦值。

private void AddInParaValues(SqlCommand comm, params object[] paraValues)

{

comm.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int));

comm.Parameters["@RETURN_VALUE"].Direction =

ParameterDirection.ReturnValue;

if (paraValues != null)

{

ArrayList al = GetParas();

for (int i = 0; i < paraValues.Length; i++)

{

comm.Parameters.AddWithValue(al[i + 1].ToString(),

paraValues[i]);

}

}

}

}

存儲過程：

ALTER procedure [dbo].[Pr_Monitor_Pagination]

-- ============================================= == Paging == =============================================

--Author: Lee

--Create date: 2010\06\11

--Parameter:

-- 1.Tables :The Name Of Table or view

-- 2.PrimaryKey :Primary Key

-- 3.Sort :Ordering Statement，Without Order By, For Example：NewsID Desc,OrderRows Asc

-- 4.CurrentPage :The Page Number Of Current page

-- 5.PageSize :The Size Of One Page‘s Group

-- 6.Fields :The Field Of You Needed

-- 7.Filter :Where Condition，Without Where

-- 8.Group :Group Condition。Without Group By

-- 9.GetCount :Return The Number Of All, Not Zero

--Updates:

-- 2010\06\09 Create Procedure.

-- ========================================================================================================

@Tables varchar(600),

@PrimaryKey varchar(100),

@Sort varchar(200)=null,

@CurrentPage bigint=1,

@PageSize bigint=10,

@Fields varchar(1000)=‘*‘,

@Filter varchar(1000)=null,

@Group varchar(1000)=null,

@GetCount bit=0

as

if(@GetCount=0)

begin

/*Ordering Of Default */

if @Sort is null or @Sort=‘‘

set @Sort=@PrimaryKey

declare @SortTable varchar(100)

declare @SortName varchar(100)

declare @strSortColumn varchar(200)

declare @operator char(2)

declare @type varchar(100)

declare @prec int

/*Setting Condition Of Ordering*/

if charindex(‘desc‘,@Sort)>0

begin

set @strSortColumn=replace(@Sort,‘desc‘,‘‘)

set @operator=‘<=‘

end

else

begin

if charindex(‘asc‘,@Sort)=0

set @strSortColumn=replace(@Sort,‘asc‘,‘‘)

set @operator=‘>=‘

end

if charindex(‘.‘,@strSortColumn)>0

begin

set @SortTable=substring(@strSortColumn,0,charindex(‘.‘,@strSortColumn))

set @SortName=substring(@strSortColumn,charindex(‘.‘,@strSortColumn)+1,len(@strSortColumn))

end

else

begin

set @SortTable=@Tables

set @SortName=@strSortColumn

end

select @type=t.name,@prec=c.prec from sysobjects o join syscolumns c on o.id=c.id join systypes t on c.xusertype=t.xusertype where o.name=@SortTable and c.name=@SortName

if charindex(‘char‘,@type)>0

set @type=@type+‘(‘+cast(@prec as varchar)+‘)‘

declare @strPageSize varchar(50)

declare @strStartRow varchar(50)

declare @strFilter varchar(1000)

declare @strSimpleFilter varchar(1000)

declare @strGroup varchar(1000)

/*CurrentPage Of Default*/

if @CurrentPage<1

set @CurrentPage=1

/*Setting Paging param*/

set @strPageSize=cast(@PageSize as varchar(50))

set @strStartRow=cast(((@CurrentPage-1)*@PageSize+1) as varchar(50))

/*Condition Of Filter And Group*/

if @Filter is not null and @Filter!=‘‘

begin

set @strFilter=‘ where ‘+@Filter+‘ ‘

set @strSimpleFilter=‘ and ‘+@Filter +‘ ‘

end

else

begin

set @strSimpleFilter=‘‘

set @strFilter=‘‘

end

if @Group is not null and @Group!=‘‘

set @strGroup=‘ group by ‘+@Group+‘ ‘

else

set @strGroup=‘‘

exec(‘ declare @SortColumn ‘+ @type + ‘ set RowCount ‘ + @strStartRow+ ‘ select @SortColumn=‘ + @strSortColumn + ‘ from ‘ + @Tables+ @strFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort+ ‘ set rowcount ‘ + @strPageSize + ‘ select ‘ + @Fields + ‘ from ‘ + @Tables + ‘ where ‘ + @strSortColumn + @operator+ ‘@SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ Order by ‘ + @Sort + ‘ ‘)

end

else

begin

declare @strSQL varchar(5000)

if @Filter !=‘‘

set @strSQL = ‘select count(‘ + @PrimaryKey + ‘) as Total from [‘ + @Tables + ‘] where ‘ + @Filter

else

set @strSQL = ‘select count(‘ + @PrimaryKey + ‘) as Total from [‘ + @Tables + ‘]‘

exec(@strSQL)

end

效果：

技術分享