ASP.net 2.0 自定义控件的开发之数据分页-技术开发专区

ASP.net 2.0 自定义控件的开发之数据分页

作者：shadowsky 编辑：阿雪 2007-06-01 10:31

【IT168技术文档】本文将向大家介绍使用ASP.net 2.0 开发自定义的数据分页控件

数据分页的好处 1、减少网络流量 2、提高运行效率

在大数据量时，数据分页是一个必须面对的现实，没有哪个客户愿意花上几分钟或更多的时间来等待。数据分页也有多种方式比如内存分页 SQL Server 存储过程分页等，这里将向大家介绍的是 SQL Server 存储过程分页方式，并结合ASP.net 2.0 的来编写一个通用的数据分页控件。

本文使用的存储过程来自 http://www.codeproject.com/aspnet/PagingLarge.asp 在这里感谢作者，他为我们提供了多种存储过程分页方法的速度对比。

现在启动你的Visual Studio 2005 然后新建一个类库项目取名为SqlPaging 如下图

然后添加两个类文件取名为AssemblyInfo及SqlPaging如下图

然后打开本工程的属性对话框将程序集名称设置SqlPaging 将根命名空间设置为DRMSystem 将应用程序类型设置为 Web 控件库如下图

打开 AssemblyInfo类输入如下代码

Imports System
Imports System.Web.UI
<Assembly: TagPrefix("DRMSystem.DataLayer", "drmsp")>

'定义在网页中用于标识自定义控件的标记前缀。

打开 SqlPaging 类输入如下代码（导入程序所用到的命名空间。）

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Text
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Security.Permissions
'定义转到按钮的风格
Public Enum StyleGoTo
LinkButton = 1
ImageButton = 2
End Enum

输入如下代码添加一个命名空间 DataLayer 在添加一个名为 SqlPaging 的类。这里我们只讲解ToolboxData它表示当
从 Microsoft Visual Studio 等工具中的工具箱拖动自定义控件时为它生成的默认标记。

Namespace DataLayer
<AspNetHostingPermission(SecurityAction.Demand, _
Level:=AspNetHostingPermissionLevel.Minimal), _
AspNetHostingPermission(SecurityAction.InheritanceDemand, _
Level:=AspNetHostingPermissionLevel.Minimal), _
DefaultProperty("PageInfo"), _
ToolboxData("<{0}:SqlPaging runat=""server""> </{0}:SqlPaging>")> _
Public Class SqlPaging
Inherits CompositeControl
End Class
End Namespace

以下为控件的主要部分 (UI 部分)

通知使用基于合成的实现的服务器控件创建它们包含的任何子控件，以便为回发或呈现做准备。

 Protected Overrides Sub CreateChildControls()Sub CreateChildControls()
Controls.Clear()
labPageInfo = New Label()
labPageInfoText_01 = New Label
labPageInfoText_01.Text = "每页"
txtPageSize = New TextBox
txtPageSize.ID = "txtPageSize"
txtPageSize.Width = 66
labPageInfoText_02 = New Label
labPageInfoText_02.Text = "条记录"
labPageInfoText_03 = New Label
labPageInfoText_03.Text = "转到"
labPageInfoText_04 = New Label
labPageInfoText_04.Text = "页"
txtPageIndex = New TextBox
txtPageIndex.ID = "txtPageIndex"
txtPageIndex.Width = 66
ibGotoPage = New ImageButton
ibGotoPage.ID = "ibGotoPage"
ibGotoPage.Width = 60
ibGotoPage.Height = 20
AddHandler ibGotoPage.Click, AddressOf ibGotoPage_Click
lbGotoPage = New LinkButton
lbGotoPage.ID = "lbGotoPage"
lbGotoPage.Text = "转到"
AddHandler lbGotoPage.Click, AddressOf lbGotoPage_Click
lbFirstPage = New LinkButton
lbFirstPage.ID = "lbFirstPage"
lbFirstPage.Text = "首页"
AddHandler lbFirstPage.Click, AddressOf lbFirstPage_Click
lbPrevPage = New LinkButton
lbPrevPage.ID = "lbPrevPage"
lbPrevPage.Text = "上页"
AddHandler lbPrevPage.Click, AddressOf lbPrevPage_Click
lbNextPage = New LinkButton
lbNextPage.ID = "lbNextPage"
lbNextPage.Text = "下页"
AddHandler lbNextPage.Click, AddressOf lbNextPage_Click
lbLastPage = New LinkButton
lbLastPage.ID = "lbLastPage"
lbLastPage.Text = "末页"
AddHandler lbLastPage.Click, AddressOf lbLastPage_Click
Me.Controls.Add(labPageInfo)
Me.Controls.Add(labPageInfoText_01)
Me.Controls.Add(txtPageSize)
Me.Controls.Add(labPageInfoText_02)
Me.Controls.Add(labPageInfoText_03)
Me.Controls.Add(txtPageIndex)
Me.Controls.Add(ibGotoPage)
Me.Controls.Add(lbGotoPage)
Me.Controls.Add(lbFirstPage)
Me.Controls.Add(lbPrevPage)
Me.Controls.Add(lbNextPage)
Me.Controls.Add(lbLastPage)
End Sub

