[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 @[email protected]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 @[email protected]               set @[email protected]                 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 [email protected] and [email protected]if charindex(‘char‘,@type)>0                 set @[email protected]+‘(‘+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 ‘[email protected]+‘ ‘                 set @strSimpleFilter=‘ and ‘[email protected] +‘ ‘                 end             else                 begin                 set @strSimpleFilter=‘‘                 set @strFilter=‘‘                 end                      if @Group is not null and @Group!=‘‘                 set @strGroup=‘ group by ‘[email protected]+‘ ‘             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 

效果：

[DevExpress]GridControl分頁的實現