asp.net 数据分页(vb)

最新推荐文章于 2024-10-07 16:04:54 发布

lg_lin

最新推荐文章于 2024-10-07 16:04:54 发布

阅读量1.6k

点赞数 1

分类专栏： asp.net 文章标签： asp.net vb datagrid integer textbox string

asp.net 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

想来学习 .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: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>
<meta name=vs_snapToGrid content="False">
<meta name=vs_showGrid content="False">
<meta content="Microsoft Visual Studio .NET 7.1" name=GENERATOR>
<meta content="Visual Basic .NET 7.1" name=CODE_LANGUAGE>
<meta content=JavaScript name=vs_defaultClientScript>
<meta content=http://schemas.microsoft.com/intellisense/ie5 name=vs_targetSchema>
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 32px" runat="server"

AutoGenerateColumns="False" Width="392px" Height="184px" AllowSorting="True">
<Columns>
<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>

对该文的评论

vbcc ( 2005-06-30)
API 服务器游标实现 SQL Server OLE DB 提供程序、SQL Server ODBC 驱动程序和 DB-Library DLL 使用这些特殊的系统存储过程向服务器示意游标操作。 sp_cursoropen 定义与游标和游标选项相关的 SQL 语句，然后生成游标。 sp_cursorfetch 从游标中提取一行或多行。 sp_cursorclose 关闭并释放游标。 sp_cursoroption 设置各种游标选项。 sp_cursor 用于请求定位更新。 sp_cursorprepare 把与游标有关的 Transact-SQL 语句或批处理编译成执行计划，但并不创建游标。 sp_cursorexecute 从由 sp_cursorprepare 创建的执行计划中创建并填充游标。 sp_cursorunprepare 废弃由 sp_cursorprepare 生成的执行计划。