public List <Book> GetIssuedBooks() { DatabasePackage dbPackage = new DatabasePackage(_ConnectionString); List <Book> books = new List <Book>(); SqlDataReader reader = null; try { dbPackage.CommandText = "Select ID, Name, IssueDate, MemberID From Book Where MemberID is Not Null Order By Name"; reader = dbPackage.GetReader(); while (reader.Read()) { Book book = new Book(); book.ID = dbPackage.FieldToInt(reader["ID"]); book.Name = dbPackage.FieldToString(reader["Name"]); book.IssueDate = dbPackage.FieldToDateTime(reader["IssueDate"]); book.MemberID = dbPackage.FieldToInt(reader["MemberID"]); books.Add(book); } } catch (DataAccessException ex) { throw new DataLayerException("Collection could not populated.", ex); } catch (Exception ex) { throw new DataLayerException("Collection could not populated.", ex); } finally { if (reader != null) { reader.Close(); } dbPackage.CloseConnection(); } return(books); }
public void SaveBookTest() { DatabasePackage databasePackage = new DatabasePackage(_ConnectionString); SqlDataReader reader = null; try { LibraryRepository libraryRepository = new LibraryRepository(_ConnectionString); // Test Add Book Book newBook = new Book(); newBook.Name = "Unit Test Book"; libraryRepository.SaveBook(newBook); databasePackage.CommandText = "Select ID From Book Where Name = 'Unit Test Book'"; reader = databasePackage.GetReader(); reader.Read(); int bookID = databasePackage.FieldToInt(reader["ID"]); Assert.AreNotEqual(0, bookID); // Test Modify Book Book existingBook = libraryRepository.GetBookByID(bookID); existingBook.Name = "Unit Test Book (Modified)"; libraryRepository.SaveBook(existingBook); Book modifiedBook = libraryRepository.GetBookByID(bookID); Assert.AreEqual("Unit Test Book (Modified)", modifiedBook.Name); // Delete newly added book libraryRepository.DeleteBook(bookID); } catch (Exception) { throw; } finally { if (reader != null) { reader.Close(); } } }
public void GetBookByIDTest() { DatabasePackage databasePackage = new DatabasePackage(_ConnectionString); SqlDataReader reader = null; LibraryRepository libraryRepository = new LibraryRepository(_ConnectionString); try { // Add a new book Book newBook = new Book(); newBook.Name = "Unit Test Book"; libraryRepository.SaveBook(newBook); databasePackage.CommandText = "Select ID From Book Where Name = 'Unit Test Book'"; reader = databasePackage.GetReader(); reader.Read(); int bookID = databasePackage.FieldToInt(reader["ID"]); // Test the function Book book = libraryRepository.GetBookByID(bookID); Assert.AreEqual(book.ID, bookID); // Delete newly added book libraryRepository.DeleteBook(bookID); } catch (Exception) { throw; } finally { if (reader != null) { reader.Close(); } } }
public List <User> GetUsers() { DatabasePackage dbPackage = new DatabasePackage(_ConnectionString); List <User> users = new List <User>(); SqlDataReader reader = null; try { dbPackage.CommandText = "Select ID, Name, Password From User Order By Name"; reader = dbPackage.GetReader(); while (reader.Read()) { User user = new User(); user.ID = dbPackage.FieldToInt(reader["ID"]); user.Name = dbPackage.FieldToString(reader["Name"]); user.Password = dbPackage.FieldToString(reader["Password"]); users.Add(user); } } catch (DataAccessException ex) { throw new DataLayerException("Collection could not populated.", ex); } catch (Exception ex) { throw new DataLayerException("Collection could not populated.", ex); } finally { if (reader != null) { reader.Close(); } dbPackage.CloseConnection(); } return(users); }
public List <Member> GetNoBookIssuedMembers() { DatabasePackage dbPackage = new DatabasePackage(_ConnectionString); List <Member> members = new List <Member>(); SqlDataReader reader = null; try { dbPackage.CommandText = "Select ID, Name From Member Where ID Not In (Select MemberID From Book Where MemberID Is Null) Order By Name"; reader = dbPackage.GetReader(); while (reader.Read()) { Member member = new Member(); member.ID = dbPackage.FieldToInt(reader["ID"]); member.Name = dbPackage.FieldToString(reader["Name"]); members.Add(member); } } catch (DataAccessException ex) { throw new DataLayerException("Collection could not populated.", ex); } catch (Exception ex) { throw new DataLayerException("Collection could not populated.", ex); } finally { if (reader != null) { reader.Close(); } dbPackage.CloseConnection(); } return(members); }
public Book GetBookByID(int id) { DatabasePackage dbPackage = new DatabasePackage(_ConnectionString); SqlDataReader reader = null; try { dbPackage.CommandText = "Select ID, Name, IssueDate, MemberID From Book Where ID = " + id; reader = dbPackage.GetReader(); reader.Read(); Book book = new Book { ID = id, Name = dbPackage.FieldToString(reader["Name"]), IssueDate = dbPackage.FieldToDateTime(reader["IssueDate"]), MemberID = dbPackage.FieldToInt(reader["MemberID"]) }; return(book); } catch (DataAccessException ex) { throw new DataLayerException("Unable to retrieve data.", ex); } catch (Exception ex) { throw new DataLayerException("Unable to retrieve data.", ex); } finally { if (reader != null) { reader.Close(); } } }
public void ReturnBookTest() { DatabasePackage databasePackage = new DatabasePackage(_ConnectionString); SqlDataReader reader = null; LibraryRepository libraryRepository = new LibraryRepository(_ConnectionString); try { // Add a new Book Book newBook = new Book(); newBook.Name = "Unit Test Book"; libraryRepository.SaveBook(newBook); databasePackage.CommandText = "Select ID From Book Where Name = 'Unit Test Book'"; reader = databasePackage.GetReader(); reader.Read(); int bookID = databasePackage.FieldToInt(reader["ID"]); // Add a new Member Member newMember = new Member(); newMember.Name = "Unit Test Member"; libraryRepository.SaveMember(newMember); databasePackage.CommandText = "Select ID From Member Where Name = 'Unit Test Member'"; reader = databasePackage.GetReader(); reader.Read(); int memberID = databasePackage.FieldToInt(reader["ID"]); // Issue this Book to a Member libraryRepository.IssueBook(bookID, memberID, DateTime.Now); // Test the function libraryRepository.ReturnBook(bookID); Book returnBook = libraryRepository.GetBookByID(bookID); Assert.AreEqual(returnBook.MemberID, 0); // Delete newly added book libraryRepository.DeleteBook(bookID); // Delete newly added Member libraryRepository.DeleteMember(memberID); } catch (Exception) { throw; } finally { if (reader != null) { reader.Close(); } } }