public List<Book> GetUserBooks(User user)
{
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
List<Book> books = new List<Book>();
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("GetUserBooks", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@user_name", SqlDbType.VarChar).Value = user.user_name;
db.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Book book = new Book();
book.isbn = dataReader.GetString(0);
book.title = dataReader.GetString(1);
book.author = dataReader.GetString(2);
book.genre = dataReader.GetString(3);
books.Add(book);
}
}
}
return books;
}
public bool UserHasBook(User user, Book book)
{
init();
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("UserHasBook", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@isbn", SqlDbType.VarChar).Value = book.isbn;
cmd.Parameters.Add("@user_name", SqlDbType.VarChar).Value = user.user_name;
db.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
if (dataReader.GetInt32(0) > 0)
{
return true;
}
}
}
return false;
}
public void RemoveBookFromUser(User user, Book book)
{
init();
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("RemoveBookFromUser", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@isbn", SqlDbType.VarChar).Value = book.isbn;
cmd.Parameters.Add("@user_name", SqlDbType.VarChar).Value = user.user_name;
db.Open();
cmd.ExecuteNonQuery();
}
}
}
public void CreateBook(Book b)
{
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("InsertBook", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@isbn", SqlDbType.VarChar).Value = b.isbn;
cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = b.title;
cmd.Parameters.Add("@author", SqlDbType.VarChar).Value = b.author;
cmd.Parameters.Add("@genre", SqlDbType.VarChar).Value = b.genre;
db.Open();
cmd.ExecuteNonQuery();
}
}
}
public string CreateBook(User user, Book book)
{
init();
if (userController.Login(user).Equals("You have been successfully logged in!"))
{
if (BookExists(book))
{
return "This book already exists";
}
else
{
bookUtil.CreateBook(book);
return "The book has been successfully created";
}
}
else
{
return "The user is invalid";
}
}
public bool BookExists(Book b)
{
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("CheckBookExistence", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@isbn", SqlDbType.VarChar).Value = b.isbn;
db.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
if (dataReader.GetInt32(0) > 0)
{
return true;
}
}
}
return false;
}
public void AddBookToUser(User user, Book book)
{
init();
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
if (!bookDomain.BookExists(book))
{
bookDomain.CreateBook(user, book);
}
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("AddBookToUser", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@isbn", SqlDbType.VarChar).Value = book.isbn;
cmd.Parameters.Add("@user_name", SqlDbType.VarChar).Value = user.user_name;
db.Open();
cmd.ExecuteNonQuery();
}
}
}
public bool BookExists(Book book)
{
init();
return bookUtil.BookExists(book);
}
public void LendBook(User owner, User borrower, Book book, String lendDate, String returnDate)
{
string connStr = Ebla.Models.Configuration.CONNECTION_STRING;
using (SqlConnection db = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("LendBook", db))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@owner_name", SqlDbType.VarChar).Value = owner.user_name;
cmd.Parameters.Add("@borrower_name", SqlDbType.VarChar).Value = borrower.user_name;
cmd.Parameters.Add("@lend_date", SqlDbType.VarChar).Value = lendDate;
cmd.Parameters.Add("@return_date", SqlDbType.VarChar).Value = returnDate;
cmd.Parameters.Add("@book_isbn", SqlDbType.VarChar).Value = book.isbn;
db.Open();
cmd.ExecuteNonQuery();
}
}
}
