if exists (select 1 from sysobjects where id = object_id('tb_Menu') and type = 'U') drop table tb_Menu go /*==============================================================*/ /* Table: tb_Menu */ /*==============================================================*/ create table tb_Menu ( Id int identity, name varchar(50) null, twoname varchar(50) null, pId int null, level int null, state int null, sort int null, dt datetime null, url varchar(100) null, des text null, cNum int null, constraint PK_TB_MENU primary key (Id) ) go
/**递归删除菜单**/ if exists (select * from sysobjects where name='proc_menu_P_delete') drop proc proc_menu_P_delete go create proc proc_menu_P_delete(@id int) as declare @count int,@tmpid int,@err int select @count=count(1) from tb_menu where pId=@id--通过父Id获得子集数据 if(@count>0)--判断是否存在子集数据 begin --根据父Id获得子集并将子集数据Id保存到游标中 declare ar_cursor cursor local for select id from tb_menu where pid=@id open ar_cursor--打开游标 fetch next from ar_cursor into @tmpid --取值 /**@@FETCH_STATUS是MSSQL的一个全局变量(0:FETCH 语句成功,-1:FETCH 语句失败或此行不在结果集中,-2:被提取的行不存在 @@fetch_status值的改变是通过fetch next from实现的,“FETCH NEXT FROM Cursor”)**/ while @@FETCH_STATUS=0 begin exec proc_menu_P_delete @tmpid--将游标中的子集Id作为参数调用存储过程（递归） --递归出来以后将游标中的下一个Id赋值给变量 fetch next from ar_cursor into @tmpid end close ar_cursor deallocate ar_cursor end delete tb_menu where Id=@id go /**删除菜单**/ if exists (select * from sysobjects where name='proc_menu_delete') drop proc proc_menu_delete go create proc proc_menu_delete(@id int) as begin declare @cNum int,@pId int begin try begin tran select @cNum=-(CNum+1),@pId=pId from tb_Menu where Id=@id exec proc_menu_updatecNum @pId,@cNum--修改父节点的子节点个数 exec proc_menu_P_delete @id--删除自身及其子节点 commit tran end try begin catch rollback tran end catch end go /**修改父节点的子节点数量**/ if exists (select * from sysobjects where name='proc_menu_updatecNum') drop proc proc_menu_updatecNum go create proc proc_menu_updatecNum(@pId int,@cNum int) as declare @tmpId int,@_pId int select @_pId=pId from tb_menu where id=@pId if(@_pId<>0) begin declare cor_CNum cursor local for select pId from tb_menu where id=@pId open cor_CNum fetch next from cor_CNum into @tmpId while @@FETCH_STATUS=0 begin exec proc_menu_updatecNum @tmpId,@cNum fetch next from cor_CNum into @tmpId end close cor_CNum deallocate cor_CNum end update tb_Menu set cNum=cNum+@cNum where id=@pId go /**修改子节点的深度**/ if exists (select * from sysobjects where name='proc_menu_updateLevel') drop proc proc_menu_updateLevel go create proc proc_menu_updateLevel(@id int,@level int) as begin declare @tmpid int,@count int select @count=COUNT(1) from tb_Menu where pId=@id if(@count<>0) begin declare cur_level cursor local for select id from tb_menu where pId=@id open cur_level fetch next from cur_level into @tmpid while(@@FETCH_STATUS=0) begin exec proc_menu_updateLevel @tmpid,@level fetch next from cur_level into @tmpid end close cur_level deallocate cur_level end update tb_Menu set level=level+@level where Id=@id end go /**修改节点顺序：针对Jquery的zTree控件的拖动节点排序功能**/ if exists (select * from sysobjects where name='proc_menu_updateSort') drop proc proc_menu_updateSort go create proc proc_menu_updateSort(@id int,@newpId int,@sibId int,@dir int) as begin /** @id：被移动的节点，@newpId：新父节点，@sibId：新父节点下的兄弟节点，@dir：方向，移动目标前面：1，移到后面：-1 假设：原节点父节点为1，兄弟节点值以及顺序为：5,4,3,2,1，目标父节点为2，兄弟节点值以及顺序为：3,2,1 a：现将原节点4，移动到父节点为2的子节点2的“前”面 改变后的值为： 原：5,4,3,2,1 =>(移走3并且将大于3的减1)=> 4,3,2,1 新：3,2,1 =>(将大于2的节点加1)=>4,2,1 =>(新插入的排序为：目标节点排序值+1)=>4,3(插入值),2,1 b：现将原节点4，移动到父节点为2的子节点2的“后”面 改变后的值为： 原：5,4,3,2,1 =>(移走3并且将大于3的减1)=> 4,3,2,1 新：3,2,1 =>(将大于等于2的节点加1)=>4,3,1 =>(新插入的排序为：目标节点排序值+1)=>4,3,2(插入值),1 **/ begin try begin tran --修改原兄弟节点的顺序,大于被移走的节点全部-1 declare @oldsort int,@oldpId int,@cNum int,@level int select @oldsort=sort,@oldpId=pId,@cNum=CNum,@level=level from tb_Menu where Id=@id update tb_Menu set sort=sort-1 where sort>@oldsort and pId=@oldpId if(@sibId<>0)--是否指定了兄弟节点 begin --修改新兄弟节点的顺序、修改移动后自己节点顺序 declare @newsort int,@sibIdsort int --获得目标节点的排序，并将目标节点的父节点重新付给传进来的@newpId(防止参数@newpId传错) select @sibIdsort=sort,@newpId=pId from tb_Menu where Id=@sibId if(@dir=1)--移动到目标节点的前面 begin update tb_Menu set sort=sort+1 where pId=@newpId and sort>@sibIdsort update tb_Menu set sort=@sibIdsort+1 where id=@id end else if(@dir=-1) begin update tb_Menu set sort=sort+1 where pId=@newpId and sort>=@sibIdsort update tb_Menu set sort=@sibIdsort where Id=@id end end else if(@newpId<>0 and @sibId=0)--直接移动某个父节点最下面，即没有选中目标兄弟节点 begin update tb_Menu set sort=sort+1 where pId=@newpId update tb_Menu set sort=1 where Id=@id end if(@newpId<>@oldpId)--判断是否改变了父节点 begin declare @newpLevel int set @cNum=@cNum+1 if(@newpId<>0) begin select @newpLevel=level from tb_Menu where id=@newpId end else begin set @newpLevel=0 end update tb_Menu set pId=@newpId where Id=@id declare @levelCount int PRINT @newpLevel set @levelCount=@newpLevel+1-@level exec proc_menu_updateLevel @id,@levelCount exec proc_menu_updatecNum @newpId,@cNum set @cNum=-(@cNum) exec proc_menu_updatecNum @oldpId,@cNum end commit tran end try begin catch rollback tran end catch end go /**新增节点**/ if exists (select * from sysobjects where name='proc_menu_add') drop proc proc_menu_add go create proc proc_menu_add(@name varchar(50),@pId int,@id int output) as begin begin try begin tran declare @pLevel int if(@pId<>0) begin select @pLevel=level+1 from tb_Menu where Id=@pId end else begin set @pLevel=1 end insert into tb_Menu(name,twoname,pId,level,cNum,state,sort,dt,url,des) values(@name,'',@pId,@pLevel,0,1,0,getdate(),'','') set @id=@@IDENTITY select @id update tb_Menu set sort=sort+1 where pId=@pId--兄弟元素排序加1 exec proc_menu_updatecNum @pId,1--修改父节点的子节点数量 commit tran end try begin catch set @id=0 rollback tran end catch end go /**根据节点获得所有子节点数据**/ --方法1：连接查询 if exists (select * from sysobjects where name='proc_menu_select') drop procedure proc_menu_select go create proc proc_menu_select(@id int) as declare @i int declare @count int, @tmpid int create table #tb(id int,name varchar(50),pId int,level int,sort int) select @count=COUNT(1) from tb_Menu where pId=@id insert #tb select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m where pId=@id if(@id=0)--2014/2/27修改：针对Id为0时查不到数据
begin
@i=0
end
else
begin
select @i=level from tb_Menu where id=@id
end
while(@count<>0) begin set @i=@i+1 print @i select @count=COUNT(1) from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i insert #tb select m.id,m.name,m.pId,m.level,m.sort from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i end select id,name,pId,level,sort from #tb go --方法2：递归查询 if exists(select * from sysobjects where name='proc_menu_sele') drop proc proc_menu_sele go create proc proc_menu_sele(@id int,@t int) as if(@t<>0) begin declare @tmppId int select @tmppId=pId from tb_Menu where Id=@id create table #tab( id int, name varchar(50), twoname varchar(50), pId int, level int, state int, url varchar(100) ) end declare @tmpid int,@count int select @count=COUNT(1) from tb_Menu where Id=@id if(@count<>0) begin insert INTO #tab select id,name,twoname,pId,level,state,url from tb_Menu where Id=@id declare cur_select cursor local for select id from tb_menu where pId=@id open cur_select fetch next from cur_select into @tmpid while(@@FETCH_STATUS=0) begin exec proc_menu_sele @tmpid,0 fetch next from cur_select into @tmpid end close cur_select deallocate cur_select end else begin insert INTO #tab select id,name,twoname,pId,level,state,url from tb_Menu where Id=@id end declare @p int select @p=pId from tb_Menu where Id=@id if(@p=@tmppId) begin select * from #tab end go
declare @id1 int exec proc_menu_add '节点1',0,@id1 out declare @id2 int exec proc_menu_add '节点1-1',1,@id2 out declare @id3 int exec proc_menu_add '节点1-1-1',2,@id3 out declare @id4 int exec proc_menu_add '节点1-1-2',2,@id4 out declare @id5 int exec proc_menu_add '节点1-2',1,@id5 out declare @id6 int exec proc_menu_add '节点1-2-1',5,@id6 out declare @id7 int exec proc_menu_add '节点1-2-2',5,@id7 out select * from tb_menu;
部分页面内容：
@{ Layout = "../Shared/_Layout.cshtml"; } <script src="@Url.Content("~/Content/jqztree3.5/js/jquery.ztree.core-3.5.js")" type="text/javascript"></script> <script src="@Url.Content("~/Content/jqztree3.5/js/jquery.ztree.excheck-3.5.js")" type="text/javascript"></script> <script src="@Url.Content("~/Content/jqztree3.5/js/jquery.ztree.exedit-3.5.js")" type="text/javascript"></script> <link href="@Url.Content("~/Content/jqztree3.5/css/zTreeStyle/zTreeStyle.css")" rel="stylesheet" type="text/css"/> <link href="../../../../Content/BM_Content/css/Detail.css" rel="stylesheet" type="text/css" /> <style type="text/css"> .ztree li span.button.add {margin-left:2px; margin-right: -1px; background-position:-144px 0; vertical-align:top; *vertical-align:middle} </style> <script type="text/javascript"> <!-- var setting = { view: { addHoverDom: addHoverDom, removeHoverDom: removeHoverDom, selectedMulti: false }, edit: { enable: true, editNameSelectAll: true, //, showRemoveBtn: isRemove, showRenameBtn: isRename }, data: { simpleData: { enable: true } }, callback: { beforeDrag: beforeDrag, beforeEditName: beforeEditName, beforeRemove: beforeRemove, beforeRename: beforeRename, onRemove: onRemove, onRename: onRename, beforeClick: beforeClick, onClick: onClick, onDrag: onDrag, onDrop: onDrop } }; var zNodes = [ { id: 1, pId: 0, name: "父节点 1", open: true }, { id: 11, pId: 1, name: "叶子节点 1-1" }, { id: 12, pId: 1, name: "叶子节点 1-2" }, { id: 13, pId: 1, name: "叶子节点 1-3" }, { id: 2, pId: 0, name: "父节点 2", open: true }, { id: 21, pId: 2, name: "叶子节点 2-1" }, { id: 22, pId: 2, name: "叶子节点 2-2" }, { id: 23, pId: 2, name: "叶子节点 2-3" }, { id: 3, pId: 0, name: "父节点 3", open: true }, { id: 31, pId: 3, name: "叶子节点 3-1" }, { id: 32, pId: 3, name: "叶子节点 3-2" }, { id: 33, pId: 3, name: "叶子节点 3-3" } ]; var a = [ { "id": 30, "name": "new node1" }, { "id": 31, "name": "new node2" }, { "id": 32, "name": "new node3" }, { "id": 33, "name": "new node1" }, { "id": 34, "name": "new node2" }, { "id": 35, "name": "new node1" }, { "id": 36, "name": "new node2" }, { "id": 37, "name": "new node3"}] var log, className = "dark"; function beforeDrag(treeId, treeNodes) { return true; } function onDrag(event, treeId, treeNodes) { //showLog("onDrag:" + JSON.stringify(treeNodes)); } function onDrop(event, treeId, treeNodes, targetNode, moveType) { if (moveType == null) { return; } // showLog(treeNodes.length + "," + (targetNode ? (targetNode.tId + ", " + targetNode.name) : "isRoot") + "moveType:" + moveType); //showLog(JSON.stringify(treeNodes)); // showLog(JSON.stringify(targetNode)); var id = treeNodes[0].id var newpId = targetNode.pId != null ? targetNode.pId : 0; var sibId = targetNode.id != null ? targetNode.id : 0; var dir = 0; switch (moveType) { case 'inner': newpId = targetNode.id; sibId = 0; break; case 'prev': sibId = targetNode.id != null ? targetNode.id : 0; dir = 1; break; case 'next': sibId = targetNode.id != null ? targetNode.id : 0; dir = -1; break; } showLog("id:" + id + "newpId:" + newpId + "sibId:" + sibId + "dir:" + dir); $.post("Move", { "id": id, "newpId": newpId, "sibId": sibId, "dir": dir }, function () { }, "json"); } function Move(id) { //Move(int id, int pId, int sibId, int dir) } function beforeClick(treeId, treeNodes) { return true; } function onClick(e, treeId, treeNode) { GetDetail(treeNode.id); showLog("[ " + getTime() + " onClick ] " + treeNode.name + "" + JSON.stringify(treeNode)); } function beforeEditName(treeId, treeNode) { className = (className === "dark" ? "" : "dark"); showLog("[ " + getTime() + " beforeEditName ] " + treeNode.name); var zTree = $.fn.zTree.getZTreeObj("treeDemo"); zTree.selectNode(treeNode); // return confirm("进入节点 -- " + treeNode.name + " 的编辑状态吗？"); } function beforeRemove(treeId, treeNode) { className = (className === "dark" ? "" : "dark"); showLog("[ " + getTime() + " beforeRemove ] " + treeNode.name); var zTree = $.fn.zTree.getZTreeObj("treeDemo"); zTree.selectNode(treeNode); if (confirm("确认删除 节点 -- " + treeNode.name + " 吗？")) { Delete(treeNode.id, function (data) { return data; }); } } function onRemove(e, treeId, treeNode) { showLog("[ " + getTime() + " onRemove ] " + treeNode.name); } function beforeRename(treeId, treeNode, newName) { className = (className === "dark" ? "" : "dark"); if (newName.length == 0) { alert("节点名称不能为空."); var zTree = $.fn.zTree.getZTreeObj("treeDemo"); setTimeout(function () { zTree.editName(treeNode) }, 10); return false; } return true; } function onRename(e, treeId, treeNode) { AjaxUpdateNameById(treeNode.id, treeNode.name, function (data) { }); showLog("[ " + getTime() + " onRename ] " + treeNode.name); } function showRemoveBtn(treeId, treeNode) { return !treeNode.isFirstNode; } function showRenameBtn(treeId, treeNode) { return !treeNode.isLastNode; } function showLog(str) { if (!log) log = $("#log"); log.append("<li class='" + className + "'>" + str + "</li>"); if (log.children("li").length > 8) { log.get(0).removeChild(log.children("li")[0]); } } function getTime() { var now = new Date(), h = now.getHours(), m = now.getMinutes(), s = now.getSeconds(), ms = now.getMilliseconds(); return (h + ":" + m + ":" + s + " " + ms); } var newCount = 1; function addHoverDom(treeId, treeNode) { var sObj = $("#" + treeNode.tId + "_span"); if (treeNode.editNameFlag || $("#addBtn_" + treeNode.id).length > 0) return; var addStr = "<span class='button add' id='addBtn_" + treeNode.id + "' title='add node' onfocus='this.blur();'></span>"; sObj.after(addStr); var btn = $("#addBtn_" + treeNode.id); if (btn) btn.bind("click", function () { var nodeName = "new node" + (newCount++); AjaxAddMenu(nodeName, 0, treeNode.id, function (id) { if (id == 0) { return; } var zTree = $.fn.zTree.getZTreeObj("treeDemo"); zTree.addNodes(treeNode, { id: id, pId: treeNode.id, name: nodeName }); return false; }); }); }; function removeHoverDom(treeId, treeNode) { $("#addBtn_" + treeNode.id).unbind().remove(); }; function selectAll() { var zTree = $.fn.zTree.getZTreeObj("treeDemo"); zTree.setting.edit.editNameSelectAll = $("#selectAll").attr("checked"); } $(document).ready(function () { loadTree(); $("#btnAdd").live("click", function () { AjaxAddMenu("", 0, 0, function () { loadTree(); }); }); }); function loadTree() { $.post("LoadMenuTree", {}, function (data) { $.fn.zTree.init($("#treeDemo"), setting, data); $("#selectAll").bind("click", selectAll); }, "json") } function AjaxAddMenu(name, sort, pId, callback) { $.post("AjaxAddMenu", { "name": name, "sort": sort, "pId": pId }, function (data) { callback(data); }, "json"); } function GetDetail(id) { $("#divDetail").load("PartialDetail", { "id": id }, function () { }); } //是否现实删除按钮 function isRemove() { return $("#chkDele").is(":checked"); } //是否现实编辑按钮 function isRename() { return $("#chkEdit").is(":checked"); } //删除菜单 function Delete(id, callback) { $.post("Delete", { "id": id }, function (data) { callback(data) }, "json"); } function AjaxUpdateNameById(id, name, callback) { $.post("AjaxUpdateNameById", { "id": id, "name": name }, function (data) { callback(data); }, "json"); } //--> </script> <div class="mg10" style="height: 400px"> <fieldset class="mg0 mt10 mb10 pd10 bo"> <legend>工具栏</legend> <input id="btnAdd" type="button" class="btn" value="添加根节点" /> <input id="chkAdd" type="checkbox" onclick="loadTree()" />添加 <input id="chkEdit" type="checkbox" onclick="loadTree()" />修改 <input id="chkDele" type="checkbox" onclick="loadTree()" />删除 </fieldset> <div class="fl h_100 bo" style="overflow: scroll; 25%;"> <ul id="treeDemo" class="ztree"> </ul> </div> <div id="divDetail" class="fr h_100 bo" style=" 74%"> </div> </div> <div id='log'> </div>
底层代码：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using PO_Model; using System.Data.SqlClient; using System.Data; using Common.Repository; using System.Collections; namespace Common.Infrastructure { public class MenuRepository : IMenuRepository { /// <summary> /// 增加一条数据 /// </summary> public int Add(Menu model, string a) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into tb_Menu("); strSql.Append("name,twoname,pId,level,cNum,state,sort,dt,url,des)"); strSql.Append(" values ("); strSql.Append("@name,@twoname,@pId,@level,0,@state,@sort,getdate(),@url,@des)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@name", model.name), new SqlParameter("@twoname", model.twoname), new SqlParameter("@pId", model.pId), new SqlParameter("@level", model.level), new SqlParameter("@state", model.state), new SqlParameter("@sort", model.sort), new SqlParameter("@url", model.url), new SqlParameter("@des", model.des)}; var id = 0; object obj = SqlHelper.GetSingle(strSql.ToString(), parameters); if (obj != null) id = Convert.ToInt32(obj); if (id != 0) { var rowsaffect = 0; string proc_menu_updatecNum = "proc_menu_updatecNum"; SqlParameter[] paras = { new SqlParameter("@pId", model.pId), new SqlParameter("@cNum", 1) }; SqlHelper.RunProcedure(proc_menu_updatecNum, paras, out rowsaffect); } return id; } public int Add(Menu model) { string sql = "proc_menu_add"; SqlParameter[] para = { new SqlParameter("@name",model.name), new SqlParameter("@pId",model.pId), new SqlParameter("@id",SqlDbType.Int,4) }; para[2].Direction = ParameterDirection.Output; int rowsAffected; SqlHelper.RunProcedure(sql, para, out rowsAffected); var id = Convert.ToInt32(para[2].Value); return id; } /// <summary> /// 更新一条数据 /// </summary> public bool Update(Menu model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update tb_Menu set "); strSql.Append("name=@name,"); strSql.Append("twoname=@twoname,"); strSql.Append("pId=@pId,"); strSql.Append("level=@level,"); strSql.Append("state=@state,"); strSql.Append("sort=@sort,"); strSql.Append("dt=@dt,"); strSql.Append("url=@url,"); strSql.Append("des=@des"); strSql.Append(" where Id=@Id"); SqlParameter[] parameters = { new SqlParameter("@name", SqlDbType.VarChar,50), new SqlParameter("@twoname", SqlDbType.VarChar,50), new SqlParameter("@pId", SqlDbType.Int,4), new SqlParameter("@level", SqlDbType.Int,4), new SqlParameter("@state", SqlDbType.Int,4), new SqlParameter("@sort", SqlDbType.Int,4), new SqlParameter("@dt", SqlDbType.DateTime), new SqlParameter("@url", SqlDbType.VarChar,100), new SqlParameter("@des", SqlDbType.VarChar,1), new SqlParameter("@Id", SqlDbType.Int,4)}; parameters[0].Value = model.name; parameters[1].Value = model.twoname; parameters[2].Value = model.pId; parameters[3].Value = model.level; parameters[4].Value = model.state; parameters[5].Value = model.sort; parameters[6].Value = model.dt; parameters[7].Value = model.url; parameters[8].Value = model.des; parameters[9].Value = model.Id; return SqlHelper.ExecuteSql(strSql.ToString(), parameters) > 0; } /// <summary> /// 删除一条数据 /// </summary> public bool Delete(int Id) { int rowsAffected = 0; string sql = "proc_menu_delete"; SqlParameter[] parameters = { new SqlParameter("@Id", Id) }; SqlHelper.RunProcedure(sql, parameters, out rowsAffected); return rowsAffected > 0; } /// <summary> /// 删除一条数据 /// </summary> public bool DeleteList(string Idlist) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from tb_Menu "); strSql.Append(" where Id in (" + Idlist + ") "); int rows = SqlHelper.ExecuteSql(strSql.ToString()); if (rows > 0) { return true; } else { return false; } } /// <summary> /// 得到一个对象实体 /// </summary> public Menu GetModel(int Id) { Menu menu = null; StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 Id,name,twoname,pId,level,cNum,state,sort,dt,url,des from tb_Menu "); strSql.Append(" where Id=@Id"); SqlParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int,4) }; parameters[0].Value = Id; using (SqlDataReader dr = SqlHelper.ExecuteReader(strSql.ToString(), parameters)) { if (dr.Read()) { menu = Fetch(dr); } } return menu; } private Menu Fetch(SqlDataReader dr) { return new Menu() { Id = dr.GetInt32(dr.GetOrdinal("Id")), name = dr.GetString(dr.GetOrdinal("name")), twoname = dr.IsDBNull(dr.GetOrdinal("twoname")) ? "" : dr.GetString(dr.GetOrdinal("twoname")), pId = dr.GetInt32(dr.GetOrdinal("pId")), level = dr.GetInt32(dr.GetOrdinal("level")), CNum = dr.GetInt32(dr.GetOrdinal("cNum")), state = dr.GetInt32(dr.GetOrdinal("state")), sort = dr.GetInt32(dr.GetOrdinal("sort")), dt = dr.GetDateTime(dr.GetOrdinal("dt")), url = dr.IsDBNull(dr.GetOrdinal("url")) ? "" : dr.GetString(dr.GetOrdinal("url")), des = dr.IsDBNull(dr.GetOrdinal("des")) ? "" : dr.GetString(dr.GetOrdinal("des")) }; } /// <summary> /// 获得数据列表 /// </summary> public List<Menu> GetList(string strWhere) { List<Menu> list = new List<Menu>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,name,twoname,pId,level,cNum,state,sort,dt,url,des "); strSql.Append(" FROM tb_Menu order by sort desc"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } using (SqlDataReader dr = SqlHelper.ExecuteReader(strSql.ToString(), null)) { while (dr.Read()) list.Add(Fetch(dr)); } return list; } public bool Exists(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from tb_Menu"); strSql.Append(" where Id=@Id "); SqlParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int,4)}; parameters[0].Value = Id; return SqlHelper.Exists(strSql.ToString(), parameters); } public void Move(int id, int newpId, int sibId, int dir) { string sql = "proc_menu_updateSort"; SqlParameter[] para = { new SqlParameter("@id",id), new SqlParameter("@newpId",newpId), new SqlParameter("@sibId",sibId), new SqlParameter("@dir",dir), }; int rowsAffected; SqlHelper.RunProcedure(sql, para, out rowsAffected); } } }