数据库(数据库名为test)中表的内容:
if exists (select * from sysobjects where id = OBJECT_ID('[ItemTree]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [ItemTree]
CREATE TABLE [ItemTree] (
[id] [bigint] IDENTITY (1, 1) NOT NULL,
[title] [nvarchar] (50) NOT NULL,
[pageName] [nvarchar] (50) NULL,
[parentID] [bigint] NOT NULL)
ALTER TABLE [ItemTree] WITH NOCHECK ADD CONSTRAINT [PK_ItemTree] PRIMARY KEY NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [ItemTree] ON
INSERT [ItemTree] ([id],[title],[parentID]) VALUES ( 1,N'会员管理',0)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 2,N'添加会员',N'addmember',1)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 3,N'编辑会员',N'editmember',1)
INSERT [ItemTree] ([id],[title],[parentID]) VALUES ( 4,N'公告管理',0)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 5,N'添加公告',N'addnotice',4)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 6,N'编辑公告',N'editnotice',4)
INSERT [ItemTree] ([id],[title],[parentID]) VALUES ( 7,N'产品管理',0)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 8,N'产品入库',N'inproduct',7)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 9,N'产品出库',N'outproduct',7)
INSERT [ItemTree] ([id],[title],[parentID]) VALUES ( 10,N'产品清单',7)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 12,N'出库清单',N'outproductmenu',10)
INSERT [ItemTree] ([id],[title],[pageName],[parentID]) VALUES ( 13,N'入库清单',N'inproductmenu',10)
SET IDENTITY_INSERT [ItemTree] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[ItemTreeToUsers]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [ItemTreeToUsers]
CREATE TABLE [ItemTreeToUsers] (
[id] [bigint] IDENTITY (1, 1) NOT NULL,
[ItemTreeID] [bigint] NOT NULL,
[UserID] [bigint] NOT NULL)
ALTER TABLE [ItemTreeToUsers] WITH NOCHECK ADD CONSTRAINT [PK_ItemTreeToUsers] PRIMARY KEY NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [ItemTreeToUsers] ON
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 1,1,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 4,7,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 8,12,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 9,4,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 10,2,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 11,10,1)
INSERT [ItemTreeToUsers] ([id],[ItemTreeID],[UserID]) VALUES ( 12,5,1)
SET IDENTITY_INSERT [ItemTreeToUsers] OFF
数据模型:
界面设计:
操作类:
public class CommonFuncs
{
#region 加载树结构
/// <summary>
/// 加载树结构,没有加载权限
/// </summary>
/// <param name="tv"></param>
/// <returns></returns>
public static TreeView GetTree(TreeView tv)
{
DataTable dt = null;
dt = new Tools.Common.DbHelper().ExecuteDataTable("select * from ItemTree");//获取全部数据到dt中
var roots = from node in dt.AsEnumerable()
where node.Field<long>("parentID") == 0
select node;
tv.Nodes.Clear();
TreeNode tnode;
foreach (var node in roots)
{
tnode = new TreeNode();
tnode.Tag = node.Field<long>("parentID");
tnode.Text = node.Field<string>("title");
tnode.ToolTipText = node.Field<string>("pageName");
tnode.Name = node.Field<long>("id").ToString();//Name可用来存放ID,它是唯一的
tv.Nodes.Add(tnode);
GetChildNodes(tnode, dt);
}
return tv;
}
/// <summary>
/// 加载子节点
/// </summary>
/// <param name="parentNode">父节点</param>
/// <param name="dt">整个结构的dt</param>
private static void GetChildNodes(TreeNode parentNode, DataTable dt)
{
long ID = Convert.ToInt64(parentNode.Name);
var ChildNodes = from node in dt.AsEnumerable()
where node.Field<long>("parentID") == ID
select node;
TreeNode tnode;
foreach (var node in ChildNodes)
{
tnode = new TreeNode();
tnode.Tag = node.Field<long>("parentID");
tnode.Text = node.Field<string>("title");
tnode.ToolTipText = node.Field<string>("pageName");
tnode.Name = node.Field<long>("id").ToString();
parentNode.Nodes.Add(tnode);
if (dt.AsEnumerable().Count(item => item.Field<long>("parentID") == node.Field<long>("id")) > 0)
{
GetChildNodes(tnode, dt);
}
}
}
#endregion
#region 加载初始权限到TreeView
/// <summary>
/// 加载初始权限到TreeView
/// </summary>
/// <param name="tv">TreeView</param>
/// <param name="userid">用户id号</param>
public static void GetPermis(TreeView tv, long userid)
{
//查询此用户的所有权限
DataTable dtuserpermission = new Tools.Common.DbHelper().ExecuteDataTable("select * from ItemTreeToUsers where userid=" + userid);
GetPermission(tv.Nodes, dtuserpermission, userid);
}
/// <summary>
/// 为节点集合初始化选择状态
/// </summary>
/// <param name="tns">节点集合</param>
/// <param name="dt">权限DataTable</param>
/// <param name="userid">用户id</param>
private static void GetPermission(TreeNodeCollection tns, DataTable dt, long userid)
{
for (int i = 0; i < tns.Count; i++)
{
TreeNode tn = tns[i];
var count = dt.AsEnumerable().Count(item => item.Field<long>("userid") == userid && item.Field<long>("itemtreeID") == Convert.ToInt64(tn.Name));
if (count == 0)
{
tn.Checked = false;
}
else tn.Checked = true;
if (tn.Nodes.Count > 0)//加载子节点权限
{
GetPermission(tn.Nodes, dt, userid);
}
}
}
#endregion
#region 设置TreeView上选择的权限到数据库
/// <summary>
/// 设置TreeView上选择的权限到数据库
/// </summary>
/// <param name="tv">TreeView</param>
/// <param name="userid">用户id号</param>
/// <returns></returns>
public static bool SetPermis(TreeView tv, long userid)
{
bool isSuccess = true;
try
{
TestEntities myEntities = new TestEntities();//TestModel.edmx文件生成的,test为数据库名
SetPermission(myEntities, tv.Nodes, userid);//这个函数只是标记,并不操作数据库
myEntities.SaveChanges();//接受更改,一次性写回数据库
}
catch
{
isSuccess = false;
}
return isSuccess;
}
/// <summary>
/// 设置每个节点上的权限到数据库
/// </summary>
/// <param name="myEntities"></param>
/// <param name="tns"></param>
/// <param name="userid"></param>
private static void SetPermission(TestEntities myEntities, TreeNodeCollection tns, long userid)
{
for (int i = 0; i < tns.Count; i++)
{
TreeNode tn = tns[i];
long id = Convert.ToInt64(tn.Name);
var OldData = from t in myEntities.ItemTreeToUsers
where t.ItemTreeID == id && t.UserID == userid
select t;
if (tn.Checked)
{
if (OldData.Count() == 0)//没有记录才插入
{
ItemTreeToUsers newUserItem = new ItemTreeToUsers
{
ItemTreeID = Convert.ToInt64(tn.Name),
UserID = userid
};
myEntities.AddToItemTreeToUsers(newUserItem);
}
}
else
{
if (OldData.Count() > 0)//有记录才删除
{
foreach (var toDelete in OldData)//可能的都删除
{
myEntities.DeleteObject(toDelete);
}
}
}
if (tn.Nodes.Count > 0)//设置子节点权限
{
SetPermission(myEntities, tn.Nodes, userid);
}
}
}
#endregion
}
Form1.cs代码
public partial class Form1 : Form
{
private long UserID = 1;
private bool isInited=false;
//构造函数,窗体上 TreeView名称为:TVUser
public Form1()
{
InitializeComponent();
TVUser.CheckBoxes = true;//TreeView前有复选框
}
//初始化
private void Form1_Load(object sender, EventArgs e)
{
loadData();
}
//刷新按钮
private void buttonRefresh_Click(object sender, EventArgs e)
{
loadData();
}
private void loadData()
{
CommonFuncs.GetTree(TVUser);
CommonFuncs.GetPermis(TVUser, UserID);
isInited = true;
}
//保存按钮
private void buttonSave_Click(object sender, EventArgs e)
{
bool flag= CommonFuncs.SetPermis(TVUser, UserID);
if (flag)
{
MessageBox.Show("设置成功!");
}
else
{
MessageBox.Show("设置失败!");
}
}
//treeView事件
private void TVUser_AfterCheck(object sender, TreeViewEventArgs e)
{
try
{
if (!isInited) return;
if (e.Node.Checked)
{
if (e.Node.Parent != null)
{
e.Node.Parent.Checked = true;//选择时,把父节点选中
}
}
else
{
UnCheckSubNodes(e.Node);
}
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
}
#region 子节点取消选中
/// <summary>
/// 子节点取消选中
/// </summary>
/// <param name="node"></param>
private void UnCheckSubNodes(TreeNode node)
{
if (node.Nodes.Count > 0)
{
foreach (TreeNode subnode in node.Nodes)
{
subnode.Checked = false;//取消时,把子节点全部取消
if (subnode.Nodes.Count > 0)
{
UnCheckSubNodes(subnode);//无限级取消子节点
}
}
}
}
#endregion
}
运行正确,并能将数据更改到数据库。
界面如下: