张三(销售主管)
--李四(销售主任)
--小李(组员)
--小李(组员)
--小王(销售主任)数据结构是这样的:表Sales(销售人员表)
SaleID SaleName ParentID
1 张三 0
2 李四 1
3 小李 2
4 小李 2
5 小王 1
我的问题是,如当"李四"登录,只能看他本人和他所有子节数据.请问该怎么实现
--李四(销售主任)
--小李(组员)
--小李(组员)
--小王(销售主任)数据结构是这样的:表Sales(销售人员表)
SaleID SaleName ParentID
1 张三 0
2 李四 1
3 小李 2
4 小李 2
5 小王 1
我的问题是,如当"李四"登录,只能看他本人和他所有子节数据.请问该怎么实现
</asp:TreeView>private void populateTreeView(TreeView tree)
{
TreeNode rootNode = new TreeNode("配菜管理", "0");
rootNode.PopulateOnDemand = true;
tree.Nodes.Add(rootNode);
} protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
FoodStuffCatalogCollection catalogs = FoodStuffCatalogBll.GetByParent(Convert.ToUInt32(e.Node.Value));
TreeNode childNode = null;
foreach (FoodStuffCatalog catalog in catalogs)
{
childNode = new TreeNode(catalog.Name,catalog.PrimaryKey.ToString());
childNode.PopulateOnDemand = catalog.HasChild;
e.Node.ChildNodes.Add(childNode);
}
}
1 张三 0 1,
2 李四 1 1,2,
3 小李 2 1,2,3,
4 小李 2 1,2,4,
5 小王 1 1,5,查询的时候如果查李四,那就path like '%,2,%'
下面是我写的代码
protected void Page_Load(object sender, EventArgs e)
{
/*
SaleID SaleName ParentID
1 张三 0
2 李四 1
3 小李 2
4 小李 2
5 小王 1
我的问题是,
*/
//添加数据
DataTable dt = new DataTable(); dt.Columns.Add("SaleID"); dt.Columns.Add("SaleName"); dt.Columns.Add("parentID"); DataRow dr = dt.NewRow();
dr["SaleID"] = 1; dr["SaleName"] = "张三"; dr["ParentID"] = 0;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["SaleID"] = 2; dr["SaleName"] = "李四 "; dr["ParentID"] = 1;
dt.Rows.Add(dr); dr = dt.NewRow();
dr["SaleID"] = 3; dr["SaleName"] = "小李"; dr["ParentID"] = 2;
dt.Rows.Add(dr); dr = dt.NewRow();
dr["SaleID"] = 4; dr["SaleName"] = "小李"; dr["ParentID"] = 2;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["SaleID"] = 5; dr["SaleName"] = "小王"; dr["ParentID"] = 1;
dt.Rows.Add(dr);
//当前要输出的id
int userID = 1;
DataRow row=dt.Select("saleID="+userID)[0];
TreeNode n = new TreeNode(row["saleName"].ToString(), row["saleID"].ToString());
//添加第一项
this.TreeView1.Nodes.Add(n);
BindTree(dt,n, userID); } void BindTree(DataTable dt, TreeNode n, int parentID) {
DataRow[] rows = dt.Select("parentID=" + parentID);
foreach (DataRow dr in rows) {
TreeNode c_Node = new TreeNode(dr["saleName"].ToString(), dr["saleID"].ToString());
n.ChildNodes.Add(c_Node);
BindTree(dt, c_Node, Convert.ToInt32(dr["SaleID"]));
} }
-- 测试数据
CREATE TABLE Catalog(
[ID] int primary key,
TypeName varchar(100),
SupTypeID int
)
INSERT INTO Catalog
SELECT 1, 'Agriculture', 0
UNION
SELECT 2, 'Apparel & Fashion', 0
UNION
SELECT 3, 'Automobile', 0
UNION
SELECT 4, 'Business Services', 0
UNION
SELECT 5, 'Agriculture & By-product Agents', 1
UNION
SELECT 6, 'Agriculture Product Stocks', 1
UNION
SELECT 7, 'Agriculture Products Processing', 1
UNION
SELECT 8, 'Other', 6
UNION
SELECT 9, 'Animal Extract', 6
UNION
SELECT 10, 'Animal Fodders', 7GOCREATE TABLE Product(
PID int primary key,
ProductName varchar(100),
TypeID int,
State int
)
INSERT INTO Product
SELECT 1, 'dfdsf', 8, 1
UNION
SELECT 2, 'dfdsfdsf', 9, 0
UNION
SELECT 3, 'dfdfd32', 10, 1
UNION
SELECT 4, 'dsfdsf', 8, 1
UNION
SELECT 5, 'dfdsfds', 6, 2GO-- 存储过程, 临时表版本
CREATE PROCEDURE dbo.usp_SearchProductByAncestorCatalog(
@AncestorCatalogId int
)
AS
SET NOCOUNT ON
-- 目录树临时表
CREATE TABLE #CatalogTree(
CatalogId int,
Generation int)
DECLARE
@Generation int
SET @Generation = 1
INSERT INTO #CatalogTree
SELECT @AncestorCatalogId, @Generation
WHILE @@RowCount > 0
BEGIN
SET @Generation = @Generation + 1
INSERT #CatalogTree
SELECT c.[ID], @Generation
FROM Catalog c
JOIN #CatalogTree t
ON t.Generation = @Generation - 1 AND t.CatalogId = c.SupTypeId
END
--SELECT * FROM #CatalogTree
SELECT * FROM Product WHERE TypeId IN(SELECT CatalogId FROM #CatalogTree)
SET NOCOUNT OFF
RETURNGO-- 存储过程,表变量版本,性能比临时表好
CREATE PROCEDURE dbo.usp_SearchProductByAncestorCatalog2(
@AncestorCatalogId int
)
AS
SET NOCOUNT ON
-- 目录树表变量
DECLARE @CatalogTree TABLE (
CatalogId int,
Generation int)
DECLARE
@Generation int
SET @Generation = 1
INSERT INTO @CatalogTree
SELECT @AncestorCatalogId, @Generation
WHILE @@RowCount > 0
BEGIN
SET @Generation = @Generation + 1
INSERT @CatalogTree
SELECT c.[ID], @Generation
FROM Catalog c
JOIN @CatalogTree t
ON t.Generation = @Generation - 1 AND t.CatalogId = c.SupTypeId
END
--SELECT * FROM @CatalogTree
SELECT * FROM Product WHERE TypeId IN(SELECT CatalogId FROM @CatalogTree)
SET NOCOUNT OFF
RETURNGOEXEC usp_SearchProductByAncestorCatalog 1
EXEC usp_SearchProductByAncestorCatalog2 1
2。 根据 1 获取的数据递归创建 TreeView 节点
ASP.NET DEMO 15: 如何编程动态创建
TreeView http://www.cnblogs.com/Jinglecat/archive/2007/08/10/850090.html
可以看到,每种策略,都有他的优缺点,所以优秀的设计方案,需要根据实际需求,以及大量经验的积累来抉择的Hope helpful!