ASP.net中的Datagrid自定义分页功能

看到在CSDN中已经有了不少自定义分页的例子,参考了很多,但是老觉得别人写的功能不是很完备,自己也摸索的写了一个,主要参考了“孟子E章”的分页例子。

这里使用的是VB.net语言。

DataGridPaging.aspx

 

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataGridPaging.aspx.vb" Inherits="Webwuye.DataGridPaging"%>  参考下面我的回复中,这里帖不上代码。

       

DataGridPaging.aspx.vb

Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI

Public Class DataGridPaging
    Inherits System.Web.UI.Page

    Protected WithEvents btnFirst As System.Web.UI.WebControls.LinkButton
    Protected WithEvents btnPrev As System.Web.UI.WebControls.LinkButton
    Protected WithEvents btnNext As System.Web.UI.WebControls.LinkButton
    Protected WithEvents btnLast As System.Web.UI.WebControls.LinkButton
    Protected WithEvents lblCurrentIndex As System.Web.UI.WebControls.Label
    Protected WithEvents lblPageCount As System.Web.UI.WebControls.Label
    Protected WithEvents lblRegisterCount As System.Web.UI.WebControls.Label
    Protected WithEvents DataGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Dim cnn As New SqlConnection("Password=;  User ID=sa;Initial Catalog=northwind;Data Source=localhost;Connect Timeout=30")
    Dim adp As New SqlDataAdapter
    Dim cmd As New SqlCommand
    Dim ds As New DataSet

    '   Dim intStartIndex As Long

    '定义全局静态变量,记录选中页面之前的总记录数
    Private Shared preceding As Long
    Private Shared gridPageSize As Int16

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

        btnFirst.Text = "最首页"
        btnPrev.Text = "前一页"
        btnNext.Text = "下一页"
        btnLast.Text = "最后页"

        If Not IsPostBack Then
            Try
                Dim cmd As New SqlCommand("select count(*) from products", cnn)
                cnn.Open()
                '设置在使用自定义分页时 DataGrid 控件中的实际项数。
                'Dim i As Int16
                'i = cmd.ExecuteScalar()
                'MyDataGrid.VirtualItemCount=(i / MyDataGrid.PageSize)
                DataGrid1.VirtualItemCount = cmd.ExecuteScalar()
            Catch ex As Exception
                Response.Write("<script language=javascript>alert('错误原因:' + exp.tostring() )</script>")
            Finally
                cnn.Close()
            End Try
            preceding = DataGrid1.PageSize
            BindGrid()

        End If

    End Sub

    Sub BindGrid()
        '使用DataSet方法填充DataGrid
        Dim str As String = " * "
        'Dim gridPageSize As Int16 = DataGrid1.PageSize
        gridPageSize = DataGrid1.PageSize

        Dim cmdNumber As New SqlCommand("select count(*) from products ", cnn)

        Try
            cnn.Open()
            '设置在使用自定义分页时 DataGrid 控件中.VirtualItemCount存放总的实际项数。
            DataGrid1.VirtualItemCount = cmdNumber.ExecuteScalar()

            '使用求余保证最后一页的合理显示
            If preceding >= DataGrid1.VirtualItemCount Then
                gridPageSize = DataGrid1.VirtualItemCount Mod DataGrid1.PageSize
                If gridPageSize = 0 Then
                    gridPageSize = DataGrid1.PageSize
                End If
            End If

            str = "select " + str + " from( select top " & gridPageSize.ToString + str + " from( select  top " + CStr(preceding) + str + " from products where productID >0 order by productID ) as p order by productID desc )as p order by productID "

            Cmd.CommandText = str
            Cmd.Connection = cnn
            adp.SelectCommand = Cmd
            'adp = New SqlDataAdapter(str, cnn)
            adp.Fill(ds, "products")
            DataGrid1.DataSource = ds.Tables("products").DefaultView
            DataGrid1.DataBind()
        Catch ex As Exception
            Response.Write("错误原因:" + ex.ToString())
            'Throw ex
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        ShowStats()
        HideButton()


    End Sub

    ' -------------------------------------------  下面是分页功能    -----------------------------------------------

    Sub DataGrid1_Page(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
        '点击GRID某一页时发生
        'GridXQ.aspx中DataGrid 的 OnPageIndexChanged="DataGrid1_Page"

        preceding = (e.NewPageIndex + 1) * DataGrid1.PageSize
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        BindGrid()
        ShowStats()
        HideButton()
    End Sub

    Sub PagerButtonClick(ByVal sender As Object, ByVal e As EventArgs)
        'used by external paging UI

        '页面上Grid下方 linkbutton 的 οnclick="PagerButtonClick" CommandArgument="next/prev/last/……"
        Dim arg As String = sender.CommandArgument

        Select Case arg
            Case "next"
                If (DataGrid1.CurrentPageIndex <= (DataGrid1.PageCount - 1)) Then
                    DataGrid1.CurrentPageIndex += 1
                    If DataGrid1.CurrentPageIndex = DataGrid1.PageCount - 1 Then
                        preceding = DataGrid1.VirtualItemCount
                    Else
                        preceding += gridPageSize
                    End If
                End If
            Case "prev"
                If (DataGrid1.CurrentPageIndex > 0) Then
                    If DataGrid1.CurrentPageIndex = DataGrid1.PageCount - 1 Then
                        preceding = (DataGrid1.PageCount - 1) * DataGrid1.PageSize
                    Else
                        preceding -= gridPageSize
                    End If
                    DataGrid1.CurrentPageIndex -= 1
                End If
            Case "last"
                DataGrid1.CurrentPageIndex = (DataGrid1.PageCount - 1)
                preceding = DataGrid1.VirtualItemCount
            Case Else
                'page number 因为在button.commandArgument设置为0
                DataGrid1.CurrentPageIndex = System.Convert.ToInt32(arg)
                preceding = DataGrid1.PageSize
        End Select

        If (DataGrid1.CurrentPageIndex = (DataGrid1.PageCount - 1)) Then
            btnNext.Enabled = False
        Else
            btnNext.Enabled = True
        End If

        If (DataGrid1.CurrentPageIndex = 0) Then
            btnPrev.Enabled = False
        Else
            btnPrev.Enabled = True
        End If

        BindGrid()
        ShowStats()
        HideButton()
    End Sub

    Sub ShowStats()

        lblCurrentIndex.Text = "第 " + (DataGrid1.CurrentPageIndex + 1).ToString() + " 页"
        lblPageCount.Text = "总共 " + DataGrid1.PageCount.ToString() + " 页"
        lblRegisterCount.Text = "共有 " + DataGrid1.VirtualItemCount.ToString + " 条记录"
    End Sub

    Sub HideButton()
        If (DataGrid1.CurrentPageIndex = (DataGrid1.PageCount - 1)) Then
            btnNext.Enabled = False
            btnLast.Enabled = False
        Else
            btnNext.Enabled = True
            btnLast.Enabled = True
        End If

        If (DataGrid1.CurrentPageIndex = 0) Then
            btnPrev.Enabled = False
            btnFirst.Enabled = False
        Else
            btnPrev.Enabled = True
            btnFirst.Enabled = True
        End If
    End Sub
End Class

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
VB.NET使用MySQL数据库连接并显示数据在DataGrid的步骤如下: 1. 首先,确保已经安装并导入了MySQL连接器。在项目添加对`MySql.Data.dll`的引用。 2. 创建MySQL连接:使用`MySqlConnection`类来连接MySQL数据库。需要传递连接字符串,包括服务器地址、用户名、密码等信息。 ```vb Dim conn As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=1234;database=mydatabase") ``` 3. 打开连接:使用`Open()`方法打开连接。 ```vb conn.Open() ``` 4. 创建SQL查询字符串:可以根据需要编写SELECT语句,并将其分配给一个字符串变量。 ```vb Dim sql As String = "SELECT * FROM mytable" ``` 5. 创建DataAdapter和DataSet:使用`MySqlDataAdapter`类和`DataSet`类来填充数据。 ```vb Dim da As MySqlDataAdapter = New MySqlDataAdapter(sql, conn) Dim ds As DataSet = New DataSet() ``` 6. 填充DataSet:使用`Fill()`方法将数据填充到DataSet。 ```vb da.Fill(ds, "mytable") ``` 7. 绑定DataGrid:使用`SetDataBinding()`方法将DataSet绑定到DataGrid。 ```vb DataGrid1.SetDataBinding(ds, "mytable") ``` 8. 关闭连接:使用`Close()`方法关闭数据库连接。 ```vb conn.Close() ``` 通过以上步骤,您可以成功地将MySQL数据显示在VB.NETDataGrid。请注意,在连接MySQL数据库之前,您需要确保已经正确安装并配置了MySQL数据库,并且需要正确提供数据库连接字符串的服务器地址、用户名、密码等信息。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值