/// <summary>
/// Gets all DComic objects from the database.
/// </summary>
/// <param name="command">Command containing the get all query.</param>
/// <returns>All dcomics from the database.</returns>
private IEnumerable <DComic> ToDComicList(IDbCommand command)
{
List <DComic> items = new List <DComic>();
//gets all comic objects without authors
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new DComic();
Map(reader, item);
items.Add(item);
}
}
//gets authors for each comic object
for (int i = 0; i < items.Count; i++)
{
command.CommandText = "SELECT Authors.ID, Authors.Name " +
"from Authors " +
"INNER Join Comics_Authors ON Authors.ID = Authors_ID " +
$"Where Comics_ID = @id{i};";
command.AddParameter($"id{i}", items[i].Id);
items[i].Authors = ToDAuthorList(command).ToList();
}
return(items);
}
/// <summary>
/// Adds a DComic to the database
/// </summary>
/// <param name="dComic">DComic to add</param>
private void AddDComic(DComic dComic)
{
using (var command = context.CreateCommand())
{
command.CommandText = @"Select * From Comics Where Comics.Title = @title AND Comics.SeriesNr = @series_Nr OR @series_Nr IS NULL AND Comics.Publisher_ID = @publiser_Id AND Comics.Series_ID = @series_Id;";
command.AddParameter("title", dComic.Title);
command.AddParameter("series_Nr", dComic.SeriesNumber);
command.AddParameter("publiser_Id", dComic.Publisher.Id);
command.AddParameter("series_Id", dComic.Series.Id);
int?id = (int?)command.ExecuteScalar();
if (id != null)
{
command.CommandText = @"Update Comics SET IsInCatalogue = 1 WHERE Id = @id;";
command.AddParameter("id", id);
command.ExecuteNonQuery();
dComic.Id = (int)id;
}
else
{
command.CommandText = @"Insert into Comics (Title, SeriesNr, Publisher_ID, Series_ID) " +
"values (@title, @series_Nr, @publiser_Id, @series_Id) " +
"SELECT CAST(scope_identity() AS int);";
dComic.Id = (int)command.ExecuteScalar();
}
}
}
public void AddComic(Comic comic)
{
DComic toAdd = Mapper.ToDComic(comic);
AddDPublisher(toAdd.Publisher);
AddDSeries(toAdd.Series);
AddDComic(toAdd);
AddDAuthors(toAdd);
LinkAuthorComic(toAdd);
AddStock(toAdd);
}
/// <summary>
/// Deletes the link between comic and authors
/// </summary>
/// <param name="oldComic">comic to delete links from</param>
private void DeleteComicAuthorsLinks(DComic oldComic)
{
using (var command = context.CreateCommand())
{
command.AddParameter($"ComicId", oldComic.Id);
for (int i = 0; i < oldComic.Authors.Count; i++)
{
command.CommandText = @$ "DELETE From Comics_Authors Where Authors_ID = @AuthorId{i} And Comics_Id = @ComicId";
command.AddParameter($"AuthorId{i}", oldComic.Authors[i].Id);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Maps a record to a DComic object.
/// </summary>
/// <param name="record">Record to bind.</param>
/// <param name="dComic">Comic to bind to.</param>
private void Map(IDataRecord record, DComic dComic)
{
dComic.Id = (int)record["Comic_Id"];
dComic.Title = (string)record["Title"];
dComic.SeriesNumber = !Convert.IsDBNull(record["SeriesNr"]) ? (int?)record["SeriesNr"] : null;
dComic.Publisher = new DPublisher();
dComic.Publisher.Id = (int)record["Publisher_Id"];
dComic.Publisher.Name = (string)record["Publisher_Name"];
dComic.Series = new DSeries();
dComic.Series.Id = (int)record["Series_ID"];
dComic.Series.Name = (string)record["Series_Name"];
dComic.AmountAvailable = (int)record["Amount_Available"];
}
private void AddStock(DComic toAdd)
{
using (var command = context.CreateCommand())
{
command.CommandText = @"Select * From Stock Where Stock.ComicID = @comic_Id";
command.AddParameter("comic_Id", toAdd.Id);
int?id = (int?)command.ExecuteScalar();
if (id == null)
{
command.CommandText = @"Insert into Stock (ComicID, Stock) " +
"values (@comic_Id, @stock)" +
"SELECT CAST(scope_identity() AS int);";
command.AddParameter("stock", toAdd.AmountAvailable);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Adds a link between a comic and a author in the database.
/// </summary>
/// <param name="dComic">DComic to use.</param>
private void LinkAuthorComic(DComic dComic)
{
using (var command = context.CreateCommand())
{
command.AddParameter($"ComicId", dComic.Id);
for (int i = 0; i < dComic.Authors.Count; i++)
{
command.CommandText = @$ "Select * From Comics_Authors Where Authors_ID = @AuthorId{i} And Comics_Id = @ComicId";
command.AddParameter($"AuthorId{i}", dComic.Authors[i].Id);
int?id = (int?)command.ExecuteScalar();
if (id == null)
{
command.CommandText = @"Insert into Comics_Authors (Authors_ID, Comics_Id) " +
$"values (@AuthorId{i}, @ComicId);";
command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Adds a collection DAuthors to the database.
/// </summary>
/// <param name="dComic">DComic with authors to add.</param>
private void AddDAuthors(DComic dComic)
{
using (var command = context.CreateCommand())
{
for (int i = 0; i < dComic.Authors.Count; i++)
{
command.CommandText = @$ "Select * From Authors Where Authors.name = @name{i}";
command.AddParameter($"name{i}", dComic.Authors[i].Name);
int?id = (int?)command.ExecuteScalar();
if (id != null)
{
dComic.Authors[i].Id = (int)id;
}
else
{
command.CommandText = @"Insert into Authors (Name) " +
$"values (@name{i})" +
"SELECT CAST(scope_identity() AS int);";
dComic.Authors[i].Id = (int)command.ExecuteScalar();
}
}
}
}
