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 + ‘]‘
加入兩個元件：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)
{
return;
}
else
{
pageCurrent = 1;
dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);
}
}
if (e.ClickedItem.Text == "上一頁")
{
pageCurrent--;
if (pageCurrent <= 0)
{
return;
}
else
{
dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);
}
}
if (e.ClickedItem.Text == "下一頁")
{
pageCurrent++;
if (pageCurrent > pageCount)
{
return;
}
else
{
dtInfo = sp.ExecuteDataTable("DZ_LoginLog", "Id", "Id desc", pageCurrent, pageSize);
}
}
if (e.ClickedItem.Text == "尾頁")
{
pageCurrent++;
if (pageCurrent > pageCount)
{
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
效果：
技術分享