public List<Book> GetAllBooks()
{
int count = 0;
List<Book> booklist = new List<Book>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM BookTBL";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Book aBook =new Book();
aBook.Id = int.Parse(reader["Id"].ToString());
aBook.Title = reader["Title"].ToString();
aBook.Author = reader["Author"].ToString();
aBook.Publisher = reader["Publisher"].ToString();
count++;
aBook.SerialNo = count;
booklist.Add(aBook);
}
reader.Close();
connection.Close();
return booklist;
}
public List<Book> BorrowedBookList()
{
int count = 0;
List<Book> booklist = new List<Book>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM BorrowBookTBL";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Book myBook = new Book();
int bookId = int.Parse(reader["BookId"].ToString());
int memberId = int.Parse(reader["MemberId"].ToString());
Book book = bookGateway.GetBookInformation(bookId);
Member member = memberGateway.GetMemberInformation(memberId);
myBook.AMember = new Member();
myBook.Title = book.Title;
myBook.Author = book.Author;
myBook.Publisher = book.Publisher;
myBook.AMember.Name = member.Name;
count++;
myBook.SerialNo = count;
booklist.Add(myBook);
}
reader.Close();
connection.Close();
return booklist;
}
public string SaveBook(Book aBook)
{
int value = bookGateway.SaveBook(aBook);
if (value > 0)
{
return "This book has been saved!";
}
else
{
return "Faild to save!";
}
}
protected void saveButton_Click(object sender, EventArgs e)
{
Book aBook=new Book();
aBook.Title = titleBookEntryTextBox.Text;
aBook.Author = authorBookEntryTextBox.Text;
aBook.Publisher = publisherBookEntryTextBox.Text;
if (bookManager.IsBookNameExist(aBook))
{
messageLabel.Text = "Sorry,Book already exists!";
}
else
{
messageLabel.Text = bookManager.SaveBook(aBook);
bookEntryGridView.DataSource = bookManager.GetAllBooks();
bookEntryGridView.DataBind();
}
}
public Book GetBookInformation(int id)
{
Book aBook = new Book();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM BookTBL WHERE Id='"+id+"'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
aBook.Id = int.Parse(reader["Id"].ToString());
aBook.Title = reader["Title"].ToString();
aBook.Author = reader["Author"].ToString();
aBook.Publisher = reader["Publisher"].ToString();
}
reader.Close();
connection.Close();
return aBook;
}
public bool IsBookNameExist(Book aBook)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM BookTBL WHERE Title='" + aBook.Title + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
reader.Close();
connection.Close();
return true;
}
else
{
reader.Close();
connection.Close();
return false;
}
}
public int SaveBook(Book aBook)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO BookTBL VALUES('" + aBook.Title + "','" + aBook.Author+ "','" + aBook.Publisher + "')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public bool IsBookNameExist(Book aBook)
{
return bookGateway.IsBookNameExist(aBook);
}
