Example #1
2
        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;
        }
Example #2
1
        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;
                }
            }
        }
Example #3
0
        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;
        }
Example #4
0
        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;
                    }
                }
            }
        }
Example #5
0
        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;
        }
Example #6
0
        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;
        }
Example #7
0
        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 [email protected]", 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;
        }
Example #8
0
        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;
        }
Example #9
0
        /// <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;
        }