重写控件的输出。

  Protected Overrides Sub Render()Sub Render(ByVal writer As HtmlTextWriter)
AddAttributesToRender(writer)
writer.AddAttribute(HtmlTextWriterAttribute.Cellpadding, "1", False)
writer.AddAttribute(HtmlTextWriterAttribute.Width, "100%", False)
writer.RenderBeginTag(HtmlTextWriterTag.Table)
writer.RenderBeginTag(HtmlTextWriterTag.Tr)
writer.RenderBeginTag(HtmlTextWriterTag.Td)
writer.AddStyleAttribute(HtmlTextWriterStyle.Width, "50%")
labPageInfo.Text = "共 " & RecordCount & "条记录 共 " & PageCount & 
            " 页 当前第" & CurrentPageIndex & "页"
labPageInfo.RenderControl(writer)
writer.RenderEndTag()
writer.RenderBeginTag(HtmlTextWriterTag.Td)
writer.AddStyleAttribute(HtmlTextWriterStyle.Width, "50%")
labPageInfoText_01.RenderControl(writer)
writer.WriteLine(" ")
txtPageSize.RenderControl(writer)
writer.WriteLine(" ")
labPageInfoText_02.RenderControl(writer)
writer.WriteLine(" ")
If GoToStyle = StyleGoTo.ImageButton Then
labPageInfoText_03.RenderControl(writer)
Else
lbGotoPage.RenderControl(writer)
End If
writer.WriteLine(" ")
txtPageIndex.RenderControl(writer)
writer.WriteLine(" ")
labPageInfoText_04.RenderControl(writer)
If GoToStyle = StyleGoTo.ImageButton Then
ibGotoPage.Attributes.Add("onmouseover", "this.src='" +
                HoverImageUrl + "';")
ibGotoPage.Attributes.Add("onmouseout", "this.src='" + 
                DefaultImageUrl + "';")
ibGotoPage.Attributes.Add("onmouseDown", "this.src='" + 
                PressedImageUrl + "';")
ibGotoPage.RenderControl(writer)
End If
writer.WriteLine("&nbsp;")
lbFirstPage.RenderControl(writer)
writer.WriteLine("&nbsp;")
lbPrevPage.RenderControl(writer)
writer.WriteLine("&nbsp;")
lbNextPage.RenderControl(writer)
writer.WriteLine("&nbsp;")
lbLastPage.RenderControl(writer)
writer.RenderEndTag()
writer.RenderEndTag()
writer.RenderEndTag()
End Sub

由复合控件的标准设计器用于在设计时重建控件树。

MSDN 中的解释重新创建派生自 CompositeControl 的控件的子控件。

  Protected Overrides Sub RecreateChildControls()Sub RecreateChildControls()
EnsureChildControls()
End Sub

数据库分页部分，后面将给出分页的SQL Server 存储过程。

Protected Function GoToPage()Function GoToPage() As DataTable
Dim dt As New DataTable()
Conn = New SqlConnection(SQLConnection)
Conn.Open()
Using Conn
Dim Command As New SqlCommand
Command.Connection = Conn
Command.CommandText = StoredProcedureName
Command.CommandType = CommandType.StoredProcedure
Dim Parameter As SqlParameter
Parameter = Command.Parameters.Add("@Table", SqlDbType.NVarChar)
Parameter.Value = TableName
Parameter = Command.Parameters.Add("@PrimaryKeyField", 
                            SqlDbType.NVarChar)
Parameter.Value = PrimaryKeyField
Parameter = Command.Parameters.Add("@Field", SqlDbType.NVarChar)
Parameter.Value = Field
Parameter = Command.Parameters.Add("@Where", SqlDbType.NVarChar)
Parameter.Value = Where
Parameter = Command.Parameters.Add("@GroupBy",SqlDbType.NVarChar)
Parameter.Value = GroupBy
Parameter = Command.Parameters.Add("@OrderBy",SqlDbType.NVarChar)
Parameter.Value = OrderBy
Parameter = Command.Parameters.Add("@PageNumber", 
                            SqlDbType.NVarChar)
Parameter.Value = CurrentPageIndex
Parameter = Command.Parameters.Add("@PageSize", 
                            SqlDbType.NVarChar)
