public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var song = context.Songs.Where(s => s.Name == "Bentasil"); var songs = context .Songs .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new SongDto { SongName = s.Name, Writer = s.Writer.Name, Performer = s.SongPerformers .Select(sp => sp.Performer.FirstName + " " + sp.Performer.LastName) .FirstOrDefault(), AlbumProducer = s.Album.Producer.Name, Duration = s.Duration.ToString("c", CultureInfo.InvariantCulture) }) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer) .ToArray(); var serializer = new XmlSerializer(typeof(SongDto[]), new XmlRootAttribute("Songs")); var sb = new StringBuilder(); serializer.Serialize(new StringWriter(sb), songs, new XmlSerializerNamespaces(new XmlQualifiedName[] { XmlQualifiedName.Empty })); var result = sb.ToString().TrimEnd(); return(result); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var writersDto = JsonConvert.DeserializeObject <Writer[]>(jsonString); var sb = new StringBuilder(); var validWriters = new List <Writer>(); foreach (var writerDto in writersDto) { if (!IsValid(writerDto)) { sb.AppendLine(ErrorMessage); continue; } validWriters.Add(writerDto); sb.AppendLine(string.Format(SuccessfullyImportedWriter, writerDto.Name)); } context.Writers.AddRange(validWriters); context.SaveChanges(); var result = sb.ToString().TrimEnd(); return(result); }
private static void ResetDatabase(MusicHubDbContext context, bool shouldDropDatabase = false) { if (shouldDropDatabase) { context.Database.EnsureDeleted(); } if (context.Database.EnsureCreated()) { return; } var disableIntegrityChecksQuery = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'"; context.Database.ExecuteSqlCommand(disableIntegrityChecksQuery); var deleteRowsQuery = "EXEC sp_MSforeachtable @command1='SET QUOTED_IDENTIFIER ON;DELETE FROM ?'"; context.Database.ExecuteSqlCommand(deleteRowsQuery); var enableIntegrityChecksQuery = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'"; context.Database.ExecuteSqlCommand(enableIntegrityChecksQuery); var reseedQuery = "EXEC sp_MSforeachtable @command1='IF OBJECT_ID(''?'') IN (SELECT OBJECT_ID FROM SYS.IDENTITY_COLUMNS) DBCC CHECKIDENT(''?'', RESEED, 0)'"; context.Database.ExecuteSqlCommand(reseedQuery); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var songs = context.Songs .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new ExportSongDto { SongName = s.Name, AlbumProducer = s.Album.Producer.Name, Performer = s.SongPerformers .Select(p => p.Performer.FirstName + " " + p.Performer.LastName) .FirstOrDefault(), Writer = s.Writer.Name, Duration = s.Duration.ToString(@"hh\:mm\:ss") }) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer) .ToArray(); var xmlSerializer = new XmlSerializer(typeof(ExportSongDto[]), new XmlRootAttribute("Songs")); var sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }); xmlSerializer.Serialize(new StringWriter(sb), songs, namespaces); return(sb.ToString().TrimEnd()); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var sb = new StringBuilder(); var writers = new List <Writer>(); var writersDtos = JsonConvert.DeserializeObject <ImportWritersDro[]>(jsonString); foreach (var writerDto in writersDtos) { if (!IsValid(writerDto)) { sb.AppendLine(ErrorMessage); continue; } var writer = new Writer { Name = writerDto.Name, Pseudonym = writerDto.Pseudonym }; writers.Add(writer); sb.AppendLine($"Imported {writer.Name}"); } context.Writers.AddRange(writers); context.SaveChanges(); return(sb.ToString().TrimEnd()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { StringBuilder sb = new StringBuilder(); var songs = context.Songs.ToList() .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new { Name = s.Name, PerformerFullName = s.SongPerformers .Select(sp => $"{sp.Performer.FirstName} {sp.Performer.LastName}") .FirstOrDefault(), WriterName = s.Writer.Name, AlbumProducer = s.Album.Producer.Name, Duration = s.Duration.ToString(@"hh\:mm\:ss", CultureInfo.InvariantCulture) }) .OrderBy(s => s.Name) .ThenBy(s => s.WriterName) .ThenBy(s => s.PerformerFullName) .ToList(); int counter = 1; foreach (var song in songs) { sb.AppendLine($"-Song #{counter++}"); sb.AppendLine($"---SongName: {song.Name}"); sb.AppendLine($"---Writer: {song.WriterName}"); sb.AppendLine($"---Performer: {song.PerformerFullName}"); sb.AppendLine($"---AlbumProducer: {song.AlbumProducer}"); sb.AppendLine($"---Duration: {song.Duration}"); } return(sb.ToString().TrimEnd()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var songs = context.Songs .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new ExportSongDto { SongName = s.Name, Writer = s.Writer.Name, Performer = s.SongPerformers .Select(sp => $"{sp.Performer.FirstName} {sp.Performer.LastName}") .FirstOrDefault(), AlbumProducer = s.Album.Producer.Name, Duration = s.Duration.ToString("c", CultureInfo.InvariantCulture) }) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer) .ToArray(); using var writer = new StringWriter(); var ns = new XmlSerializerNamespaces(); ns.Add(string.Empty, string.Empty); var xmlSerializer = new XmlSerializer(typeof(ExportSongDto[]), new XmlRootAttribute("Songs")); xmlSerializer.Serialize(writer, songs, ns); var songsXml = writer.GetStringBuilder(); return(songsXml.ToString().TrimEnd()); }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albums = context.Albums .Where(a => a.ProducerId == producerId) .OrderByDescending(x => x.Price) .Select(x => new { AlbumName = x.Name, ReleaseDate = x.ReleaseDate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture), ProducerName = x.Producer.Name, Songs = x.Songs .Select(s => new { SongName = s.Name, Price = s.Price.ToString("f2"), Writer = s.Writer.Name }) .OrderByDescending(s => s.SongName) .ThenBy(s => s.Writer) .ToArray(), AlbumPrice = x.Price.ToString("f2") }) .ToArray(); var jsonResult = JsonConvert.SerializeObject(albums, Newtonsoft.Json.Formatting.Indented); return(jsonResult); }
public static string ImportSongPerformers(MusicHubDbContext context, string xmlString) { var serializer = new XmlSerializer(typeof(List <PerformerImputDTO>), new XmlRootAttribute("Performers")); var performersDTOs = (List <PerformerImputDTO>)serializer.Deserialize(new StringReader(xmlString)); var sb = new StringBuilder(); var performers = new List <Performer>(); var validSongs = context.Songs.Select(s => s.Id).ToList(); foreach (var dto in performersDTOs) { var isValid = IsValid(dto); if (!isValid) { sb.AppendLine(ErrorMessage); continue; } var performer = new Performer() { FirstName = dto.FirstName, LastName = dto.LastName, Age = dto.Age, NetWorth = dto.NetWorth }; var isOkToImport = true; foreach (var song in dto.PerformerSongs) { if (!validSongs.Contains(song.Id)) { sb.AppendLine(ErrorMessage); isOkToImport = false; break; } var songPerformer = new SongPerformer() { SongId = song.Id }; performer.PerformerSongs.Add(songPerformer); } if (isOkToImport) { var result = String.Format(SuccessfullyImportedPerformer, performer.FirstName, performer.PerformerSongs.Count()); sb.AppendLine(result); performers.Add(performer); } } context.Performers.AddRange(performers); context.SaveChanges(); return(sb.ToString().TrimEnd()); }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albumsDto = context.Albums .Where(p => p.ProducerId == producerId) .OrderByDescending(a => a.Price) .Select(a => new AlbumDto { AlbumName = a.Name, ReleaseDate = a.ReleaseDate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture), ProducerName = a.Producer.Name, Songs = a.Songs.Select(s => new SongDto { SongName = s.Name, Price = s.Price.ToString("F2"), Writer = s.Writer.Name }) .OrderByDescending(s => s.SongName) .ThenBy(w => w.Writer) .ToList(), AlbumPrice = a.Price.ToString("F2") }) .ToList(); var json = JsonConvert.SerializeObject(albumsDto, Newtonsoft.Json.Formatting.Indented); return(json); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var sb = new StringBuilder(); var dtos = JsonConvert.DeserializeObject <ImportWriterDto[]>(jsonString); var results = new List <Writer>(); foreach (var dto in dtos) { if (!IsValid(dto)) { sb.AppendLine(ErrorMessage); continue; } sb.AppendLine(String.Format(SuccessfullyImportedWriter, dto.Name)); results.Add(new Writer { Name = dto.Name, Pseudonym = dto.Pseudonym, }); } context.Writers.AddRange(results); context.SaveChanges(); return(sb.ToString().TrimEnd()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var songs = context.Songs .Where(x => x.Duration.TotalSeconds > duration) .OrderBy(x => x.Name) .ThenBy(x => x.Writer.Name) .ThenBy(x => x.SongPerformers.Select(y => $"{y.Performer.FirstName} {y.Performer.LastName}").FirstOrDefault()) .Select(x => new SongXmlDto { SongName = x.Name, Writer = x.Writer.Name, Performer = x.SongPerformers.Select(y => $"{y.Performer.FirstName} {y.Performer.LastName}").FirstOrDefault(), AlbumProducer = x.Album.Producer.Name, Duration = x.Duration.ToString("c") }) .ToArray(); var serializer = new XmlSerializer(typeof(SongXmlDto[]), new XmlRootAttribute("Songs")); var namespaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }); var sb = new StringBuilder(); serializer.Serialize(new StringWriter(sb), songs, namespaces); return(sb.ToString().TrimEnd()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { StringBuilder sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(); namespaces.Add(String.Empty, String.Empty); var encoding = Encoding.UTF8; var songs = context.Songs.Where(x => x.Duration.TotalSeconds > duration) .Select(x => new ExportSongDto() { SongName = x.Name, Writer = x.Writer.Name, Performer = x.SongPerformers.FirstOrDefault().Performer.FirstName + " " + x.SongPerformers.FirstOrDefault().Performer.LastName, AlbumProducer = x.Album.Producer.Name, Duration = x.Duration.ToString("c") }) .OrderBy(x => x.SongName) .ThenBy(x => x.Writer) .ThenBy(x => x.Performer) .ToArray(); XmlSerializer xmlSerializer = new XmlSerializer(typeof(ExportSongDto[]), new XmlRootAttribute("Songs")); xmlSerializer.Serialize(new StringWriter(sb), songs, namespaces); return(sb.ToString().Trim()); }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albums = context.Albums.Where(x => x.Producer.Id == producerId) .Select(x => new { AlbumName = x.Name, ReleaseDate = x.ReleaseDate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture), ProducerName = x.Producer.Name, Songs = x.Songs.Select(y => new { SongName = y.Name, Price = y.Price.ToString("f2"), Writer = y.Writer.Name }) .OrderByDescending(y => y.SongName) .ThenBy(y => y.Writer) .ToList(), AlbumPrice = x.Songs.Sum(y => y.Price).ToString("f2") }) .OrderByDescending(x => x.AlbumPrice) .ToList(); string json = JsonConvert.SerializeObject(albums, Formatting.Indented); return(json); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var outputResult = new StringBuilder(); var resultWritersDtos = JsonConvert.DeserializeObject <ImportWriterDto[]>(jsonString); var newWriters = new List <Writer>(); foreach (var writerDto in resultWritersDtos) { if (!IsValid(writerDto)) { outputResult.AppendLine(ErrorMessage); continue; } var newWriter = new Writer { Name = writerDto.Name, Pseudonym = writerDto.Pseudonym }; newWriters.Add(newWriter); outputResult.AppendLine(string.Format(SuccessfullyImportedWriter, newWriter.Name)); } context.Writers.AddRange(newWriters); context.SaveChanges(); return(outputResult.ToString().TrimEnd()); }
// JSON public static string ImportWriters(MusicHubDbContext context, string jsonString) { var writers = JsonConvert.DeserializeObject <List <WriterImportDTO> >(jsonString); var writersToAdd = new HashSet <Writer>(); var sb = new StringBuilder(); foreach (var writer in writers) { if (!IsValid(writer)) { sb.AppendLine(ErrorMessage); continue; } var newWriter = new Writer { Name = writer.Name, Pseudonym = writer.Pseudonym }; writersToAdd.Add(newWriter); sb.AppendLine(string.Format(SuccessfullyImportedWriter, newWriter.Name)); } context.Writers.AddRange(writersToAdd); context.SaveChanges(); return(sb.ToString().TrimEnd()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var sb = new StringBuilder(); var longSongs = context.Songs.Where(x => x.Duration.TotalSeconds > duration) .Select(s => new expXmlSong() { SongName = s.Name, Writer = s.Writer.Name, AlbumProducer = s.Album.Producer.Name, PerformerFullName = s.SongPerformers.Any() ? s.SongPerformers.Select(p => $"{p.Performer.FirstName} {p.Performer.LastName}").First() : null, Duration = s.Duration.ToString("c", CultureInfo.InvariantCulture) }).OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.PerformerFullName) .ToArray(); var serializer = new XmlSerializer(typeof(expXmlSong[]), new XmlRootAttribute("Songs")); var ns = new XmlSerializerNamespaces(); ns.Add("", ""); using (var sw = new StringWriter(sb)) { serializer.Serialize(sw, longSongs, ns); } return(sb.ToString().Trim()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { StringBuilder sb = new StringBuilder(); var info = context.Songs.ToArray() .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new { SongName = s.Name, Writer = s.Writer.Name, Performer = s.SongPerformers.ToArray() .Select(p => $"{p.Performer.FirstName} {p.Performer.LastName}").FirstOrDefault(), AlbumProducer = s.Album.Producer.Name, Duration = s.Duration }) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer) .ToArray(); int i = 0; foreach (var song in info) { i++; sb.AppendLine($"-Song #{i}"); sb.AppendLine($"---SongName: {song.SongName}"); sb.AppendLine($"---Writer: {song.Writer}"); sb.AppendLine($"---Performer: {song.Performer}"); sb.AppendLine($"---AlbumProducer: {song.AlbumProducer}"); sb.AppendLine($"---Duration: {song.Duration.ToString("c",CultureInfo.InvariantCulture)}"); } return(sb.ToString().TrimEnd()); }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albums = context.Albums .Where(a => a.ProducerId == producerId) .OrderByDescending(a => a.Songs.Sum(s => s.Price)) .Select(a => new { AlbumName = a.Name, ReleaseDate = a.ReleaseDate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture), ProducerName = a.Producer.Name, Songs = a.Songs .Select(s => new { SongName = s.Name, Price = $"{s.Price:F2}", Writer = s.Writer.Name }) .OrderByDescending(s => s.SongName) .ThenBy(w => w.Writer), AlbumPrice = $"{a.Songs.Sum(s => s.Price):F2}" }) .ToArray(); var albumsJson = JsonConvert.SerializeObject(albums, Formatting.Indented); return(albumsJson); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var itemsDtos = JsonConvert.DeserializeObject <ImportWritersDto[]>(jsonString); var sb = new StringBuilder(); var items = new List <Writer>(); foreach (var itemDto in itemsDtos) { Writer writer = Mapper.Map <Writer>(itemDto); if (!IsValid(writer)) { sb.AppendLine(ErrorMessage); continue; } items.Add(writer); sb.AppendLine(String.Format(SuccessfullyImportedWriter, writer.Name)); } context.Writers.AddRange(items); context.SaveChanges(); return(sb.ToString().TrimEnd()); }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albums = context.Albums .Where(a => a.ProducerId == producerId) .OrderByDescending(a => a.Songs.Sum(s => s.Price)) .Select(a => new ExportAlbumDto { AlbumName = a.Name, ProducerName = a.Producer.Name, ReleaseDate = a.ReleaseDate.ToString(@"MM/dd/yyyy"), Songs = a.Songs.Select(s => new ExportAlbumSongsDto { SongName = s.Name, Price = s.Price.ToString("F2"), Writer = s.Writer.Name }) .OrderByDescending(s => s.SongName) .ThenBy(s => s.Writer) .ToList(), AlbumPrice = a.Songs.Sum(s => s.Price).ToString("F2") }) .ToList(); var json = JsonConvert.SerializeObject(albums, Formatting.Indented); return(json); }
public static string ImportProducersAlbums(MusicHubDbContext context, string jsonString) { var producersDto = JsonConvert.DeserializeObject <ImportProducerDto[]>(jsonString); StringBuilder sb = new StringBuilder(); List <Producer> validProducers = new List <Producer>(); foreach (var producerDto in producersDto) { if (!IsValid(producerDto) || !producerDto.Albums.All(IsValid)) { sb.AppendLine(ErrorMessage); continue; } var producer = AutoMapper.Mapper.Map <Producer>(producerDto); validProducers.Add(producer); string message = producer.PhoneNumber == null ? string.Format(SuccessfullyImportedProducerWithNoPhone, producer.Name, producer.Albums.Count) : string.Format(SuccessfullyImportedProducerWithPhone, producer.Name, producer.PhoneNumber, producer.Albums.Count); sb.AppendLine(message); validProducers.Add(producer); } context.Producers.AddRange(validProducers); context.SaveChanges(); var result = sb.ToString().TrimEnd(); return(result); }
private static void Seed(MusicHubDbContext context) { var datasetsJson = "{\"Writer\":[{\"Id\":1,\"Name\":\"Mik Jonathan\",\"Pseudonym\":\"The Mik\"},{\"Id\":2,\"Name\":\"Maitilde Sangar\",\"Pseudonym\":\"Dilly Marjoram\"},{\"Id\":3,\"Name\":\"Linnie Petrolli\",\"Pseudonym\":\"Teodorico Skyppe\"},{\"Id\":4,\"Name\":\"Bili Franek\",\"Pseudonym\":\"Cornelius Cranson\"},{\"Id\":5,\"Name\":\"Quillan Grover\",\"Pseudonym\":null},{\"Id\":6,\"Name\":\"Tiebout Standall\",\"Pseudonym\":\"Ransom Siemens\"},{\"Id\":7,\"Name\":\"Chloe Trayhorn\",\"Pseudonym\":\"Jonie Driscoll\"},{\"Id\":8,\"Name\":\"Holly Coppen\",\"Pseudonym\":null},{\"Id\":9,\"Name\":\"Chelsy Pennyman\",\"Pseudonym\":\"Roberto Fullard\"},{\"Id\":10,\"Name\":\"Sibelle Hanton\",\"Pseudonym\":null},{\"Id\":11,\"Name\":\"Rosalyn Humphris\",\"Pseudonym\":\"Melesa Sussems\"},{\"Id\":12,\"Name\":\"Marlee Olivet\",\"Pseudonym\":\"Alyda Blundel\"},{\"Id\":13,\"Name\":\"Carol Mitchell\",\"Pseudonym\":\"Fannie Davenhill\"},{\"Id\":14,\"Name\":\"Jessie Townby\",\"Pseudonym\":null},{\"Id\":15,\"Name\":\"Stanford Daykin\",\"Pseudonym\":\"Erny Wiggam\"},{\"Id\":16,\"Name\":\"Kara-lynn Sharpous\",\"Pseudonym\":\"Devina Abatelli\"},{\"Id\":17,\"Name\":\"Verine Eschalotte\",\"Pseudonym\":\"Blair Chilton\"},{\"Id\":18,\"Name\":\"Padget Steptowe\",\"Pseudonym\":\"Loise Topp\"},{\"Id\":19,\"Name\":\"Gleda Messum\",\"Pseudonym\":\"Nonie Beadell\"},{\"Id\":20,\"Name\":\"Lusa Steers\",\"Pseudonym\":\"Granny Hyndley\"},{\"Id\":21,\"Name\":\"Petko Matisse\",\"Pseudonym\":\"Michaelina Liffe\"},{\"Id\":22,\"Name\":\"Norina Renihan\",\"Pseudonym\":\"Valentin Lorenc\"},{\"Id\":23,\"Name\":\"Kizzie Hoyle\",\"Pseudonym\":\"Karlik Lillistone\"}],\"Producer\":[{\"Id\":1,\"Name\":\"Ab Pittham\",\"Pseudonym\":null,\"PhoneNumber\":null},{\"Id\":2,\"Name\":\"Georgi Milkov\",\"Pseudonym\":\"Gosho Goshev\",\"PhoneNumber\":\"+359 899 345 045\"},{\"Id\":3,\"Name\":\"Jana Karaivanova\",\"Pseudonym\":\"Jani Gog\",\"PhoneNumber\":null},{\"Id\":4,\"Name\":\"Evtim Miloshev\",\"Pseudonym\":\"Evo Mils\",\"PhoneNumber\":\"+359 567 234 345\"},{\"Id\":5,\"Name\":\"Dobromir Slavchev\",\"Pseudonym\":\"Doba Dog\",\"PhoneNumber\":\"+359 353 355 789\"},{\"Id\":6,\"Name\":\"Denney Allott\",\"Pseudonym\":null,\"PhoneNumber\":null},{\"Id\":7,\"Name\":\"F.O.\",\"Pseudonym\":\"Rancell Caughey\",\"PhoneNumber\":\"+359 343 244 797\"},{\"Id\":8,\"Name\":\"Rolph Nibley\",\"Pseudonym\":\"Rea Netley\",\"PhoneNumber\":\"+359 343 234 454\"},{\"Id\":9,\"Name\":\"Evgeni Dimitrov\",\"Pseudonym\":\"Evgeni Maestroto\",\"PhoneNumber\":\"+359 456 244 321\"}],\"Album\":[{\"Id\":1,\"Name\":\"Fight and flight\",\"ReleaseDate\":\"2018-11-05T00:00:00\",\"Price\":23.24,\"ProducerId\":2},{\"Id\":2,\"Name\":\"Cherry\",\"ReleaseDate\":\"2018-06-09T00:00:00\",\"Price\":27.288,\"ProducerId\":2},{\"Id\":3,\"Name\":\"No history\",\"ReleaseDate\":\"2019-03-05T00:00:00\",\"Price\":36.5577,\"ProducerId\":2},{\"Id\":4,\"Name\":\"Blinded by fame\",\"ReleaseDate\":\"2018-12-12T00:00:00\",\"Price\":22.2768,\"ProducerId\":3},{\"Id\":5,\"Name\":\"Barrage of noise\",\"ReleaseDate\":\"2018-07-03T00:00:00\",\"Price\":31.583,\"ProducerId\":4},{\"Id\":6,\"Name\":\"The fat lady sings\",\"ReleaseDate\":\"2018-06-01T00:00:00\",\"Price\":7,\"ProducerId\":4},{\"Id\":7,\"Name\":\"Zero gravity\",\"ReleaseDate\":\"2019-08-05T00:00:00\",\"Price\":11.9452,\"ProducerId\":4},{\"Id\":8,\"Name\":\"Hit the road\",\"ReleaseDate\":\"2019-07-03T00:00:00\",\"Price\":29.016,\"ProducerId\":5},{\"Id\":9,\"Name\":\"Louder actions\",\"ReleaseDate\":\"2019-06-01T00:00:00\",\"Price\":9.1649,\"ProducerId\":5},{\"Id\":10,\"Name\":\"Don't Step to This\",\"ReleaseDate\":\"2018-07-01T00:00:00\",\"Price\":23.3699,\"ProducerId\":7},{\"Id\":11,\"Name\":\"Rap Time\",\"ReleaseDate\":\"2019-06-06T00:00:00\",\"Price\":19.5746,\"ProducerId\":7},{\"Id\":12,\"Name\":\"Game on\",\"ReleaseDate\":\"2018-07-03T00:00:00\",\"Price\":3.5,\"ProducerId\":8},{\"Id\":13,\"Name\":\"Two to tango\",\"ReleaseDate\":\"2018-04-03T00:00:00\",\"Price\":25.4087,\"ProducerId\":9},{\"Id\":14,\"Name\":\"Flower shower\",\"ReleaseDate\":\"2018-07-17T00:00:00\",\"Price\":38.0394,\"ProducerId\":9},{\"Id\":15,\"Name\":\"Devil's advocate\",\"ReleaseDate\":\"2018-07-21T00:00:00\",\"Price\":40.4854,\"ProducerId\":9},{\"Id\":16,\"Name\":\"Dark matters\",\"ReleaseDate\":\"2018-07-22T00:00:00\",\"Price\":11.99,\"ProducerId\":9}],\"Song\":[{\"Id\":1,\"Name\":\"What Goes Around\",\"Duration\":\"00:03:23\",\"CreatedOn\":\"2018-12-21T00:00:00\",\"Genre\":0,\"AlbumId\":2,\"WriterId\":2,\"Price\":12},{\"Id\":2,\"Name\":\"Cough Relief\",\"Duration\":\"00:10:34\",\"CreatedOn\":\"2018-06-03T00:00:00\",\"Genre\":3,\"AlbumId\":14,\"WriterId\":14,\"Price\":25.0394},{\"Id\":3,\"Name\":\"Levothyroxine Sodium\",\"Duration\":\"00:10:41\",\"CreatedOn\":\"2018-12-21T00:00:00\",\"Genre\":2,\"AlbumId\":13,\"WriterId\":13,\"Price\":16.4187},{\"Id\":4,\"Name\":\"Medique Diphen\",\"Duration\":\"00:09:31\",\"CreatedOn\":\"2018-09-24T00:00:00\",\"Genre\":1,\"AlbumId\":11,\"WriterId\":11,\"Price\":19.5746},{\"Id\":5,\"Name\":\"Hydralazine Hydroch\",\"Duration\":\"00:09:54\",\"CreatedOn\":\"2018-09-19T00:00:00\",\"Genre\":0,\"AlbumId\":10,\"WriterId\":10,\"Price\":23.3699},{\"Id\":6,\"Name\":\"rx act pain relief\",\"Duration\":\"00:03:39\",\"CreatedOn\":\"2018-03-06T00:00:00\",\"Genre\":1,\"AlbumId\":9,\"WriterId\":9,\"Price\":5.186},{\"Id\":7,\"Name\":\"Crayola Wild Blue\",\"Duration\":\"00:01:58\",\"CreatedOn\":\"2018-02-21T00:00:00\",\"Genre\":3,\"AlbumId\":8,\"WriterId\":8,\"Price\":17.516},{\"Id\":8,\"Name\":\"Carvedilol\",\"Duration\":\"00:02:39\",\"CreatedOn\":\"2018-02-13T00:00:00\",\"Genre\":4,\"AlbumId\":7,\"WriterId\":7,\"Price\":2.9452},{\"Id\":9,\"Name\":\"Water Additive\",\"Duration\":\"00:02:33\",\"CreatedOn\":\"2018-08-08T00:00:00\",\"Genre\":4,\"AlbumId\":5,\"WriterId\":5,\"Price\":11.0343},{\"Id\":10,\"Name\":\"Kids SPF 60\",\"Duration\":\"00:01:17\",\"CreatedOn\":\"2018-12-24T00:00:00\",\"Genre\":3,\"AlbumId\":4,\"WriterId\":4,\"Price\":8.7768},{\"Id\":11,\"Name\":\"Quinapril\",\"Duration\":\"00:10:31\",\"CreatedOn\":\"2018-06-01T00:00:00\",\"Genre\":2,\"AlbumId\":3,\"WriterId\":3,\"Price\":28.0577},{\"Id\":12,\"Name\":\"La Vaquita\",\"Duration\":\"00:08:32\",\"CreatedOn\":\"2018-12-11T00:00:00\",\"Genre\":1,\"AlbumId\":2,\"WriterId\":2,\"Price\":8.788},{\"Id\":13,\"Name\":\"Bentasil\",\"Duration\":\"00:04:03\",\"CreatedOn\":\"2018-07-30T00:00:00\",\"Genre\":0,\"AlbumId\":9,\"WriterId\":1,\"Price\":3.9789},{\"Id\":14,\"Name\":\"Don't call me up\",\"Duration\":\"00:05:20\",\"CreatedOn\":\"2015-09-12T00:00:00\",\"Genre\":4,\"AlbumId\":8,\"WriterId\":14,\"Price\":5},{\"Id\":15,\"Name\":\"Cry Me A River\",\"Duration\":\"00:04:20\",\"CreatedOn\":\"2016-09-18T00:00:00\",\"Genre\":4,\"AlbumId\":7,\"WriterId\":16,\"Price\":9},{\"Id\":16,\"Name\":\"Wait For A Minute\",\"Duration\":\"00:04:20\",\"CreatedOn\":\"2016-09-21T00:00:00\",\"Genre\":0,\"AlbumId\":6,\"WriterId\":17,\"Price\":7},{\"Id\":17,\"Name\":\"Just the two of us\",\"Duration\":\"00:02:40\",\"CreatedOn\":\"2011-09-05T00:00:00\",\"Genre\":1,\"AlbumId\":5,\"WriterId\":18,\"Price\":7},{\"Id\":18,\"Name\":\"Personal\",\"Duration\":\"00:02:40\",\"CreatedOn\":\"2011-09-05T00:00:00\",\"Genre\":2,\"AlbumId\":4,\"WriterId\":19,\"Price\":3.5},{\"Id\":19,\"Name\":\"Ride it\",\"Duration\":\"00:04:35\",\"CreatedOn\":\"2011-03-05T00:00:00\",\"Genre\":4,\"AlbumId\":3,\"WriterId\":20,\"Price\":8.5},{\"Id\":20,\"Name\":\"Say It Right\",\"Duration\":\"00:04:35\",\"CreatedOn\":\"2011-06-05T00:00:00\",\"Genre\":3,\"AlbumId\":2,\"WriterId\":21,\"Price\":6.5},{\"Id\":21,\"Name\":\"Away\",\"Duration\":\"00:05:35\",\"CreatedOn\":\"2008-06-03T00:00:00\",\"Genre\":2,\"AlbumId\":1,\"WriterId\":22,\"Price\":7.5},{\"Id\":22,\"Name\":\"Tiempo\",\"Duration\":\"00:05:35\",\"CreatedOn\":\"2008-06-03T00:00:00\",\"Genre\":2,\"AlbumId\":1,\"WriterId\":22,\"Price\":7.5},{\"Id\":23,\"Name\":\"In the end\",\"Duration\":\"00:02:11\",\"CreatedOn\":\"2006-09-03T00:00:00\",\"Genre\":3,\"AlbumId\":16,\"WriterId\":17,\"Price\":11.99},{\"Id\":24,\"Name\":\"Numb\",\"Duration\":\"00:04:11\",\"CreatedOn\":\"2002-09-03T00:00:00\",\"Genre\":3,\"AlbumId\":15,\"WriterId\":16,\"Price\":13.99},{\"Id\":25,\"Name\":\"It is my life\",\"Duration\":\"00:06:11\",\"CreatedOn\":\"2001-11-03T00:00:00\",\"Genre\":3,\"AlbumId\":14,\"WriterId\":15,\"Price\":13},{\"Id\":26,\"Name\":\"Wide Awake\",\"Duration\":\"00:04:11\",\"CreatedOn\":\"2014-11-03T00:00:00\",\"Genre\":0,\"AlbumId\":13,\"WriterId\":12,\"Price\":8.99},{\"Id\":27,\"Name\":\"In the start\",\"Duration\":\"00:03:15\",\"CreatedOn\":\"2016-08-08T00:00:00\",\"Genre\":2,\"AlbumId\":12,\"WriterId\":11,\"Price\":3.5},{\"Id\":28,\"Name\":\"Lose Yourself\",\"Duration\":\"00:03:30\",\"CreatedOn\":\"2016-08-01T00:00:00\",\"Genre\":3,\"AlbumId\":8,\"WriterId\":9,\"Price\":6.5},{\"Id\":29,\"Name\":\"River\",\"Duration\":\"00:03:10\",\"CreatedOn\":\"2018-12-01T00:00:00\",\"Genre\":2,\"AlbumId\":1,\"WriterId\":5,\"Price\":8.24},{\"Id\":30,\"Name\":\"Morning After\",\"Duration\":\"00:04:23\",\"CreatedOn\":\"2007-12-21T00:00:00\",\"Genre\":1,\"AlbumId\":4,\"WriterId\":3,\"Price\":10},{\"Id\":31,\"Name\":\"Ibuprofen\",\"Duration\":\"00:01:04\",\"CreatedOn\":\"2018-02-22T00:00:00\",\"Genre\":4,\"AlbumId\":15,\"WriterId\":15,\"Price\":26.4954},{\"Id\":32,\"Name\":\"Dorzolamide HCl\",\"Duration\":\"00:02:50\",\"CreatedOn\":\"2018-03-22T00:00:00\",\"Genre\":1,\"AlbumId\":5,\"WriterId\":22,\"Price\":13.5487}],\"Performer\":[{\"Id\":1,\"FirstName\":\"Peter\",\"LastName\":\"Bree\",\"Age\":25,\"NetWorth\":3243},{\"Id\":2,\"FirstName\":\"Gennifer\",\"LastName\":\"Lopez\",\"Age\":38,\"NetWorth\":5531},{\"Id\":3,\"FirstName\":\"Tine\",\"LastName\":\"Althorp\",\"Age\":35,\"NetWorth\":1184},{\"Id\":4,\"FirstName\":\"Gilligan\",\"LastName\":\"Caney\",\"Age\":50,\"NetWorth\":5211},{\"Id\":5,\"FirstName\":\"Zabrina\",\"LastName\":\"Amor\",\"Age\":62,\"NetWorth\":8921},{\"Id\":6,\"FirstName\":\"Georgia\",\"LastName\":\"Winchurch\",\"Age\":21,\"NetWorth\":5072},{\"Id\":7,\"FirstName\":\"Dasi\",\"LastName\":\"Pirrey\",\"Age\":26,\"NetWorth\":5913},{\"Id\":8,\"FirstName\":\"Lula\",\"LastName\":\"Zuan\",\"Age\":19,\"NetWorth\":9558},{\"Id\":9,\"FirstName\":\"Alidia\",\"LastName\":\"Horsewood\",\"Age\":19,\"NetWorth\":2499},{\"Id\":10,\"FirstName\":\"Rhody\",\"LastName\":\"Bettam\",\"Age\":19,\"NetWorth\":8013},{\"Id\":11,\"FirstName\":\"Livia\",\"LastName\":\"Baddoe\",\"Age\":18,\"NetWorth\":6699},{\"Id\":12,\"FirstName\":\"Perl\",\"LastName\":\"Pruvost\",\"Age\":19,\"NetWorth\":9114}],\"SongPerformer\":[{\"SongId\":1,\"PerformerId\":1},{\"SongId\":2,\"PerformerId\":1},{\"SongId\":3,\"PerformerId\":2},{\"SongId\":4,\"PerformerId\":2},{\"SongId\":5,\"PerformerId\":2},{\"SongId\":5,\"PerformerId\":10},{\"SongId\":5,\"PerformerId\":11},{\"SongId\":6,\"PerformerId\":3},{\"SongId\":6,\"PerformerId\":10},{\"SongId\":6,\"PerformerId\":11},{\"SongId\":7,\"PerformerId\":3},{\"SongId\":7,\"PerformerId\":10},{\"SongId\":7,\"PerformerId\":11},{\"SongId\":8,\"PerformerId\":3},{\"SongId\":8,\"PerformerId\":10},{\"SongId\":9,\"PerformerId\":4},{\"SongId\":9,\"PerformerId\":10},{\"SongId\":10,\"PerformerId\":4},{\"SongId\":10,\"PerformerId\":10},{\"SongId\":11,\"PerformerId\":4},{\"SongId\":12,\"PerformerId\":5},{\"SongId\":13,\"PerformerId\":5},{\"SongId\":14,\"PerformerId\":6},{\"SongId\":15,\"PerformerId\":6},{\"SongId\":16,\"PerformerId\":7},{\"SongId\":17,\"PerformerId\":7},{\"SongId\":18,\"PerformerId\":8},{\"SongId\":19,\"PerformerId\":8},{\"SongId\":20,\"PerformerId\":8},{\"SongId\":21,\"PerformerId\":8},{\"SongId\":22,\"PerformerId\":8},{\"SongId\":23,\"PerformerId\":8},{\"SongId\":23,\"PerformerId\":12},{\"SongId\":24,\"PerformerId\":9},{\"SongId\":24,\"PerformerId\":12},{\"SongId\":25,\"PerformerId\":9},{\"SongId\":25,\"PerformerId\":12},{\"SongId\":26,\"PerformerId\":9},{\"SongId\":27,\"PerformerId\":9},{\"SongId\":28,\"PerformerId\":9},{\"SongId\":29,\"PerformerId\":9}]}"; var datasets = JsonConvert.DeserializeObject <Dictionary <string, IEnumerable <JObject> > >(datasetsJson); foreach (var dataset in datasets) { var entityType = GetType(dataset.Key); using (var transaction = context.Database.BeginTransaction()) { var entities = dataset.Value .Select(j => j.ToObject(entityType)) .ToArray(); var entityName = $"{entityType.Name}s"; context.AddRange(entities); if (entityType != typeof(SongPerformer)) { context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + entityName + " ON;"); } context.SaveChanges(); if (entityType != typeof(SongPerformer)) { context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + entityName + " OFF;"); } transaction.Commit(); } } }
public static string ExportAlbumsInfo(MusicHubDbContext context, int producerId) { var albums = context.Albums.Where(x => x.ProducerId == producerId) .Select(s => new { AlbumName = s.Name, ReleaseDate = s.ReleaseDate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture), ProducerName = s.Producer.Name, Songs = s.Songs.Select(x => new { SongName = x.Name, Price = x.Price.ToString("F2"), Writer = x.Writer.Name }) .OrderByDescending(n => n.SongName) .ThenBy(f => f.Writer) .ToArray(), AlbumPrice = s.Price.ToString("F2") }) .OrderByDescending(p => decimal.Parse(p.AlbumPrice)) .ToArray(); var serAlbums = JsonConvert.SerializeObject(albums, new JsonSerializerSettings { Formatting = Newtonsoft.Json.Formatting.Indented }); return(serAlbums); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { StringBuilder sb = new StringBuilder(); var writers = JsonConvert.DeserializeObject <IEnumerable <WriterJsonInputModel> >(jsonString); foreach (var currentWriter in writers) { if (!IsValid(currentWriter)) { sb.AppendLine(ErrorMessage); continue; } var writerToAdd = new Writer { Name = currentWriter.Name, Pseudonym = currentWriter.Pseudonym }; context.Writers.Add(writerToAdd); sb.AppendLine(string.Format(SuccessfullyImportedWriter, writerToAdd.Name)); context.SaveChanges(); } return(sb.ToString().Trim()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var allSongs = context.Songs .ToList() .Where(x => x.Duration.TotalSeconds > duration) .Select(x => new { SongName = x.Name, Writer = x.Writer.Name, PerformerFullName = x.SongPerformers.Select(x => x.Performer.FirstName + " " + x.Performer.LastName).FirstOrDefault(), AlbumProducer = x.Album.Producer.Name, Duration = x.Duration }) .OrderBy(x => x.SongName) .ThenBy(x => x.Writer) .ThenBy(x => x.PerformerFullName) .ToList(); var sb = new StringBuilder(); int counter = 1; foreach (var song in allSongs) { sb.AppendLine($"-Song #{counter++}"); sb.AppendLine($"---SongName: {song.SongName}"); sb.AppendLine($"---Writer: {song.Writer}"); sb.AppendLine($"---Performer: {song.PerformerFullName}"); sb.AppendLine($"---AlbumProducer: {song.AlbumProducer}"); sb.AppendLine($"---Duration: {song.Duration:c}"); } return(sb.ToString().TrimEnd()); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var writerDtos = JsonConvert.DeserializeObject <ImportWriterDto[]>(jsonString); var sb = new StringBuilder(); var writers = new List <Writer>(); foreach (var writerDto in writerDtos) { if (IsValid(writerDto)) { var writer = new Writer { Name = writerDto.Name, Pseudonym = writerDto.Pseudonym }; writers.Add(writer); sb.AppendLine(string.Format(SuccessfullyImportedWriter, writer.Name)); } else { sb.AppendLine(ErrorMessage); } } context.Writers.AddRange(writers); context.SaveChanges(); return(sb.ToString().Trim()); }
public static string ImportWriters(MusicHubDbContext context, string jsonString) { var writerData = JsonConvert.DeserializeObject <WriterImportDto[]>(jsonString); StringBuilder sb = new StringBuilder(); List <Writer> writers = new List <Writer>(); foreach (var dto in writerData) { if (IsValid(dto)) { var writer = Mapper.Map <Writer>(dto); sb.AppendLine(String.Format(SuccessfullyImportedWriter, writer.Name)); writers.Add(writer); } else { sb.AppendLine(ErrorMessage); } } context.Writers.AddRange(writers); context.SaveChanges(); var result = sb.ToString().TrimEnd(); return(result); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { StringBuilder result = new StringBuilder(); var songs = context.Songs .Select(s => new { SongName = s.Name, Writer = s.Writer.Name, Performer = s.SongPerformers.Select(p => p.Performer.FirstName + " " + p.Performer.LastName).FirstOrDefault(), AlbumProducer = s.Album.Producer.Name, s.Duration, }) .ToList() .Where(s => s.Duration.TotalSeconds > duration) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer); int count = 0; foreach (var song in songs) { result.AppendLine($"-Song #{++count}"); result.AppendLine($"---SongName: {song.SongName}"); result.AppendLine($"---Writer: {song.Writer}"); result.AppendLine($"---Performer: {song.Performer}"); result.AppendLine($"---AlbumProducer: {song.AlbumProducer}"); result.AppendLine($"---Duration: {song.Duration:c}"); } return(result.ToString().Trim()); }
public static string ExportSongsAboveDuration(MusicHubDbContext context, int duration) { var songs = context .Songs .Where(s => s.Duration.TotalSeconds > duration) .Select(s => new ExportSongDto { SongName = s.Name, Writer = s.Writer.Name, Performer = s.SongPerformers.Select(sp => $"{sp.Performer.FirstName} {sp.Performer.LastName}").FirstOrDefault(), AlbumProducer = s.Album.Producer.Name, Duration = s.Duration.ToString("c") }) .OrderBy(s => s.SongName) .ThenBy(s => s.Writer) .ThenBy(s => s.Performer) .ToArray(); var serializer = new XmlSerializer(typeof(ExportSongDto[]), new XmlRootAttribute("Songs")); var sb = new StringBuilder(); var namespaces = new XmlSerializerNamespaces(new[] { new XmlQualifiedName("", "") }); serializer.Serialize(new StringWriter(sb), songs, namespaces); var result = sb.ToString(); return(result); }