最大的網站源碼資源下載站，
結合一個存儲過程，將分頁做成最簡單，請看以下源碼
此分頁類所操作的存儲過程#region 此分頁類所操作的存儲過程
/**//*********************************************************
*
* 功能強大，配合以下這個存儲過程
*
* *******************************************************/
/**//*
-- pager 1,10,0,0, 'employeeid>2 and employeeid<5 ' , 'employees','*','lastname',0
create procedure pager
@pageindex int,--索引頁 1
@pagesize int,--每頁數量2
@recordcount int out,--總行數3
@pagecount int out,--總頁數4
@wherecondition nvarchar(1000),--查詢條件5
@tablename nvarchar(500),--查詢表名6
@selectstr nvarchar(500) = '*',--查詢的列7
@order nvarchar(500),--排序的列8
@ordertype bit = 0, -- 設置排序類型, 非 0 值則降序 9
@groupby nvarchar(100) = ''
as
declare @strsql nvarchar(2000) -- 主語句
declare @strtmp nvarchar(1000) -- 臨時變量
declare @strorder nvarchar(1000) -- 排序類型
if @ordertype != 0
begin
set @strtmp = '<(select min'
set @strorder = ' order by ' + @order +' desc'
end
else
begin
set @strtmp = '>(select max'
set @strorder = ' order by ' + @order +' asc'
end
set @strsql = 'select top ' + str(@pagesize) + ' ' + @selectstr + ' from '
+ @tablename + ' where ' + @order + '' + @strtmp + '(['
+ @order + ']) from (select top ' + str((@pageindex-1)*@pagesize) + ' ['
+ @order + '] from ' + @tablename + '' + @strorder + ') as tbltmp)'
+ @groupby + @strorder
if @wherecondition != ''
set @strsql = 'select top ' + str(@pagesize) + ' ' + @selectstr + ' from '
+ @tablename + ' where ' + @order + '' + @strtmp + '(['
+ @order + ']) from (select top ' + str((@pageindex-1)*@pagesize) + ' ['
+ @order + '] from ' + @tablename + ' where (' + @wherecondition + ') '
+ @strorder + ') as tbltmp) and (' + @wherecondition + ') ' + @groupby + @strorder
if @pageindex = 1
begin
set @strtmp = ''
if @wherecondition != ''
set @strtmp = ' where (' + @wherecondition + ')'
set @strsql = 'select top ' + str(@pagesize) + ' ' + @selectstr + ' from '
+ @tablename + '' + @strtmp + ' ' + @groupby + @strorder
end
exec (@strsql)
--print @strsql
if @wherecondition <>''
begin
set @strtmp = 'select -1 from ' + @tablename + ' where ' + (@wherecondition)
end
else
begin
set @strtmp = 'select -1 from ' + @tablename
end
exec sp_executesql @strtmp
set @recordcount = @@rowcount
-- 獲取總頁數
-- "ceiling"函數:取得不小于某數的最小整數
set @pagecount = ceiling(@recordcount * 1.0 / @pagesize)
go
*****************************************************************************/
/**//****************************************************************************
*
* 用法
*
* ***************************************************************************/
/**//*
dim ts as string = request.form.item("txtdate")
if (ts = "" or ts is nothing) then
ts = request.querystring("txtdate")
end if
dim ts2 as string = request.form.item("txtdate2")
if (ts2 = "" or ts2 is nothing) then
ts2 = request.querystring("txtdate2")
end if
dim ps as string = request.form.item("pageindex")
if (ps = "" or ps is nothing) then
ps = request.querystring("pageindex")
end if
dim t as integer = 2
dim p as integer = 1
if ts is nothing then
ts = ""
end if
if ps is nothing then
ps = ""
end if
if not (ps = "") then
p = integer.parse(ps)
end if
dim pager as pager = new pager
pager.pageindex = p
pager.pagesize = 20
pager.pagemode = pagemode.str
pager.wherecondition = "thedate between convert(datetime,'" + ts + "') and convert(datetime,'" + ts2 + "')"
'pager.wherecondition = " convert(char(10),thedate,120)= '" + ts + "'"
pager.tablename = "loadcountlog"
pager.selectstr = "*"
pager.order = "id"
pager.ordertype = false
dim dt as system.data.datatable = pager.getdatas(p)
mydatagrid.datasource = dt
mydatagrid.databind()
dim gourl as string = "webform1.aspx?txtdate=" + ts + "&txtdate2=" + ts2
me.label3.text = "共：" + pager.pagecount.tostring + "頁，" + pager.recordcount.tostring() + "條 <strong>" + pager.outpager(pager, gourl, false) + "</strong>"
*/
#endregion
using system;
using system.data;
using system.data.sqlclient;
using system.configuration;
using system.collections;
using system.text;
namespace solucky
{
/**//// <summary>
/// 分頁模式
/// </summary>
public enum pagemode
{
/**//// <summary>
/// 數字分頁
/// </summary>
num =0,
/**//// <summary>
/// 字符分頁
/// </summary>
str =1
}
/**//// <summary>
/// 分頁類,能過存儲過程進行分頁,功能相當強大。
/// </summary>
public class pager
{
private int pageindex = 0;
private int recordcount = 0;
private int pagesize = 20;
private int pagecount = 0;
private int rowcount = 0;
private string tablename = "";
private string wherecondition = "1=1";
private string selectstr = "*";
private string order = "";
private string procedure ="pager";
private bool ordertype = true;
private pagemode pagemode =pagemode.num;
private string sqlconnectionstring = configurationsettings.appsettings["database"];
private string databaseowner = "dbo";
數據連接#region 數據連接
/**//// <summary>
/// 數據連接字符串
/// </summary>
private string sqlconnectionstring
{
get
{
return this.sqlconnectionstring;
}
set
{
this.sqlconnectionstring=value;
}
}
/**//// <summary>
///獲取連接實例
/// </summary>
/// <returns></returns>
private sqlconnection getsqlconnectionstring()
{
try
{
return new sqlconnection(sqlconnectionstring);
}
catch
{
throw new exception("sql connection string is invalid.");
}
}
/**//// <summary>
/// 數據對象所有者
/// </summary>
private string databaseowner
{
get
{
return this.databaseowner;
}
set{
this.databaseowner=value;
}
}
#endregion
public pager()
{
//
// todo: 在此處添加構造函數邏輯
//
//enum.parse(tyo
}
public pager(string connstr )
{
if (connstr!=null)
this.sqlconnectionstring=connstr;
}
#region
/**//// <summary>
/// 所要操作的存儲過程名稱,已有默認的分頁存儲過程
/// </summary>
public string procedure
{
get{
return this.procedure ;
}
set {
if (value==null || value.length <=0)
{
this.procedure="pager";
}
else
{
this.procedure=value;
}
}
}
/**//// <summary>
/// 當前所要顯示的頁面數
/// </summary>
public int pageindex
{
get
{
return this.pageindex;
}
set
{
this.pageindex = value;
}
}
/**//// <summary>
/// 總的頁面數
/// </summary>
public int pagecount
{
get
{
return this.pagecount;
}
set
{
this.pagecount = value;
}
}
/**//// <summary>
/// 總行數
/// </summary>
public int recordcount
{
get
{
return this.recordcount;
}
set
{
this.recordcount = value;
}
}
/**//// <summary>
/// 每頁條數
/// </summary>
public int pagesize
{
get
{
return this.pagesize;
}
set
{
this.pagesize = value;
}
}
/**//// <summary>
/// 表名稱
/// </summary>
public string tablename
{
get
{
return tablename;
}
set
{
this.tablename = value;
}
}
/**//// <summary>
/// 條件查詢
/// </summary>
public string wherecondition
{
get
{
return wherecondition;
}
set
{
wherecondition = value;
}
}
/**//// <summary>
/// 查詢目標(搜索目標),比如:addtime as 時間,id as 編號
/// </summary>
public string selectstr
{
get
{
return selectstr;
}
set
{
selectstr = value;
}
}
/**//// <summary>
/// 排序的列
/// </summary>
public string order
{
get
{
return order;
}
set
{
order = value;
}
}
/**//// <summary>
/// 排序類型 true:asc false:desc
/// </summary>
public bool ordertype
{
get
{
return ordertype;
}
set
{
ordertype = value;
}
}
/**//// <summary>
/// 分頁模式
/// </summary>
public pagemode pagemode
{
get
{
return this.pagemode;
}
set
{
this.pagemode = value;
}
}
/**//// <summary>
/// 得到當前返回的數量
/// </summary>
public int rowcount
{
get
{
return this.rowcount;
}
}
private string groupby;
public string groupby
{
get
{
return this.groupby;
}
set
{
this.groupby = value;
}
}
#endregion
/**//// <summary>
/// 分頁查尋結果
/// </summary>
public datatable getdatas(int pageindex)
{
this.pageindex = pageindex;
pager pager = this;
//pager.pageindex = pageindex;
datatable returntb = pagination(ref pager).tables[0];
this.rowcount = returntb.rows.count;
return returntb;
}
/**//// <summary>
/// 分頁操作存儲過程函數
/// </summary>
/// <param name="pager"></param>
/// <returns></returns>
private dataset pagination(ref pager pager)
{
using ( sqlconnection myconnection = getsqlconnectionstring() )
{
sqldataadapter mycommand = new sqldataadapter(pager.databaseowner + "."+pager.procedure, myconnection);
mycommand.selectcommand.commandtype = commandtype.storedprocedure;
sqlparameter parameterpageindex = new sqlparameter("@pageindex", sqldbtype.int);
parameterpageindex.value = pager.pageindex;
mycommand.selectcommand.parameters.add(parameterpageindex);
sqlparameter parameterpagesize = new sqlparameter("@pagesize", sqldbtype.int);
parameterpagesize.value = pager.pagesize;
mycommand.selectcommand.parameters.add(parameterpagesize);
sqlparameter parameterrecordcount = new sqlparameter("@recordcount", sqldbtype.int);
parameterrecordcount.value = 0;
parameterrecordcount.direction = parameterdirection.inputoutput;
mycommand.selectcommand.parameters.add(parameterrecordcount);
sqlparameter parameterpagecount = new sqlparameter("@pagecount", sqldbtype.int);
parameterpagecount.value = 0;
parameterpagecount.direction = parameterdirection.inputoutput;
mycommand.selectcommand.parameters.add(parameterpagecount);
sqlparameter parameterwherecondition = new sqlparameter("@wherecondition", sqldbtype.nvarchar,500);
parameterwherecondition.value = pager.wherecondition;
mycommand.selectcommand.parameters.add(parameterwherecondition);
sqlparameter parametertablename = new sqlparameter("@tablename", sqldbtype.nvarchar,500);
parametertablename.value = pager.tablename;
mycommand.selectcommand.parameters.add(parametertablename);
sqlparameter parameterorder = new sqlparameter("@order", sqldbtype.nvarchar,500);
parameterorder.value = pager.order;
mycommand.selectcommand.parameters.add(parameterorder);
sqlparameter parameterselectstr = new sqlparameter("@selectstr", sqldbtype.nvarchar,500);
parameterselectstr.value = pager.selectstr;
mycommand.selectcommand.parameters.add(parameterselectstr);
sqlparameter parametergroupby = new sqlparameter("@groupby", sqldbtype.nvarchar, 100);
parametergroupby.value = pager.groupby;
mycommand.selectcommand.parameters.add(parametergroupby);
sqlparameter parameterordertype = new sqlparameter("@ordertype", sqldbtype.bit);
parameterordertype.value = pager.ordertype==false?0:1;
mycommand.selectcommand.parameters.add(parameterordertype);
dataset returnds = new dataset();
//sqldataadapter sqlda = mycommand.crnew sqldataadapter(mycommand);
mycommand.fill(returnds);
pager.pagecount = (int)parameterpagecount.value;
pager.recordcount = (int)parameterrecordcount.value;
return returnds;
}
}
生成分頁#region 生成分頁
/**//// <summary>
/// 生成分頁格式
/// </summary>
/// <param name="pager"></param>
/// <param name="url"></param>
/// <param name="isbr"></param>
/// <returns></returns>
public string outpager(pager pager,string url,bool isbr)
{
stringbuilder returnourwml;
if(isbr)
{
returnourwml= new stringbuilder("["+ pager.pagecount.tostring() + "頁," + pager.recordcount.tostring() +"條]<br/>");
}
else
{
returnourwml = new stringbuilder();
}
if (pager.pagemode == pagemode.num)
{
//分頁每行顯示的數量
int pagerscount = 10;
int pagers = 0;
int startint = 1;
int endint = pager.pagecount;
int i = 1;
string endstr = "";
if (pager.pagecount>pagerscount)
{
//double k = ;
pagers = pager.pageindex / pagerscount;
if (pagers == 0)
{
pagers = 1;
}
else if((pager.pageindex % pagerscount)!=0)
{
pagers +=1;
}
endint = pagers * pagerscount;
if (pager.pageindex <= endint)
{
startint = endint +1 - pagerscount;
if (startint <1)
{
startint = 1;
}
}
//顯示數量不足時pagerscount
if (endint>=pager.pagecount)
{
endint = pager.pagecount;
}
else
{
//if (pager.pageindex)
endstr = " <a href=/"";
endstr += url + "&pageindex=" + (endint + 1).tostring() + "/" title='第"+ (endint + 1).tostring()+"頁'>";
endstr += ">>";
endstr += "</a> ";
}
if (pagers > 1)
{
returnourwml.append(" <a href=/"");
returnourwml.append(url + "&pageindex=" + (startint - 1).tostring() + "/" title='第"+ (startint - 1).tostring()+"頁'>");
returnourwml.append("<<");
returnourwml.append("</a> ");
}
}
for (i = startint; i<=endint;i++)
{
if (i!=pager.pageindex)
{
returnourwml.append(" <a href=/"");
returnourwml.append(url + "&pageindex=" + i.tostring() + "/" title='第"+ i.tostring()+"頁'>");
returnourwml.append("["+i.tostring() + "]");
returnourwml.append("</a> ");
}
else
{
returnourwml.append("<u>"+ i.tostring() + "</u>");
}
}
returnourwml.append(endstr);
return returnourwml.append("<br/>").tostring();
}
else
{
if ( pager.pageindex > 1)
{
returnourwml.append(" <a href=/"");
returnourwml.append(url + "&pageindex=" + (pager.pageindex -1).tostring() + "/">");
returnourwml.append("上一頁");
returnourwml.append("</a> ");
}
if (pager.pageindex < pager.pagecount)
{
returnourwml.append(pager.pageindex.tostring());
returnourwml.append(" <a href=/"");
returnourwml.append(url + "&pageindex=" + (pager.pageindex +1).tostring() + "/">");
returnourwml.append("下一頁");
returnourwml.append("</a> ");
}
return returnourwml.append("<br/>").tostring();
}
}
#endregion
}
}
新聞熱點
疑難解答
圖片精選
