public static Book GetBookByISBN(string iSBN)
{
string sql = "SELECT * FROM Books WHERE ISBN = @ISBN";
int publisherId;
int categoryId;
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@ISBN", iSBN));
if (reader.Read())
{
Book book = new Book();
book.Id = (int)reader["Id"];
book.Title = (string)reader["Title"];
book.Author = (string)reader["Author"];
book.PublishDate = (DateTime)reader["PublishDate"];
book.ISBN = (string)reader["ISBN"];
book.WordsCount = (int)reader["WordsCount"];
book.UnitPrice = (decimal)reader["UnitPrice"];
book.ContentDescription = (string)reader["ContentDescription"];
book.AurhorDescription = (string)reader["AurhorDescription"];
book.EditorComment = (string)reader["EditorComment"];
book.TOC = (string)reader["TOC"];
book.Clicks = (int)reader["Clicks"];
book.Votes = (int)reader["Votes"];
book.TotalRating = (int)reader["TotalRating"];
publisherId = (int)reader["PublisherId"]; //FK
categoryId = (int)reader["CategoryId"]; //FK
reader.Close();
book.Publisher = PublisherService.GetPublisherById(publisherId);
book.Category = CategoryService.GetCategoryById(categoryId);
return(book);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 返回不完整字段的图书
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
private static IList <Book> GetPartialBooksBySql(string safeSql)
{
List <Book> list = new List <Book>();
DataTable table = DBHelper.GetDataSet(safeSql);
foreach (DataRow row in table.Rows)
{
Book book = new Book();
book.Id = (int)row["Id"];
book.Title = (string)row["Title"];
book.Author = (string)row["Author"];
if (table.Columns.Contains("UnitPrice"))
{
book.UnitPrice = (decimal)row["UnitPrice"];
}
if (table.Columns.Contains("ShortContent"))
{
book.ContentDescription = (string)row["ShortContent"];
}
if (table.Columns.Contains("ISBN"))
{
book.ISBN = (string)row["ISBN"];
}
if (table.Columns.Contains("Clicks"))
{
book.Clicks = (int)row["Clicks"];
}
if (table.Columns.Contains("PublishDate"))
{
book.PublishDate = (DateTime)row["PublishDate"];
}
if (table.Columns.Contains("CategoryId"))
{
book.Category = CategoryService.GetCategoryById((int)row["CategoryId"]);
}
book.Publisher = PublisherService.GetPublisherById((int)row["PublisherId"]); //FK
list.Add(book);
}
return(list);
}
private static IList <Book> GetBooksBySql(string sql, params SqlParameter[] values)
{
List <Book> list = new List <Book>();
try
{
DataTable table = DBHelper.GetDataSet(sql, values);
foreach (DataRow row in table.Rows)
{
Book book = new Book();
book.Id = (int)row["Id"];
book.Title = (string)row["Title"];
book.Author = (string)row["Author"];
book.PublishDate = (DateTime)row["PublishDate"];
book.ISBN = (string)row["ISBN"];
book.WordsCount = (int)row["WordsCount"];
book.UnitPrice = (decimal)row["UnitPrice"];
book.ContentDescription = (string)row["ContentDescription"];
book.AurhorDescription = (string)row["AurhorDescription"];
book.EditorComment = (string)row["EditorComment"];
book.TOC = (string)row["TOC"];
book.Clicks = (int)row["Clicks"];
book.Votes = (int)row["Votes"];
book.TotalRating = (int)row["TotalRating"];
book.Publisher = PublisherService.GetPublisherById((int)row["PublisherId"]); //FK
book.Category = CategoryService.GetCategoryById((int)row["CategoryId"]); //FK
list.Add(book);
}
return(list);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}