Parameter.Value = PageSize
Parameter = Command.Parameters.Add("@RecordCount",SqlDbType.Int)
Parameter.Direction = ParameterDirection.Output
Command.ExecuteNonQuery()
intRecordCount = Command.Parameters("@RecordCount").Value
ViewState("RecordCount") = intRecordCount
ViewState("PageCount") = Fix(intRecordCount / PageSize) + 1
Dim da As New SqlDataAdapter(Command)
da.Fill(dt)
Return dt
End Using
End Function

数据库分页部分，后面将给出分页的SQL Server 存储过程。

数据绑定代码

Public Overrides Sub DataBind()Sub DataBind()
'查找页面中的GridView及DataList并绑定数据
            Dim objGridView As New GridView
Dim objDataList As New DataList
If ControlToPaginate = "" Then
Exit Sub
End If
_controlToPaginat = Page.FindControl(ControlToPaginate)
If (_controlToPaginat Is Nothing) Then
Exit Sub
End If
If (TypeOf _controlToPaginat Is GridView) Then
objGridView = CType(_controlToPaginat, GridView)
objGridView.DataSource = GoToPage()
objGridView.DataBind()
End If
If (TypeOf _controlToPaginat Is DataList) Then
objDataList = CType(_controlToPaginat, DataList)
objDataList.DataSource = GoToPage.DefaultView
objDataList.DataBind()
End If
End Sub

控件的属性及事件定义

 Private Shared ReadOnly EventibGotoPageClick As New Object()
Private Shared ReadOnly EventlbGotoPageClick As New Object()
Private Conn As SqlConnection
Private _StoredProcedureName As String
Private _ControlToDataBind As String
Private _GoToStyle As StyleGoTo = StyleGoTo.LinkButton
Private intPageCount As Int32 = 0
Private intRecordCount As Int32 = 0
Private intPageSize As Int32
Private _DefaultImageUrl As String
Private _HoverImageUrl As String
Private _PressedImageUrl As String
Private _controlToPaginat As Control
Private labPageInfo As Label
Private labPageInfoText_01 As Label
Private labPageInfoText_02 As Label
Private labPageInfoText_03 As Label
Private labPageInfoText_04 As Label
Private txtPageSize As TextBox
Private txtPageIndex As TextBox
Private ibGotoPage As ImageButton
Private lbGotoPage As LinkButton
Private lbFirstPage As LinkButton
Private lbPrevPage As LinkButton
Private lbNextPage As LinkButton
Private lbLastPage As LinkButton
属性定义#Region "属性定义"
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
        < _
Bindable(True), _
Category("PageInfo"), _
DefaultValue(""), _
Description("设置页的信息(例如：当前 100 条记录...).") _
> _
Public Property PageInfoText()Property PageInfoText() As String
Get
EnsureChildControls()
Return labPageInfo.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
labPageInfo.Text = value
End Set
End Property
< _
Bindable(True), _
Category("PageInfo"), _
DefaultValue(""), _
Description("设置显示 每页 字符.") _
> _
Public Property PageInfoText_01()Property PageInfoText_01() As String
Get
EnsureChildControls()
Return labPageInfoText_01.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
labPageInfoText_01.Text = value
End Set
End Property
< _
Bindable(True), _
Category("PageInfo"), _
DefaultValue(""), _
Description("设置显示 条记录 字符.") _
> _
Public Property PageInfoText_02()Property PageInfoText_02() As String
Get
EnsureChildControls()
Return labPageInfoText_02.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
labPageInfoText_02.Text = value
End Set
End Property
< _
Bindable(True), _
Category("PageInfo"), _
DefaultValue(""), _
Description("设置显示 转到 字符.") _
> _
Public Property PageInfoText_03()Property PageInfoText_03() As String
Get
EnsureChildControls()
Return labPageInfoText_03.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
labPageInfoText_03.Text = value
End Set
End Property
< _
Bindable(True), _
Category("PageInfo"), _
DefaultValue(""), _
Description("设置显示 页 字符.") _
> _
Public Property PageInfoText_04()Property PageInfoText_04() As String
Get
EnsureChildControls()
Return labPageInfoText_04.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
labPageInfoText_04.Text = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("页大小 Textbox 边框的宽度.") _
> _
Public Property TextboxBorderWidth()Property TextboxBorderWidth() As Unit
Get
EnsureChildControls()
Return txtPageSize.BorderWidth
End Get
Set(ByVal value As Unit)
EnsureChildControls()
txtPageSize.BorderWidth = value
txtPageIndex.BorderWidth = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("页大小 Textbox 边框的颜色.") _
> _
Public Property TextboxBorderColor()Property TextboxBorderColor() As 
                  System.Drawing.Color
