/// <summary>
/// Deletes an object from the table
/// </summary>
/// <param name="dalObject">dal object to delete</param>
/// <returns>true if was deleted otherwise false</returns>
public bool Delete(DalObject dalObject)
{
int res = -1;
long id = dalObject.Id;
if (id == -1)
{
return(false);
}
using (var connection = new SQLiteConnection(_connectionString))
{
var command = new SQLiteCommand
{
Connection = connection,
CommandText = $"DELETE FROM {_tableName} WHERE {DalObject.IDColumnName}={id}"
};
try
{
connection.Open();
res = command.ExecuteNonQuery();
}
finally
{
command.Dispose();
connection.Close();
}
}
return(res > 0);
}
/// <summary>
/// Inserts a task to the db
/// </summary>
/// <param name="dalObject"></param>
/// <returns>The id generated by the database</returns>
public override long Insert(DalObject dalObject)
{
using (var connection = new SQLiteConnection(_connectionString))
{
TaskDAL task = (TaskDAL)dalObject;
long output = -1;
SQLiteCommand command = new SQLiteCommand(null, connection);
SQLiteDataReader dataReader = null;
try
{
connection.Open();
command.CommandText = $"INSERT INTO {tasksTable} ({TaskDAL.TaskTitleColumn} ,{TaskDAL.TaskCreationTimeColumn} ,{TaskDAL.TaskDueDateColumn} ,{TaskDAL.TaskDescriptionColumn},{TaskDAL.TaskColumnIdColumn},{TaskDAL.TaskAssigneeColumn}) " +
$"VALUES (@TitleVal,@CreationVal,@DueVal,@DescVal,@colIdVal,@assigneeIdVal); SELECT {DalController.seqCol} FROM {DalController.SqlSeq} WHERE {DalController.seqName}='{tasksTable}';";
SQLiteParameter TitleParam = new SQLiteParameter(@"TitleVal", task.Title);
SQLiteParameter CreationParam = new SQLiteParameter(@"CreationVal", task.CreationTime);
SQLiteParameter DueDateParam = new SQLiteParameter(@"DueVal", task.DueDate);
SQLiteParameter DescParam = new SQLiteParameter(@"DescVal", task.Description);
SQLiteParameter colIdParam = new SQLiteParameter(@"colIdVal", task.ColumnID);
SQLiteParameter assigneeParam = new SQLiteParameter(@"assigneeIdVal", task.AssigneeID);
command.Parameters.Add(TitleParam);
command.Parameters.Add(CreationParam);
command.Parameters.Add(DueDateParam);
command.Parameters.Add(DescParam);
command.Parameters.Add(colIdParam);
command.Parameters.Add(assigneeParam);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
output = (long)dataReader.GetValue(0);
}
}
catch (Exception e)
{
log.Error($"Failed to insert task to database{task.Title}", e);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
command.Dispose();
connection.Close();
}
return(output);
}
}
/// <summary>
/// Inserting new column to database
/// </summary>
/// <param name="column">column to insert</param>
/// <returns>the id of the new column</returns>
public override long Insert(DalObject dalObject)
{
ColumnDAL column = (ColumnDAL)dalObject;
long output = -1;
using (var connection = new SQLiteConnection(_connectionString))
{
SQLiteCommand command = new SQLiteCommand(null, connection);
SQLiteDataReader dataReader = null;
try
{
connection.Open();
command.CommandText = $"INSERT INTO {columnsTable} ({ColumnDAL.columnNameCol} ,{ColumnDAL.columnOrdinalCol} ,{ColumnDAL.LimitCol},{ColumnDAL.boardIdCol}) " +
$"VALUES (@NameVal,@OrdinalVal,@LimitVal,@boardIdVal); SELECT {DalController.seqCol} FROM {DalController.SqlSeq} WHERE {DalController.seqName}='{columnsTable}';";
SQLiteParameter NameParam = new SQLiteParameter(@"NameVal", column.ColumnName);
SQLiteParameter OrdinalParam = new SQLiteParameter(@"OrdinalVal", column.ColumnOrdinal);
SQLiteParameter LimitParam = new SQLiteParameter(@"LimitVal", column.Limit);
SQLiteParameter boardIDParam = new SQLiteParameter(@"boardIdVal", column.BoardId);
command.Parameters.Add(NameParam);
command.Parameters.Add(OrdinalParam);
command.Parameters.Add(LimitParam);
command.Parameters.Add(boardIDParam);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
output = (long)dataReader.GetValue(0);
}
}
catch (Exception e)
{
log.Error($"Failed to add column{column.ColumnName}", e);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
command.Dispose();
connection.Close();
}
return(output);
}
}
/// <summary>
/// Insert new user to database
/// </summary>
/// <param name="dalObject">The user to insert</param>
/// <returns>The id of the inserted user</returns>
public override long Insert(DalObject dalObject)
{
UserDAL user = (UserDAL)dalObject;
long output = -1;
using (var connection = new SQLiteConnection(_connectionString))
{
SQLiteCommand command = new SQLiteCommand(null, connection);
SQLiteDataReader dataReader = null;
try
{
connection.Open();
command.CommandText = $"INSERT INTO {UsersTable} ({UserDAL.UserEmailColumn} ,{UserDAL.NicknameColumn} ,{UserDAL.PasswordColumn},{UserDAL.boardIdColumn}) " +
$"VALUES (@emailVal,@nickVal,@passVal,@boardVal); SELECT {DalController.seqCol} FROM {DalController.SqlSeq} WHERE {DalController.seqName}='{UsersTable}';";
SQLiteParameter emailParam = new SQLiteParameter(@"emailVal", user.Email);
SQLiteParameter nickParam = new SQLiteParameter(@"nickVal", user.Nickname);
SQLiteParameter passParam = new SQLiteParameter(@"passVal", user.Password);
SQLiteParameter boardParam = new SQLiteParameter(@"boardVal", user.BoardId);
command.Parameters.Add(emailParam);
command.Parameters.Add(nickParam);
command.Parameters.Add(passParam);
command.Parameters.Add(boardParam);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
output = (long)dataReader.GetValue(0);
}
}
catch (Exception e)
{
log.Error($"Failed to insert user {user.Email}", e);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
command.Dispose();
connection.Close();
}
return(output);
}
}
/// <summary>
/// Inserts a new board to the table
/// </summary>
/// <param name="dalObject">Board to insert</param>
/// <returns>The generated id of the board inserted in the database</returns>
public override long Insert(DalObject dalObject)
{
using (var connection = new SQLiteConnection(_connectionString))
{
BoardDAL board = (BoardDAL)dalObject;
long output = -1;
SQLiteCommand command = new SQLiteCommand(null, connection);
SQLiteDataReader dataReader = null;
try
{
connection.Open();
command.CommandText = $"INSERT INTO {boardsTable} ({BoardDAL.userCreatorColumn}) " +
$"VALUES (@creatorIdVal); SELECT {DalController.seqCol} FROM {DalController.SqlSeq} WHERE {DalController.seqName}='{boardsTable}';";
SQLiteParameter userIdParam = new SQLiteParameter(@"creatorIdVal", board.CreatorID);
command.Parameters.Add(userIdParam);
command.Prepare();
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
output = (long)dataReader.GetValue(0);
}
}
catch (Exception e)
{
log.Error($"Failed to insert board to database{board.Id}", e);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
command.Dispose();
connection.Close();
}
return(output);
}
}
/// <summary> /// Inserts a dal object to the db /// </summary> /// <param name="dalObject">the object to insert</param> /// <returns>returns the id generated by the db</returns> public abstract long Insert(DalObject dalObject);