public RowOpResult DeleteColumn(ColumnDTO column)
{
RowOpResult result = new RowOpResult();
// Delete task from db
using (SqliteConnection db =
new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
db.Open();
try
{
SqliteCommand deleteCommand = new SqliteCommand
("DELETE FROM tblColumns WHERE Id=@id AND BoardID=@boardId", db);
deleteCommand.Parameters.AddWithValue("id", column.Id);
deleteCommand.Parameters.AddWithValue("boardId", column.BoardId);
deleteCommand.ExecuteNonQuery();
SqliteCommand deleteTasksCommand = new SqliteCommand
("DELETE FROM tblTasks WHERE Category=@category", db);
deleteTasksCommand.Parameters.AddWithValue("category", column.ColumnName);
deleteTasksCommand.ExecuteNonQuery();
result.Success = true;
}
finally
{
db.Close();
}
}
return(result);
}
public RowOpResult UpdateColumnIndex(ColumnDTO column)
{
RowOpResult result = new RowOpResult();
using (SqliteConnection db =
new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
db.Open();
try
{
SqliteCommand updateCommand = new SqliteCommand
("UPDATE tblColumns SET Position=@position WHERE Id=@id AND BoardId=@boardId", db);
updateCommand.Parameters.AddWithValue("@id", column.Id);
updateCommand.Parameters.AddWithValue("@boardId", column.BoardId);
updateCommand.Parameters.AddWithValue("@position", column.Position);
updateCommand.ExecuteNonQuery();
}
finally
{
db.Close();
}
}
return(result);
}
/// <summary>
/// Updates the column's data in the database
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public RowOpResult <ColumnDTO> SaveColumn(ColumnDTO column)
{
RowOpResult <ColumnDTO> result = new RowOpResult <ColumnDTO>(column);
using (SqliteConnection db = new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
try
{
db.Open();
SqliteCommand command = new SqliteCommand {
Connection = db
};
command.Parameters.AddWithValue("@id", column.Id);
command.Parameters.AddWithValue("@boardId", column.BoardId);
command.Parameters.AddWithValue("@columnName", column.ColumnName);
command.Parameters.AddWithValue("@maxTaskLimit", column.MaxTaskLimit);
command.CommandText = "UPDATE tblColumns SET ColumnName=@columnName,MaxTaskLimit=@maxTaskLimit WHERE Id=@id AND BoardID=@boardId";
command.ExecuteNonQuery();
result.Success = true;
}
finally
{
db.Close();
}
}
return(result);
}
public ColumnDTO CreateColumn(ColumnDTO column)
{
RowOpResult <ColumnDTO> result = new RowOpResult <ColumnDTO>(column);
using (SqliteConnection db = new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
try
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand {
Connection = db
};
insertCommand.Parameters.AddWithValue("@boardId", column.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", column.ColumnName);
insertCommand.Parameters.AddWithValue("@position", column.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", column.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position,MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
column.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
result.Success = true;
}
finally
{
db.Close();
}
return(column);
}
}
public virtual RowOpResult <BoardDTO> ValidateBoard(RowOpResult <BoardDTO> result)
{
BoardDTO b = result.Entity;
if (string.IsNullOrEmpty(b.Name))
{
result.ErrorMessage = "Name is required.";
}
else if (b.Name.Length > 100)
{
result.ErrorMessage = "Name is too long.";
}
if (string.IsNullOrEmpty(b.Notes))
{
result.ErrorMessage = "Notes are required.";
}
else if (b.Notes.Length > 1000)
{
result.ErrorMessage = "Notes are too long.";
}
// more validation here
result.Success = string.IsNullOrEmpty(result.ErrorMessage);
return(result);
}
/// <summary>
/// Deletes the tasks associated with the current board first
/// and then removes the board from tblBoards
/// </summary>
/// <param name="boardId"></param>
/// <returns>If deletion was successful</returns>
public RowOpResult DeleteBoard(int boardId)
{
RowOpResult result = new RowOpResult();
// Delete task from db
using (SqliteConnection db =
new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
db.Open();
try
{
SqliteCommand deleteCommand = new SqliteCommand
("DELETE FROM tblTasks WHERE BoardID=@boardId", db);
deleteCommand.Parameters.AddWithValue("boardId", boardId);
deleteCommand.ExecuteNonQuery();
deleteCommand = new SqliteCommand
("DELETE FROM tblBoards WHERE Id=@id", db);
deleteCommand.Parameters.AddWithValue("id", boardId);
deleteCommand.ExecuteNonQuery();
result.Success = true;
return(result);
}
finally
{
db.Close();
}
}
}
public virtual RowOpResult DeleteBoard(int boardId)
{
RowOpResult result = new RowOpResult();
BoardDTO board = db.Boards.FirstOrDefault(x => x.Id == boardId);
if (board == null)
{
result.ErrorMessage = $"boardId {boardId} is invalid. Board may have already been deleted.";
return(result);
}
db.Entry(board).State = EntityState.Deleted;
db.SaveChanges();
result.Success = true;
return(result);
}
public virtual RowOpResult <BoardDTO> SaveBoard(BoardDTO board)
{
RowOpResult <BoardDTO> result = new RowOpResult <BoardDTO>(board);
ValidateBoard(result);
if (!result.Success)
{
return(result);
}
db.Entry(board).State = board.Id == 0 ? EntityState.Added : EntityState.Modified;
db.SaveChanges();
result.Success = true;
return(result);
}
public virtual RowOpResult DeleteTask(int id)
{
RowOpResult result = new RowOpResult();
TaskDTO task = db.Tasks.FirstOrDefault(x => x.Id == id);
if (task == null)
{
result.ErrorMessage = $"taskId {id} is invalid. Task may have already been deleted.";
return(result);
}
db.Entry(task).State = EntityState.Deleted;
db.SaveChanges();
result.Success = true;
return(result);
}
/// <summary>
/// Inserts the passed Board object if the ID is zero, otherwise updates it.
/// </summary>
/// <param name="board"></param>
/// <returns></returns>
public RowOpResult <BoardDTO> SaveBoard(BoardDTO board)
{
RowOpResult <BoardDTO> result = new RowOpResult <BoardDTO>(board);
ValidateBoard(result);
if (!result.Success)
{
return(result);
}
using (SqliteConnection db = new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
try
{
db.Open();
SqliteCommand command = new SqliteCommand {
Connection = db
};
command.Parameters.AddWithValue("@boardName", board.Name);
command.Parameters.AddWithValue("@boardNotes", board.Notes);
if (board.Id == 0)
{
command.CommandText = "INSERT INTO tblBoards (Name,Notes) VALUES (@boardName, @boardNotes); ; SELECT last_insert_rowid();";
board.Id = Convert.ToInt32(command.ExecuteScalar());
}
else
{
command.Parameters.AddWithValue("@boardId", board.Id);
command.CommandText = "UPDATE tblBoards SET Name=@boardName,Notes=@boardNotes WHERE Id=@boardId";
command.ExecuteNonQuery();
}
result.Success = true;
}
finally
{
db.Close();
}
}
return(result);
}
public virtual RowOpResult <TaskDTO> SaveTask(TaskDTO task)
{
if (task == null)
{
throw new ArgumentNullException(nameof(task));
}
RowOpResult <TaskDTO> result = new RowOpResult <TaskDTO>(task);
ValidateTask(result);
if (!result.Success)
{
return(result);
}
db.Entry(task).State = task.Id == 0 ? EntityState.Added : EntityState.Modified;
db.SaveChanges();
result.Success = true;
return(result);
}
public virtual RowOpResult <TaskDTO> ValidateTask(RowOpResult <TaskDTO> result)
{
TaskDTO t = result.Entity;
// NOTE: Require Title? For now just set it to empty string; if not db will throw exception for null
// Set the dates to null if nothing set.. otherwise sqlite insert crashes because of no value to insert
if (string.IsNullOrEmpty(t.Title))
{
t.Title = "";
}
else if (t.Title.Length > 100)
{
result.ErrorMessage = "Title is too long.";
}
if (string.IsNullOrEmpty(t.Description))
{
t.Description = "";
}
if (string.IsNullOrEmpty(t.DueDate))
{
t.DueDate = "";
}
if (string.IsNullOrEmpty(t.FinishDate))
{
t.FinishDate = "";
}
if (string.IsNullOrEmpty(t.ReminderTime))
{
t.ReminderTime = "";
}
if (string.IsNullOrEmpty(t.StartDate))
{
t.StartDate = "";
}
// more validation here
result.Success = string.IsNullOrEmpty(result.ErrorMessage);
return(result);
}
public virtual RowOpResult <TaskDTO> ValidateTask(RowOpResult <TaskDTO> result)
{
TaskDTO t = result.Entity;
// NOTE: Require Title? For now just set it to empty string; if not db will throw exception for null
if (string.IsNullOrEmpty(t.Title))
{
t.Title = "";
}
else if (t.Title.Length > 100)
{
result.ErrorMessage = "Title is too long.";
}
if (string.IsNullOrEmpty(t.Description))
{
t.Description = "";
}
// more validation here
result.Success = string.IsNullOrEmpty(result.ErrorMessage);
return(result);
}
/// <summary>
/// Inserts the passed Task object if it's ID is zero, otherwise updates it.
/// </summary>
/// <param name="task"></param>
/// <returns></returns>
public RowOpResult <TaskDTO> SaveTask(TaskDTO task)
{
if (task == null)
{
throw new ArgumentNullException(nameof(task));
}
RowOpResult <TaskDTO> result = new RowOpResult <TaskDTO>(task);
ValidateTask(result);
if (!result.Success)
{
return(result);
}
using (SqliteConnection db = new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
db.Open();
try
{
SqliteCommand command = new SqliteCommand {
Connection = db
};
command.Parameters.AddWithValue("@title", task.Title);
command.Parameters.AddWithValue("@desc", task.Description);
command.Parameters.AddWithValue("@categ", task.Category);
command.Parameters.AddWithValue("@colorKey", task.ColorKey);
command.Parameters.AddWithValue("@tags", task.Tags);
command.Parameters.AddWithValue("@columnIndex", task.ColumnIndex);
command.Parameters.AddWithValue("@dueDate", task.DueDate);
command.Parameters.AddWithValue("@finishDate", task.FinishDate);
command.Parameters.AddWithValue("@timeDue", task.TimeDue);
command.Parameters.AddWithValue("@reminderTime", task.ReminderTime);
command.Parameters.AddWithValue("@startDate", task.StartDate);
if (task.Id == 0)
{
// Insert a new row
command.Parameters.AddWithValue("@boardID", task.BoardId);
command.Parameters.AddWithValue("@dateCreated", task.DateCreated);
command.CommandText = "INSERT INTO tblTasks (BoardID,DateCreated,Title,Description,Category,ColorKey,Tags, ColumnIndex, DueDate, FinishDate, TimeDue, ReminderTime, StartDate) VALUES (" +
"@boardID, @dateCreated, @title, @desc, @categ, @colorKey, @tags, @columnIndex, @dueDate, @finishDate, @timeDue, @reminderTime, @startDate); ; SELECT last_insert_rowid();";
task.Id = Convert.ToInt32(command.ExecuteScalar());
}
else
{
// Update an existing row
command.Parameters.AddWithValue("@id", task.Id);
command.CommandText = "UPDATE tblTasks SET Title=@title, Description=@desc, Category=@categ, ColorKey=@colorKey, Tags=@tags, ColumnIndex=@columnIndex, DueDate=@dueDate, FinishDate=@finishDate, TimeDue=@timeDue, ReminderTime=@reminderTime, StartDate=@startDate WHERE Id=@id";
command.ExecuteNonQuery();
}
result.Success = true;
}
catch (Exception)
{
result.Success = false;
throw;
}
finally
{
db.Close();
}
}
return(result);
}
public RowOpResult CreateColumns(int boardId)
{
RowOpResult result = new RowOpResult();
using (SqliteConnection db =
new SqliteConnection(this.db.Database.GetDbConnection().ConnectionString))
{
db.Open();
try
{
//List<ColumnDTO> defaultColumns = CreateDefaultColumns(boardId);
// Set default columns for new board
ColumnDTO columnOne, columnTwo, columnThree, columnFour, columnFive;
columnOne = new ColumnDTO
{
BoardId = boardId,
ColumnName = "Backlog",
Position = 0,
MaxTaskLimit = 10
};
columnTwo = new ColumnDTO
{
BoardId = boardId,
ColumnName = "To Do",
Position = 1,
MaxTaskLimit = 10
};
columnThree = new ColumnDTO
{
BoardId = boardId,
ColumnName = "In Progress",
Position = 2,
MaxTaskLimit = 10
};
columnFour = new ColumnDTO
{
BoardId = boardId,
ColumnName = "Review",
Position = 3,
MaxTaskLimit = 10
};
columnFive = new ColumnDTO
{
BoardId = boardId,
ColumnName = "Completed",
Position = 4,
MaxTaskLimit = 10
};
// Insert columns and return id
SqliteCommand insertCommand = new SqliteCommand {
Connection = db
};
insertCommand.Parameters.AddWithValue("@boardId", columnOne.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", columnOne.ColumnName);
insertCommand.Parameters.AddWithValue("@position", columnOne.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", columnOne.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position, MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
columnOne.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
insertCommand.Parameters.Clear();
insertCommand.Parameters.AddWithValue("@boardId", columnTwo.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", columnTwo.ColumnName);
insertCommand.Parameters.AddWithValue("@position", columnTwo.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", columnOne.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position, MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
columnTwo.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
insertCommand.Parameters.Clear();
insertCommand.Parameters.AddWithValue("@boardId", columnThree.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", columnThree.ColumnName);
insertCommand.Parameters.AddWithValue("@position", columnThree.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", columnOne.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position, MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
columnThree.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
insertCommand.Parameters.Clear();
insertCommand.Parameters.AddWithValue("@boardId", columnFour.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", columnFour.ColumnName);
insertCommand.Parameters.AddWithValue("@position", columnFour.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", columnOne.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position, MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
columnFour.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
insertCommand.Parameters.Clear();
insertCommand.Parameters.AddWithValue("@boardId", columnFive.BoardId);
insertCommand.Parameters.AddWithValue("@columnName", columnFive.ColumnName);
insertCommand.Parameters.AddWithValue("@position", columnFive.Position);
insertCommand.Parameters.AddWithValue("@maxTaskLimit", columnOne.MaxTaskLimit);
insertCommand.CommandText =
"INSERT INTO tblColumns (BoardID,ColumnName,Position, MaxTaskLimit) " +
"VALUES (@boardId, @columnName, @position, @maxTaskLimit); ; SELECT last_insert_rowid();";
columnFive.Id = Convert.ToInt32(insertCommand.ExecuteScalar());
result.Success = true;
return(result);
}
finally
{
db.Close();
}
}
}
