025. asp.net中GridView的排序和过滤
前台HTML代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!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>无标题页</title> </head> <body> <form id="form1" runat="server"> <div> <table class="style1"> <tr> <td> 原始排序：</td> </tr> <tr> <td> <asp:GridView ID="gv1" runat="server" AutoGenerateColumns="False" Width="306px" CellPadding="4" ForeColor="#333333" GridLines="None" style="font-size: small"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="cardNo" HeaderText="卡号" /> <asp:BoundField DataField="cardBound" HeaderText="内卡号" /> <asp:BoundField DataField="name" HeaderText="姓名" /> <asp:BoundField DataField="mobileID" HeaderText="机器码" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> <tr> <td> 按内卡号排序：</td> </tr> <tr> <td> <asp:GridView ID="gv2" runat="server" AutoGenerateColumns="False" Width="306px" CellPadding="4" ForeColor="#333333" GridLines="None" style="font-size: small"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="cardNo" HeaderText="卡号" /> <asp:BoundField DataField="cardBound" HeaderText="内卡号" /> <asp:BoundField DataField="name" HeaderText="姓名" /> <asp:BoundField DataField="mobileID" HeaderText="机器码" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> <tr> <td> 按机器码排序：</td> </tr> <tr> <td> <asp:GridView ID="gv3" runat="server" AutoGenerateColumns="False" Width="306px" CellPadding="4" ForeColor="#333333" GridLines="None" style="font-size: small"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="cardNo" HeaderText="卡号" /> <asp:BoundField DataField="cardBound" HeaderText="内卡号" /> <asp:BoundField DataField="name" HeaderText="姓名" /> <asp:BoundField DataField="mobileID" HeaderText="机器码" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> </table> <asp:DropDownList ID="dlFilter" runat="server" AutoPostBack="True" DataTextFormatString="cardNo:{0}" OnSelectedIndexChanged="dlFilter_SelectedIndexChanged" Width="150px" AppendDataBoundItems="True"> <asp:ListItem Value="-1">选择分类编号</asp:ListItem> </asp:DropDownList> <asp:Label ID="lbData" runat="server"></asp:Label> </div> </form> </body> </html>
后台CS文件代码:
using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Text; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { //排序 string conStr = "server=.;database=TYW;uid=sa;pwd=123.456;"; SqlConnection conn = new SqlConnection(conStr);//创建连接对象 if (conn.State.Equals(ConnectionState.Closed)) conn.Open();//显式地打开数据库连接 string strsql = "select top 5 cardNo,cardBound,name,mobileID from card"; SqlDataAdapter da = new SqlDataAdapter(strsql, conn);//创建数据适配器 DataSet ds = new DataSet();//创建数据集 da.Fill(ds, "card");//填充数据集 原始排序 gv1.DataSource = ds.Tables["card"]; DataView view2 = new DataView(ds.Tables["card"]); view2.Sort = "cardBound";//设置排序表达式 gv2.DataSource = view2;//将排序后的DataView视图中数据绑定到GridView控件中 DataView view3 = new DataView(ds.Tables["card"]); view3.Sort = "mobileID";//设置排序表达式 gv3.DataSource = view3;//将排序后的DataView视图中数据绑定到GridView控件中 Page.DataBind();//为整个页面中控件绑定数据 conn.Close();//关闭数据库连接 ///显示UrlID的数据 ReadUrlIDData(); ///从Session中获取缓存的数据 if (Session["cardData"] == null) { ///如果没有缓存数据，则添加新的数据到缓存中 Session["cardData"] = ReadData(); ///显示数据 ShowData(((DataTable)Session["cardData"]).DefaultView); } } } private void ReadUrlIDData() { ///创建数据库链接 string conString = "server=.;database=TYW;uid=sa;pwd=123.456;"; SqlConnection myCon = new SqlConnection(conString); ///创建执行命令 SqlCommand myCmd = new SqlCommand("SELECT DISTINCT cardNo FROM card ORDER BY cardNo", myCon); ///定义dr SqlDataReader dr = null; try { ///打开数据库的链接 myCon.Open(); ///从数据库读取数据 dr = myCmd.ExecuteReader(); ///设置下拉框的数据 dlFilter.DataSource = dr; dlFilter.DataTextField = "cardNo"; dlFilter.DataValueField = "cardNo"; dlFilter.DataBind(); ///关闭数据读取器 dr.Close(); } catch (Exception ex) { Response.Write(ex.Message); } finally { ///关闭数据库的链接 myCon.Close(); } } private DataTable ReadData() { ///创建数据库链接 string conString = "server=.;database=TYW;uid=sa;pwd=123.456;"; SqlConnection myCon = new SqlConnection(conString); ///创建执行命令 SqlCommand myCmd = new SqlCommand("SELECT * FROM card", myCon); ///定义dr SqlDataReader dr = null; try { ///打开数据库的链接 myCon.Open(); ///从数据库读取数据 dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection); ///创建DataTabel，并显示DataTable中的数据 return (CreateDataTableData(dr)); } catch (Exception ex) { Response.Write(ex.Message); } finally { ///关闭数据库的链接 myCon.Close(); } return (DataTable)null; } private DataTable CreateDataTableData(SqlDataReader dr) { DataTable dt = new DataTable(); ///创dt的列 foreach (DataRow row in dr.GetSchemaTable().Rows) { ///需要列的名称和数据类型 dt.Columns.Add(new DataColumn(row["ColumnName"].ToString(), (Type)row["DataType"])); } ///读取数据 while (dr.Read()) { ///创建一个新行 DataRow newrow = dt.NewRow(); foreach (DataRow row in dr.GetSchemaTable().Rows) { ///读取数据 newrow[row["ColumnName"].ToString()] = dr[row["ColumnName"].ToString()]; } ///添加新行 dt.Rows.Add(newrow); } ///关闭读取器 dr.Close(); return (dt); } private void ShowData(DataView dv) { StringBuilder sb = new StringBuilder(); ///添加表头 sb.Append("<table border=1>"); ///添加表的标题栏 sb.Append("<tr>"); for (int i = 0; i < dv.Table.Columns.Count; i++) { sb.Append("<td bgcolor=gray>"); sb.Append(dv.Table.Columns[i].ColumnName); sb.Append("</td>"); } sb.Append("</tr>"); ///添加数据 foreach (DataRowView rowview in dv) { ///添加一行数据 DataRow row = rowview.Row; sb.Append("<tr>"); for (int i = 0; i < row.ItemArray.Length; i++) { ///添加数据的列 sb.Append("<td>"); sb.Append(row.ItemArray[i].ToString()); sb.Append("</td>"); } sb.Append("</tr>"); } ///添加结束符号 sb.Append("</table"); sb.Append("<hr />"); ///输出字符串 lbData.Text = sb.ToString(); } protected void dlFilter_SelectedIndexChanged(object sender, EventArgs e) { DataTable dt; ///从Session中获取缓存的数据, 将整个数据都缓存在Session中了 if (Session["cardData"] == null) { ///如果没有缓存数据，则添加新的数据到缓存中 dt = ReadData(); Session["cardData"] = dt; } else { dt = (DataTable)Session["cardData"]; } if (dlFilter.SelectedItem.Value == "-1") { ///设置过滤表达式 dt.DefaultView.RowFilter = ""; ///显示数据 ShowData(dt.DefaultView); } else { ///设置过滤表达式 DataView dv = dt.DefaultView; dv.RowFilter = "cardNo ='" + dlFilter.SelectedItem.Value + "'"; ///显示数据 ShowData(dv); } } }
