public void CreateStoredProcedures <T>(IDbContainer dbContainer, INestedSetsDbSetManager <T> manager) where T : NestedSetsEntity { CreateStoredProcedurePreCreateNestedSetsNode(dbContainer, manager); CreateStoredProcedurePostRemoveNestedSetsNode(dbContainer, manager); CreateStoredProcedureMoveNestedSetsNode(dbContainer, manager); }
private void InitManagers() { _objectDbSetManager = new HierarchyObjectDbSetManager(HierarchyObjects, this); _characteristicDbSetManager = new CharacteristicDbSetManager(Characteristics, this); _objectTypeDbSetManager = new ObjectTypeDbSetManager(ObjectTypes, this); _characteristicValueDbSetManager = new CharacteristicValueDbSetManager <BaseCharacteristicValue>(CharacteristicValues, this); _characteristicAliasDbSetManager = new CharacteristicAliasDbSetManager(CharacteristicAliases, this); _hierarchyObjectAliasDbSetManager = new HierarchyObjectAliasDbSetManager(HierarchyObjectAliases, this); }
private void CreateStoredProcedureMoveNestedSetsNode <T>(IDbContainer dbContainer, INestedSetsDbSetManager <T> manager) where T : NestedSetsEntity { string procedureName = GetMoveNestedSetsNodeSpName(manager); dbContainer.Database.ExecuteSqlCommand(string.Format(@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}]", procedureName)); dbContainer.Database.ExecuteSqlCommand(string.Format(@"CREATE PROCEDURE [dbo].[{0}] @NodeLeftKey int, @NodeRightKey int, @NodeLevel int, @ParentLeftKey int, @ParentRightKey int, @ParentLevel int AS BEGIN DECLARE @d int = @NodeRightKey - @NodeLeftKey + 1; UPDATE dbo.[{1}] SET LeftKey = 0 - LeftKey, RightKey = 0 - RightKey WHERE LeftKey >= @NodeLeftKey AND RightKey <= @NodeRightKey; UPDATE dbo.[{1}] SET LeftKey = LeftKey - @d WHERE LeftKey > @NodeRightKey; UPDATE dbo.[{1}] SET RightKey = RightKey - @d WHERE RightKey > @NodeRightKey; DECLARE @pr int = CASE WHEN @ParentRightKey > @NodeRightKey THEN @ParentRightKey - (@NodeRightKey - @NodeLeftKey + 1) ELSE @ParentRightKey END; UPDATE dbo.[{1}] SET LeftKey = LeftKey + @d WHERE LeftKey >= @pr; UPDATE dbo.[{1}] SET RightKey = RightKey + @d WHERE RightKey >= @pr; DECLARE @pd int = CASE WHEN @ParentRightKey > @NodeRightKey THEN @ParentRightKey - @NodeRightKey - 1 ELSE @ParentRightKey - @NodeRightKey - 1 + @d END; DECLARE @dl int = @ParentLevel + 1 - @NodeLevel; UPDATE dbo.[{1}] SET LeftKey = @pd - LeftKey, RightKey = @pd - RightKey, Level = Level + @dl WHERE LeftKey <= 0-@NodeLeftKey AND RightKey >= 0-@NodeRightKey; END", procedureName, manager.TableName)); }
private void CreateStoredProcedurePostRemoveNestedSetsNode <T>(IDbContainer dbContainer, INestedSetsDbSetManager <T> manager) where T : NestedSetsEntity { string procedureName = GetPostRemoveNestedSetsNodeSpName(manager); dbContainer.Database.ExecuteSqlCommand(string.Format(@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}]", procedureName)); dbContainer.Database.ExecuteSqlCommand(string.Format(@"CREATE PROCEDURE [dbo].[{0}] @LeftKey int, @RightKey int AS BEGIN UPDATE dbo.[{1}] SET RightKey = RightKey - (@RightKey - @LeftKey + 1) WHERE RightKey > @RightKey AND LeftKey < @LeftKey; UPDATE dbo.[{1}] SET LeftKey = LeftKey - (@RightKey - @LeftKey + 1), RightKey = RightKey - (@RightKey - @LeftKey + 1) WHERE LeftKey > @RightKey; END", procedureName, manager.TableName)); }
public string GetMoveNestedSetsNodeSpName <T>(INestedSetsDbSetManager <T> manager) where T : NestedSetsEntity { return(string.Format(MoveNestedSetsNodeSpNameTemplate, manager.TableName)); }