public bool UpdateTaskPositionAndColumn(int userId, TaskModelWithPositionAndNewColumn task) { using IDbConnection db = new SqlConnection(_connectionString); Debug.Assert(task.Id != null, "task.Id != null"); if (!IsAllowed(db, userId, (int)task.Id)) { return(false); } Debug.Assert(task.NewColumnId != null, "task.NewColumnId != null"); if (!ColumnRepository.IsAllowed(db, userId, (int)task.NewColumnId)) { return(false); } int maxTaskPosition = GetTasksCount(db, (int)task.NewColumnId); if (task.Position < 0 || task.Position > maxTaskPosition) { return(false); } return(db.Query( @"DECLARE @OldColumnId INT, @OldTaskPosition INT; SET @OldColumnId = (SELECT C.Id FROM TaskTable AS T JOIN ColumnTable AS C ON C.Id = T.ColumnId WHERE T.Id = @taskId); SET @OldTaskPosition = (SELECT Position FROM TaskTable WHERE Id = @taskId); UPDATE TaskTable SET Position = T.Position - 1 FROM TaskTable AS T JOIN ColumnTable AS C ON C.Id = T.ColumnId WHERE C.Id = @OldColumnId AND T.Position > @OldTaskPosition; UPDATE TaskTable SET Position = T.Position + 1 FROM TaskTable AS T JOIN ColumnTable AS C ON C.Id = T.ColumnId WHERE C.Id = @newColumnId AND T.Position >= @newTaskPosition; UPDATE TaskTable SET Position = @newTaskPosition, ColumnId = @newColumnId OUTPUT DELETED.Id WHERE Id = @taskId;", new { taskId = task.Id, newTaskPosition = task.Position, newColumnId = task.NewColumnId }).Any()); }
public int?CreateTask(int userId, TaskModelColumnLink task) { using IDbConnection db = new SqlConnection(_connectionString); Debug.Assert(task.ColumnId != null, "task.ColumnId != null"); if (!ColumnRepository.IsAllowed(db, userId, (int)task.ColumnId)) { return(null); } return(db.Query <int?>(@"INSERT INTO TaskTable(Name, Description, ColumnId, Position) OUTPUT INSERTED.Id VALUES (@taskName, @taskDescription, @columnId, @position);", new { taskName = task.Name, taskDescription = task.Description, columnId = task.ColumnId, position = GetTasksCount(db, (int)task.ColumnId) }).FirstOrDefault()); }