版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
添加两个组件：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
效果：
1261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
