[原创][分页] DataList URL 高效查询翻页

[原创][分页] DataList URL 高效查询翻页

Default.aspx

<%@ Page Language="VB" AutoEventWireup="false" Debug="true" EnableViewState="True"%>
<%@ Import Namespace =Namespace="System.Data" %>
<%@ Import Namespace =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()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()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()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()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()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 + "&currentpage=1 "
                linkPre.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "&currentpage=" + (currentpage - 1).ToString

            End If


            If currentpage < PageCount Then
                linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "&currentpage=" + (currentpage + 1).ToString
                linkLast.NavigateUrl = Request.CurrentExecutionFilePath + "?keywords=" + keywords + "&currentpage=" + 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>
相关阅读:
MAX导致数据库超时
 mysql查询效率提高技巧
 微信回调报文解析, 获取请求体内容
 炖汤秘方
 首字母小写
 List分页
 HttpServletRequest通过InputStream获取参数
 github命令行
 mysql死锁
 分布式锁-redis
原文地址：https://www.cnblogs.com/apiapia/p/1082465.html