Пример #1
0
        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);
            }
        }
Пример #2
0
        /// <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);
        }