[原创][分页] DataList URL 高效查询翻页
Default.aspx
<%@ Page Language="VB" AutoEventWireup="false" Debug="true" EnableViewState="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>DataList</title>
<script language=vb runat=server >
Dim conn As System.Data.SqlClient.SqlConnection
Dim currentpage As Integer
Dim PageSize, PageCount, TotalCount As Integer
Dim startIndex As Integer
Dim str, strCount As String
Dim keywords As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
conn = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("doughty_bbsConnectionString").ConnectionString)
conn.Open()
If Not Page.IsPostBack Then
'第一次页面载入，搜索所有记录
startIndex = 1
getRecordCount(keywords, startIndex)
Else
Response.Write("第二次载入")
End If
End Sub
Sub getData(ByVal str As String)
Dim mycmd As SqlDataAdapter
mycmd = New SqlDataAdapter(str, conn)
Dim ds As DataSet = New DataSet
mycmd.Fill(ds, "small_class")
DataList1.DataSource = ds.Tables(0).DefaultView
DataList1.DataBind()
conn.Close()
End Sub
Sub DataList_PageIndex(ByVal sender As Object, ByVal e As DataListItemEventArgs)
' Dim l As Label = CType(e.Item.FindControl("PageIndex"), Label)
Dim l As Label
l = e.Item.FindControl("PageIndex")
If Not (l Is Nothing) Then
l.Text = (currentpage - 1) * PageSize + (e.Item.ItemIndex + 1)
End If
End Sub
Sub btnSearch(ByVal sender As Object, ByVal e As EventArgs)
'Server.Transfer("SearchResult.aspx?keywords=" + keywordsTXT.Text.ToString)
' getRecordCount(keywordsTXT.Text.ToString, startIndex) '不在这边获取本页数据
Dim s As String = Request.CurrentExecutionFilePath + "?keywords=" + keywordsTXT.Text.ToString
Response.Redirect(s)
End Sub
Public Sub getRecordCount(ByVal keywords As String, ByVal startIndex As Integer)
linkFirst.Enabled = True
linkPre.Enabled = True
linkNext.Enabled = True
linkLast.Enabled = True
' keywords = Server.HtmlEncode(Request.QueryString("keywords"))
' web.config 中加入 <globalization requestEncoding="gb2312" responseEncoding="gb2312"/>
keywords = Trim(Request.QueryString("keywords"))
If keywords <> "" Then keywords = keywords.Replace("'", "´")
PageSize = 2 ''一页的个数
If keywords = "" Then
strCount = "select count(SmallClass_ID) From [small_class]"
Else
strCount = "select count(SmallClass_ID) From [small_class] where Memo like '%" + keywords.ToString + "%' "
End If
Dim mycmd As SqlCommand = New SqlCommand(strCount, conn)
TotalCount = mycmd.ExecuteScalar()
If TotalCount Mod PageSize = 0 Then
PageCount = TotalCount \ PageSize
Else
PageCount = TotalCount \ PageSize + 1
End If
If Request.QueryString("currentpage") = "" Then currentpage = 1
If Request.QueryString("currentpage") <> "" Then
currentpage = CInt(Request.QueryString("currentpage"))
If currentpage <= 0 Then currentpage = 1
End If
lblRecordCount.Text = TotalCount.ToString
lblPageCount.Text = PageCount.ToString()
If TotalCount = 0 Then
lblCurrentPage.Text = "0"
Else
lblCurrentPage.Text = currentpage.ToString ' 当前页
End If
If currentpage <> 1 Then
linkFirst.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=1 "
linkPre.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + (currentpage - 1).ToString
End If
If currentpage < PageCount Then
linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + (currentpage + 1).ToString
linkLast.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + PageCount.ToString
End If
If TotalCount = 0 Then
linkFirst.Enabled = False
linkPre.Enabled = False
linkNext.Enabled = False
linkLast.Enabled = False
End If
If currentpage = 1 Then
If keywords = "" Then
str = "select top " + PageSize.ToString + " SmallClass_ID,Memo From [small_class]"
Else
str = "select top " + PageSize.ToString + " SmallClass_ID,Memo From [small_class] where Memo like '%" + keywords.ToString + "%' "
End If
Else
If keywords = "" Then
str = "select SmallClass_ID,Memo From [small_class] where SmallClass_ID not In(SELECT TOP " & PageSize * (currentpage - 1) & " SmallClass_ID FROM small_class ORDER BY SmallClass_ID) and "
str = str + " SmallClass_ID In(SELECT TOP " & (PageSize * (currentpage - 1) + PageSize) & " SmallClass_ID From [small_class] ORDER BY SmallClass_ID) order by SmallClass_ID"
Else
str = "select SmallClass_ID,Memo From [small_class] where SmallClass_ID not In(SELECT TOP " & PageSize * (currentpage - 1) & " SmallClass_ID FROM small_class where Memo like '%" + keywords.ToString + "%' ORDER BY SmallClass_ID) and "
str = str + " SmallClass_ID In(SELECT TOP " & (PageSize * (currentpage - 1) + PageSize) & " SmallClass_ID From [small_class] where Memo like '%" + keywords.ToString + "%' ORDER BY SmallClass_ID) order by SmallClass_ID"
End If
End If
strSQL.Text = "关键字：" + keywords + " SQL查询语句：" + str
getData(str)
End Sub
</script>
</head>
<body>
<form id="form1" runat="server" method="post">
<div>
关键字:<asp:TextBox ID="keywordsTXT" runat="server"></asp:TextBox>
<asp:Button ID="btn" OnClick="btnSearch" runat="server" Text="Search" />
<asp:Button ID="Buttonreload" runat="server" Text="第二次载入" />
<br />
<asp:DataList ID="DataList1" runat="server" OnItemCreated="DataList_PageIndex" >
<ItemTemplate>
<asp:Label ID="PageIndex" runat="server" ></asp:Label>
<%#Eval("Memo")%>
</ItemTemplate>
</asp:DataList>
<asp:Label ID="strSQL" runat="server" ForeColor="Red"></asp:Label>
<br>
共有记录[<asp:label ID="lblRecordCount" runat="server" ForeColor="Red"></asp:label>]条；
页数[<asp:Label ID="lblCurrentPage" runat="server" Text="Label" ForeColor="Red"></asp:Label>/<asp:Label ID="lblPageCount" runat="server" Text="Label"></asp:Label>]
<asp:HyperLink ID="linkFirst" runat="server">首页</asp:HyperLink>
|<asp:HyperLink ID="linkPre" runat="server">上一页</asp:HyperLink>
|<asp:HyperLink ID="linkNext" runat="server">下一页</asp:HyperLink>
|<asp:HyperLink ID="linkLast" runat="server">末页</asp:HyperLink>
</div>
</form>
</body>
</html>
<%@ Page Language="VB" AutoEventWireup="false" Debug="true" EnableViewState="True"%><%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head id="Head1" runat="server"> <title>DataList</title> <script language=vb runat=server > Dim conn As System.Data.SqlClient.SqlConnection Dim currentpage As Integer Dim PageSize, PageCount, TotalCount As Integer Dim startIndex As Integer Dim str, strCount As String Dim keywords As String Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load conn = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("doughty_bbsConnectionString").ConnectionString) conn.Open() If Not Page.IsPostBack Then '第一次页面载入，搜索所有记录 startIndex = 1 getRecordCount(keywords, startIndex) Else Response.Write("第二次载入") End If End Sub Sub getData(ByVal str As String) Dim mycmd As SqlDataAdapter mycmd = New SqlDataAdapter(str, conn) Dim ds As DataSet = New DataSet mycmd.Fill(ds, "small_class") DataList1.DataSource = ds.Tables(0).DefaultView DataList1.DataBind() conn.Close() End Sub Sub DataList_PageIndex(ByVal sender As Object, ByVal e As DataListItemEventArgs) ' Dim l As Label = CType(e.Item.FindControl("PageIndex"), Label) Dim l As Label l = e.Item.FindControl("PageIndex") If Not (l Is Nothing) Then l.Text = (currentpage - 1) * PageSize + (e.Item.ItemIndex + 1) End If End Sub Sub btnSearch(ByVal sender As Object, ByVal e As EventArgs) 'Server.Transfer("SearchResult.aspx?keywords=" + keywordsTXT.Text.ToString) ' getRecordCount(keywordsTXT.Text.ToString, startIndex) '不在这边获取本页数据 Dim s As String = Request.CurrentExecutionFilePath + "?keywords=" + keywordsTXT.Text.ToString Response.Redirect(s) End Sub Public Sub getRecordCount(ByVal keywords As String, ByVal startIndex As Integer) linkFirst.Enabled = True linkPre.Enabled = True linkNext.Enabled = True linkLast.Enabled = True ' keywords = Server.HtmlEncode(Request.QueryString("keywords")) ' web.config 中加入 <globalization requestEncoding="gb2312" responseEncoding="gb2312"/> keywords = Trim(Request.QueryString("keywords")) If keywords <> "" Then keywords = keywords.Replace("'", "´") PageSize = 2 ''一页的个数 If keywords = "" Then strCount = "select count(SmallClass_ID) From [small_class]" Else strCount = "select count(SmallClass_ID) From [small_class] where Memo like '%" + keywords.ToString + "%' " End If Dim mycmd As SqlCommand = New SqlCommand(strCount, conn) TotalCount = mycmd.ExecuteScalar() If TotalCount Mod PageSize = 0 Then PageCount = TotalCount \ PageSize Else PageCount = TotalCount \ PageSize + 1 End If If Request.QueryString("currentpage") = "" Then currentpage = 1 If Request.QueryString("currentpage") <> "" Then currentpage = CInt(Request.QueryString("currentpage")) If currentpage <= 0 Then currentpage = 1 End If lblRecordCount.Text = TotalCount.ToString lblPageCount.Text = PageCount.ToString() If TotalCount = 0 Then lblCurrentPage.Text = "0" Else lblCurrentPage.Text = currentpage.ToString ' 当前页 End If If currentpage <> 1 Then linkFirst.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=1 " linkPre.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + (currentpage - 1).ToString End If If currentpage < PageCount Then linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + (currentpage + 1).ToString linkLast.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "¤tpage=" + PageCount.ToString End If If TotalCount = 0 Then linkFirst.Enabled = False linkPre.Enabled = False linkNext.Enabled = False linkLast.Enabled = False End If If currentpage = 1 Then If keywords = "" Then str = "select top " + PageSize.ToString + " SmallClass_ID,Memo From [small_class]" Else str = "select top " + PageSize.ToString + " SmallClass_ID,Memo From [small_class] where Memo like '%" + keywords.ToString + "%' " End If Else If keywords = "" Then str = "select SmallClass_ID,Memo From [small_class] where SmallClass_ID not In(SELECT TOP " & PageSize * (currentpage - 1) & " SmallClass_ID FROM small_class ORDER BY SmallClass_ID) and " str = str + " SmallClass_ID In(SELECT TOP " & (PageSize * (currentpage - 1) + PageSize) & " SmallClass_ID From [small_class] ORDER BY SmallClass_ID) order by SmallClass_ID" Else str = "select SmallClass_ID,Memo From [small_class] where SmallClass_ID not In(SELECT TOP " & PageSize * (currentpage - 1) & " SmallClass_ID FROM small_class where Memo like '%" + keywords.ToString + "%' ORDER BY SmallClass_ID) and " str = str + " SmallClass_ID In(SELECT TOP " & (PageSize * (currentpage - 1) + PageSize) & " SmallClass_ID From [small_class] where Memo like '%" + keywords.ToString + "%' ORDER BY SmallClass_ID) order by SmallClass_ID" End If End If strSQL.Text = "关键字：" + keywords + " SQL查询语句：" + str getData(str) End Sub </script></head><body> <form id="form1" runat="server" method="post"> <div> 关键字:<asp:TextBox ID="keywordsTXT" runat="server"></asp:TextBox> <asp:Button ID="btn" OnClick="btnSearch" runat="server" Text="Search" /> <asp:Button ID="Buttonreload" runat="server" Text="第二次载入" /> <br /> <asp:DataList ID="DataList1" runat="server" OnItemCreated="DataList_PageIndex" > <ItemTemplate> <asp:Label ID="PageIndex" runat="server" ></asp:Label> <%#Eval("Memo")%> </ItemTemplate> </asp:DataList> <asp:Label ID="strSQL" runat="server" ForeColor="Red"></asp:Label> <br> 共有记录[<asp:label ID="lblRecordCount" runat="server" ForeColor="Red"></asp:label>]条； 页数[<asp:Label ID="lblCurrentPage" runat="server" Text="Label" ForeColor="Red"></asp:Label>/<asp:Label ID="lblPageCount" runat="server" Text="Label"></asp:Label>] <asp:HyperLink ID="linkFirst" runat="server">首页</asp:HyperLink> |<asp:HyperLink ID="linkPre" runat="server">上一页</asp:HyperLink> |<asp:HyperLink ID="linkNext" runat="server">下一页</asp:HyperLink> |<asp:HyperLink ID="linkLast" runat="server">末页</asp:HyperLink> </div> </form></body></html>
