/// <summary>
/// when the submit button is click the new book is added to the db.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void submit_Click(object sender, EventArgs e)
{
if (((AddBookTxt.Text != "") && (publicationYearTxt.Text != "")) && ((book != null) || (NumberOfCopiesTxt.Text != "")))
{
Generes Genere = (Generes)GenereComboBox.SelectedValue;
Authors author = (Authors)authorComboBox.SelectedValue;
Publishers Publisher = (Publishers)publicationComboBox.SelectedValue;
string BookName = this.AddBookTxt.Text;
short publicationYear = short.TryParse(publicationYearTxt.Text, out publicationYear) ? publicationYear : (short)0;
if (!Utils.AllowOnlyInRange(0, DateTime.Now.Year, publicationYearTxt, "נא למלא את השדה עד השנה הנוכחית."))
{
return;
}
int NumberOfCopies = int.TryParse(NumberOfCopiesTxt.Text, out NumberOfCopies) ? NumberOfCopies : 1;
if (null == book)
{
book = DataManager.AddBookToDBAndUpdateCopies(BookName, Genere, author, Publisher, publicationYear, NumberOfCopies);
MessageBox.Show("הספר נוסף בהצלחה");
BackButton_Click();
}
else
{
book = DataManager.EditBookInDB(book.getBookID(), BookName, Genere, author, Publisher, publicationYear);
MessageBox.Show("הספר נערך בהצלחה");
BackButton_Click();
}
}
else
{
MessageBox.Show("נא למלא את כל השדות");
}
}
/// <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);
}
}
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);
}
}
