asp.net 学习日记数据分页

想来学习 .net 也2个月了吧，最初遇到的一个问题就是分页程序获取当前页的数据并支持字段排序而且支持搜索时的 Where 语句在网上搜了

很久也没找到，也在 Asp.net 的一些论坛上发过帖子也许是那些高手懒得理我吧（做菜鸟就是难）也许是我找东西的能力有问题，但我却找到了以下SQL 的

存储过程。

SET @page = (@page-1) * @pagesize + 1

EXEC sp_cursoropen @P1 output, @strSQL

EXEC sp_cursorfetch @P1, 16, @page, @pagesize

EXEC sp_cursorclose @P1

后来经过修改就成下面这样了（后面有个调用的例子 VB.net 版本我只会VB 不知微软何时出 masm.net 我想这辈子是没希望了）

/* 通用存储过程分页----- 江建

只读取当前页支持分类排序

CREATE PROCEDURE GetPage

(

@strTableName nvarchar(50), --表名

@fldName nvarchar (200), --要返回的字段

@strWhere nvarchar(200)="", --Where 语句

@fldOrderby nvarchar(200), --要排序的字段

@OrderType int=0, --排序类型升序还是降序

@page int = 1, --要获取的页码

@pageSize int = 5 --页大小

)

SET NOCOUNT ON

DECLARE @P1 int

DECLARE @strSQL nvarchar(1000)

DECLARE @strOrderby nvarchar(200)

IF @OrderType != 0

SET @strOrderby = ' Order By [' + @fldOrderby +'] DESC'

ELSE

SET @strOrderby = ' Order By [' + @fldOrderby +'] ASC'

IF @strWhere !=''

SET @strSQL='SELECT ' + @fldName + ' FROM [' + @strTableName + '] WHERE ' + @strWhere + @strOrderby

ELSE

SET @strSQL='SELECT ' + @fldName + ' FROM [' + @strTableName + ']' + @strOrderby

SET @page = (@page-1) * @pagesize + 1

EXEC sp_cursoropen @P1 output, @strSQL

EXEC sp_cursorfetch @P1, 16, @page, @pagesize

EXEC sp_cursorclose @P1

/*这个是获取记录总数----- 江建*/

CREATE PROCEDURE CountRow

(

@strTableName nvarchar(50),

@fldNameCount nvarchar(50),

@strWhere nvarchar(200)=""

)

DECLARE @strSQL nvarchar(1000)

IF @strWhere !=''

SET @strSQL='SELECT Count([' + @fldNameCount + ']) As CountRow FROM [' + @strTableName + '] WHERE ' +

@strWhere

ELSE

SET @strSQL='SELECT Count([' + @fldNameCount + ']) As CountRow FROM [' + @strTableName + ']'

EXEC sp_executesql @strSQL

（1）下面是类模块 DataPager.vb

'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

' Data Pager

' Programme by Jiang Jian

' Date:2005-06-23

'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

' http://vbcc.126.com

' Email:jiangjian@Ejet.com.cn

' This is class for sql database pager

'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Imports System

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Data

Imports System.Data.SqlClient

Namespace DataLayer

Public Class GetData

Inherits WebControl

Private myConn As New SqlConnection

Private intRowCount As Integer

Private strTableName As String

Private strfldNameCount As String

Private strfldName As String

Private intPagesize As Integer

Private strWhere As String

Private strfldOrderby As String

Public Function ConnectionDatabase()

Dim strConn As String

If Not (myConn.State) Then

myConn.ConnectionString = "Server=(local);database=Northwind;uid=sa"

myConn.Open()

End If

End Function

'set or get tablename

Public Property TableName() As String

Get

Return strTableName

End Get

Set(ByVal Value As String)

strTableName = Value

End Set

End Property

'set or get count(xxxx)

Public Property fldNameCount() As String

Get

Return strfldNameCount

End Get

Set(ByVal Value As String)

strfldNameCount = Value

End Set

End Property

'set or get (xxxx) FROM