Get
EnsureChildControls()
Return txtPageSize.BorderColor
End Get
Set(ByVal value As System.Drawing.Color)
EnsureChildControls()
txtPageSize.BorderColor = value
txtPageIndex.BorderColor = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("页大小 Textbox 的SkinID.") _
> _
Public Property TextboxSkinID()Property TextboxSkinID() As String
Get
EnsureChildControls()
Return txtPageSize.SkinID
End Get
Set(ByVal value As String)
EnsureChildControls()
txtPageSize.SkinID = value
txtPageIndex.SkinID = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("设置转到的的风格.") _
> _
Public Property GoToStyle()Property GoToStyle() As StyleGoTo
Get
EnsureChildControls()
Return Me._GoToStyle
End Get
Set(ByVal value As StyleGoTo)
EnsureChildControls()
Me._GoToStyle = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("设置转到的按钮默认图片.") _
> _
Public Property DefaultImageUrl()Property DefaultImageUrl() As String
Get
EnsureChildControls()
Return ibGotoPage.ImageUrl
End Get
Set(ByVal value As String)
EnsureChildControls()
ibGotoPage.ImageUrl = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("设置当鼠标指针在按钮上时显示的图片.") _
> _
Public Property HoverImageUrl()Property HoverImageUrl() As String
Get
EnsureChildControls()
Return Me._HoverImageUrl
End Get
Set(ByVal value As String)
EnsureChildControls()
Me._HoverImageUrl = value
End Set
End Property
< _
Bindable(True), _
Category("Style"), _
DefaultValue(""), _
Description("设置当鼠标按下时显示的图片.") _
> _
Public Property PressedImageUrl()Property PressedImageUrl() As String
Get
EnsureChildControls()
Return Me._PressedImageUrl
End Get
Set(ByVal value As String)
EnsureChildControls()
Me._PressedImageUrl = value
End Set
End Property
< _
Bindable(True), _
Category("Size"), _
DefaultValue(""), _
Description("设置 PageSize 文本框宽度.") _
> _
Public Property PageSizeWidth()Property PageSizeWidth() As Unit
Get
EnsureChildControls()
Return txtPageSize.Width
End Get
Set(ByVal value As Unit)
EnsureChildControls()
txtPageSize.Width = value
End Set
End Property
< _
Bindable(True), _
Category("Size"), _
DefaultValue(""), _
Description("设置 PageIndex 文本框宽度.") _
> _
Public Property PageIndexWidth()Property PageIndexWidth() As Unit
Get
EnsureChildControls()
Return txtPageIndex.Width
End Get
Set(ByVal value As Unit)
EnsureChildControls()
txtPageIndex.Width = value
End Set
End Property
< _
Bindable(True), _
Category("Size"), _
DefaultValue(""), _
Description("设置 转到 按钮的宽度.") _
> _
Public Property ibGotoPageWidth()Property ibGotoPageWidth() As Unit
Get
EnsureChildControls()
Return ibGotoPage.Width
End Get
Set(ByVal value As Unit)
EnsureChildControls()
ibGotoPage.Width = value
End Set
End Property
< _
Bindable(True), _
Category("Size"), _
DefaultValue(""), _
Description("设置 转到 按钮的高度.") _
> _
Public Property ibGotoPageHeight()Property ibGotoPageHeight() As Unit
Get
EnsureChildControls()
Return ibGotoPage.Height
End Get
Set(ByVal value As Unit)
EnsureChildControls()
ibGotoPage.Height = value
End Set
End Property
< _
Bindable(True), _
Category("Text"), _
DefaultValue(""), _
Description("设置 转到 链接按钮的文字.") _
> _
Public Property lbGotoPageText()Property lbGotoPageText() As String
Get
EnsureChildControls()
Return lbGotoPage.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
lbGotoPage.Text = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置链接数据库的字符串.") _
> _
Public Property SQLConnection()Property SQLConnection() As String
Get
EnsureChildControls()
Return ViewState("SQLConnection")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("SQLConnection") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置表名.") _
> _
Public Property TableName()Property TableName() As String
Get
EnsureChildControls()
Return ViewState("TableName")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("TableName") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置分页数据的主键.") _
> _
Public Property PrimaryKeyField()Property PrimaryKeyField() As String
Get
EnsureChildControls()
Return ViewState("PrimaryKeyField")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("PrimaryKeyField") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置返回的字段.") _
> _
Public Property Field()Property Field() As String
Get
EnsureChildControls()
Return ViewState("Field")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("Field") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置 WHERE 条件.") _
> _
Public Property Where()Property Where() As String
Get
EnsureChildControls()
Return ViewState("Where")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("Where") = value
End Set
End Property
< _
Browsable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置排序方式.") _
> _
Public Property OrderBy()Property OrderBy() As String
Get
EnsureChildControls()
Return ViewState("OrderBy")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("OrderBy") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("设置分组方式.") _
> _
Public Property GroupBy()Property GroupBy() As String
Get
EnsureChildControls()
Return ViewState("GroupBy")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("GroupBy") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
TypeConverter(GetType(ControlToDataBindConverter)), _
Description("设置要分页的控件.") _
> _
Public Property ControlToPaginate()Property ControlToPaginate() As String
Get
EnsureChildControls()
Return ViewState("ControlToPaginate")
End Get
Set(ByVal value As String)
EnsureChildControls()
ViewState("ControlToPaginate") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(1), _
Description("设置当前页.") _
> _
Public Property CurrentPageIndex()Property CurrentPageIndex() As Int32
Get
EnsureChildControls()
Return ViewState("CurrentPageIndex")
End Get
Set(ByVal value As Int32)
EnsureChildControls()
ViewState("CurrentPageIndex") = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("每页显示数据的大小.") _
> _
Public Property PageSize()Property PageSize() As Int32
Get
EnsureChildControls()
If Not IsNumeric(txtPageSize.Text) Then
txtPageSize.Text = 10
Else
If CInt(txtPageSize.Text) < 1 Then
txtPageSize.Text = 10
End If
End If
Return CInt(txtPageSize.Text)
End Get
Set(ByVal value As Int32)
EnsureChildControls()
txtPageSize.Text = value
End Set
End Property
< _
Bindable(True), _
Category("Database"), _
DefaultValue(""), _
Description("将要转到的页数.") _
> _
Public Property PageIndex()Property PageIndex() As String
Get
EnsureChildControls()
Return txtPageIndex.Text
End Get
Set(ByVal value As String)
EnsureChildControls()
txtPageIndex.Text = value
End Set
End Property
< _
Bindable(False), _
Category("Database"), _
DefaultValue(""), _
TypeConverter(GetType(StoredProcedureNameConverter)), _
Description("选择存储过程。") _
> _
Public Property StoredProcedureName()Property StoredProcedureName() As String
Get
Return _StoredProcedureName
End Get
Set(ByVal Value As String)
_StoredProcedureName = Value
End Set
End Property
< _
Bindable(False), _
Category("About"), _
DefaultValue(""), _
Description("作者：江建 QQ:33512603" & vbCrLf & "编程浪子：http://vbcc.126.com") _
> _
Public ReadOnly Property About()Property About() As String
Get
Return "作者：江建 QQ:33512603" & vbCrLf & "编程浪子：http://vbcc.126.com"
End Get
End Property
<Browsable(False)> _
Public ReadOnly Property PageCount()Property PageCount() As Int32
Get
EnsureChildControls()
Return ViewState("PageCount")
End Get
End Property
<Browsable(False)> _
Public ReadOnly Property RecordCount()Property RecordCount() As Int32
Get
EnsureChildControls()
Return ViewState("RecordCount")
End Get
End Property
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
#End Region
事件定义#Region "事件定义"
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
        Private Sub ibGotoPage_Click()Sub ibGotoPage_Click(ByVal sender As Object, 
             ByVal e As ImageClickEventArgs)
