/// <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;
}