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}"); } }