多级设计

查询数据：

public DataSet GetMyList(string strWhere)
{

StringBuilder strSql = new StringBuilder();
strSql.Append(";with f as ( select * from Table");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}

strSql.Append(" union all select a.* from Table a, f where a.Id=f.ParentId )");
strSql.Append(" select * from Table where id in(select id from f)");

return DbHelperSQL.Query(strSql.ToString());
}

contrroller：

#region ===查询===
public ActionResult Category()
{
return View();
}

/// <summary>
/// 获取分类数据
/// </summary>
/// <param name="CategoryName">课程名称</param>
/// <returns></returns>
public string CategoryData(string CategoryName)
{
CategoryName = HttpUtility.UrlDecode(CategoryName).Trim();
string where = "Status=1 " ;
if (!string.IsNullOrEmpty(CategoryName))
{
where += " and (CHARINDEX('" + CategoryName + "',Name)>0 or CHARINDEX('" + CategoryName + "',EnName)>0)";
}
var categoryList = _categoryBLL.GetMyModelList(where);//CarrierId
//搜索：1查找所有上级，2
return "{"response": [" + GetJsonData(categoryList, Guid.Empty, 0).Trim(',') + "]}";
}
/// <summary>
/// 转换json数据，tree
/// </summary>
/// <param name="list"></param>
/// <param name="parent"></param>
/// <param name="level"></param>
/// <returns></returns>
string GetJsonData(List<Model.CategoryModel> list, Guid parent, int level)
{
StringBuilder sb = new StringBuilder();
var thisList = list.Where(m => m.ParentId.Equals(parent)).OrderBy(m => m.SortNum);
foreach (var item in thisList)
{
string parentStr = string.Empty;
if (level > 0)
parentStr = parent.ToString();
var childList = list.Where(m => m.ParentId.Equals(item.Id)).OrderBy(m => m.SortNum).ToList();
sb.Append("{"Id":"" + item.Id + "","level":"" + level + "","parent":"" + parentStr + "","isLeaf":" + (childList.Count == 0).ToString().ToLower() + ","expanded":true,"loaded":true,"Name":"" + item.Name + "","EnName":"" + item.EnName + "","SortNum":"" + item.SortNum + ""},");
string childStr = GetJsonData(list, item.Id, level + 1);
sb.Append(childStr);
}
return sb.ToString();
}

#endregion

#region ===新增修改===
/// <summary>
/// 分类编辑或者新增
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public ActionResult EditCategory(Guid? Id, string oper)
{
Model.CategoryModel model = new Model.CategoryModel();
if (Id != null)
{
model = _categoryBLL.GetModel(Id.Value);
if (!string.IsNullOrEmpty(oper) && oper.Equals("add"))
{
//新增
ViewBag.CategoryList = GetCategoryList(true, model.Id, null);
//int sortNum = model.SortNum + 1;
model = new Model.CategoryModel();
//model.SortNum = sortNum;
}
else
{
//编辑
ViewBag.CategoryList = GetCategoryList(true, model.ParentId, Id.Value);
}

}
else
{
//新增
ViewBag.CategoryList = GetCategoryList(true, Guid.Empty, null);
}

return View(model);
}

List<SelectListItem> selectList = new List<SelectListItem>();
/// <summary>
/// 获取分类信息
/// </summary>
/// <param name="withSelect"></param>
/// <param name="selectId"></param>
/// <param name="selfId">排除ID</param>
/// <returns></returns>
private List<SelectListItem> GetCategoryList(bool withSelect, Guid selectId, Guid? selfId)
{
var cList = _categoryBLL.GetModelList("Status=1 " );
if (selfId != null) cList = cList.Where(m => !m.Id.Equals(selfId.Value)).ToList();
selectList = new List<SelectListItem>();
if (withSelect)
{
selectList.Add(new SelectListItem { Value = Guid.Empty.ToString(), Text = "请选择..." });//LanguageHelper.GetWord("PleaseSelect")
}
AddCategoryToList(cList, Guid.Empty, 0, selectId);
//SelectListItem select;

//foreach (var model in cList)
//{
// select = new SelectListItem
// {
// Value = model.Id.ToString(),
// Text = model.Name
// };
// if (selectId.Equals(model.Id))
// {
// select.Selected = true;
// }
// selectList.Add(select);
//}
return selectList;
}
/// <summary>
/// 递归分类层级
/// </summary>
/// <param name="list">列表</param>
/// <param name="parent">父级</param>
/// <param name="level">等级</param>
/// <param name="selectId">选中</param>
void AddCategoryToList(List<Model.CategoryModel> list, Guid parent, int level, Guid selectId)
{
var needList = list.Where(m => m.ParentId.Equals(parent));

foreach (var item in needList)
{
SelectListItem select;
select = new SelectListItem
{
Value = item.Id.ToString(),
Text = base.getEmpty(level) + item.Name
};
if (selectId.Equals(item.Id))
{
select.Selected = true;
}
selectList.Add(select);
AddCategoryToList(list, item.Id, level + 1, selectId);
}
}

/// <summary>
/// 执行修改
/// </summary>
/// <param name="model"></param>
/// <param name="oper">操作类型</param>
/// <returns></returns>
[HttpPost]
public JsonResult DoEditCategory(Model.CategoryModel model, string oper)
{
//01新增，02修改
ViewModule_ResponseInfo responseInfo = new ViewModule_ResponseInfo();
var timeNow = DateTime.Now;
model.LastUpdateDatetime = timeNow;
model.LastUpdateUserId = this.LogOnUser.User.UserID;
model.ParentId = Guid.Parse(Request.Form["CategoryList"]);// Guid.Empty;
model.Status = 1;
if (oper == "add")
{
model.CreateDateTime = timeNow;
model.CreateUserId = this.LogOnUser.User.UserID;
model.Id = GuidHelper.GetGuid();
responseInfo.Success = _categoryBLL.Add(model);
responseInfo.Msg = "新增成功" + (responseInfo.Success ? "成功" : "失败");
}
else
{
var category = _categoryBLL.GetModel(model.Id);
model.CreateDateTime = category.CreateDateTime;
model.CreateUserId = category.CreateUserId;
responseInfo.Success = _categoryBLL.Update(model);
responseInfo.Msg = "修改" + (responseInfo.Success ? "成功" : "失败");
}
return Json(responseInfo);
}

#endregion