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);
}
/// <summary>
/// edit the book in the db
/// </summary>
/// <param name="bookId"></param>
/// <param name="bookName"></param>
/// <param name="genere"></param>
/// <param name="author"></param>
/// <param name="publisher"></param>
/// <param name="publicationYear"></param>
/// <returns></returns>
public static Book EditBookInDB(int bookId, string bookName, Generes genere, Authors author, Publishers publisher, short publicationYear)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"UPDATE Books
SET BookName=@BookName,GenreID=@GenereID,AuthorID=@AuthorID,PublisherID=@PublisherID,PublicationYear=@PublicationYear
OUTPUT inserted.Bookname, Genres.Genre, Authors.Author, Publishers.Publisher ,inserted.PublicationYear, inserted.BookID, Genres.GenreID, Publishers.PublisherID, Authors.AuthorID
FROM Books b
INNER JOIN Genres ON b.GenreID = Genres.GenreID
INNER JOIN Publishers ON Publishers.PublisherID = b.PublisherID
INNER JOIN Authors ON Authors.AuthorID = b.AuthorID
WHERE b.BookID=@Bookid;";
SqlCommand sqlCommand = new SqlCommand(query, connection);
sqlCommand.Parameters.AddWithValue("@BookName", bookName);
sqlCommand.Parameters.AddWithValue("@GenereID", genere.ID);
sqlCommand.Parameters.AddWithValue("@AuthorID", author.ID);
sqlCommand.Parameters.AddWithValue("@PublisherID", publisher.ID);
sqlCommand.Parameters.AddWithValue("@PublicationYear", publicationYear);
sqlCommand.Parameters.AddWithValue("@Bookid", bookId);
Book Updated = null;
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
Updated = getBookFromReader(reader);
}
connection.Close();
return(Updated);
}
}