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); } }}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; } }}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; } }}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."); } }}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."); } }}<%@ 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>
ALTER PROCEDURE dbo.LeftTree_Data AS Select Site_No as id,Site_Name as Name,null as ParentID From YT_Site union allselect 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 allselect 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 allselect 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) ))
... string filter = string.Format("{0} IS NULL", this.DataParentKeyName);...