注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

幽狼De博客

记录成长中的点点滴滴

 
 
 

日志

 
 

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

2010-03-01 11:21:02|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

[原创][分页] 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 ObjectByVal 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 ObjectByVal 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 NothingThen
 
                l.Text 
= (currentpage - 1* PageSize + (e.Item.ItemIndex + 1)
 
            
End If
 
        
End Sub


        
Sub btnSearch()Sub btnSearch(ByVal sender As ObjectByVal 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 StringByVal 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("'""&acute;")
            
            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 + " &nbsp;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>

  评论这张
 
阅读(555)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017