分页技术@----，vb.net结合数据库

2024-04-07 00:50•php•阅读 5003

一：数据库部分

A.查询总记录数：

-- =============================================

-- Author: <闫生>

-- Create date: <2008/02/28>

-- Description: <総件数を取る>

-- =============================================

CREATE PROCEDURE [dbo].[Test]

@tablename varchar(5)

,@allInfonum int OUTPUT

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）

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>   "

Else

html = "|<<   "

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>   "

End If

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" %>

<head>

function Submit(pageId) {

var ff = document.forms[0];

//把要显示的页面赋给隐藏域

ff.Pagin_UserControl1$hiddToPage.value = pageId;

ff.action="aa.aspx";

ff.submit();

}

</script>

<!--

#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>

<%=Htmls%>

</td>

(総件数：<asp:Label runat="server" ></asp:Label>件，

総頁数：<asp:Label runat="server" ></asp:Label>頁)

</td>

最大表示件数：

<asp:DropDownList 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 runat="server" />

</td>

</tr>

</table>

</div>

<div >

<asp:GridView runat="server">

</asp:GridView>

</div>

</body>

</html>

分页技术@----，vb.net结合数据库

相关推荐

PHP 实现分页功能

【php+mysql】博客分页制作思路

Oracle数据库查询分页SQL语句

MySql实现分页查询的SQL，mysql实现分页查询的sql语句

MySQL分页查询大数据量优化方法 方法1: 直接使用数据库提供的SQL语句方法2: 建立主键或唯一索引, 利用索引，假设每页10条

数据添加步骤，vb6.0与数据库结合

第九章 VB的数据库操作

MySQL数据库分页查询，Oracle数据库分页查询，SqlServer数据库分页

MySQL分页查询大数据量优化方法方法1: 直接使用数据库提供的SQL语句方法2: 建立主键或唯一索引, 利用索引，假设每页10条