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);
}
