void UpdateTrackListItem(TrackListItem item) { string sql = GetSql(item.ID); using (DataBaseView view = DataBaseView.Create(this.DataBase, sql)) { object[] values; values = view.Read(); if (values != null) { ReadValues(values, item); } } }
void bwTrackList_DoWork(object sender, DoWorkEventArgs e) { string sql = ""; switch (CurrentViewMode) { case MainControls.CurrentViewMode.MediumTable: sql = "SELECT Medium.Name, COUNT(*) AS TotalCount " + "FROM CD LEFT JOIN " + "Medium ON CD.MediumID = Medium.MediumID " + "GROUP BY Medium.Name"; if (Condition != null && Condition.Count > 0) { sql += " HAVING Medium.Name LIKE '" + Condition[0].Value + "%'"; } break; } List <AlbumListItem> items = new List <AlbumListItem>(); using (DataBaseView view = DataBaseView.Create(this.DataBase, sql)) { object[] values; while ((values = view.Read()) != null) { AlbumListItem trackListItem = new AlbumListItem(); trackListItem.Title = values[0].ToString(); trackListItem.Count = (int)values[1]; items.Add(trackListItem); } } e.Result = items; }
void bwTrackList_DoWork(object sender, DoWorkEventArgs e) { string sql = ""; Big3.Hitbase.DataBaseEngine.Condition searchCondition = Big3.Hitbase.DataBaseEngine.Condition.Combine(Condition, ConditionFromTree); switch (CurrentViewMode) { case MainControls.CurrentViewMode.ArtistTable: sql = "SELECT PersonGroup.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + "FROM Track INNER JOIN " + "PersonGroup ON Track.ArtistID = PersonGroup.PersonGroupID " + "GROUP BY PersonGroup.Name, PersonGroup.SaveAs"; if (searchCondition != null && searchCondition.Count > 0) { if (searchCondition[0].Value.ToString() == "?") { sql += " HAVING PersonGroup.SaveAs < 'A' OR PersonGroup.SaveAs > 'ZZZZZ'"; } else { sql += " HAVING PersonGroup.SaveAs >= '" + searchCondition[0].Value + "' AND PersonGroup.SaveAs < '" + searchCondition[0].Value + "ZZZZZ'"; } } sql += " ORDER BY PersonGroup.SaveAs"; break; case MainControls.CurrentViewMode.ComposerTable: sql = "SELECT PersonGroup.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + "FROM Track INNER JOIN " + "PersonGroup ON Track.ComposerID = PersonGroup.PersonGroupID " + "GROUP BY PersonGroup.Name, PersonGroup.SaveAs "; if (searchCondition != null && searchCondition.Count > 0) { if (searchCondition[0].Value.ToString() == "?") { sql += " HAVING PersonGroup.SaveAs < 'A' OR PersonGroup.SaveAs > 'ZZZZZ'"; } else { sql += " HAVING PersonGroup.SaveAs >= '" + searchCondition[0].Value + "' AND PersonGroup.SaveAs < '" + searchCondition[0].Value + "ZZZZZ'"; } } sql += " ORDER BY PersonGroup.SaveAs"; break; case MainControls.CurrentViewMode.GenreTable: sql = "SELECT Category.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + "FROM Track LEFT JOIN " + "CD ON Track.CDID = CD.CDID LEFT JOIN " + "Category ON Track.CategoryID = Category.CategoryID OR CD.CategoryID = Category.CategoryID " + "GROUP BY Category.Name"; if (searchCondition != null && searchCondition.Count > 0) { sql += " HAVING Category.Name LIKE '" + searchCondition[0].Value + "%'"; } break; case MainControls.CurrentViewMode.MediumTable: sql = "SELECT Medium.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + "FROM Track INNER JOIN " + "CD ON Track.CDID = CD.CDID LEFT JOIN " + "Medium ON CD.MediumID = Medium.MediumID " + "GROUP BY Medium.Name"; if (searchCondition != null && searchCondition.Count > 0) { sql += " HAVING Medium.Name LIKE '" + searchCondition[0].Value + "%'"; } break; case MainControls.CurrentViewMode.YearTable: sql = "SELECT Track.YearRecorded, COUNT(*) AS TotalCount, SUM(CAST(Track.Length as bigint)) AS TotalLength " + "FROM Track " + "GROUP BY Track.YearRecorded"; if (searchCondition != null && searchCondition.Count > 0) { if (searchCondition.Count > 1) { sql += " HAVING Track.YearRecorded >= " + searchCondition[0].Value + " AND Track.YearRecorded < " + searchCondition[1].Value; } else { sql += " HAVING Track.YearRecorded=" + searchCondition[0].Value + ""; } } break; case MainControls.CurrentViewMode.RatingTable: sql = "SELECT Track.Rating, COUNT(*) AS TotalCount, SUM(CAST(Track.Length as bigint)) AS TotalLength " + "FROM Track " + "GROUP BY Track.Rating"; if (searchCondition != null && searchCondition.Count > 0 && searchCondition[0].Value != null) { sql += " HAVING Track.Rating = " + searchCondition[0].Value + ""; } break; } SafeObservableCollection <TrackListItem> items = new SafeObservableCollection <TrackListItem>(); Dictionary <string, TrackListItem> dictItems = new Dictionary <string, TrackListItem>(); // Für schnelleren Zugriff using (DataBaseView view = DataBaseView.Create(this.DataBase, sql)) { object[] values; while ((values = view.Read()) != null) { TrackListItem trackListItem = new TrackListItem(); trackListItem.Title = values[0].ToString(); if (CurrentViewMode == MainControls.CurrentViewMode.YearTable && trackListItem.Title == "0") { trackListItem.Title = StringTable.Undefined; } trackListItem.Count = (int)values[1]; trackListItem.Length = (long)values[2]; trackListItem.Rating = 0; dictItems.Add(trackListItem.Title, trackListItem); items.AddItemFromThread(trackListItem); } } // Rating ermitteln sql = ""; switch (CurrentViewMode) { case MainControls.CurrentViewMode.ArtistTable: { sql = "SELECT PersonGroup.Name, AVG(CAST(Track.Rating AS decimal)) AS Expr3, PersonGroup.SaveAs " + "FROM Track INNER JOIN " + "PersonGroup ON Track.ArtistID = PersonGroup.PersonGroupID " + "WHERE (Track.Rating > 0) " + "GROUP BY PersonGroup.Name, PersonGroup.SaveAs"; break; } case MainControls.CurrentViewMode.ComposerTable: { sql = "SELECT PersonGroup.Name, AVG(CAST(Track.Rating AS decimal)) AS Expr3 " + "FROM Track INNER JOIN " + "PersonGroup ON Track.ComposerID = PersonGroup.PersonGroupID " + "WHERE (Track.Rating > 0) " + "GROUP BY PersonGroup.Name"; break; } case MainControls.CurrentViewMode.GenreTable: { sql = "SELECT category.Name, AVG(CAST(Track.Rating AS decimal)) AS Expr3 " + "FROM Track LEFT JOIN " + "CD ON Track.CDID = CD.CDID LEFT JOIN " + "Category AS Category ON Track.CategoryID = Category.CategoryID OR CD.CategoryID = Category.CategoryID " + "WHERE (Track.Rating > 0) " + "GROUP BY Category.Name"; break; } case MainControls.CurrentViewMode.YearTable: { sql = "SELECT Track.YearRecorded, AVG(CAST(Track.Rating AS decimal)) AS Expr3 " + "FROM Track " + "WHERE (Track.Rating > 0) " + "GROUP BY Track.YearRecorded"; break; } } if (sql != "") { using (DataBaseView view = DataBaseView.Create(this.DataBase, sql)) { object[] values; while ((values = view.Read()) != null) { string key = values[0].ToString(); if (CurrentViewMode == MainControls.CurrentViewMode.YearTable && key == "0") { key = StringTable.Undefined; } if (dictItems.ContainsKey(key)) { TrackListItem trackListItem = dictItems[key]; trackListItem.Rating = (double)(decimal)values[1]; } } } } e.Result = items; }
void bwTrackList_DoWork(object sender, DoWorkEventArgs e) { string sql = ""; switch (CurrentViewMode) { case MainControls.CurrentViewMode.ArtistDetails: case MainControls.CurrentViewMode.ComposerDetails: sql = GetArtistDetailsSql(); if (Condition != null && Condition.Count > 0 && Condition[0].Value != null) { sql += " HAVING PersonGroup.Name LIKE '" + Condition[0].Value + "%'"; } break; //case MainControls.CurrentViewMode.ComposerTable: // sql = "SELECT PersonGroup.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + // "FROM Track INNER JOIN " + // "PersonGroup ON Track.ComposerID = PersonGroup.PersonGroupID " + // "GROUP BY PersonGroup.Name"; // if (Condition != null && Condition.Count > 0) // { // sql += " HAVING PersonGroup.Name LIKE '" + Condition[0].Value + "%'"; // } // break; //case MainControls.CurrentViewMode.GenreTable: // sql = "SELECT Category.Name, COUNT(*) AS TotalCount, SUM(Cast(Track.Length as bigint)) AS TotalLength " + // "FROM Track INNER JOIN " + // "Category ON Track.CategoryID = Category.CategoryID " + // "GROUP BY Category.Name"; // if (Condition != null && Condition.Count > 0) // { // sql += " HAVING Category.Name LIKE '" + Condition[0].Value + "%'"; // } // break; //case MainControls.CurrentViewMode.YearTable: // sql = "SELECT Track.YearRecorded, COUNT(*) AS TotalCount, SUM(CAST(Track.Length as bigint)) AS TotalLength " + // "FROM Track " + // "GROUP BY Track.YearRecorded"; // break; //case MainControls.CurrentViewMode.RatingTable: // sql = "SELECT Track.Rating, COUNT(*) AS TotalCount, SUM(CAST(Track.Length as bigint)) AS TotalLength " + // "FROM Track " + // "GROUP BY Track.Rating"; // if (Condition != null && Condition.Count > 0) // { // sql += " HAVING Track.Rating = " + Condition[0].Value + ""; // } // break; } SafeObservableCollection <TrackListItem> items = new SafeObservableCollection <TrackListItem>(); using (DataBaseView view = DataBaseView.Create(this.DataBase, sql)) { object[] values; while ((values = view.Read()) != null) { TrackListItem trackListItem = new TrackListItem(); ReadValues(values, trackListItem); items.AddItemFromThread(trackListItem); } } e.Result = items; }