public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { PlayableBase track; using (var xmlReader = reader.GetTextReader(8)) track = (PlayableBase) _serializer.Deserialize(xmlReader); track.Title = reader.GetString(0); track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)]; var albumGuid = reader.ReadGuid(2); if (albumGuid != Guid.Empty) track.Album = _albumsProvider.Collection[albumGuid]; track.Guid = reader.ReadGuid(3); track.LastTimePlayed = reader.GetDateTime(4); track.MusicBrainzId = reader.GetValue(5)?.ToString(); track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6)); var coverId = reader.ReadGuid(7); if (coverId != Guid.Empty) track.Cover = _imageProvider.Collection[coverId]; Collection.Add(track.Guid, track); Tracks.Add(track); } } _connection = connection; }
public async Task<List<InventurItem>> GetDataAsync() { //using (var db = new InventurContext()) //{ // return await (from i in db.InventurItems // where i.Exported == false // orderby i.ChangedAt descending // select i).ToListAsync(); //} using (var command = new SQLiteCommand($"select * from {TABNAME} where Exported=0 orderby ChangedAt DESC", _dbTool.ConnectDb())) { using (var reader = await command.ExecuteReaderAsync()) { var items = new List<InventurItem>(); if (await reader.ReadAsync()) { while (await reader.ReadAsync()) { var i = new InventurItem { ID = Convert.ToInt32(reader["ID"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), ChangedAt = Convert.ToDateTime(reader["ChangedAt"]), EANCode = reader["EANCode"].ToString(), Amount = Convert.ToInt32(reader["Amount"]), Exported = Convert.ToBoolean(reader["Exported"]) }; items.Add(i); } } return items; } } }
public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Albums`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var album = new Album { Name = reader.GetString(0), Guid = reader.ReadGuid(1), Artists = new ObservableCollection<Artist>( reader.GetString(2)? .Split( ',') .Where(x => !string.IsNullOrWhiteSpace(x)) .Select(x => _artistProvider.ArtistDictionary[Guid.ParseExact(x, "D")]) ?? new List<Artist>()) }; Collection.Add(album.Guid, album); } } _connection = connection; }
public async Task<ObservableCollection<Todo>> GetTodos() { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { using (var cmd = new SQLiteCommand("SELECT * FROM TODOs", conn)) { await conn.OpenAsync(); using (var reader = await cmd.ExecuteReaderAsync()) { if (!reader.HasRows) { return new ObservableCollection<Todo>(); } var todos = new ObservableCollection<Todo>(); Todo todo = null; while (reader.Read()) { todo = new Todo(); todo.Id = Convert.ToInt32(reader["Id"].ToString()); todo.CreateDate = Convert.ToDateTime(reader["CreateDate"].ToString()); todo.Task = reader["Task"].ToString(); todo.Done = Convert.ToBoolean(reader["Done"].ToString()); todo.DueDate = reader["DueDate"] == DBNull.Value ? null as DateTime? : Convert.ToDateTime(reader["DueDate"].ToString()); todos.Add(todo); } return todos; } } } }
public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Artists`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var id = reader.ReadGuid(3); Artist artist; if (id == UnknownArtistGuid) artist = UnknownArtist; else { artist = new Artist { Name = reader.GetString(0), MusicBrainzId = reader.GetValue(1)?.ToString(), Url = reader.GetValue(2)?.ToString(), Guid = id }; } var temp = reader.ReadGuid(4); if (temp != Guid.Empty) artist.SmallImage = _imageProvider.Collection[temp]; temp = reader.ReadGuid(5); if (temp != Guid.Empty) artist.MediumImage = _imageProvider.Collection[temp]; temp = reader.ReadGuid(6); if (temp != Guid.Empty) artist.LargeImage = _imageProvider.Collection[temp]; ArtistDictionary.Add(artist.Guid, artist); if (artist.Guid == UnknownArtistGuid) UnknownArtist = artist; } } _connection = connection; }
public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Images`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var id = reader.ReadGuid(1); switch (reader.GetInt32(0)) { case 0: Collection.Add(id, new TagImage(reader.GetString(2))); break; case 1: Collection.Add(id, new OnlineImage(reader.GetString(2))); break; } } } _connection = connection; }
public async Task Load(SQLiteConnection connection) { using (var command = new SQLiteCommand("SELECT * FROM `Playlists`", connection)) { var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var playlist = new UserPlaylist {Name = reader.GetString(0), Id = reader.GetGuid(1)}; using (var trackCommando = new SQLiteCommand("SELECT * FROM `PlaylistTracks` WHERE PlaylistId=@guid", connection )) { trackCommando.Parameters.AddGuid("@guid", playlist.Id); var trackReader = trackCommando.ExecuteReader(); while (await trackReader.ReadAsync()) playlist.Tracks.Add(_trackProvider.Collection[trackReader.GetGuid(1)]); } playlist.TrackAdded += Playlist_TrackAdded; playlist.TrackRemoved += Playlist_TrackRemoved; Playlists.Add(playlist); } } _connection = connection; }
public async Task<List<StatisticsEntry>> GetStatisticEntriesAsync(int limit) { Connection.Open(); List<StatisticsEntry> ret = new List<StatisticsEntry>(); SQLiteCommand command = new SQLiteCommand("SELECT ID, Timestamp, Runtime, CurrentPlayers, TotalPlayers, CurrentMemory, MaximumMemory, Restarts FROM statistics ORDER BY Timestamp DESC Limit $limit", Connection); command.Parameters.AddWithValue("$limit", limit); DbDataReader reader = await command.ExecuteReaderAsync(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); DateTime Timestamp = (DateTime)reader["Timestamp"]; TimeSpan Runtime = TimeSpan.Parse(reader.GetString(2)); int CurrentPlayers = Convert.ToInt32(reader["CurrentPlayers"]); int TotalPlayers = Convert.ToInt32(reader["TotalPlayers"]); long CurrentMemory = Convert.ToInt64(reader["CurrentMemory"]); long MaxMemory = Convert.ToInt64(reader["MaximumMemory"]); int Restarts = Convert.ToInt32(reader["Restarts"]); StatisticsEntry tmp = new StatisticsEntry(ID, Timestamp, Runtime, CurrentPlayers, TotalPlayers, CurrentMemory, MaxMemory, Restarts); ret.Add(tmp); } reader.Close(); Connection.Close(); return ret; }
/// <summary> /// Retreives a list of known names for an IP (async) /// </summary> /// <param name="ip"></param> /// <returns></returns> public async Task<List<string>> GetKnownNamesAsync(string IP) { await Connection.OpenAsync(); List<string> ret = new List<string>(); long ipId = await getIPIDAsync(IP, true); SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM knownPlayers WHERE IP = $ip"); command.Parameters.AddWithValue("$ip", ipId); long count = (long)await ExecuteScalarAsync(command); if (count > 0) { //await connection.OpenAsync(); command = new SQLiteCommand("SELECT names.Name as Name FROM knownPlayers LEFT JOIN names ON names.ID = knownPlayers.Name WHERE IP = $ip"); command.Parameters.AddWithValue("$ip", ipId); command.Connection = Connection; DbDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { string name = (string)reader["Name"]; ret.Add(name); } reader.Close(); //connection.Close(); ret.Sort(); } Connection.Close(); return ret; }