If Not IsNumeric(PageIndex) Then
PageIndex = CurrentPageIndex
End If
If PageIndex < 1 Then
PageIndex = 1
ElseIf PageIndex > PageCount Then
PageIndex = PageCount
End If
CurrentPageIndex = PageIndex
Call DataBind()
End Sub
Private Sub lbGotoPage_Click()Sub lbGotoPage_Click(ByVal source As Object, 
                 ByVal e As EventArgs)
If Not IsNumeric(PageIndex) Then
PageIndex = CurrentPageIndex
End If
If PageIndex < 1 Then
PageIndex = 1
ElseIf PageIndex > PageCount Then
PageIndex = PageCount
End If
CurrentPageIndex = PageIndex
Call DataBind()
End Sub
Private Sub lbFirstPage_Click()Sub lbFirstPage_Click(ByVal source As Object, 
                    ByVal e As EventArgs)
CurrentPageIndex = 1
Call DataBind()
End Sub
Private Sub lbPrevPage_Click()Sub lbPrevPage_Click(ByVal source As Object, 
            ByVal e As EventArgs)
If CurrentPageIndex > 1 Then
CurrentPageIndex -= 1
End If
Call DataBind()
End Sub
Private Sub lbNextPage_Click()Sub lbNextPage_Click(ByVal source As Object, 
             ByVal e As EventArgs)
If CurrentPageIndex < PageCount Then
CurrentPageIndex += 1
End If
Call DataBind()
End Sub
Private Sub lbLastPage_Click()Sub lbLastPage_Click(ByVal source As Object, 
                  ByVal e As EventArgs)
CurrentPageIndex = PageCount
Call DataBind()
End Sub
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
#End Region

下面的代码用于控件的下拉列表属性页

Public Class StoredProcedureNameConverterClass StoredProcedureNameConverter
Inherits StringConverter
'存储过程 有兴趣可以添加你自己的存储过程
        '要用下拉列表编辑属性 
        Public Overrides Function GetStandardValuesSupported()Function 
           GetStandardValuesSupported(ByVal context As ITypeDescriptorContext) As Boolean
