public static Book AddBookToDB(string bookName, Generes genere, Authors author, Publishers publisher, short publicationYear)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Book book = null;
SqlCommand insertTODB = new SqlCommand(@"WITH Source
AS (SELECT v.Bookname,v.GenreID,v.AuthorID,v.PublisherID,v.PublicationYear,Genres.Genre,Publishers.Publisher,Authors.Author
FROM (VALUES(@Bookname,@GenreID,@AuthorID,@PublisherID,@PublicationYear))
V(Bookname,GenreID,AuthorID,PublisherID,PublicationYear)
INNER JOIN Genres ON v.GenreID =Genres.GenreID
INNER JOIN Publishers ON Publishers.PublisherID = v.PublisherID
INNER JOIN Authors ON Authors.AuthorID = v.AuthorID)
MERGE INTO Books
USING Source
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (Bookname,
GenreID,
AuthorID,
PublisherID,
PublicationYear)
VALUES (Bookname,
GenreID,
AuthorID,
PublisherID,
PublicationYear)
OUTPUT Source.*,INSERTED.BookID;", connection);
insertTODB.Parameters.AddWithValue("@Bookname", bookName);
insertTODB.Parameters.AddWithValue("@GenreID", genere.ID);
insertTODB.Parameters.AddWithValue("@AuthorID", author.ID);
insertTODB.Parameters.AddWithValue("@PublisherID", publisher.ID);
insertTODB.Parameters.AddWithValue("@PublicationYear", publicationYear);
var reader = insertTODB.ExecuteReader();
while (reader.Read())
{
book = getBookFromReader(reader);
}
connection.Close();
return(book);
}
}
/// <summary>
/// adds the book to Db
/// </summary>
/// <param name="bookName"></param>
/// <param name="genere"></param>
/// <param name="author"></param>
/// <param name="publisher"></param>
/// <param name="publicationYear"></param>
/// <param name="numberOfCopies"></param>
public static Book AddBookToDBAndUpdateCopies(string bookName, Generes genere, Authors author, Publishers publisher, short publicationYear, int numberOfCopies)
{
Book book = AddBookToDB(bookName, genere, author, publisher, publicationYear);
AddCopiesToDb(numberOfCopies, book.getBookID());
return(book);
}