Public Property fldName() As String

Get

Return strfldName

End Get

Set(ByVal Value As String)

strfldName = Value

End Set

End Property

'set or get pagesize

Public Property Pagesize() As Integer

Get

Return intPagesize

End Get

Set(ByVal Value As Integer)

intPagesize = Value

End Set

End Property

'set or get sql where

Public Property Where() As String

Get

Return strWhere

End Get

Set(ByVal Value As String)

strWhere = Value

End Set

End Property

'set or get sql orderby

Public Property fldOrderby() As String

Get

Return strfldOrderby

End Get

Set(ByVal Value As String)

strfldOrderby = Value

End Set

End Property

Public Function GetPageCount() As Integer

Call ConnectionDatabase()

Dim RountCommand As SqlDataAdapter

RountCommand = New SqlDataAdapter("CountRow", myConn)

RountCommand.SelectCommand.CommandType = CommandType.StoredProcedure

RountCommand.SelectCommand.Parameters.Add(New SqlParameter("@strTableName", SqlDbType.NVarChar, 50))

RountCommand.SelectCommand.Parameters("@strTableName").Value = strTableName

RountCommand.SelectCommand.Parameters.Add(New SqlParameter("@fldNameCount", SqlDbType.NVarChar, 50))

RountCommand.SelectCommand.Parameters("@fldNameCount").Value = strfldNameCount

Dim intCount As Integer = RountCommand.SelectCommand.ExecuteScalar()

myConn.Close()

If (intCount Mod intPagesize) > 0 Then

Return (intCount \ intPagesize) + 1

Else

Return (intCount \ intPagesize)

End If

End Function

Public Function GotoPage(Optional ByVal intPage As Integer = 1) As DataView

Call ConnectionDatabase()

Dim myCommand As SqlDataAdapter

myCommand = New SqlDataAdapter("GetPage", myConn)

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@strTableName", SqlDbType.NVarChar, 50))

myCommand.SelectCommand.Parameters("@strTableName").Value = strTableName

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@fldName", SqlDbType.NVarChar, 200))

myCommand.SelectCommand.Parameters("@fldName").Value = strfldName

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@strWhere", SqlDbType.NVarChar, 200))

myCommand.SelectCommand.Parameters("@strWhere").Value = strWhere

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@fldOrderby", SqlDbType.NVarChar, 200))

myCommand.SelectCommand.Parameters("@fldOrderby").Value = strfldOrderby

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@page", SqlDbType.Int))

myCommand.SelectCommand.Parameters("@page").Value = intPage

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@pageSize", SqlDbType.Int))

myCommand.SelectCommand.Parameters("@pageSize").Value = intPagesize

Dim ds As New DataSet

myCommand.Fill(ds, "@Employees")

Return ds.Tables("@Employees1").DefaultView

myConn.Close()

End Function

End Class

End Namespace

（2）下面是WebForm1.aspx 的Codebehind

Imports bbs.DataLayer

Imports System

Imports System.Web

Imports System.Data

Imports System.Data.SqlClient

Public Class WebForm1

Inherits System.Web.UI.Page

Private clsGetData As New GetData

#Region " Web 窗体设计器生成的代码 "

'该调用是 Web 窗体设计器所必需的。

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Protected WithEvents LinkButton1 As System.Web.UI.WebControls.LinkButton

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox

Protected WithEvents Button1 As System.Web.UI.WebControls.Button

Protected WithEvents lnkNext As System.Web.UI.WebControls.LinkButton

Protected WithEvents lnkPrve As System.Web.UI.WebControls.LinkButton

Protected WithEvents Label1 As System.Web.UI.WebControls.Label

Protected WithEvents Label2 As System.Web.UI.WebControls.Label

Protected WithEvents Label3 As System.Web.UI.WebControls.Label

'注意: 以下占位符声明是 Web 窗体设计器所必需的。

'不要删除或移动它。

Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init

'CODEGEN: 此方法调用是 Web 窗体设计器所必需的

'不要使用代码编辑器修改它。