Return True
End Function
'这个override返回下拉列表项。 
        Public Overrides Function GetStandardValues()Function GetStandardValues
          (ByVal context As ITypeDescriptorContext) As StandardValuesCollection
Dim StringArray As ArrayList = New ArrayList()
StringArray.Add("sys_QuickSortPaging")
StringArray.Add("sys_SortDataPager")
Return New StandardValuesCollection(StringArray)
End Function
'Return True的话只能选，Return flase可选可填 
        Public Overrides Function GetStandardValuesExclusive()Function 
        GetStandardValuesExclusive(ByVal context As ITypeDescriptorContext) As Boolean
Return True
End Function
End Class
Public Class ControlToDataBindConverterClass ControlToDataBindConverter
Inherits StringConverter
'列出可以绑定的控件
        '要用下拉列表编辑属性 
        Public Overrides Function GetStandardValuesSupported()Function 
        GetStandardValuesSupported(ByVal context As ITypeDescriptorContext) As Boolean
Return True
End Function
'这个override返回下拉列表项。 
        Public Overrides Function GetStandardValues()Function GetStandardValues
           (ByVal context As ITypeDescriptorContext) As StandardValuesCollection
Dim StringArray As ArrayList = New ArrayList()
Dim I As Long
Dim objControl As ControlCollection
objControl = CType(context.Container.Components(0), Page).Controls
For I = 0 To objControl.Count - 1
If TypeOf objControl(I) Is GridView Or TypeOf objControl(I) 
                       Is DataList Then
StringArray.Add(objControl(I).ClientID)
End If
Next
Return New StandardValuesCollection(StringArray)
End Function
'Return True的话只能选，Return false可选可填 
        Public Overrides Function GetStandardValuesExclusive()Function 
          GetStandardValuesExclusive(ByVal context As ITypeDescriptorContext) As Boolean
Return False
End Function
End Class

存储过程为 SQL Server 2000版本，请打开SQL server 2000 的查询分析器执行下面的SQL 语句。
程序用到的存储过程（仅支持主键排序）

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_QuickSortPaging]') 
        and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sys_QuickSortPaging]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE  sys_QuickSortPaging
(
@Table nvarchar(4000),            --表名(必须)
@PrimaryKeyField nvarchar(50),        --表的主键字段
@Field nvarchar (4000)='*',         --需要返回字段名(必须)
@Where nvarchar(1000)=NULL,        --Where 条件(可选)
@GroupBy nvarchar(1000) = NULL,     --分组
@OrderBy nvarchar(1000)=NULL,     --排序用到的字段()
@PageNumber int = 1,             --要返回的页(第X页) (默认为第一页)
@PageSize int = 10,            --每页大小(默认为5)
@RecordCount int output        --返回记录总数
)
AS
SET NOCOUNT ON
DECLARE @SortTable nvarchar(100)
DECLARE @SortName nvarchar(100)
DECLARE @strSortColumn nvarchar(200)
DECLARE @Operator nvarchar(50)
DECLARE @Type varchar(100)
DECLARE @Prec int
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy =  @PrimaryKeyField
/**//* 获取用于定位的字段*/
IF CHARINDEX('DESC',@OrderBy)>0
BEGIN
SET @strSortColumn = REPLACE(@OrderBy, 'DESC', '')
SET @Operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @OrderBy) = 0
SET @strSortColumn = REPLACE(@OrderBy, '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 = @Table
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 nvarchar) + ')'
DECLARE @strStartRow nvarchar(50)
DECLARE @strPageSize nvarchar(50)
DECLARE @strWhere nvarchar(1000)
DECLARE @strWhereAnd nvarchar(1000)
DECLARE @strGroupBy nvarchar(1000)
IF  @PageNumber < 1
SET @PageNumber = 1
SET @strPageSize = CONVERT (nvarchar(50), @PageSize)
SET @strStartRow = CONVERT ( nvarchar(50), (@PageNumber - 1)*@PageSize + 1)
IF @Where IS NOT NULL AND @Where !=''
BEGIN
SET @strWhere = ' WHERE '+ @Where
SET @strWhereAnd= ' AND ' + @Where
END
ELSE
BEGIN
SET @strWhere =  ''
SET @strWhereAnd=''
END
IF @GroupBy IS NOT NULL AND @GroupBy != ''
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy
END
ELSE
BEGIN
SET @strGroupBy = ''
END
DECLARE @strSQL nvarchar(4000)
SET @strSql= ' SELECT  @RecordCount = Count (*)  FROM ' + @Table + @strWhere + ' ' 
                    + @strGroupBy
EXEC   sp_executesql   @strSql,N'@RecordCount   int   OUTPUT',@RecordCount   
            OUTPUT--计算总页数
