使用DataView过滤数据

Default.aspx

View Code

<%@ 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>
  <asp:DropDownList ID="dlFilter" runat="server" AutoPostBack="True" DataTextFormatString="CategoryID:{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>

Default.aspx.cs

View Code

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)
        {   ///显示UrlID的数据
            ReadUrlIDData();
            ///从Session中获取缓存的数据
            if (Session["PhotoData"] == null)
            {   ///如果没有缓存数据，则添加新的数据到缓存中
                Session["PhotoData"] = ReadData();
                ///显示数据
                ShowData(((DataTable)Session["PhotoData"]).DefaultView);
            }
        }
    }
    private void ReadUrlIDData()
    {   ///创建数据库链接
        string conString = ConfigurationSettings.AppSettings["strCon"];
        SqlConnection myCon = new SqlConnection(conString);
        ///创建执行命令
        SqlCommand myCmd = new SqlCommand("SELECT DISTINCT CategoryID FROM Photo ORDER BY CategoryID", myCon);
        ///定义dr
        SqlDataReader dr = null;
        try
        {   ///打开数据库的链接
            myCon.Open();
            ///从数据库读取数据
            dr = myCmd.ExecuteReader();
            ///设置下拉框的数据
            dlFilter.DataSource = dr;
            dlFilter.DataTextField = "CategoryID";
            dlFilter.DataValueField = "CategoryID";
            dlFilter.DataBind();
            ///关闭数据读取器
            dr.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {   ///关闭数据库的链接
            myCon.Close();
        }
    }

    private DataTable ReadData()
    {   ///创建数据库链接
        string conString = ConfigurationSettings.AppSettings["strCon"];
        SqlConnection myCon = new SqlConnection(conString);
        ///创建执行命令
        SqlCommand myCmd = new SqlCommand("SELECT * FROM Photo", 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中获取缓存的数据
        if (Session["PhotoData"] == null)
        {   ///如果没有缓存数据，则添加新的数据到缓存中
            dt = ReadData();
            Session["PhotoData"] = dt;
        }
        else
        {
            dt = (DataTable)Session["PhotoData"];
        }
        if (dlFilter.SelectedItem.Value == "-1")
        {   ///设置过滤表达式
            dt.DefaultView.RowFilter = "";
            ///显示数据
            ShowData(dt.DefaultView);
        }
        else
        {   ///设置过滤表达式
            DataView dv = dt.DefaultView;
            dv.RowFilter = "CategoryID ='" + dlFilter.SelectedItem.Value + "'";
            ///显示数据
            ShowData(dv);
        }
    }
}

posted on 2013-03-16 05:21 松竹梅阅读(698) 评论(1) 编辑收藏举报