C# SQL 语句 更新树表的全路径

 /// <summary>
        /// 更新表
        /// </summary>
        /// <param name="employsmodel"></param>
        /// <param name="id"></param>
        public static void UpdateCategory(MbsEmployesCategoryModel employsmodel,object id,string oldpath,string newpath)
        {
            string sql = @"with Special_MbsEmployes as(SELECT * FROM [MbsEmployesCategory] WHERE [ID] = @0
  union all select [MbsEmployesCategory].* from Special_MbsEmployes, [MbsEmployesCategory] where Special_MbsEmployes.[ID] = [MbsEmployesCategory].[ParentID]
)
UPDATE [MbsEmployesCategory] SET [MbsEmployesCategory].FullPath = Replace(Cast(so.FullPath as varchar(8000)),@1,@2)
FROM [MbsEmployesCategory] AS sp JOIN Special_MbsEmployes AS so ON sp.ID = so.ID where sp.ID <> @0
select * from MbsEmployesCategory
";
            var db = new PetaPoco.Db();
            db.BeginTransaction();
            db.Execute(sql,id,oldpath,newpath);
            db.Update("MbsEmployesCategory", "ID", new { FullPath=employsmodel.FullPath,EmployeCategoryCode = employsmodel.EmployeCategoryCode, EmployeCategoryName = employsmodel.EmployeCategoryName, EmployeCategoryDescribe =employsmodel.EmployeCategoryDescribe}, id);
            db.CompleteTransaction();
        }

 

转载于:https://www.cnblogs.com/lushousong/p/3259950.html