InitializeComponent()

End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

If Not IsPostBack Then

GridBind()

End If

End Sub

Private Sub GridBind()

clsGetData.TableName = "Products" '表名

clsGetData.fldNameCount = "ProductID" '获取记录总数时所用到的字段

clsGetData.fldName = "ProductID,ProductName" '要返回的字段

clsGetData.Pagesize = 10 '每页的大小

If clsGetData.fldOrderby = "" Then '排序的字段

clsGetData.fldOrderby = "ProductID"

End If

If PageCount = 0 Then

PageCount = clsGetData.GetPageCount()

End If

If CurPage = PageCount Then

lnkNext.Enabled = False

Else

lnkNext.Enabled = True

End If

If CurPage > 1 Then

lnkPrve.Enabled = True

Else

lnkPrve.Enabled = False

End If

Label3.Text = "共 " & PageCount & " 页当前第 " & CurPage & " 页"

DataGrid1.DataSource = clsGetData.GotoPage(CurPage)

DataGrid1.DataBind()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If CInt(TextBox1.Text) > PageCount Then

CurPage = PageCount

TextBox1.Text = CurPage

Else

CurPage = Convert.ToInt32(TextBox1.Text)

End If

GridBind()

End Sub

Private Sub lnkNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkNext.Click

If CurPage < PageCount Then

CurPage += 1

GridBind()

End If

End Sub

Private Sub lnkPrve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkPrve.Click

If CurPage > 1 Then

CurPage -= 1

GridBind()

End If

End Sub

Public Property PageCount() As Integer

Get

Return ViewState("PageCount")

End Get

Set(ByVal Value As Integer)

ViewState("PageCount") = Value

End Set

End Property

Public Property CurPage() As Integer

Get

If ViewState("CurPage") = 0 Then

ViewState("CurPage") = 1

End If

Return ViewState("CurPage")

End Get

Set(ByVal Value As Integer)

ViewState("CurPage") = Value

End Set

End Property

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As

System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand

clsGetData.fldOrderby = e.SortExpression

GridBind()

End Sub

End Class

（3）下面是WebForm1.aspx 代码

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="bbs.WebForm1"%>

<HTML>

<HEAD>

<title>WebForm1</title>

</HEAD>

<asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 32px" runat="server"

AutoGenerateColumns="False" Width="392px" Height="184px" AllowSorting="True">

<asp:BoundColumn DataField="ProductID" SortExpression="ProductID" HeaderText="ProductID"></asp:BoundColumn>

<asp:BoundColumn DataField="ProductName" SortExpression="ProductName" HeaderText="ProductName"></asp:BoundColumn>

</Columns>

</asp:datagrid><asp:Label id="Label2" style="Z-INDEX: 107; LEFT: 318px; POSITION: absolute; TOP: 12px" runat="server">页

</asp:Label><asp:LinkButton id="lnkPrve" style="Z-INDEX: 105; LEFT: 8px; POSITION: absolute; TOP: 8px"

runat="server">PrvePage</asp:LinkButton><asp:LinkButton id="lnkNext" style="Z-INDEX: 104; LEFT: 92px; POSITION: absolute;

TOP: 8px" runat="server">NextPage</asp:LinkButton>

<asp:TextBox id="TextBox1" style="Z-INDEX: 102; LEFT: 251px; POSITION: absolute; TOP: 8px" runat="server"

Width="57px"></asp:TextBox>

<asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 349px; POSITION: absolute; TOP: 6px" runat="server" Width="40px"

Text="GO" Height="24px">

</asp:Button><asp:Label id="Label1" style="Z-INDEX: 106; LEFT: 179px; POSITION: absolute; TOP: 11px" runat="server"

Height="16px" Width="70px">转到第</asp:Label><asp:Label id="Label3" style="Z-INDEX: 108; LEFT: 8px; POSITION: absolute; TOP:

300px" runat="server" Height="18px" Width="317px">XXXXX</asp:Label>

</form>

</body>

</HTML>