分頁技術@—–（vb.net結合資料庫）

一：資料庫部分
  A.查詢總記錄數：
    

-- =============================================
-- Author:  <閆生>
-- Create date: <2008/02/28>
-- Description: <総件數を取る>
-- =============================================
CREATE PROCEDURE [dbo].[Test]
  @tablename  varchar(5)
  ,@allInfonum int OUTPUT
AS

BEGIN
set @allInfonum=(
 SELECT count(*) as number
      FROM @tablename  
    WHERE *****
                         )
END
-- =============================================
-- Author:  <閆生>
-- Create date: <2008/02/28>
-- Description: <分頁>
-- =============================================
ALTER PROCEDURE [dbo].[TT]
 @tablename as varchar(5)    ======表名
,@showNubers as int            ======要顯示的條數
,@cursorNum  as int             ======檢索資料的開始的位置（cursor）
as
begin
  SELECT *
  FROM (select *,ROW_NUMBER() Over(order by 主鍵 ）as rowNum
           from @tablename
          ) as myTable
  where rowNum between @cursorNum and (@showNubers+@cursorNum)
end

二：前台代碼

A.vb代碼：

Imports System.Data.SqlClient
Imports System.Data

Partial Class Pagin_UserControl
    Inherits System.Web.UI.UserControl
    Public AllInfo As Integer         '総件數
    Public AllPage As Integer         '総頁數
    Public ShowNumber As Integer      '現し數
    Public ToPages As Integer         '要跳轉的頁面ID
    Public HidToPage As String        '.HiddCursorのvalue用於存放要跳轉的頁面的ID
    Public Htmls As String            '頁面連結的html

    Public tablename As String        'tablename
    Public cursor As Integer          '要顯示記錄的開始

    Dim connection As SqlConnection = Nothing
    Dim cm As New Common
    Public Parameter() As SqlParameter
    Dim flag As Integer
    Public dataSet As DataSet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ShowNumber = CInt(Me.drpShowNum.SelectedValue.ToString()) '取得每頁顯示的條數

        HidToPage = Me.hiddToPage.Value                           '取得要跳轉的的頁面

        tablename = "0001"                                         '表名賦初值

        If (HidToPage.Equals("")) Then

            ToPages = 1
        Else

            ToPages = CInt(HidToPage)

        End If

        flag = Me.GetAllInfoNub(tablename)

        If (flag = -1) Then

            AllInfo = Me.Parameter(1).Value()                   '取得總資訊數

        End If

        AllPage = Me.GetAllPageNub(CInt(ShowNumber), AllInfo)   '判斷總頁數

        If (ToPages <= AllPage And ToPages > 1) Then            '對要跳往的頁面進行邏輯處理

            cursor = (ToPages - 1) * ShowNumber

        Else
            ToPages = 1
            cursor = 1
        End If

        '列印連結資料表++++++++++++++++++++++++++++++++++=

        Htmls = Me.GetLinkHtml(AllPage, ToPages)

        '列印詳細的資訊++++++++++++++++++++++++++++++=

      
        Me.GetData(tablename, ShowNumber, cursor)

        '儲存基本資料++++++++++++++++++++++++++++++=

        Me.lbAllInfo.Text = AllInfo

        Me.lbAllPage.Text = AllPage

        Me.hiddToPage.Value = ToPages.ToString()

       
        '===========================================================================================================================
    End Sub
    '総件數を取る
    Protected Function GetAllInfoNub(ByVal TableName As String) As Integer

        ' 串連資料庫
        connection = cm.GetConnection()
        Parameter = New SqlParameter(2) {}

        ' [tablename]
        Parameter(0) = New SqlParameter("@tablename", Data.SqlDbType.VarChar, 5)
        Parameter(0).Direction = Data.ParameterDirection.Input
        Parameter(0).Value = TableName
        ' [allinfo_num]
        Parameter(1) = New SqlParameter("@allInfonum", Data.SqlDbType.Int)
        Parameter(1).Direction = Data.ParameterDirection.Output

        flag = DBUtility.ExecuteNonQuery(connection, Data.CommandType.StoredProcedure, _
                                                        "Test", Parameter)
        ' 關閉資料庫連接
        connection.Close()

        Return flag

    End Function
    '総頁數を取る
    Protected Function GetAllPageNub(ByVal ShowInfoNumber As Integer, ByVal AllInfoNumber As Integer) As Integer
        Dim PageNum As Integer

        PageNum = AllInfoNumber / ShowInfoNumber

        If ((PageNum * ShowInfoNumber) < AllInfoNumber) Then

            PageNum = PageNum + 1

        End If

        Return PageNum
    End Function
    'ページを分けて表示すります<===>得到詳細的資訊
    Protected Function GetData(ByVal TableName As String, ByVal ShowNubers As Integer, ByVal CursorNum As String) As Integer
        ' 串連資料庫
        connection = cm.GetConnection()
        Parameter = New SqlParameter(3) {}

        ' [tablename]
        Parameter(0) = New SqlParameter("@tablename", Data.SqlDbType.VarChar, 5)
        Parameter(0).Direction = Data.ParameterDirection.Input
        Parameter(0).Value = TableName
        '[show numbers]
        Parameter(1) = New SqlParameter("@showNubers", Data.SqlDbType.Int)
        Parameter(1).Direction = Data.ParameterDirection.Input
        Parameter(1).Value = ShowNubers
        '[show numbers]
        Parameter(2) = New SqlParameter("@cursorNum", Data.SqlDbType.Int)
        Parameter(2).Direction = Data.ParameterDirection.Input
        Parameter(2).Value = CursorNum

        dataSet = DBUtility.ExecuteDataset(connection, Data.CommandType.StoredProcedure, _
                                                       "TT", Parameter)
      
        '在此繫結控制項的資料來源+++++++++++++++++++++++++++++++++++++++++++++++++++++===

        Me.gwDataView.DataSource = dataSet
        Me.gwDataView.DataBind()

        ' 關閉資料庫連接
        connection.Close()

        Return 0

    End Function
    'get LinK html<===============>得到頁面的連結HTML
    Protected Function GetLinkHtml(ByVal allPages As Integer, ByVal toPage As Integer) As String

        Dim html As String

        Dim showE As Integer   '判斷顯示的連結數和總頁數的大小

        Dim showS As Integer   '判斷顯示的連結數和0的大小

        If (toPage > 1) Then   '判斷第一頁是否要加連結

            '列印連結資料表++++++++++++++++++++++++++++++++++=
            html = "<a href='#'onclick=Submit('1')>|<<</a> &nbsp; "

        Else
            html = "|<< &nbsp; "
        End If

        If (toPage - 3 > 0) Then        '判斷顯示的連結開始
            showS = toPage - 3
        Else
            showS = 1
        End If

        If (toPage + 3 <= allPages) Then '判斷顯示的連結結尾
            showE = toPage + 3
        Else
            showE = allPages
        End If

        Dim i As Integer

        For i = showS To showE
            If (i = toPage) Then
                html = html + i.ToString() + "&nbsp"
            Else
                html = html + "<a href='#' onclick=Submit('" + i.ToString() + "')>" + i.ToString() + "</a> &nbsp; "
            End If

        Next

        If (toPage >= allPages) Then   '判斷尾頁是否加連結
            html = html + ">>|"
        Else
            html = html + "<a href='#' onclick=Submit('" + (allPages).ToString() + "')>>>|</a>"
        End If

        Return html

    End Function
End Class

B。頁面的代碼：

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="Pagin_UserControl.ascx.vb" Inherits="Pagin_UserControl" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<script language="javascript" type="text/javascript">
 function Submit(pageId) {
 var ff = document.forms[0];
 //把要顯示的頁面賦給隱藏欄位
  ff.Pagin_UserControl1$hiddToPage.value = pageId;
  ff.action="aa.aspx";
 
  ff.submit();
}
</script>

<style type="text/css">
<!--
#kong{

font-family: Arial,Helvetica,sans-serif;
font-size: 9pt;
color: #0099ff;
width:auto;
background:#ffffff;
border-left:1px solid #0099ff;
border-right:1px solid #0099ff;
border-top:1px solid #0099ff;
border-bottom:1px solid #0099ff;

}

-->
</style>

</head>
<body>
<div>
    <table id="kong" border="0px" >
    <tr>    <!------------Link------------------------->
            <td align="center" style=" height:25px; width :auto">
            <%=Htmls%>
           </td>
           <!--------------kongjian----------------------->
            <td align="center" style=" height:25px; width:250px">
            (総件數：<asp:Label ID="lbAllInfo" runat="server" ></asp:Label>件，
             総頁數：<asp:Label ID="lbAllPage" runat="server" ></asp:Label>頁)
            </td>
            <!-------------selectValue----------------------->
             <td align="center" style=" height:25px; width:200px">
             最大表示件數：
            <asp:DropDownList ID="drpShowNum" runat="server">
            <asp:ListItem>10</asp:ListItem>
            <asp:ListItem>20</asp:ListItem>
            <asp:ListItem>50</asp:ListItem>
            <asp:ListItem>100</asp:ListItem>
            </asp:DropDownList>
            <asp:HiddenField ID="hiddToPage" runat="server" />
            </td>
    </tr>
    </table>
 </div>
 
<div style="height:100px;overflow-y:scroll">
     <asp:GridView ID="gwDataView" runat="server">
    </asp:GridView>
</div>
  
 </body>
 </html>