结合一个存储过程，将分页做成最简单，请看以下源码
此分页类所操作的存储过程#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;
}
}
