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 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 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 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 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 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());
}