19.5创建分层的SQL数据源控件

创建分层的SQL数据源控件

SqlHierarchicaldataSource

SqlHierarchicalDataSource.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace AspNetUnleashed
{

    /// <summary>
    /// Extends SqlDataSource control to support hierarchical database data
    /// </summary>
    public class SqlHierarchicalDataSource : SqlDataSource, IHierarchicalDataSource
    {
        private string _dataKeyName;
        private string _dataParentKeyName;


        public event EventHandler DataSourceChanged;


        /// <summary>
        /// The database table primary key
        /// </summary>
        public string DataKeyName
        {
            get { return _dataKeyName; }
            set { _dataKeyName = value; }
        }

        /// <summary>
        /// The database table parent id
        /// </summary>
        public string DataParentKeyName
        {
            get { return _dataParentKeyName; }
            set { _dataParentKeyName = value; }
        }

        /// <summary>
        /// Return hierarchical data
        /// </summary>
        public HierarchicalDataSourceView GetHierarchicalView(string viewPath)
        {
            return new SqlHierarchicalDataSourceView(this, viewPath);
        }

    }
}

SqlHierarchicalDataSourceView.cs

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace AspNetUnleashed
{

    /// <summary>
    /// Represents the data returned from database
    /// </summary>
    public class SqlHierarchicalDataSourceView : HierarchicalDataSourceView
    {
        private SqlHierarchicalDataSource _owner;
        private string _viewPath;
        private DataTable _data;

        public SqlHierarchicalDataSourceView(SqlHierarchicalDataSource owner, string viewPath)
        {
            _owner = owner;
            _viewPath = viewPath;
        }

        /// <summary>
        /// The DataTable which contains all rows from
        /// underlying database table
        /// </summary>
        public DataTable Data
        {
            get { return _data; }
        }

        /// <summary>
        /// We need to expose this for the SqlNodes
        /// </summary>
        public string DataKeyName
        {
            get { return _owner.DataKeyName; }
        }

        /// <summary>
        /// We need to expose this for the SqlNodes
        /// </summary>
        public string DataParentKeyName
        {
            get { return _owner.DataParentKeyName; }
        }

        /// <summary>
        /// Get the top-level rows (rows without parent rows)
        /// </summary>
        /// <returns></returns>
        public override IHierarchicalEnumerable Select()
        {
            // Verify DataKeyName and DataParentKeyName properties
            if (String.IsNullOrEmpty(DataKeyName))
                throw new Exception("You must set the DataKeyName property");
            if (String.IsNullOrEmpty(DataParentKeyName))
                throw new Exception("You must set the DataParentKeyName property");

            // Return DataView from SqlDataSource 
            if (_owner.DataSourceMode != SqlDataSourceMode.DataSet)
                throw new Exception("DataSourceMode must be set to DataSet");
            DataView view = (DataView)_owner.Select(DataSourceSelectArguments.Empty);
            _data = view.Table;

            // Get the root rows
            string filter = string.Format("{0} IS NULL", this.DataParentKeyName);
            DataRow[] rootRows = _data.Select(filter);

            // Build up the hierarchical collection
            SqlHierarchicalEnumerable en = new SqlHierarchicalEnumerable();
            foreach (DataRow row in rootRows)
                en.Add(new SqlNode(this, row));
            return en;
        }


    }
}

SqlHierarchicalEnumerable.cs

using System;
using System.Web.UI;
using System.Collections;

namespace AspNetUnleashed
{

    /// <summary>
    /// Represents a collection of SqlNodes
    /// </summary>
    public class SqlHierarchicalEnumerable : ArrayList, IHierarchicalEnumerable
    {

        public SqlHierarchicalEnumerable() : base() { }

        public IHierarchyData GetHierarchyData(object enumeratedItem)
        {
            return enumeratedItem as IHierarchyData;
        }



    }
}

SqlNode.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.ComponentModel;
using System.Web.UI;

namespace AspNetUnleashed
{

    /// <summary>
    /// Represents a node (row) from the database
    /// </summary>
    public class SqlNode : IHierarchyData, ICustomTypeDescriptor
    {

        private SqlHierarchicalDataSourceView _owner;
        private DataRow _row;

        public SqlNode(SqlHierarchicalDataSourceView owner, DataRow row)
        {
            _owner = owner;
            _row = row;
        }

        /// <summary>
        /// Does the current database row have child rows?
        /// </summary>
        public bool HasChildren
        {
            get
            {
                string filter = String.Format("{0}={1}", _owner.DataParentKeyName, _row[_owner.DataKeyName]);
                DataRow[] childRows = _owner.Data.Select(filter);

                return childRows.Length > 0;
            }
        }


        /// <summary>
        /// Returns the DataRow
        /// </summary>
        public object Item
        {

            get { return _row; }

        }

        /// <summary>
        /// A unique identifier for the row
        /// </summary>
        public string Path
        {

            get { return _row[_owner.DataKeyName].ToString(); }

        }


        /// <summary>
        /// The Type is used in switching logic
        /// </summary>
        public string Type
        {

            get { return "SqlNode"; }

        }

        /// <summary>
        /// The ToString() method is called to show
        /// the value of a row (we default to showing
        /// the value of the first column)
        /// </summary>
        public override string ToString()
        {
            return _row[0].ToString();
        }

        /// <summary>
        /// Get child rows of current row
        /// </summary>
        public IHierarchicalEnumerable GetChildren()
        {

            string filter = string.Format("{0}={1}", _owner.DataParentKeyName, _row[_owner.DataKeyName]);
            DataRow[] childRows = _owner.Data.Select(filter);

            SqlHierarchicalEnumerable en = new SqlHierarchicalEnumerable();
            foreach (DataRow row in childRows)
                en.Add(new SqlNode(_owner, row));
            return en;
        }


