加入兩個(gè)組件：BindingNavigator和BindingSource
技術(shù)分享
技術(shù)分享
代碼：
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Linq;
usingSystem.Threading.Tasks;
usingSystem.Windows.Forms;
usingDevExpress.XtraEditors;
usingDZAMS.DBUtility;
namespaceDZAMS.Demo
{
publicpartialclassGridPage_Frm:DevExpress.XtraEditors.XtraForm
{
publicDataTabledt=newDataTable();
StoreProceduresp;
privateintpageSize=10;//每頁(yè)顯示行數(shù)
privateintnMax=0;//總記錄數(shù)
privateintpageCount=0;//頁(yè)數(shù)＝總記錄數(shù)/每頁(yè)顯示行數(shù)
privateintpageCurrent=0;//當(dāng)前頁(yè)號(hào)
privateDataSetds=newDataSet();
privateDataTabledtInfo=newDataTable();
publicGridPage_Frm()
{
InitializeComponent();
}
privatevoidGridPage_Frm_Load(objectsender,EventArgse)
{
stringstrQuery=string.Format("SELECTId,UserCode,UserName,RoleName,Ip,Mac,LoginTimeFROMDZ_LoginLog");
dt=SqlHelper.ExecuteDataset(SqlHelper.conn,CommandType.Text,strQuery.ToString()).Tables[0];
gridControl1.DataSource=dt;
stringstrConn="SERVER=(local);DATABASE=DZ;UID=sa;PWD=XXXX";//數(shù)據(jù)庫(kù)連接字符串
SqlConnectionconn=newSqlConnection(strConn);
conn.Open();
stringstrSql="SELECTcount(*)asnumFROMDZ_LoginLog";
SqlDataAdaptersda=newSqlDataAdapter(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=newStoreProcedure("Pr_Monitor_Pagination",strConn);
dtInfo=sp.ExecuteDataTable("DZ_LoginLog","Id","Iddesc",pageCurrent++,pageSize);
InitDataSet();
}
privatevoidInitDataSet()
{
pageCount=(nMax/pageSize);//計(jì)算出總頁(yè)數(shù)
if((nMax%pageSize)>0)pageCount++;
pageCurrent=1;//當(dāng)前頁(yè)數(shù)從1開(kāi)始
LoadData();
}
privatevoidLoadData()
{
lblPageCount.Text="/"+pageCount.ToString();
txtCurrentPage.Text=Convert.ToString(pageCurrent);
this.bdsInfo.DataSource=dtInfo;
this.bdnInfo.BindingSource=bdsInfo;
this.gridControl1.DataSource=bdsInfo;
}
privatevoidbdnInfo_ItemClicked(objectsender,ToolStripItemClickedEventArgse)
{
if(e.ClickedItem.Text=="導(dǎo)出當(dāng)前頁(yè)")
{
SaveFileDialogsaveFileDialog=newSaveFileDialog();
saveFileDialog.Title="導(dǎo)出Excel";
saveFileDialog.Filter="Excel文件(*.xls)|*.xls";
DialogResultdialogResult=saveFileDialog.ShowDialog(this);
if(dialogResult==DialogResult.OK)
{
DevExpress.XtraPrinting.XlsExportOptionsoptions=newDevExpress.XtraPrinting.XlsExportOptions();
gridControl1.ExportToXls(saveFileDialog.FileName,options);
//gridControl1.ExportToExcelOld(saveFileDialog.FileName);
DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
if(e.ClickedItem.Text=="關(guān)閉")
{
this.Close();
}
if(e.ClickedItem.Text=="首頁(yè)")
{
pageCurrent--;
if(pageCurrent<=0)
{
MessageBox.Show("已經(jīng)是首頁(yè)。請(qǐng)點(diǎn)擊“下一頁(yè)”查看！");
return;
}
else
{
pageCurrent=1;
dtInfo=sp.ExecuteDataTable("DZ_LoginLog","Id","Iddesc",pageCurrent,pageSize);
}
}
if(e.ClickedItem.Text=="上一頁(yè)")
{
pageCurrent--;
if(pageCurrent<=0)
{
MessageBox.Show("已經(jīng)是第一頁(yè)，請(qǐng)點(diǎn)擊“下一頁(yè)”查看！");
return;
}
else
{
dtInfo=sp.ExecuteDataTable("DZ_LoginLog","Id","Iddesc",pageCurrent,pageSize);
}
}
if(e.ClickedItem.Text=="下一頁(yè)")
{
pageCurrent++;
if(pageCurrent>pageCount)
{
MessageBox.Show("已經(jīng)是最后一頁(yè)。請(qǐng)點(diǎn)擊“上一頁(yè)”查看。");
return;
}
else
{
dtInfo=sp.ExecuteDataTable("DZ_LoginLog","Id","Iddesc",pageCurrent,pageSize);
}
}
if(e.ClickedItem.Text=="尾頁(yè)")
{
pageCurrent++;
if(pageCurrent>pageCount)
{
MessageBox.Show("已經(jīng)是尾頁(yè)，請(qǐng)點(diǎn)擊“上一頁(yè)”查看。");
return;
}
else
{
pageCurrent=pageCount;
dtInfo=sp.ExecuteDataTable("DZ_LoginLog","Id","Iddesc",pageCount,pageSize);
}
}
LoadData();
}
}
}
StoreProcedure類：
publicclassStoreProcedure
{
//存儲(chǔ)過(guò)程名稱。
privatestring_name;
//數(shù)據(jù)庫(kù)連接字符串。
privatestring_conStr;
//構(gòu)造函數(shù)
//sprocName:存儲(chǔ)過(guò)程名稱；
//conStr:數(shù)據(jù)庫(kù)連接字符串。
publicStoreProcedure(stringsprocName,stringconStr)
{
_conStr=conStr;
_name=sprocName;
}
//運(yùn)行存儲(chǔ)過(guò)程，不返回值。
//paraValues:參數(shù)值列表。
//return:void
publicvoidExecuteNoQuery(paramsobject[]paraValues)
{
using(SqlConnectioncon=newSqlConnection(_conStr))
{
SqlCommandcomm=newSqlCommand(_name,con);
comm.CommandType=CommandType.StoredProcedure;
AddInParaValues(comm,paraValues);
con.Open();
comm.ExecuteNonQuery();
con.Close();
}
}
//運(yùn)行存儲(chǔ)過(guò)程返回一個(gè)表。
//paraValues:參數(shù)值列表。
//return:DataTable
publicDataTableExecuteDataTable(paramsobject[]paraValues)
{
SqlCommandcomm=newSqlCommand(_name,newSqlConnection(_conStr));
comm.CommandType=CommandType.StoredProcedure;
AddInParaValues(comm,paraValues);
SqlDataAdaptersda=newSqlDataAdapter(comm);
DataTabledt=newDataTable();
sda.Fill(dt);
returndt;
}
//運(yùn)行存儲(chǔ)過(guò)程。返回SqlDataReader對(duì)象。
//在SqlDataReader對(duì)象關(guān)閉的同一時(shí)候。數(shù)據(jù)庫(kù)連接自己主動(dòng)關(guān)閉。
//paraValues:要傳遞給給存儲(chǔ)過(guò)程的參數(shù)值類表。
//return:SqlDataReader
publicSqlDataReaderExecuteDataReader(paramsobject[]paraValues)
{
SqlConnectioncon=newSqlConnection(_conStr);
SqlCommandcomm=newSqlCommand(_name,con);
comm.CommandType=CommandType.StoredProcedure;
AddInParaValues(comm,paraValues);
con.Open();
returncomm.ExecuteReader(CommandBehavior.CloseConnection);
}
//獲取存儲(chǔ)過(guò)程的參數(shù)列表。
privateArrayListGetParas()
{
SqlCommandcomm=newSqlCommand("dbo.sp_sproc_columns_90",
newSqlConnection(_conStr));
comm.CommandType=CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@procedure_name",(object)_name);
SqlDataAdaptersda=newSqlDataAdapter(comm);
DataTabledt=newDataTable();
sda.Fill(dt);
ArrayListal=newArrayList();
for(inti=0;i<dt.Rows.Count;i++)
{
al.Add(dt.Rows[i][3].ToString());
}
returnal;
}
//為SqlCommand加入?yún)?shù)及賦值。
privatevoidAddInParaValues(SqlCommandcomm,paramsobject[]paraValues)
{
comm.Parameters.Add(newSqlParameter("@RETURN_VALUE",SqlDbType.Int));
comm.Parameters["@RETURN_VALUE"].Direction=
ParameterDirection.ReturnValue;
if(paraValues!=null)
{
ArrayListal=GetParas();
for(inti=0;i<paraValues.Length;i++)
{
comm.Parameters.AddWithValue(al[i+1].ToString(),
paraValues[i]);
}
}
}
}
存儲(chǔ)過(guò)程：
ALTERprocedure[dbo].[Pr_Monitor_Pagination]
--===============================================Paging===============================================
--Author:Lee
--Createdate:2010\06\11
--Parameter:
--1.Tables:TheNameOfTableorview
--2.PrimaryKey:PrimaryKey
--3.Sort:OrderingStatement，WithoutOrderBy,ForExample：NewsIDDesc,OrderRowsAsc
--4.CurrentPage:ThePageNumberOfCurrentpage
--5.PageSize:TheSizeOfOnePage‘sGroup
--6.Fields:TheFieldOfYouNeeded
--7.Filter:WhereCondition，WithoutWhere
--8.Group:GroupCondition。WithoutGroupBy
--9.GetCount:ReturnTheNumberOfAll,NotZero
--Updates:
--2010\06\09CreateProcedure.
--========================================================================================================
@Tablesvarchar(600),
@PrimaryKeyvarchar(100),
@Sortvarchar(200)=null,
@CurrentPagebigint=1,
@PageSizebigint=10,
@Fieldsvarchar(1000)=‘*‘,
@Filtervarchar(1000)=null,
@Groupvarchar(1000)=null,
@GetCountbit=0
as
if(@GetCount=0)
begin
/*OrderingOfDefault*/
if@Sortisnullor@Sort=‘‘
set@Sort=@PrimaryKey
declare@SortTablevarchar(100)
declare@SortNamevarchar(100)
declare@strSortColumnvarchar(200)
declare@operatorchar(2)
declare@typevarchar(100)
declare@precint
/*SettingConditionOfOrdering*/
ifcharindex(‘desc‘,@Sort)>0
begin
set@strSortColumn=replace(@Sort,‘desc‘,‘‘)
set@operator=‘<=‘
end
else
begin
ifcharindex(‘a(chǎn)sc‘,@Sort)=0
set@strSortColumn=replace(@Sort,‘a(chǎn)sc‘,‘‘)
set@operator=‘>=‘
end
ifcharindex(‘.‘,@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.precfromsysobjectsojoinsyscolumnscono.id=c.idjoinsystypestonc.xusertype=t.xusertypewhereo.name=@SortTableandc.name=@SortName
ifcharindex(‘char‘,@type)>0
set@type=@type+‘(‘+cast(@precasvarchar)+‘)‘
declare@strPageSizevarchar(50)
declare@strStartRowvarchar(50)
declare@strFiltervarchar(1000)
declare@strSimpleFiltervarchar(1000)
declare@strGroupvarchar(1000)
/*CurrentPageOfDefault*/
if@CurrentPage<1
set@CurrentPage=1
/*SettingPagingparam*/
set@strPageSize=cast(@PageSizeasvarchar(50))
set@strStartRow=cast(((@CurrentPage-1)*@PageSize+1)asvarchar(50))
/*ConditionOfFilterAndGroup*/
if@Filterisnotnulland@Filter!=‘‘
begin
set@strFilter=‘where‘+@Filter+‘‘
set@strSimpleFilter=‘a(chǎn)nd‘+@Filter+‘‘
end
else
begin
set@strSimpleFilter=‘‘
set@strFilter=‘‘
end
if@Groupisnotnulland@Group!=‘‘
set@strGroup=‘groupby‘+@Group+‘‘
else
set@strGroup=‘‘
exec(‘declare@SortColumn‘+@type+‘setRowCount‘+@strStartRow+‘select@SortColumn=‘+@strSortColumn+‘from‘+@Tables+@strFilter+‘‘+@strGroup+‘Orderby‘+@Sort+‘setrowcount‘+@strPageSize+‘select‘+@Fields+‘from‘+@Tables+‘where‘+@strSortColumn+@operator+‘@SortColumn‘+@strSimpleFilter+‘‘+@strGroup+‘Orderby‘+@Sort+‘‘)
end
else
begin
declare@strSQLvarchar(5000)
if@Filter!=‘‘
set@strSQL=‘selectcount(‘+@PrimaryKey+‘)asTotalfrom[‘+@Tables+‘]where‘+@Filter
else
set@strSQL=‘selectcount(‘+@PrimaryKey+‘)asTotalfrom[‘+@Tables+‘]‘
exec(@strSQL)
end
效果：
技術(shù)分享