EXEC
(
'
        DECLARE @Sort ' + @Type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT  @Sort = ' + @strSortColumn + '  FROM ' + @Table + @strWhere + ' ' 
                         + @strGroupBy + ' ORDER BY ' + @OrderBy + '
SET ROWCOUNT ' + @strPageSize + '
SELECT '+@Field+'  FROM ' + @Table + ' WHERE ' + @strSortColumn  + @Operator  +
               ' @Sort ' + @strWhereAnd + ' '  + @strGroupBy + ' ORDER BY ' +  @OrderBy
        )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

支持任意字段排序的存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_SortDataPager]')
           and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sys_SortDataPager]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sys_SortDataPager (
@Table nvarchar(4000),            --表名(必须)
@PrimaryKeyField nvarchar(50),        --表的主键字段
@Field nvarchar (4000)='*',         --需要返回字段名(必须)
@Where nvarchar(1000)=NULL,        --Where 条件(可选)
@GroupBy nvarchar(1000) = NULL,     --分组
@OrderBy nvarchar(1000)=NULL,     --排序用到的字段()
@PageNumber int = 1,             --要返回的页(第X页) (默认为第一页)
@PageSize int = 10,            --每页大小(默认为5)
@RecordCount int out            --返回记录总数
)
AS
/**//*Find the @PrimaryKeyField type*/
DECLARE @PKTable varchar(1000)
DECLARE @PKName varchar(1000)
DECLARE @type varchar(1000)
DECLARE @prec int
IF CHARINDEX('.', @PrimaryKeyField) > 0
BEGIN
SET @PKTable = SUBSTRING(@PrimaryKeyField, 0, CHARINDEX('.',@PrimaryKeyField))
SET @PKName = SUBSTRING(@PrimaryKeyField, CHARINDEX('.',@PrimaryKeyField) + 1,
            LEN(@PrimaryKeyField))
END
ELSE
BEGIN
SET @PKTable = @Table
SET @PKName = @PrimaryKeyField
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 = @PKTable AND c.name = @PKName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strWhere varchar(1000)
DECLARE @strGroupBy varchar(1000)
/**//*Default Sorting*/
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy = @PrimaryKeyField
/**//*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/**//*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
/**//*Set filter & group variables.*/
IF @Where IS NOT NULL AND @Where != ''
SET @strWhere = ' WHERE ' + @Where + ' '
ELSE
SET @strWhere = ''
IF @GroupBy IS NOT NULL AND @GroupBy != ''
SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
ELSE
SET @strGroupBy = ''
/**//*Execute dynamic query*/
DECLARE @strSQL nvarchar(4000)
SET @strSql= ' SELECT  @RecordCount = Count (*)  FROM ' + @Table + @strWhere + ' '
                  + @strGroupBy
EXEC   sp_executesql   @strSql,N'@RecordCount   int   OUTPUT',@RecordCount 
                OUTPUT--计算总页数
EXEC(
'DECLARE @PageSize int
    SET @PageSize = ' + @strPageSize + '
DECLARE @PrimaryKeyField ' + @type + '
DECLARE @tblPK TABLE (
PK  ' + @type + ' NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT '  + @PrimaryKeyField + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy +
            ' ORDER BY ' + @OrderBy + '
OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PrimaryKeyField
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK)  VALUES (@PrimaryKeyField)
FETCH NEXT FROM PagingCursor INTO @PrimaryKeyField
SET @PageSize = @PageSize - 1
END
CLOSE       PagingCursor
DEALLOCATE  PagingCursor
SELECT ' + @Field + ' FROM ' + @Table + ' JOIN @tblPK tblPK ON ' + @PrimaryKeyField +
           ' = tblPK.PK ' + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy
    )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

创建一个用于测试的表

CREATE TABLE [dbo].[Employees] (
[EmployeesID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[Address] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[City] [nvarchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[HomePhone] [nvarchar] (24) COLLATE Chinese_PRC_CI_AS NULL ,
[Extension] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

生成 1000000 条测试数据

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertTableData]')
     and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertTableData]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertTableData
AS
DECLARE @cnt bigint
SET @cnt = 0
WHILE @cnt < 1000000
BEGIN
INSERT INTO Employees(LastName,FirstName,BirthDate,Address,City,HomePhone,Extension)
Values
(
CAST('LastName ' +CONVERT(nvarchar(10), @cnt) as nvarchar(30)),
CAST('FirstName '+  CONVERT(nvarchar(10), @cnt) as nvarchar(30)),
GETDATE(),
CAST('Address IS No.'+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)),
CAST('City  '+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)),
CAST('021-0000'+ LEFT(CONVERT(nvarchar(10), @cnt),4) as nvarchar(30)),
CAST('00' +  LEFT(CONVERT(nvarchar(10), @cnt) ,1) as nvarchar(30))
)
SET @cnt = @cnt + 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

