1.添加根节点: hierarchyid GetRoot()方法
--创建数据库
create table Employeeh(EmployeeID int,Name varchar(500),ManagerID int,EmplyeeNode hierarchyID)
--添加根节点的记录
insert Employeeh values(1,'CEO',null,hierarchyid::GetRoot()) --添加两个平级的子节点
--1.定义父节点
declare @rootmanager hierarchyid
--对变量进行赋值
set @rootmanager=(select EmplyeeNode from Employeeh where EmployeeID=1)
--父节点作为子节点进行插入
insert Employeeh values(2,'Manager1',1,@rootmanager.GetDescendant(null,null)) --插入第二条数据
declare @manager1 hierarchyid
set @manager1=(select EmplyeeNode from Employeeh where EmployeeID=2)
insert Employeeh values(3,'Manager2',1,@rootmanager.GetDescendant(@manager1,null)) --访问表的数据
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString() from Employeeh
结果:
2.添加子节点:父节点GetDescendant()方法
在子节点中在添加子节点:
--在Manager1 中添加子节点
declare @manager1 hierarchyid
set @manager1=(select EmplyeeNode from Employeeh where EmployeeID=3)
insert Employeeh values(4,'SubManager1',2,@manager1.GetDescendant(null,null)) declare @manager2 hierarchyid
set @manager2=(select EmplyeeNode from Employeeh where EmployeeID=3)
insert Employeeh values(5,'SubManager2',3,@manager2.GetDescendant(null,null)) --查询
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString() from Employeeh
--访问节点所在的层次结构
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString(),EmplyeeNode.GetLevel() from Employeeh
3.获取当前级别:当前节点GetLevel()方法
4.获取当前节点下所有子节点,判断条件:where 列名 IsDescendantOf(当前节点)=1 or 0
--查看当前节点下的子节点
--1.申明当前的节点
declare @current hierarchyid
set @current=(select EmplyeeNode from Employeeh where Name='Manager2')
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString(),EmplyeeNode.GetLevel() from Employeeh
--判断当前节点的子节点为真
where EmplyeeNode.IsDescendantOf(@current)=1
5.获取当前节点的父节点:判断条件:where 列名 当前节点 GetAncestor(级别)
--查看当前节点所在的父节点
declare @current hierarchyid
set @current=(select EmplyeeNode from Employeeh where Name='SubManager2')
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString(),EmplyeeNode.GetLevel() from Employeeh
where EmplyeeNode=@current.GetAncestor(1)
6.移动节点到新的父节点:Set 列名=当前节点 GetReparentedValue(@OldParent,@NewParent) where
7.索引:深度优化(查询所有的后代)、宽度优先(查询直接后代)
将旧表数据导入层次结构数据新表
新建旧表插入数据:
create table EmployeeOld(employeeid int,managerid int,name varchar(500))
insert EmployeeOld values(1,null,'ceo')
insert EmployeeOld values(2,1,'manager1')
insert EmployeeOld values(3,1,'manager2')
insert EmployeeOld values(4,2,'sub1')
insert EmployeeOld values(5,2,'sub2')
insert EmployeeOld values(6,3,'sub3')
insert EmployeeOld values(7,3,'sub4')
把旧表的数据导入到新表中:
--创建新表(有层次结构的表)
create table EmployeeNew(employeeid int,managerid int,name varchar(500),node hierarchyid) --查看以前的表结构
select EmployeeID,Name,ManagerID,EmplyeeNode.ToString() from Employeeh --思路:1.获取子节点在父节点下所对应的的行号
--2.将父节点的hierarchyid 给链接到行号的前面 --1.通用表表达式 获取旧表的数据 并且存储在父节点下的行号
--2.第二个通用表达式使用第一个通用表达式的值,同时连接父节点 hierarchyid的值
;with eh(employeeid,managerid,name,position)
as
(
select employeeid,managerid,name,ROW_NUMBER() over(partition by managerid
order by employeeid) from EmployeeOld
),
employeenewtemp(employeeid,managerid,name,node)
as
(
select employeeid,managerid,name,hierarchyid::GetRoot() from eh where managerid is null
union all
select eh.employeeid,eh.managerid,eh.name,
CAST(employeenewtemp.node.ToString()+CAST(eh.position as varchar(500))
+'/' as hierarchyid) from eh inner join employeenewtemp on eh.managerid=employeenewtemp.employeeid
)
--插入到新表中
insert into EmployeeNew select employeeid,managerid,name,node.ToString() from employeenewtemp
查看数据:
select * from EmployeeOld
select employeeid,managerid,name,node.ToString() from EmployeeNew
C#中控制台调用。
一:建立连接返回数据
引入这个命名空间
using Microsoft.SqlServer.Types;
public DataTable getdata()
{
SqlConnection conn = new SqlConnection("data source=localhost;initial catalog=HRDB1;user id=sa;password=sunliyuan123456");
//打开连接
conn.Open();
//执行Sql语句
SqlDataAdapter da = new SqlDataAdapter("select * from EmployeeNew", conn);
//实例化DataSet
DataSet ds = new DataSet();
//进行填充
da.Fill(ds, "EmployeeNew");
//释放资源
ds.Dispose();
conn.Close();
//返回第一个表
return ds.Tables[0];
}
二:生成树节点:
/// <summary>
/// 生成树的节点
/// </summary>
/// <param name="oParent">树节点</param>
/// <param name="oTable">得到的表</param>
private void LoadTreeNode(TreeNode oParent,DataTable oTable)
{
//清空所有的节点
oParent.Nodes.Clear();
//取得父节点的数据 放入DataRow
DataRow oRow=(DataRow)oParent.Tag;
//获取node的值并进行转换
SqlHierarchyId iID =(SqlHierarchyId)oRow["node"];
//的到第一节父节点 出入的值是父节点的id 说明传进来的值就是子节点
var query = from employee in oTable.AsEnumerable()
where employee.Field<SqlHierarchyId>("node").GetAncestor(1)
.Equals(iID)
select employee;
//把数据放入到DataView 中
DataView oDV = query.AsDataView();
foreach(DataRowView oR in oDV)
{
//得到一节子节点
TreeNode oNode = new TreeNode(oR["name"].ToString());
oNode.Tag = oR.Row;
LoadTreeNode(oNode,oTable);
//附加到父节点
oParent.Nodes.Add(oNode);
}
}
三:生成树视图并得到根节点
/// <summary>
/// 生成树视图并得到根节点
/// </summary>
/// <param name="oTV"></param>
/// <param name="oTable"></param>
/// <param name="key"></param>
/// <param name="text"></param>
public void LoadTreeView(TreeView oTV,DataTable oTable,string key,string text)
{
oTV.Nodes.Clear();
SqlHierarchyId iID = new SqlHierarchyId();
TreeNode roonode;
//查询根节点
var query = from employee in oTable.AsEnumerable()
where employee.Field<SqlHierarchyId>(key).GetAncestor(1)
.Equals(iID)
select employee;
DataView oDV = query.AsDataView();
//找到根节点
if(oDV.Count ==1)
{
roonode = new TreeNode(oDV[0][text].ToString());
roonode.Tag = oDV[0].Row;
LoadTreeNode(roonode, oTable);
oTV.Nodes.Add(roonode);
}
}
对数据进行绑定:
private clsData oData = new clsData();
private DataTable dtData; private void btnGetData_Click(object sender, EventArgs e)
{
dtData = oData.getdata();
dgData.DataSource = dtData; } private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
} private void doTV_Click(object sender, EventArgs e)
{ oData.LoadTreeView(tvData, dtData, "node", "name");
}
效果: