public ArtistListViewModel(TypeListEnum type, string filter = "All") { ArtistStatuses = DataAccess.GetStatusList(); ArtistScores = DataAccess.GetScoreList(); TypeList = type; ArtistList = DataAccess.GetArtistList(TypeList, filter); FirstLetterFilterList = Enumerable.Range('A', 26).Select(x => (char)x).Select(c => c.ToString()).ToList(); FirstLetterFilterList.Insert(0, "All"); FirstLetterFilterList.Add("0-9"); FirstLetterFilterList.Add("*"); ArtistSelected = new ArtistModel(); UpdateArtistUpToDateCommand = new RelayCommand(UpdateArtistUpToDate, param => canExecute); FirstLetterFilterCommand = new RelayCommand(FirstLetterFilter, param => canExecute); }
public static ObservableCollection <ArtistModel> GetArtistList(TypeListEnum type, string filter) { ObservableCollection <ArtistModel> artistList = new ObservableCollection <ArtistModel>(); using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}")) { db.Open(); SqliteCommand selectCommand = new SqliteCommand(); selectCommand.Connection = db; selectCommand.CommandText = "SELECT a.id, a.name, a.alias, a.upToDate, a.date, st.id, st.label, st.color, sc.id, sc.label FROM Artist a LEFT JOIN Status st ON st.id = a.statusId LEFT JOIN Score sc ON sc.id = a.scoreId "; switch (type) { case TypeListEnum.Listened: selectCommand.CommandText += "WHERE a.statusId <> 6 "; break; case TypeListEnum.Unlistened: selectCommand.CommandText += "WHERE a.statusId = 6 "; break; case TypeListEnum.Searched: selectCommand.CommandText += "WHERE 0 = 0 "; break; } if (!string.IsNullOrEmpty(filter) && filter != "All") { if (filter == "0-9") { selectCommand.CommandText += "AND a.name GLOB '[0-9]*' "; } else if (filter == "*") { selectCommand.CommandText += "AND a.name NOT GLOB '[0-9]*' AND a.name NOT GLOB '[A-Z]*' "; } else { selectCommand.CommandText += "AND a.name LIKE '" + filter + "%' "; } } switch (type) { case TypeListEnum.Listened: selectCommand.CommandText += "ORDER BY a.name "; break; case TypeListEnum.Unlistened: selectCommand.CommandText += "ORDER BY a.date "; break; } selectCommand.CommandText += " "; SqliteDataReader query = selectCommand.ExecuteReader(); while (query.Read()) { artistList.Add(new ArtistModel() { ArtistId = query.GetInt32(0), ArtistName = query.GetString(1), ArtistAlias = query.GetString(2), ArtistUpToDate = query.GetBoolean(3), ArtistDate = query.GetDateTime(4), ArtistStatus = new ArtistStatusModel() { StatusId = query.GetInt32(5), StatusLabel = query.GetString(6), StatusColor = (Brush) new BrushConverter().ConvertFromString(query.GetString(7)) }, ArtistScore = new ArtistScoreModel() { ScoreId = query.GetInt32(8), ScoreLabel = query.GetString(9) } }); } db.Close(); } return(artistList); }