/// <summary> /// Perform a full backup and save of the current set of managed sites /// </summary> public async Task StoreSettings() { var watch = Stopwatch.StartNew(); var path = GetDbPath(); //create database if it doesn't exist if (!File.Exists(path)) { using (var db = new SQLiteConnection($"Data Source={path}")) { await db.OpenAsync(); using (var cmd = new SQLiteCommand("CREATE TABLE manageditem (id TEXT NOT NULL UNIQUE PRIMARY KEY, json TEXT NOT NULL)", db)) { await cmd.ExecuteNonQueryAsync(); } } } // save all new/modified items into settings database using (var db = new SQLiteConnection($"Data Source={path}")) { await db.OpenAsync(); using (var tran = db.BeginTransaction()) { foreach (var deleted in ManagedSitesCache.Values.Where(s => s.Deleted).ToList()) { using (var cmd = new SQLiteCommand("DELETE FROM manageditem WHERE id=@id", db)) { cmd.Parameters.Add(new SQLiteParameter("@id", deleted.Id)); await cmd.ExecuteNonQueryAsync(); } ManagedSitesCache.Remove(deleted.Id); } foreach (var changed in ManagedSitesCache.Values.Where(s => s.IsChanged)) { using (var cmd = new SQLiteCommand("INSERT OR REPLACE INTO manageditem (id,json) VALUES (@id,@json)", db)) { cmd.Parameters.Add(new SQLiteParameter("@id", changed.Id)); cmd.Parameters.Add(new SQLiteParameter("@json", JsonConvert.SerializeObject(changed))); await cmd.ExecuteNonQueryAsync(); } changed.IsChanged = false; } tran.Commit(); } } // reset IsChanged as all items have been persisted Debug.WriteLine($"StoreSettings[SQLite] took {watch.ElapsedMilliseconds}ms for {ManagedSitesCache.Count} records"); }
public async Task DeleteManagedSite(ManagedSite site) { // save modified items into settings database using (var db = new SQLiteConnection($"Data Source={GetDbPath()}")) { db.Open(); using (var tran = db.BeginTransaction()) { using (var cmd = new SQLiteCommand("DELETE FROM manageditem WHERE id=@id", db)) { cmd.Parameters.Add(new SQLiteParameter("@id", site.Id)); await cmd.ExecuteNonQueryAsync(); } tran.Commit(); Debug.WriteLine($"DeleteManagedSite: Completed {site.Id}"); ManagedSitesCache.Remove(site.Id); } } }