private async Task InsertOrUpdateUserSetting(string userSettingName, string paramValue) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var getCmd = new SQLiteCommand(SQLQueries.GetUserSetting(userSettingName), conn); var response = (string)(await getCmd.ExecuteScalarAsync()); if (String.IsNullOrWhiteSpace(response)) { var cmd = new SQLiteCommand(SQLQueries.InsertUserSetting(userSettingName), conn); cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue)); await cmd.ExecuteNonQueryAsync(); } else { var cmd = new SQLiteCommand(SQLQueries.UpdateUserSetting(userSettingName), conn); cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue)); await cmd.ExecuteNonQueryAsync(); } } }
public async Task <long> AddNote(Note note) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var group = await GetGroupWithConnection(conn, note.Group); long groupID = 0; if (group == null) { groupID = await AddGroupWithConnection(conn, note.Group); } else { groupID = group.ID; } using (var cmd = new SQLiteCommand(SQLQueries.AddNote(), conn)) { cmd.Parameters.Add(new SQLiteParameter("@CloudKey", note.CloudKey)); cmd.Parameters.Add(new SQLiteParameter("@LastMOdified", note.LastModified.ToString())); cmd.Parameters.Add(new SQLiteParameter("@Content", note.Content)); cmd.Parameters.Add(new SQLiteParameter("@GroupID", groupID)); await cmd.ExecuteNonQueryAsync(); } using (var cmdGetID = new SQLiteCommand(SQLQueries.GetLastInsertedRowID(), conn)) return((long)cmdGetID.ExecuteScalar()); } }
private async Task CreateTables() { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); await new SQLiteCommand(SQLQueries.CreateTables(), conn).ExecuteNonQueryAsync(); } }
private async Task <string> GetUserSetting(string userSettingName) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var cmd = new SQLiteCommand(SQLQueries.GetUserSetting(userSettingName), conn); return((string)(await cmd.ExecuteScalarAsync())); } }
private async Task <long> AddGroupWithConnection(SQLiteConnection conn, string groupName) { using (var cmd = new SQLiteCommand(SQLQueries.AddGroup(), conn)) { cmd.Parameters.Add(new SQLiteParameter("@Group", groupName.ToUpper())); await cmd.ExecuteNonQueryAsync(); } using (var cmdGetID = new SQLiteCommand(SQLQueries.GetLastInsertedRowID(), conn)) return((long)await cmdGetID.ExecuteScalarAsync()); }
public async Task DeleteUserSetting(string userSettingName, string paramValue) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var cmd = new SQLiteCommand(SQLQueries.DeleteUserSetting(userSettingName), conn); cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue)); await cmd.ExecuteNonQueryAsync(); } }
public async Task DeleteGroup(string groupName) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var cmdDelete = new SQLiteCommand(SQLQueries.DeleteGroup(), conn); cmdDelete.Parameters.Add(new SQLiteParameter("@Group", groupName.ToUpper())); await cmdDelete.ExecuteNonQueryAsync(); } }
public Task DeleteNote(Note note) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(SQLQueries.DeleteNote(), conn)) { cmd.Parameters.Add(new SQLiteParameter("@ID", note.ID)); return(cmd.ExecuteNonQueryAsync()); } } }
private async Task <List <Group> > GetAllGroupsWithConnection(SQLiteConnection conn) { using (var reader = await new SQLiteCommand(SQLQueries.GetAllGroups(), conn) .ExecuteReaderAsync()) { var groups = new List <Group>(); while (reader.Read()) { groups.Add(ReadGroupFromSQL(reader)); } return(groups); } }
public async Task UpdateGroup(string oldGroupName, string newGroupName) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); var oldGroup = await GetGroupWithConnection(conn, oldGroupName.ToUpper()); var cmdUpdate = new SQLiteCommand(SQLQueries.UpdateGroup(), conn); cmdUpdate.Parameters.Add(new SQLiteParameter("@Group", newGroupName.ToUpper())); cmdUpdate.Parameters.Add(new SQLiteParameter("@GroupID", oldGroup.ID)); await cmdUpdate.ExecuteNonQueryAsync(); } }
public Task UpdateNote(Note note) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); using (var cmd = new SQLiteCommand(SQLQueries.UpdateNote(), conn)) { cmd.Parameters.Add(new SQLiteParameter("@CloudKey", note.CloudKey)); cmd.Parameters.Add(new SQLiteParameter("@LastModified", note.LastModified)); cmd.Parameters.Add(new SQLiteParameter("@Content", note.Content)); cmd.Parameters.Add(new SQLiteParameter("@Group", note.Group)); return(cmd.ExecuteNonQueryAsync()); } } }
private async Task <Group> GetGroupWithConnection(SQLiteConnection conn, string groupName) { using (var cmd = new SQLiteCommand(SQLQueries.GetGroup(), conn)) { cmd.Parameters.Add(new SQLiteParameter("@Group", groupName)); using (var reader = await cmd.ExecuteReaderAsync()) { if (reader.Read()) { return(ReadGroupFromSQL(reader)); } else { return(null); } } } }
public async Task <List <Note> > GetAllNotes() { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); using (var reader = await new SQLiteCommand(SQLQueries.GetAllNotes(), conn) .ExecuteReaderAsync()) { var notes = new List <Note>(); while (reader.Read()) { notes.Add(ReadNoteFromSQL(reader)); } return(notes); } } }