部门数据库的设计：
代码：
/// <summary>
/// 获取部门（入口）
/// </summary>
/// <returns></returns>
[HttpGet]
[Route("GetDepart")]
public ResultEntity<List<JieDian>> GetDepart()
{
ResultEntity<List<JieDian>> result = new ResultEntity<List<JieDian>>();
int? officeId = ;
try
{
officeId = new AuthInfo().GetOfficeId(User as ClaimsPrincipal);
if (!officeId.HasValue)
{
result.IsSuccess = false;
result.ErrorCode = ;
result.Msg = "未登录或已过期";
return result;
}
ProvinceMeetCase meetCase = new ProvinceMeetCase();
var data = meetCase.creatTree();
result.Data = data;
result.IsSuccess = true;
result.Msg = "部门获取成功";
}
catch (Exception e)
{
result.ErrorCode = ;
result.Msg = e.ToString();
} return result;
}
第一种解决方案（递归）：
//生成树的方法
public List<JieDian> creatTree()
{
List<JieDian> result = new List<JieDian>();
try
{
using (var container = new TRA_EXTContainer())
{
string sql = string.Format(@"SELECT a.Name [DepartName],a.Id [DepartId]
FROM [HYSYZSCCODB].[dbo].[Department] a
where a.IsValid='true' and a.Level = 1");
//这里改成你的数据库表
var data = container.Database.SqlQuery<DepartDTO>(sql).ToList(); //这里我直接调用了我库中的类
foreach (var item in data)
{
JieDian jd = new JieDian();
jd.Id = item.DepartId;
jd.Name = item.DepartName;
var d = creatTheTree(item.DepartId, jd);
if (d == null)
{
result.Add(jd);
continue;
}
result.Add(d);
}
}
}
catch (Exception e)
{
throw e;
} return result;
} //生成树的方法(自身方法不断循环)
public JieDian creatTheTree(Guid parentId, JieDian jd)
{
//获取
DepartDTO[] items = GetTheItems(parentId);
//如果没有字节点了，那就返回空
if (items.Length == )
return null;
List<JieDian> jdList = new List<JieDian>();
for (int i = ; i < items.Length; i++)
{
JieDian jiedian = new JieDian();
jiedian.Id = items[i].DepartId;
jiedian.Name = items[i].DepartName;
jiedian.ParentId = items[i].ParentId;
//递归循环
creatTheTree(items[i].DepartId, jiedian);
jdList.Add(jiedian);
}
jd.children = jdList.ToArray(); //由于对象是引用类型，因为可以改变参数的值
return jd;
} public class JieDian
{
/// <summary>
///
/// </summary>
public string Name { get; set; }
/// <summary>
///
/// </summary>
public Guid Id { get; set; }
/// <summary>
///
/// </summary>
public Guid ParentId { get; set; }
/// <summary>
///
/// </summary>
public JieDian[] children = null;
}
第二种解决方案（key-Value）：
#region 尝试不用递归生成树（更简洁高效）
public class ResChapter
{
public string Id { get; set; }
public string Name { get; set; }
public string Pid { get; set; }
public List<ResChapter> Children { get; set; }
} public List<ResChapter> toTree()
{
using (var container = new TRA_EXTContainer())
{
//数据库中的Id和父级Id都是Guid类型，取出的时候转为string类型，而第一级部门PDepartmentId为空，我们用isnull(cast(a.Id as varchar(40)),'')进行处理，如果为空就返回空字符串，否则就把guid转为字符串取出来。
//cast(a.Id as varchar(40)) -----将id转为字符串的操作
string sql = string.Format(@"SELECT a.Name [Name],isnull(cast(a.Id as varchar(40)),'') [Id],isnull(cast(a.PDepartmentId as varchar(40)),'') [Pid]
FROM [HYSYZSCCODB].[dbo].[Department] a
where a.IsValid='true' ");
//这里改成你的数据库表
var data = container.Database.SqlQuery<ResChapter>(sql).ToList(); //这里我直接调用了我库中的类
var chapterlist = data;
var dic = new Dictionary<string, ResChapter>(chapterlist.Count);
foreach (var chapter in chapterlist)
{
dic.Add(chapter.Id, chapter);
}
foreach (var chapter in dic.Values)
{
if (dic.ContainsKey(chapter.Pid))
{
if (dic[chapter.Pid].Children == null)
dic[chapter.Pid].Children = new List<ResChapter>();
dic[chapter.Pid].Children.Add(chapter);
}
}
var dateTree = dic.Values.Where(t => string.IsNullOrEmpty(t.Pid)).ToList();
return dateTree;
}
}
#endregion
获取上属父级部门：
using (var context = new YZS_TRAEntities())
{
var Office = context.事务所主任.Where(f => f.事务所主任ID == officeId).FirstOrDefault();
if (Office == null)
{
result.ErrorCode = ;
result.IsSuccess = false;
result.Msg = "未找到当前登陆人";
return result;
}
UpdateMeetStatus(container);
string sql = string.Format(@"SELECT a.[Name] [EmployeeName],a.Mobile [EmployeePhone],b.Name [DepartName],b.Id [DepartId],b.PDepartmentId [ParentId]
FROM [HYSYZSCCODB].[dbo].[Employee] a
inner join [HYSYZSCCODB].[dbo].[Department] b on b.EmployeeId = a.Id
where b.IsValid='true' and a.Mobile =" + "'" + Office.手机.Trim() + "'");
var entity = container.Database.SqlQuery<OfficeDTO>(sql).FirstOrDefault(); //当前事务所主任的部门
if (entity.ParentId.HasValue)
{
var data = GetParentDeparts(entity.ParentId.Value, list); //获取上级所有部门
data.Add(entity); //再把自身加上
ids = data.Select(f => f.DepartId).ToList();
}
ids.Add(entity.DepartId);
} #region 递归获取父级部门
public List<OfficeDTO> GetParentDeparts(Guid parentId, List<OfficeDTO> list)
{
using (var container = new TRA_EXTContainer())
{ //根据父节点获取选项集合
string sql = string.Format(@"SELECT a.Name [DepartName],a.Id [DepartId],a.PDepartmentId [ParentId]
FROM [HYSYZSCCODB].[dbo].[Department] a
where a.IsValid='true' and a.Id = " + "'" + parentId + "'");
//这里改成你的数据库表
var entity = container.Database.SqlQuery<OfficeDTO>(sql).FirstOrDefault();
if (entity != null)
{
list.Add(entity);
if (entity.ParentId.HasValue)
{
GetParentDeparts(entity.ParentId.Value, list);
}
}
}
return list; }
#endregion