/// <summary> /// Get a book given its ISBN. /// </summary> /// <param name="book">A variable of type Book. Will be set to a new instance if a record was found, /// and to null if no record was found.</param> /// <param name="isbn">The ISBN as a string.</param> /// <returns>Returns true if no error occured.</returns> public static bool GetBook(out Book book, string isbn) { book = null; SqlCommand command = new SqlCommand("SELECT * from BOOK WHERE ISBN = @ISBN"); command.Parameters.AddWithValue("@ISBN", isbn); List<Book> bookList; bool result = getBooks(out bookList, command); if (result && bookList.Count > 0) { book = bookList[0]; } return result; }
/// <summary> /// Update a Book or create a Book in the database if it doesn't exist. This means existing items will /// be overwritten. /// </summary> /// <param name="copy">The Copy to write to the database.</param> /// <returns>Returns true if one row was written and no error occured.</returns> /// <exception cref="System.ArgumentException">Thrown when authorIdList is empty or given author id:s /// doesn't exist.</exception> public static bool Upsert(Book book, List<int> authorIdList) { // Are there any authorId:s and if so, do they exist? If not, the authorIdList argument was invalid. if (authorIdList.Count < 1) throw new ArgumentException("Inga författare angavs.", "authorIdList"); foreach (int aid in authorIdList) { Author a; Author.GetAuthor(out a, aid); if (a == null) { throw new ArgumentException("En eller flera angivna författare finns inte i databasen.", "authorIdList"); } } // Update or create BOOK try { using (SqlConnection connection = HelperFunctions.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand("EXEC UpsertBook @ISBN, @Title, @SignId, @PublicationYear, @PublicationInfo, @Pages")) { command.Connection = connection; command.Parameters.AddWithValue("@ISBN", book.ISBN); command.Parameters.AddWithValue("@Title", book.Title); command.Parameters.AddWithValue("@SignId", book.SignId); command.Parameters.AddWithValue("@PublicationYear", HelperFunctions.ValueOrDBNull(book.PublicationYear)); command.Parameters.AddWithValue("@PublicationInfo", HelperFunctions.ValueOrDBNull(book.PublicationInfo)); command.Parameters.AddWithValue("@Pages", HelperFunctions.ValueOrDBNull(book.Pages)); if (command.ExecuteNonQuery() != 1) { return false; } } // Get current authorId's coupled with this isbn var bookAuthors = new List<BookAuthor>(); var deleteAuthorIds = new List<int>(); var addAuthorIds = new List<int>(); if (BookAuthor.GetBookAuthors(out bookAuthors, book.ISBN)) { List<int> dbIdList = bookAuthors.Select(ba => ba.Aid).ToList(); addAuthorIds = (from id in authorIdList where !dbIdList.Contains(id) select id).ToList(); deleteAuthorIds = (from id in dbIdList where !authorIdList.Contains(id) select id).ToList(); } // Add new authors foreach(int aid in addAuthorIds) { using (SqlCommand command = new SqlCommand("INSERT INTO BOOK_AUTHOR(ISBN, Aid) VALUES(@ISBN, @Aid)")) { command.Connection = connection; command.Parameters.AddWithValue("@ISBN", book.ISBN); command.Parameters.AddWithValue("@Aid", aid); command.ExecuteNonQuery(); } } // Delete "unused" author ids foreach (int aid in deleteAuthorIds) { using (SqlCommand command = new SqlCommand("DELETE FROM BOOK_AUTHOR WHERE ISBN = @ISBN AND Aid = @Aid")) { command.Connection = connection; command.Parameters.AddWithValue("@ISBN", book.ISBN); command.Parameters.AddWithValue("@Aid", aid); command.ExecuteNonQuery(); } } } } catch (Exception) { return false; } return true; }