public static int UpdateBook(Book b, string isbn)
{
string sql = "UPDATE [dbo].[Book]" +
" Set [book_title] = @title" +
" ,[publisher] = @publisher" +
" ,[description] = @des" +
" ,[cover_img] = @img" +
" ,[added_time] = @date" +
" WHERE[isbn] = @isbn";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@title", SqlDbType.VarChar),
new SqlParameter("@publisher", SqlDbType.VarChar),
new SqlParameter("@des", SqlDbType.VarChar),
new SqlParameter("@img", SqlDbType.VarChar),
new SqlParameter("@date", SqlDbType.DateTime),
new SqlParameter("@isbn", SqlDbType.VarChar),
};
para[0].Value = b.BookTitle;
para[1].Value = b.Publisher;
para[2].Value = b.Description;
para[3].Value = b.CoverImage;
para[4].Value = b.Add_date;
para[5].Value = isbn;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static int InsertBook(Book book)
{
string sql =
"insert into Book(isbn,book_title,publisher,description,cover_img,added_time) values(@isbn,@title,@pub,@des,@img,@date)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@isbn", SqlDbType.VarChar),
new SqlParameter("@title", SqlDbType.NVarChar),
new SqlParameter("@pub", SqlDbType.VarChar),
new SqlParameter("@des", SqlDbType.NVarChar),
new SqlParameter("@img", SqlDbType.VarChar),
new SqlParameter("@date", SqlDbType.DateTime),
};
para[0].Value = book.Isbn;
para[1].Value = book.BookTitle;
para[2].Value = book.Publisher;
para[3].Value = book.Description;
para[4].Value = book.CoverImage;
para[5].Value = book.Add_date;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static List <Reservation> GetReservationByUsername(string username)
{
string sql = "select * from Reservation where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <Reservation> reservations = new List <Reservation>();
Reservation r = null;
while (reader.Read())
{
// get the results of each column
int id = (int)reader["id"];
string isbn = (string)reader["isbn"];
DateTime create = (DateTime)reader["created_date"];
DateTime due = (DateTime)reader["due_date"];
double amount = (double)reader["amount"];
r = new Reservation(id, username, isbn, create, due, 0, amount);
reservations.Add(r);
}
return(reservations);
}
public static bool updateOrInsertReadingLog(string username, string isbn, float percent)
{
string sql = "select top 1 * from ReadingLog where username=@username and isbn=@isbn";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
string nonQuery = "";
if (cmd.ExecuteReader().Read())
{
nonQuery =
"update ReadingLog set percentage = @percent where username=@username and isbn=@isbn";
}
else
{
nonQuery = "insert into ReadingLog(username, isbn, percentage) values (@username, @isbn, @percentage)";
}
var command = new SqlCommand(nonQuery, InitConnection.GetInstance().GetConnection());
command.Connection.Open();
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@isbn", isbn);
command.Parameters.AddWithValue("@percent", percent);
return(command.ExecuteNonQuery() == 1);
}
}
public static List<Review> GetReviewByUsername(string username)
{
string sql = "select * from Review where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<Review> reviews = new List<Review>();
Review r = null;
while (reader.Read())
{
// get the results of each column
int id = (int)reader["id"];
string title = (string)reader["title"];
string content = (string)reader["content"];
DateTime date = (DateTime)reader["date"];
string isbn = (string)reader["isbn"];
//int score = (int)reader["score"];
int score = 5;
r = new Review(id,title,content,date,isbn,username,score);
reviews.Add(r);
}
return reviews;
}
public static List<Review> GetReviewsByIsbn(string isbn)
{
List<Review> list = new List<Review>();
string sql = "select * from Review where isbn=@isbn";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
string title = read.GetString(1);
string content = read.GetString(2);
DateTime date = read.GetDateTime(3);
int score = read.GetInt16(6);
Review review = new Review();
review.Content = content;
review.Title = title;
review.Date = date;
review.Score = score;
list.Add(review);
}
}
return list;
}
public static bool LogInAsAdmin(string username, string pass)
{
string sql = "select * from Borrower where username=@username and password=@pass";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@pass", pass);
SqlDataReader reader = cmd.ExecuteReader();
return(reader.Read());
}
}
public static List <Book> GetBooksByCategoryf(string cName)
{
string sql =
"select * from Category c inner join Book_Category BC on c.category_name = BC.category_name inner join Book on Book.isbn = BC.isbn where c.category_name = @cName";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@cName", cName);
SqlDataReader read = cmd.ExecuteReader();
return(GetBookFromDataReader(read));
}
}
public static List <Book> GetBookIRent(string username)
{
string sql =
"select distinct Book.* from Book inner join Reservation R2 on Book.isbn = R2.isbn where R2.username = @username and due_date >= @date";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
SqlDataReader reader = cmd.ExecuteReader();
return(GetBookFromDataReader(reader));
}
}
public static int InsertAuthor(string authorname)
{
string sql = "insert into Author(author_name) values (@name)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@name", SqlDbType.NVarChar),
};
para[0].Value = authorname;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static bool IsLoginSuccessfully(string username, string password)
{
Console.WriteLine("username: {0}, {1}", username, password);
string sql = "select top 1 * from Borrower where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter user = new SqlParameter("@username", SqlDbType.VarChar);
SqlParameter pass = new SqlParameter("@password", SqlDbType.VarChar);
user.Value = username;
cmd.Parameters.Add(user);
pass.Value = password;
cmd.Parameters.Add(pass);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteReader().Read());
}
public static bool IsUserExisted(string username)
{
string sql = "select top 1 username from Borrower where username=@username";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter para = new SqlParameter("@username", SqlDbType.VarChar)
{
Value = username
};
cmd.Parameters.Add(para);
InitConnection.OpenConnection(GenericConnection);
SqlDataReader reader = cmd.ExecuteReader();
return(reader.Read());
}
public static int DeleteBookCategory(string isbn)
{
string sql = "Delete from [dbo].[Book_Category]" +
" WHERE[isbn] = @isbn";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@isbn", SqlDbType.VarChar),
};
para[0].Value = isbn;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static bool InsertReview(string title, string content, DateTime date, string isbn, string username,
int score)
{
string sql = "insert into Review values(@a, @b, @c, @d, @e, @f)";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@a", title);
cmd.Parameters.AddWithValue("@b", content);
cmd.Parameters.AddWithValue("@c", date);
cmd.Parameters.AddWithValue("@d", isbn);
cmd.Parameters.AddWithValue("@e", username);
cmd.Parameters.AddWithValue("@f", score);
return cmd.ExecuteNonQuery() == 1;
}
}
public static int InsertBookAuthor(string isbn, int id)
{
string sql = "insert into Book_Author(book_isbn,author_id) values(@isbn,@id)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@isbn", SqlDbType.VarChar),
new SqlParameter("@id", SqlDbType.Int)
};
para[0].Value = isbn;
para[1].Value = id;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static int InsertBookCategory(BookCategory bc)
{
string sql = "insert into Book_Category(isbn,category_name) values (@isbn,@category_name)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@isbn", SqlDbType.VarChar),
new SqlParameter("@category_name", SqlDbType.NVarChar),
};
para[0].Value = bc.Isbn;
para[1].Value = bc.Name;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static bool InsertReservation(string username, string isbn, DateTime reservedTime, DateTime expiredTime,
float amount)
{
Console.WriteLine("isbN fuckkk: " + isbn + " us: " + username);
string sql =
"insert into Reservation(username, isbn, created_date, due_date, amount) values (@username, @isbn, @reservedTime, @expiredTime, @amount)";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
cmd.Parameters.AddWithValue("@reservedTime", reservedTime);
cmd.Parameters.AddWithValue("@expiredTime", expiredTime);
cmd.Parameters.AddWithValue("@amount", amount);
return(cmd.ExecuteNonQuery() == 1);
}
}
public static string GetAuthorName(string isbn)
{
string sql =
"select top 1 author_name from Author a inner join Book_Author ba on a.author_id = ba.author_id inner join Book b on ba.book_isbn = b.isbn where b.isbn = @isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return((string)reader["author_name"]);
}
}
return("");
}
public static int UpdateDeposit(string username, double amount)
{
string sql = "update Borrower" +
" set deposit = @deposit " +
" where username = @username";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@deposit", SqlDbType.Float),
new SqlParameter("@username", SqlDbType.VarChar)
};
para[0].Value = amount;
para[1].Value = username;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static Borrower GetBorrowerByUsername(string username)
{
string sql = "select * from Borrower where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
Borrower b = null;
if (reader.Read())
{
// get the results of each column
string name = (string)reader["name"];
string email = (string)reader["email"];
double deposit = (double)reader["deposit"];
b = new Borrower(username, name, email, deposit);
}
return(b);
}
public static int InsertBorrower(Borrower borrower)
{
string sql = "insert into Borrower(username,password,name,email) values (@username,@password,@name,@email)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@username", SqlDbType.VarChar),
new SqlParameter("@password", SqlDbType.VarChar),
new SqlParameter("@name", SqlDbType.VarChar),
new SqlParameter("@email", SqlDbType.VarChar)
};
para[0].Value = borrower.Username;
para[1].Value = borrower.Password;
para[2].Value = borrower.Fullname;
para[3].Value = borrower.Email;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static Book GetBookByBookCopyId(int copyId)
{
string sql =
"select top 1 * from Book inner join BookCopy BC on Book.isbn = BC.isbn where BC.book_id = @copyId";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@copyId", copyId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (GetBookFromDataReader(reader).Count != 0)
{
return(GetBookFromDataReader(reader).ElementAt(0));
}
}
return(null);
}
}
public static int GetTotalBookLength(int bookCopyId)
{
int length = 0;
Console.WriteLine("copy id: " + bookCopyId);
string sql = "select datalength(content) as [count] from BookCopy where book_id = @id";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@id", bookCopyId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
length = (int)reader["count"];
}
reader.Dispose();
}
return(length);
}
public static int InsertPayment(Payment payment)
{
string sql = "insert into Payment(payment_amount,username,date,payment_type)" +
" values(@amount, @username, @date, @type)";
SqlCommand cmd = new SqlCommand(sql, GenericConnection);
SqlParameter[] para =
{
new SqlParameter("@amount", SqlDbType.Float),
new SqlParameter("@username", SqlDbType.VarChar),
new SqlParameter("@date", SqlDbType.DateTime),
new SqlParameter("@type", SqlDbType.VarChar)
};
para[0].Value = payment.Amount;
para[1].Value = payment.Username;
para[2].Value = payment.Date;
para[3].Value = payment.Type;
cmd.Parameters.AddRange(para);
InitConnection.OpenConnection(GenericConnection);
return(cmd.ExecuteNonQuery());
}
public static bool CanReadBookNow(string username, string isbn)
{
string sql = "select due_date from Reservation where username=@username and isbn=@isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DateTime dueDate = (DateTime)reader["due_date"];
if (dueDate.CompareTo(DateTime.Now) >= 0)
{
return(true);
}
}
}
return(false);
}
public static void IncreaseBookRentedCount(string isbn)
{
string sql = "select count(*) as c from BookRentedCount where isbn=@isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
int count = 0;
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.Read())
{
count = reader.GetInt32(0);
}
string dml = "";
if (count != 0)
{
dml =
"update BookRentedCount set book_rented_count = @count, date=@date where isbn=@isbn";
}
else
{
dml = "insert into BookRentedCount(isbn, book_rented_count, date) values (@isbn, @count, @date)";
}
using (SqlCommand command = new SqlCommand(dml, InitConnection.GetInstance().GetConnection()))
{
command.Connection.Open();
command.Parameters.AddWithValue("@isbn", isbn);
command.Parameters.AddWithValue("@count", count + 1);
command.Parameters.AddWithValue("@date", DateTime.Now);
int rows = command.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);
}
}
}
static BookDao()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static ReservationDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static BorrowerDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static ReviewDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
