一个通用的分页类

结合一个存储过程，将分页做成最简单，请看以下源码

此分页类所操作的存储过程#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>"

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有