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 --页大小
)
AS
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
GO

/这个是获取记录总数----- 江建/
CREATE PROCEDURE CountRow
(
@strTableName nvarchar (50),
@fldNameCount nvarchar (50),
@strWhere nvarchar (200)=""
)
AS
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
GO

（1）下面是类模块 DataPager.vb
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
' Data Pager
' Programme by Jiang Jian
' Date:2005-06-23
' Corpright(C) 2005 Jiang Jian.
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
' http://vbcc.126.com
' Email:[email protected]
' 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 窗体设计器所必需的。

  1<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent() 
  2
  3End Sub   
  4Protected WithEvents LinkButton1 As System.Web.UI.WebControls.LinkButton   
  5Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid   
  6Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox   
  7Protected WithEvents Button1 As System.Web.UI.WebControls.Button   
  8Protected WithEvents lnkNext As System.Web.UI.WebControls.LinkButton   
  9Protected WithEvents lnkPrve As System.Web.UI.WebControls.LinkButton   
 10Protected WithEvents Label1 As System.Web.UI.WebControls.Label   
 11Protected WithEvents Label2 As System.Web.UI.WebControls.Label   
 12Protected WithEvents Label3 As System.Web.UI.WebControls.Label 
 13
 14'注意: 以下占位符声明是 Web 窗体设计器所必需的。   
 15'不要删除或移动它。   
 16Private designerPlaceholderDeclaration As System.Object 
 17
 18Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init   
 19'CODEGEN: 此方法调用是 Web 窗体设计器所必需的   
 20'不要使用代码编辑器修改它。   
 21InitializeComponent()   
 22End Sub 
 23
 24#End Region 
 25
 26Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load   
 27If Not IsPostBack Then   
 28GridBind()   
 29End If   
 30End Sub 
 31
 32Private Sub GridBind()   
 33clsGetData.TableName = "Products" '表名   
 34clsGetData.fldNameCount = "ProductID" '获取记录总数时所用到的字段   
 35clsGetData.fldName = "ProductID,ProductName" '要返回的字段   
 36clsGetData.Pagesize = 10 '每页的大小   
 37If clsGetData.fldOrderby = "" Then '排序的字段   
 38clsGetData.fldOrderby = "ProductID"   
 39End If 
 40
 41If PageCount = 0 Then   
 42PageCount = clsGetData.GetPageCount()   
 43End If 
 44
 45If CurPage = PageCount Then   
 46lnkNext.Enabled = False   
 47Else   
 48lnkNext.Enabled = True   
 49End If 
 50
 51If CurPage &gt; 1 Then   
 52lnkPrve.Enabled = True   
 53Else   
 54lnkPrve.Enabled = False   
 55End If   
 56Label3.Text = "共 " &amp; PageCount &amp; " 页 当前第 " &amp; CurPage &amp; " 页"   
 57DataGrid1.DataSource = clsGetData.GotoPage(CurPage)   
 58DataGrid1.DataBind()   
 59End Sub 
 60
 61Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click   
 62If CInt(TextBox1.Text) &gt; PageCount Then   
 63CurPage = PageCount   
 64TextBox1.Text = CurPage   
 65Else   
 66CurPage = Convert.ToInt32(TextBox1.Text)   
 67End If   
 68GridBind()   
 69End Sub 
 70
 71  
 72Private Sub lnkNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkNext.Click   
 73If CurPage &lt; PageCount Then   
 74CurPage += 1   
 75GridBind()   
 76End If   
 77End Sub 
 78
 79Private Sub lnkPrve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkPrve.Click   
 80If CurPage &gt; 1 Then   
 81CurPage -= 1   
 82GridBind()   
 83End If   
 84End Sub 
 85
 86Public Property PageCount() As Integer   
 87Get   
 88Return ViewState("PageCount")   
 89End Get 
 90
 91Set(ByVal Value As Integer)   
 92ViewState("PageCount") = Value   
 93End Set   
 94End Property 
 95
 96Public Property CurPage() As Integer   
 97Get   
 98If ViewState("CurPage") = 0 Then   
 99ViewState("CurPage") = 1   
100End If   
101Return ViewState("CurPage")   
102End Get 
103
104Set(ByVal Value As Integer)   
105ViewState("CurPage") = Value   
106End Set   
107End Property 
108
109  
110Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As 
111
112System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand   
113clsGetData.fldOrderby = e.SortExpression   
114GridBind()   
115End Sub   
116End Class 
117
118  
119（3）下面是WebForm1.aspx 代码    

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

 1<html>
 2<head>
 3<title>WebForm1</title>
 4<meta content="False" name="vs_snapToGrid"/>
 5<meta content="False" name="vs_showGrid"/>
 6<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR"/>
 7<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE"/>
 8<meta content="JavaScript" name="vs_defaultClientScript"/>
 9<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"/>
10</head>
11<body ms_positioning="GridLayout">
12<form id="Form1" method="post" runat="server">
13<asp:datagrid allowsorting="True" autogeneratecolumns="False" height="184px" id="DataGrid1" runat="server" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 32px" width="392px">
14<columns>
15<asp:boundcolumn datafield="ProductID" headertext="ProductID" sortexpression="ProductID"></asp:boundcolumn>
16<asp:boundcolumn datafield="ProductName" headertext="ProductName" sortexpression="ProductName"></asp:boundcolumn>
17</columns>
18</asp:datagrid><asp:label id="Label2" runat="server" style="Z-INDEX: 107; LEFT: 318px; POSITION: absolute; TOP: 12px">页 
19
20</asp:label><asp:linkbutton id="lnkPrve" runat="server" style="Z-INDEX: 105; LEFT: 8px; POSITION: absolute; TOP: 8px">PrvePage</asp:linkbutton><asp:linkbutton id="lnkNext" runat="server" style="Z-INDEX: 104; LEFT: 92px; POSITION: absolute; 
21
22TOP: 8px">NextPage</asp:linkbutton>
23<asp:textbox id="TextBox1" runat="server" style="Z-INDEX: 102; LEFT: 251px; POSITION: absolute; TOP: 8px" width="57px"></asp:textbox>
24<asp:button height="24px" id="Button1" runat="server" style="Z-INDEX: 103; LEFT: 349px; POSITION: absolute; TOP: 6px" text="GO" width="40px">
25</asp:button><asp:label height="16px" id="Label1" runat="server" style="Z-INDEX: 106; LEFT: 179px; POSITION: absolute; TOP: 11px" width="70px">转到第</asp:label><asp:label height="18px" id="Label3" runat="server" style="Z-INDEX: 108; LEFT: 8px; POSITION: absolute; TOP: 
26
27300px" width="317px">XXXXX</asp:label>
28</form>
29</body>
30</html></system.diagnostics.debuggerstepthrough()>