Esempio n. 1
0
        /// <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;
        }
Esempio n. 2
0
        /// <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;
        }