public static async Task <bool> DeleteNote(Models.Note note) { 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 = @"DELETE FROM Notes WHERE ID = @ID;"; insertCommand.Parameters.AddWithValue("@ID", note.ID); await insertCommand.ExecuteReaderAsync(); return(true); } } catch (Exception eSql) { System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}"); return(false); } }
public static async Task <IEnumerable <Models.Note> > GetNotes() { string getDataQuery = @" SELECT Notes.ID, Notes.Title, Notes.Content FROM Notes;"; List <Models.Note> entries = new List <Models.Note>(); string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName); try { using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}")) { db.Open(); SqliteCommand selectCommand = new SqliteCommand (getDataQuery, db); SqliteDataReader query = selectCommand.ExecuteReader(); while (await query.ReadAsync()) { Models.Note note = new Models.Note() { ID = query.GetInt32(0), Title = query.IsDBNull(1) ? null : query.GetString(1), Content = query.IsDBNull(2) ? null : query.GetString(2), }; entries.Add(note); } db.Close(); } } catch (Exception eSql) { // Your code may benefit from more robust error handling or logging. // This logging is just a reminder that you should handle exceptions when connecting to remote data. System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}"); } return(entries); }
public static async void UpdateNote(Models.Note note) { 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 Notes SET Title = @Title, Content = @Content WHERE ID = @ID;"; if (note.Title == null) { insertCommand.Parameters.AddWithValue("@Title", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@Title", note.Title); } if (note.Content == null) { insertCommand.Parameters.AddWithValue("@Content", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@Content", note.Content); } insertCommand.Parameters.AddWithValue("@ID", note.ID); await insertCommand.ExecuteReaderAsync(); } } catch (Exception eSql) { System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}"); } }
public static async Task <long> AddNote(Models.Note note) { 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 Notes VALUES (NULL, @Title, @Content);"; if (note.Title == null) { insertCommand.Parameters.AddWithValue("@Title", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@Title", note.Title); } if (note.Content == null) { insertCommand.Parameters.AddWithValue("@Content", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@Content", note.Content); } insertCommand.Parameters.AddWithValue("@CreationDate", note.CreationDate); 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); } }