Example #1
0
        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());
        }
Example #2
0
        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());
        }