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