GridView 自定义查询翻页
1.自定义LINKBUTTON
2.dropdownlist翻页
3.过滤引号及用 myAdapter.SelectCommand.Parameters.Add 方法过滤,防止SQL注入
4.先显示所有值,后可查询关键字再翻页
.ASPX
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="MemberList.aspx.vb" Inherits="Manage_Members_MemberList" Debug="true"%><!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 runat="server"> <title>MemberList</title> <link rel="stylesheet" href="../Css/Moban_MM.css" type="text/css" /></head><body> <form id="form1" runat="server"> <div> <table width="100%" height="100%" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td><table width="90%" border="0" align="center" cellpadding="2" cellspacing="2"> <tr> <td style="height: 45px"><table style="border-collapse:collapse" width="100%" border="1" cellspacing="1" cellpadding="1" bordercolor="#CCCCCC"> <tr>
<td bgcolor="#999999" height="21"><b><span class="a180"><font color="#FFFFFF">会员列表 
<asp:TextBox ID="keywords" runat="server" BackColor="NavajoWhite"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" OnClick="searchGridView" Text="查询" /></font></span></b></td> </tr> </table> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" DataKeyNames="U_ID" ForeColor="Black" GridLines="None" Width="100%" OnRowDeleting="GridView1_OnRowDeleting" OnRowDataBound="GridView1_OnRowDataBound" PagerSettings-Visible="false" OnPageIndexChanging ="GridView1_OnPageIndexChanging" > <FooterStyle BackColor="Tan" /> <Columns> <asp:TemplateField HeaderText="编号"> <HeaderTemplate> <asp:Label ID="Label1" runat="server" Text="编号"></asp:Label><asp:CheckBox ID="chkAll" Visible="false" runat="server" ToolTip="全选" Text="编号" /> </HeaderTemplate> <ItemTemplate > <asp:CheckBox ID="chkItem" Visible="false" runat="server" /><%#Container.DataItemIndex + 1%> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="U_GameID" HeaderText="游戏ID" SortExpression="U_GameID" /> <asp:BoundField DataField="U_Name" HeaderText="用户名" SortExpression="U_Name" /> <asp:TemplateField HeaderText="性别" SortExpression="U_Sex"> <EditItemTemplate> <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("U_Sex") %>' /> </EditItemTemplate> <ItemTemplate> <%#isSex(Eval("U_Sex"))%> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="U_EMail" HeaderText="Email" SortExpression="U_EMail" /> <asp:BoundField DataField="U_UserTruename" HeaderText="真实姓名" SortExpression="U_UserTruename" /> <asp:BoundField DataField="U_MsnQQ" HeaderText="Msn/QQ" SortExpression="U_MsnQQ" /> <asp:BoundField DataField="U_RegDatetime" HeaderText="注册时间" SortExpression="U_RegDatetime" /> <asp:CommandField DeleteText="<span onclick="return confirm('确定删除吗?')">删除</span>" HeaderText="操作" ShowDeleteButton="True" ShowEditButton="True" /> </Columns> <PagerTemplate> </PagerTemplate> <RowStyle HorizontalAlign="Center" /> <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /> <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" /> <HeaderStyle BackColor="Tan" Font-Bold="True" /> <AlternatingRowStyle BackColor="PaleGoldenrod" /> <PagerSettings Visible="False" /> </asp:GridView> <asp:Panel ID="PanelHyperLink" Visible="false" runat="server" Height="50px" Width="100%" HorizontalAlign="right" > 总共有 [<asp:Label ID="myRecordCount1" runat="server" Text="Label"></asp:Label>] 条记录; 页数 [<asp:Label ID="myPageIndex1" runat="server" Text="Label"></asp:Label>/<asp:Label ID="myPageCount1" runat="server" Text="Label"></asp:Label>] ; <asp:HyperLink ID="PageFirst1" runat="server">首 页</asp:HyperLink> | <asp:HyperLink ID="PagePrevious1" runat="server">上一页</asp:HyperLink> | <asp:HyperLink ID="PageNext1" runat="server">下一页</asp:HyperLink> | <asp:HyperLink ID="PageLast1" runat="server">尾 页</asp:HyperLink> <asp:DropDownList AppendDataBoundItems="true" ID="myDDL1" runat="server"> <asp:ListItem Value="-1">跳转→</asp:ListItem> </asp:DropDownList> </asp:Panel> <asp:Panel ID="PanelLinkButton" runat="server" Height="50px" Width="100%" HorizontalAlign="right" > 总共有 [<asp:Label ID="myRecordCount" runat="server" Text="Label"></asp:Label>] 条记录; 页数 [<asp:Label ID="myPageIndex" runat="server" Text="Label"></asp:Label>/<asp:Label ID="myPageCount" runat="server" Text="Label"></asp:Label>] ; <asp:LinkButton ID="PageFirst" CommandArgument="goPageFirst" OnClick="pageButtonClick" runat="server">首 页</asp:LinkButton> | <asp:LinkButton ID="PagePrevious" CommandArgument="goPagePrevious" OnClick="pageButtonClick" runat="server">上一页</asp:LinkButton> | <asp:LinkButton ID="PageNext" CommandArgument="goPageNext" OnClick="pageButtonClick" runat="server">下一页</asp:LinkButton> | <asp:LinkButton ID="PageLast" CommandArgument="goPageLast" OnClick="pageButtonClick" runat="server">尾 页</asp:LinkButton> <asp:DropDownList AppendDataBoundItems="true" ID="myDDL" runat="server" OnSelectedIndexChanged="myDDL_OnSelectedIndexChanged" AutoPostBack="true" > <asp:ListItem Value="-1">跳转→</asp:ListItem> </asp:DropDownList> </asp:Panel> </td> </tr> </table></td> </tr> </table> </div> </form></body>
</html>.vb
Imports System.DataImports System.Data.SqlClientImports SystemImports System.Web.UIImports System.Web.HttpUtilityPartial Class Manage_Members_MemberList Inherits System.Web.UI.Page Dim conn As SqlConnection Dim myappclass As New myAppClass Dim sql As String Public myPageSize As String
Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
myPageSize = 10 '初始化一页为 10条数据 If Not Page.IsPostBack Then getGridView(Me.keywords.Text) End If
End Sub Sub getGridView(ByVal keywords As String) conn = New SqlConnection(ConfigurationManager.ConnectionStrings("gameDBConnectionString").ConnectionString) conn.Open() ' keywords = myappclass.FenHao(keywords) keywords = keywords.Trim.ToString If keywords = "" Then sql = "Select [U_ID],[U_GameID],[U_Name],[U_Sex],[U_Email],[U_UserTruename],[U_MsnQQ],[U_RegDatetime] From [MemTable] Order By " sql += " [U_RegDatetime] Desc " Else sql = "Select [U_ID],[U_GameID],[U_Name],[U_Sex],[U_Email],[U_UserTruename],[U_MsnQQ],[U_RegDatetime] From [MemTable] " ' sql += " where [U_Name] like '%" + keywords + "%'" sql += " where [U_Name] like @U_name" sql += " Order By [U_RegDatetime] Desc " End If Dim myAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(sql, conn) If keywords <> "" Then myAdapter.SelectCommand.Parameters.Add(New SqlParameter("@U_Name", SqlDbType.NVarChar)) myAdapter.SelectCommand.Parameters("@U_Name").Value = "%" + keywords + "%" End If Dim ds As New DataSet myAdapter.Fill(ds, "MemTable") Me.GridView1.PageSize = myPageSize Me.GridView1.SelectedIndex = -1 Me.GridView1.DataSource = ds.Tables("MemTable").DefaultView ' Me.GridView1.PageIndex = 0 default 为 0 Me.GridView1.DataBind() '要先绑定GIRDVIEW.DataBind()后才可以使用以下的方法 CType(Me.FindControl("myRecordCount"), Label).Text = ds.Tables("MemTable").Rows.Count.ToString CType(Me.FindControl("myPageIndex"), Label).Text = Me.GridView1.PageIndex + 1 CType(Me.FindControl("myPageCount"), Label).Text = Me.GridView1.PageCount Dim getMyDDL As DropDownList = CType(Me.FindControl("myDDL"), DropDownList) getMyDDL.Items.Clear() getMyDDL.Items.Add(New ListItem("跳转→", -1)) For i As Integer = 1 To Me.GridView1.PageCount getMyDDL.Items.Add(New ListItem("第" + i.ToString + "页", i.ToString)) If i = Me.GridView1.PageIndex + 1 Then getMyDDL.SelectedValue = i End If Next myAdapter.Dispose() ds.Dispose() conn.Close() End Sub Protected Sub GridView1_OnRowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs) conn = New SqlConnection(ConfigurationManager.ConnectionStrings("gameDBConnectionString").ConnectionString) conn.Open() Dim str As String ' str = e.RowIndex str = Me.GridView1.DataKeys(e.RowIndex).Value.ToString sql = "Delete From [MemTable] Where U_ID=" + str + "" Dim cmdDel As SqlCommand = New SqlCommand(sql, conn) cmdDel.ExecuteNonQuery() Me.GridView1.EditIndex = -1 getGridView(Me.keywords.Text) conn.Close() End Sub Protected Sub GridView1_OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then ' e.Row.Cells.Item(0).Text = e.Row.RowIndex + 1 End If End Sub Protected Sub GridView1_OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) ' Dim mylinkCommand As LinkButton = CType(Me.FindControl(""), LinkButton) End Sub Protected Sub myDDL_OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Dim getMyDDL As DropDownList = CType(Me.FindControl("myDDL"), DropDownList) If getMyDDL.SelectedValue = -1 Then Exit Sub End If Me.GridView1.PageIndex = getMyDDL.SelectedValue - 1 getGridView(Me.keywords.Text) End Sub Protected Sub pageButtonClick(ByVal sender As Object, ByVal e As System.EventArgs) 'Dim arg As String = CType(sender,LinkButton).CommandArgument.ToString Dim myLinkButton As LinkButton = CType(sender, LinkButton) Dim arg As String = myLinkButton.CommandArgument.ToString() Select Case arg Case "goPageFirst" Me.GridView1.PageIndex = 0 getGridView(Me.keywords.Text) Case "goPagePrevious" If Me.GridView1.PageIndex > 0 Then Me.GridView1.PageIndex -= 1 getGridView(Me.keywords.Text) End If Case "goPageNext" If Me.GridView1.PageIndex < Me.GridView1.PageCount - 1 Then Me.GridView1.PageIndex += 1 getGridView(Me.keywords.Text) End If Case "goPageLast" Me.GridView1.PageIndex = Me.GridView1.PageCount - 1 getGridView(Me.keywords.Text) End Select End Sub Protected Sub searchGridView(ByVal sender As Object, ByVal e As EventArgs) Me.GridView1.PageIndex = 0 getGridView(Me.keywords.Text) End Sub Function isSex(ByVal str As Boolean) As String If str = True Then Return "男" Else Return "女" End If End FunctionEnd Class.web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="gameDBConnectionString_At_Home" connectionString="Data Source=OEMUSER;Initial Catalog=gameDB;Persist Security Info=True;User ID=sa;Password=iloveyouBB"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>