public static async Task <long> AddTask(Models.Task task)
{
try
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = @"INSERT INTO Tasks VALUES (NULL, @Title, @Description, @Position, @SprintPosition, @BoardID, @Status, @SprintRelevant, @CreationDate, @DueDate, @Priority);";
if (task.Title == null)
{
insertCommand.Parameters.AddWithValue("@Title", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Title", task.Title);
}
if (task.Description == null)
{
insertCommand.Parameters.AddWithValue("@Description", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Description", task.Description);
}
insertCommand.Parameters.AddWithValue("@Position", task.Position);
if (task.SprintPosition == null)
{
insertCommand.Parameters.AddWithValue("@SprintPosition", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@SprintPosition", task.SprintPosition);
}
insertCommand.Parameters.AddWithValue("@BoardID", task.BoardID);
insertCommand.Parameters.AddWithValue("@Status", task.Status);
insertCommand.Parameters.AddWithValue("@SprintRelevant", task.SprintRelevant);
insertCommand.Parameters.AddWithValue("@CreationDate", task.CreationDate);
if (task.DueDate == null)
{
insertCommand.Parameters.AddWithValue("@DueDate", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@DueDate", task.DueDate);
}
insertCommand.Parameters.AddWithValue("@Priority", task.Priority);
await insertCommand.ExecuteReaderAsync();
SqliteCommand selectCommand = new SqliteCommand
("SELECT last_insert_rowid()", db);
SqliteDataReader query = selectCommand.ExecuteReader();
long id;
if (await query.ReadAsync())
{
id = query.GetInt32(0);
}
else
{
id = -1;
}
db.Close();
return(id);
}
} catch (Exception eSql)
{
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
return(-1);
}
}
public static async void UpdateTask(Models.Task task)
{
try
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = @"UPDATE Tasks SET
Title = @Title,
Description = @Description,
Position = @Position,
SprintPosition = @SprintPosition,
BoardID = @BoardID,
Status = @Status,
SprintRelevant = @SprintRelevant,
CreationDate = @CreationDate,
DueDate = @DueDate,
Priority = @Priority
WHERE ID = @ID;";
if (task.Title == null)
{
insertCommand.Parameters.AddWithValue("@Title", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Title", task.Title);
}
if (task.Description == null)
{
insertCommand.Parameters.AddWithValue("@Description", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@Description", task.Description);
}
insertCommand.Parameters.AddWithValue("@Position", task.Position);
if (task.SprintPosition == null)
{
insertCommand.Parameters.AddWithValue("@SprintPosition", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@SprintPosition", task.SprintPosition);
}
insertCommand.Parameters.AddWithValue("@BoardID", task.BoardID);
insertCommand.Parameters.AddWithValue("@Status", task.Status);
insertCommand.Parameters.AddWithValue("@SprintRelevant", task.SprintRelevant);
insertCommand.Parameters.AddWithValue("@CreationDate", task.CreationDate);
if (task.DueDate == null)
{
insertCommand.Parameters.AddWithValue("@DueDate", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@DueDate", task.DueDate);
}
insertCommand.Parameters.AddWithValue("@Priority", task.Priority);
insertCommand.Parameters.AddWithValue("@ID", task.ID);
await insertCommand.ExecuteReaderAsync();
}
}
catch (Exception eSql)
{
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
}
}