public void UpdateRecord(MusicQueryableItem musicEditableItem, object obj) { using (var db = new MusicDatabaseContext(connectionString)) { switch (musicEditableItem) { case MusicQueryableItem.Albums: db.albums.Update((Album)obj); break; case MusicQueryableItem.Artists: db.artists.Update((Artist)obj); break; case MusicQueryableItem.Customers: db.customers.Update((Customer)obj); break; case MusicQueryableItem.Genres: db.genres.Update((Genre)obj); break; case MusicQueryableItem.Invoices: db.invoices.Update((Invoice)obj); break; case MusicQueryableItem.InvoiceLines: db.invoice_lines.Update((InvoiceLine)obj); break; case MusicQueryableItem.Tracks: db.tracks.Update((Track)obj); break; default: throw new NotImplementedException(); } db.SaveChanges(); } }
private async Task selectAndFillCbox(MusicQueryableItem musicQueryableItem) { DataView data; EnumerableRowCollection <DataRow> selection; switch (musicQueryableItem) { case MusicQueryableItem.Albums: cbAlbumArtist.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Artists)); selection = data.Table.AsEnumerable().OrderBy(x => x.Field <string>("Name")); foreach (var i in selection) { cbAlbumArtist.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("ArtistId"), Content = string.Format("{0} [ID: {1}]", i.Field <string>("Name"), (i.Field <string>("ArtistId"))) }); } break; case MusicQueryableItem.Invoices: cbInvoiceCustomer.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Customers)); selection = data.Table.AsEnumerable().OrderBy(x => x.Field <string>("LastName")).ThenBy(x => x.Field <string>("FirstName")); foreach (var i in selection) { cbInvoiceCustomer.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("CustomerId"), Content = string.Format("{0} {1} [ID: {2}]", i.Field <string>("LastName"), i.Field <string>("FirstName"), (i.Field <string>("CustomerId"))) }); } break; case MusicQueryableItem.InvoiceLines: cbInvoiceLineInvoice.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Invoices)); selection = data.Table.AsEnumerable(); foreach (var i in selection) { cbInvoiceLineInvoice.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("InvoiceId"), Content = string.Format("[ID: {0}]", i.Field <string>("InvoiceId")) }); } cbInvoiceLineTrack.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Tracks)); selection = data.Table.AsEnumerable().OrderBy(x => x.Field <string>("Name")); foreach (var i in selection) { cbInvoiceLineTrack.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("TrackId"), Content = string.Format("{0} [ID: {1}]", i.Field <string>("Name"), i.Field <string>("TrackId")) }); } break; case MusicQueryableItem.Tracks: cbTrackAlbum.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Albums)); selection = data.Table.AsEnumerable().OrderBy(x => x.Field <string>("Title")); foreach (var i in selection) { cbTrackAlbum.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("AlbumId"), Content = string.Format("{0} [ID: {1}]", i.Field <string>("Title"), i.Field <string>("AlbumId")) }); } cbTrackGenre.Items.Clear(); data = await Task.Run(() => musicDbManager.SelectData(MusicQueryableItem.Genres)); selection = data.Table.AsEnumerable().OrderBy(x => x.Field <string>("Name")); foreach (var i in selection) { cbTrackGenre.Items.Add(new ComboBoxItem() { ToolTip = i.Field <string>("GenreId"), Content = string.Format("{0} [ID: {1}]", i.Field <string>("Name"), i.Field <string>("GenreId")) }); } break; default: break; } }
public DataView SelectData(MusicQueryableItem musicQueryableItem, string filter = "") { using (var db = new MusicDatabaseContext(connectionString)) { filter = filter.ToLower(); switch (musicQueryableItem) { case MusicQueryableItem.Albums: return(DataConversion.ToDataView(( from n in db.albums where n.Title.ToLower().Contains(filter) || n.Artist.Name.ToLower().Contains(filter) select new { n.AlbumId, n.ArtistId, n.Title, Artist = n.Artist.Name } ).ToList())); case MusicQueryableItem.Artists: return(DataConversion.ToDataView(( from n in db.artists where n.Name.ToLower().Contains(filter) select new { n.ArtistId, n.Name } ).ToList())); case MusicQueryableItem.Customers: return(DataConversion.ToDataView(( from n in db.customers where n.FirstName.ToLower().Contains(filter) || n.LastName.ToLower().Contains(filter) || n.Phone.Contains(filter) || n.Email.ToLower().Contains(filter) select new { n.CustomerId, n.FirstName, n.LastName, n.Company, n.Address, n.City, n.Country, n.Phone, n.Email } ).ToList())); case MusicQueryableItem.Genres: return(DataConversion.ToDataView(( from n in db.genres where n.Name.ToLower().Contains(filter) select new { n.GenreId, n.Name } ).ToList())); case MusicQueryableItem.Invoices: return(DataConversion.ToDataView(( from n in db.invoices where n.Customer.FirstName.ToLower().Contains(filter) || n.Customer.LastName.ToLower().Contains(filter) || n.Customer.Phone.Contains(filter) || n.Customer.Email.ToLower().Contains(filter) //|| n.InvoiceDate.ToString().Contains(filter) select new { n.InvoiceId, n.CustomerId, n.InvoiceDate, Customer = n.Customer.FirstName + " " + n.Customer.LastName, n.Customer.Phone, n.Customer.Email } ).ToList())); case MusicQueryableItem.InvoiceLines: return(DataConversion.ToDataView(( from n in db.invoice_lines where n.Invoice.Customer.FirstName.ToLower().Contains(filter) || n.Invoice.Customer.LastName.ToLower().Contains(filter) || n.Invoice.Customer.Phone.Contains(filter) || n.Invoice.Customer.Email.ToLower().Contains(filter) //|| n.Invoice.InvoiceDate.ToShortDateString().Contains(filter) || n.Track.Name.ToLower().Contains(filter) || n.Track.Genre.Name.ToLower().Contains(filter) || n.Track.Album.Title.ToLower().Contains(filter) || n.Track.Album.Artist.Name.ToLower().Contains(filter) select new { n.InvoiceLineId, n.InvoiceId, n.TrackId, n.Invoice.InvoiceDate, Customer = n.Invoice.Customer.FirstName + " " + n.Invoice.Customer.LastName, n.Invoice.Customer.Phone, n.Invoice.Customer.Email, Track = n.Track.Name, Album = n.Track.Album.Title, Artist = n.Track.Album.Artist.Name, Genre = n.Track.Genre.Name, n.Track.Bytes, n.Track.UnitPrice } ).ToList())); case MusicQueryableItem.Tracks: return(DataConversion.ToDataView(( from n in db.tracks where n.Name.ToLower().Contains(filter) || n.Genre.Name.ToLower().Contains(filter) || n.Album.Title.Contains(filter) || n.Album.Artist.Name.ToLower().Contains(filter) select new { n.TrackId, n.AlbumId, n.GenreId, n.Name, Album = n.Album.Title, Artist = n.Album.Artist.Name, Genre = n.Genre.Name, n.Milliseconds, n.Bytes, n.UnitPrice } ).ToList())); case MusicQueryableItem.InvoicesSummary: return(DataConversion.ToDataView(( from il in db.invoice_lines group il by new { il.Invoice.InvoiceId, il.Invoice.InvoiceDate, il.Invoice.Customer.FirstName, il.Invoice.Customer.LastName } into s select new { s.Key.InvoiceId, s.Key.InvoiceDate, Customer = s.Key.FirstName + " " + s.Key.LastName, TracksQuantity = s.Count(), MbTotal = Math.Round(s.Sum(sg => sg.Track.Bytes / Math.Pow(1024, 2)), 2), PriceTotal = s.Sum(sg => sg.Track.UnitPrice) } ).ToList())); default: throw new NotImplementedException(); } } }
public void RemoveRecords(MusicQueryableItem musicEditableItem, int[] ids) { using (var db = new MusicDatabaseContext(connectionString)) { switch (musicEditableItem) { case MusicQueryableItem.Albums: var albums = new Album[ids.Length]; for (int i = 0; i < ids.Length; i++) { albums[i] = new Album() { AlbumId = ids[i] } } ; db.albums.RemoveRange(albums); break; case MusicQueryableItem.Artists: var artists = new Artist[ids.Length]; for (int i = 0; i < ids.Length; i++) { artists[i] = new Artist() { ArtistId = ids[i] } } ; db.artists.RemoveRange(artists); break; case MusicQueryableItem.Customers: var customers = new Customer[ids.Length]; for (int i = 0; i < ids.Length; i++) { customers[i] = new Customer() { CustomerId = ids[i] } } ; db.customers.RemoveRange(customers); break; case MusicQueryableItem.Genres: var genres = new Genre[ids.Length]; for (int i = 0; i < ids.Length; i++) { genres[i] = new Genre() { GenreId = ids[i] } } ; db.genres.RemoveRange(genres); break; case MusicQueryableItem.Invoices: var invoices = new Invoice[ids.Length]; for (int i = 0; i < ids.Length; i++) { invoices[i] = new Invoice() { InvoiceId = ids[i] } } ; db.invoices.RemoveRange(invoices); break; case MusicQueryableItem.InvoiceLines: var invoice_lines = new InvoiceLine[ids.Length]; for (int i = 0; i < ids.Length; i++) { invoice_lines[i] = new InvoiceLine() { InvoiceLineId = ids[i] } } ; db.invoice_lines.RemoveRange(invoice_lines); break; case MusicQueryableItem.Tracks: var tracks = new Track[ids.Length]; for (int i = 0; i < ids.Length; i++) { tracks[i] = new Track() { TrackId = ids[i] } } ; db.tracks.RemoveRange(tracks); break; default: throw new NotImplementedException(); } db.SaveChanges(); } }