新建一个网站

将控件添加到工具箱中，在工具箱中点击鼠标右键，点选选择项菜单，然后在弹出的对话框中点击浏览按钮，找到编译后的 SqlPaging.dll 最后点击确定按钮即可。

新建一个 Default2.aspx 页面然后在页面中添加控件 GridView Datalist 各一个添加两个 SqlPaging 控件添加一个按钮控件如下图

然后设置 SqlPaging 控件的两个重要的属性如下图

下面是控件的测试页面 default2.aspx 文件的html源码

<%...@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" 
    Inherits="Default2" %>
<%...@ Register Assembly="SqlPaging" Namespace="DRMSystem.DataLayer" TagPrefix="drmsp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
           "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;</div>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="height: 125px">
GridView Sample<br />
<br />
<asp:GridView ID="GridView1" runat="server" Width="100%">
</asp:GridView>
&nbsp;&nbsp;<br />
<drmsp:SqlPaging ID="SqlPaging1" runat="server" 
                   StoredProcedureName="sys_QuickSortPaging" ControlToPaginate="GridView1" />
</td>
</tr>
<tr>
<td style="height: 29px">
&nbsp;
</td>
</tr>
<tr>
<td style="height: 361px">
DataList Sample<br />
<br />
<asp:DataList ID="DataList1" runat="server" BackColor="White" 
                          BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" 
                     CellPadding="3" GridLines="Horizontal" HorizontalAlign="Justify"
                     RepeatLayout="Flow" Width="100%">
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<SelectedItemStyle BackColor="#738A9C" Font-Bold="True" 
                                       ForeColor="#F7F7F7" />
<AlternatingItemStyle BackColor="#F7F7F7" />
<ItemStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" 
                                     ForeColor="#F7F7F7" />
<ItemTemplate>
<%...#DataBinder.Eval(Container.DataItem, "EmployeesID")%>
<%...#DataBinder.Eval(Container.DataItem, "LastName")%>
<%...#DataBinder.Eval(Container.DataItem, "FirstName")%>
<%...#DataBinder.Eval(Container.DataItem, "BirthDate")%>
<%...#DataBinder.Eval(Container.DataItem, "Address")%>
<%...#DataBinder.Eval(Container.DataItem, "City")%>
<%...#DataBinder.Eval(Container.DataItem, "HomePhone")%>
<%...#DataBinder.Eval(Container.DataItem, "Extension")%>
</ItemTemplate>
</asp:DataList><br />
<drmsp:SqlPaging ID="SqlPaging2" runat="server" 
                  ControlToPaginate="DataList1" StoredProcedureName="sys_QuickSortPaging"/>
<asp:Button ID="Button1" runat="server" Text="生成1百万条测试数据" />
更具硬件大约需要 5-20 分钟</td>
</tr>
</table>
</form>
</body>
</html>

测试页 Default2.aspx.vb 源码

Imports System.Data
Imports System.Data.SqlClient
Partial Class Default2Class Default2
Inherits System.Web.UI.Page
'请更改Sql 数据库连接字符串的 DATABASE 部分

Private Const strSQlConn As String = "server=(local);Trusted_Connection=true;
              DATABASE=pubs;uid=sa"
Protected Sub Page_Load()Sub Page_Load(ByVal sender As Object,
                ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
SqlPaging1.SQLConnection = strSQlConn
SqlPaging1.PageInfoText_01 = "每页"
SqlPaging1.PageInfoText_02 = "条记录"
SqlPaging1.PageInfoText_03 = "转到"
SqlPaging1.PageInfoText_04 = "页"
SqlPaging1.TableName = "Employees"
SqlPaging1.PrimaryKeyField = "EmployeesID"
SqlPaging1.Field = "*"
SqlPaging1.DataBind()
SqlPaging2.SQLConnection = strSQlConn
SqlPaging2.PageInfoText_01 = "每页"
SqlPaging2.PageInfoText_02 = "条记录"
SqlPaging2.PageInfoText_03 = "转到"
SqlPaging2.PageInfoText_04 = "页"
SqlPaging2.TableName = "Employees"
SqlPaging2.PrimaryKeyField = "EmployeesID"
SqlPaging2.Field = "*"
SqlPaging2.Where = "EmployeesID<2000 And EmployeesID>1000"
SqlPaging2.DataBind()
End If
End Sub
Protected Sub Button1_Click()Sub Button1_Click(ByVal sender As Object,
            ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As New SqlConnection(strSQlConn)
conn.Open()
Using conn
Dim Command As New SqlCommand
Command.Connection = conn
Command.CommandText = "InsertTableData"
Command.CommandTimeout = "600"
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()
End Using
End Sub
End Class

关注我们