/// <summary>
/// insert the book object, after success return the book with bookid.
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public static bool AddOneBook(Book book)
{
string sql = "insert into book (id, categoryid, bookname, location, picture, uri, readtimes)"
+ " values (?id, ?categoryid, ?bookname, ?location, ?picture, ?uri, ?readtimes)";
MySqlParameter[] parameters =
{
new MySqlParameter("?id",book.Id),
new MySqlParameter("?categoryid",book.CategoryId),
new MySqlParameter("?bookname",book.BookName),
new MySqlParameter("?location",book.Location),
new MySqlParameter("?picture",book.Picutre),
new MySqlParameter("?uri",book.URI),
new MySqlParameter("?readtimes",book.ReadTimes)
};
int result;
try
{
result = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
return false;
}
return result > 0;
}
private static void AddaBook(string fileName, string dPath, Category category)
{
Book book = new Book();
book.Id = Guid.NewGuid().ToString();
book.BookName = fileName;
book.CategoryId = category.Id;
book.Location = dPath + "\\" + fileName;
book.URI = string.Format(URI, fileName);
DBInteraction.AddOneBook(book);
book.BookDetail.BookId = book.Id;
}
public void UpdateaBook(Book book)
{
DBInteraction.UpdateBook(book);
}
public static bool UpdateBook(Book book)
{
string sql = "update book set categoryid=?categoryid, bookname=?bookname, location=?location, picture=?picture, uri=?uri, readtimes=?readtimes " +
" where id=?id;";
MySqlParameter[] parameters =
{
new MySqlParameter("?id",book.Id),
new MySqlParameter("?categoryid",book.CategoryId),
new MySqlParameter("?bookname",book.BookName),
new MySqlParameter("?location",book.Location),
new MySqlParameter("?picture",book.Picutre),
new MySqlParameter("?uri",book.URI),
new MySqlParameter("?readtimes",book.ReadTimes)
};
int result;
try
{
result = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MessageBox.Show("UpdateBook error: " + ex.Message);
return false;
}
return result > 0;
}
public static Book[] GetPageBooks(int pageSize, int pageNum)
{
int startPoint = (pageNum - 1) * pageSize;
List<Book> books = new List<Book>();
string sql = "SELECT id, categoryid, bookname, location, picture, uri, readtimes FROM `book` order by readtimes desc limit ?startpoint, ?pagesize;";
MySqlParameter[] parameters =
{
new MySqlParameter("?startpoint", startPoint),
new MySqlParameter("?pagesize", pageSize)
};
try
{
using (MySqlDataReader msdr = MySqlHelper.ExecuteReader(CommandType.Text, sql, parameters))
{
while (msdr.Read())
{
Book book = new Book();
book.Id = msdr["id"].ToString();
book.CategoryId = msdr["categoryid"].ToString();
book.BookName = msdr["bookname"].ToString();
book.Location = msdr["location"].ToString();
book.Picutre = msdr["picture"].ToString();
book.URI = msdr["uri"].ToString();
book.ReadTimes = (int)msdr["readtimes"];
books.Add(book);
}
}
}
catch (Exception ex)
{
MessageBox.Show("GetPageBooks error: " + ex.Message);
}
return books.ToArray();
}
public static bool GetBooksbyCategoryId(Category category)
{
string sql =
"select id, categoryid, bookname, location, picture, uri, readtimes from book where categoryid = ?categoryid order by bookname desc;";
MySqlParameter[] parameters = { new MySqlParameter("?categoryid", category.Id) };
try
{
using (MySqlDataReader msdr = MySqlHelper.ExecuteReader(CommandType.Text, sql, parameters))
{
List<Book> categoryBooks = new List<Book>();
while (msdr.Read())
{
Book book = new Book();
book.Id = msdr["id"].ToString();
book.CategoryId = category.Id;
book.BookName = msdr["bookname"].ToString();
book.Location = msdr["location"].ToString();
book.Picutre = msdr["picture"].ToString();
book.URI = msdr["uri"].ToString();
book.ReadTimes = (int)msdr["readtimes"];
categoryBooks.Add(book);
}
category.Books = categoryBooks.ToArray();
}
return true;
}
catch (Exception ex)
{
return false;
}
}
public static List<Book> GetAllBooks()
{
List<Book> books = new List<Book>();
string sql = "select id, categoryid, bookname, location, picture, uri, readtimes from book;";
try
{
using (MySqlDataReader msdr = MySqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (msdr.Read())
{
Book book = new Book();
book.Id = msdr["id"].ToString();
book.CategoryId = msdr["categoryid"].ToString();
book.BookName = msdr["bookname"].ToString();
book.Location = msdr["location"].ToString();
book.Picutre = msdr["picture"].ToString();
book.URI = msdr["uri"].ToString();
book.ReadTimes = (int)msdr["readtimes"];
books.Add(book);
}
}
}
catch (Exception ex)
{
MessageBox.Show("GetAllBooks error: " + ex.Message);
}
return books;
}