        /// <summary>
        /// Get Parent Row of current row
        /// </summary>
        public IHierarchyData GetParent()
        {
            string filter = string.Format("{0}={1}", _owner.DataKeyName, _row[_owner.DataParentKeyName]);
            DataRow[] parentRows = _owner.Data.Select(filter);

            if (parentRows.Length > 0)
                return new SqlNode(_owner, parentRows[0]);
            else
                return null;

        }

        /// <summary>
        /// Get the list of properties supported by the SqlNode
        /// </summary>
        public PropertyDescriptorCollection GetProperties()
        {
            List<PropertyDescriptor> props = new List<PropertyDescriptor>();
            foreach (DataColumn col in _owner.Data.Columns)
                props.Add(new SqlNodePropertyDescriptor(col.ColumnName));
            return new PropertyDescriptorCollection(props.ToArray());
        }


        // The following properties and methods are required by the
        // ICustomTypeDescriptor interface but are not implemented

        public System.ComponentModel.AttributeCollection GetAttributes()
        {
            throw new Exception("Not implemented.");
        }

        public string GetClassName()
        {
            throw new Exception("Not implemented.");
        }

        public string GetComponentName()
        {
            throw new Exception("Not implemented.");
        }

        public TypeConverter GetConverter()
        {
            throw new Exception("Not implemented.");
        }

        public EventDescriptor GetDefaultEvent()
        {
            throw new Exception("Not implemented.");
        }

        public PropertyDescriptor GetDefaultProperty()
        {
            throw new Exception("Not implemented.");
        }

        public object GetEditor(Type editorBaseType)
        {
            throw new Exception("Not implemented.");
        }

        public EventDescriptorCollection GetEvents(Attribute[] attributes)
        {
            throw new Exception("Not implemented.");
        }

        public EventDescriptorCollection GetEvents()
        {
            throw new Exception("Not implemented.");
        }

        public PropertyDescriptorCollection GetProperties(Attribute[] attributes)
        {
            throw new Exception("Not implemented.");
        }

        public object GetPropertyOwner(PropertyDescriptor pd)
        {
            throw new Exception("Not implemented.");
        }

    }
}

SqlNodePropertyDescriptor.cs

using System;
using System.ComponentModel;
using System.Data;


namespace AspNetUnleashed
{

    /// <summary>
    /// Describes a property of a SqlNode
    /// </summary>
    public class SqlNodePropertyDescriptor : PropertyDescriptor
    {

        public SqlNodePropertyDescriptor(string name) : base(name, null) { }


        /// <summary>
        /// Return the value of a DataColumn represented by
        /// a particular SqlNode
        /// </summary>
        public override object GetValue(object component)
        {
            SqlNode node = (SqlNode)component;
            return ((DataRow)node.Item)[this.Name];
        }


        // Don't bother to implement any of the other methods or properties
        // of this class

        public override bool CanResetValue(object component)
        {
            throw new Exception("Not implemented.");
        }

        public override Type ComponentType
        {
            get { throw new Exception("Not implemented."); }
        }

        public override bool IsReadOnly
        {
            get { throw new Exception("Not implemented."); }
        }

        public override Type PropertyType
        {
            get { throw new Exception("Not implemented."); }
        }

        public override void ResetValue(object component)
        {
            throw new Exception("Not implemented.");
        }

        public override void SetValue(object component, object value)
        {
            throw new Exception("Not implemented.");
        }

        public override bool ShouldSerializeValue(object component)
        {
            throw new Exception("Not implemented.");
        }
    }
}

课本上用的例子是简单SQL，这个比较好写，但我的程序却是多张表中查找数据。所以我只好用存储过程。
Demo
treeView.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="treeview.aspx.cs" Inherits="treeview" %>

<%@ Register TagPrefix="user" Namespace="AspNetUnleashed" %>
<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TreeView ID="TreeView1" DataSourceID="srcLeftTree" runat="server">
            <DataBindings>
                <asp:TreeNodeBinding TextField="name" ValueField="id" />
            </DataBindings>
        </asp:TreeView>
        <user:SqlHierarchicalDataSource ID="srcLeftTree" DataKeyName="id" DataParentKeyName="ParentId"
            ConnectionString="<%$ ConnectionStrings:ytdl %>" SelectCommand="LeftTree_Data"
            SelectCommandType="StoredProcedure" runat="server" />
    </div>
    </form>
</body>
</html>

存储过程：LeftTree_Data

ALTER PROCEDURE dbo.LeftTree_Data
 AS
 
	 
	Select Site_No as id,Site_Name as Name,null as ParentID From YT_Site  

union all

select dev_id as id, dev_name as Name ,Site_No as ParentID from YT_Dev where Site_No in (select Site_no from YT_Site) 

union all

select WD_ID as id,WD_Name as Name,  Dev_id as ParentID  from YT_WD where WD_Type=0 and Dev_ID in (
select dev_id  from YT_Dev where Site_No in (select Site_no from YT_Site) )

union all

select  WD_ID as ID,WD_Name as Name, WD_TO_WD as ParentID  from YT_WD where WD_TO_WD in (
select  WD_ID  from YT_WD where WD_Type=0 and Dev_ID in (
select dev_id  from YT_Dev where Site_No in (select Site_no from YT_Site) )
)

刚开始时写错了一个地方，null as ParentID From YT_Site  直接写成了 0 as Parentid
结果不能正确显示数据。
查看代码才发现，原来是要为空。
在SqlHierarchicalDataSourceView.cs中有一段代码：

...
   string filter = string.Format("{0} IS NULL", this.DataParentKeyName);
...

才发现是要写空。当然，正常情况下也应该是为空的。



2011-5-31 21:01 danny