admin 管理员组文章数量: 887021
添加两个组件：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
效果：
本文标签： 分页 DevExpress GridControl
版权声明：本文标题：[DevExpress]GridControl分页的实现 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1726435506h960137.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论