public static Book AddNewBook(Book book)
{
if (BookService.GetBookByISBN(book.ISBN) == null)
{
return BookService.AddBook(book);
}
else
{
return null;
}
}
/// <summary>
/// רΪͼ����ҳ�ṩ�����ͼ��
/// </summary>
/// <param name="title"></param>
/// <param name="isbn"></param>
/// <param name="Publisherid"></param>
/// <param name="WordsCount"></param>
/// <param name="AurhorDescription"></param>
/// <param name="UnitPrice"></param>
/// <param name="author"></param>
/// <param name="TOC"></param>
/// <param name="PublishDate"></param>
/// <param name="ContentDescription"></param>
/// <param name="editorComment"></param>
public static void AddBook(string title,string isbn, int Publisherid, int WordsCount, string AurhorDescription, decimal UnitPrice, string author, string TOC, DateTime PublishDate, string ContentDescription, string editorComment)
{
Book book = new Book();
book.Category = CategoryService.GetCategoryById(1);
book.Title = title;
book.ISBN = isbn;
book.UnitPrice = UnitPrice;
book.Author = author;
book.TOC = TOC;
book.Publisher = PublisherService.GetPublisherById(Publisherid);
book.WordsCount = WordsCount;
book.AurhorDescription = AurhorDescription;
book.PublishDate = PublishDate;
book.ContentDescription = ContentDescription;
book.EditorComment = editorComment;
book.Clicks = 0;
book.Votes = 0;
book.TotalRating = 0;
AddNewBook(book);
}
public static Book AddBook(Book book)
{
string sql =
"INSERT Books (Title, Author, PublisherId, PublishDate, ISBN, WordsCount, UnitPrice, ContentDescription, AurhorDescription, EditorComment, TOC, CategoryId, Clicks, Votes, TotalRating)" +
"VALUES (@Title, @Author, @PublisherId, @PublishDate, @ISBN, @WordsCount, @UnitPrice, @ContentDescription, @AurhorDescription, @EditorComment, @TOC, @CategoryId, @Clicks, @Votes, @TotalRating)";
sql += " ; SELECT @@IDENTITY";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@PublisherId", book.Publisher.Id), //FK
new SqlParameter("@CategoryId", book.Category.Id), //FK
new SqlParameter("@Title", book.Title),
new SqlParameter("@Author", book.Author),
new SqlParameter("@PublishDate", book.PublishDate),
new SqlParameter("@ISBN", book.ISBN),
new SqlParameter("@WordsCount", book.WordsCount),
new SqlParameter("@UnitPrice", book.UnitPrice),
new SqlParameter("@ContentDescription", book.ContentDescription),
new SqlParameter("@AurhorDescription", book.AurhorDescription),
new SqlParameter("@EditorComment", book.EditorComment),
new SqlParameter("@TOC", book.TOC),
new SqlParameter("@Clicks", book.Clicks),
new SqlParameter("@Votes", book.Votes),
new SqlParameter("@TotalRating", book.TotalRating)
};
int newId = DBHelper.GetScalar(sql, para);
return GetBookById(newId);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static void DeleteBook(Book book)
{
DeleteBookById(book.Id);
}
/// <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;
}
}
public static void ModifyBook(Book book)
{
string sql =
"UPDATE Books " +
"SET " +
"PublisherId = @PublisherId, " + //FK
"CategoryId = @CategoryId, " + //FK
"Title = @Title, " +
"Author = @Author, " +
"PublishDate = @PublishDate, " +
"ISBN = @ISBN, " +
"WordsCount = @WordsCount, " +
"UnitPrice = @UnitPrice, " +
"ContentDescription = @ContentDescription, " +
"AurhorDescription = @AurhorDescription, " +
"EditorComment = @EditorComment, " +
"TOC = @TOC, " +
"Clicks = @Clicks, " +
"Votes = @Votes, " +
"TotalRating = @TotalRating " +
"WHERE Id = @Id";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", book.Id),
new SqlParameter("@PublisherId", book.Publisher.Id), //FK
new SqlParameter("@CategoryId", book.Category.Id), //FK
new SqlParameter("@Title", book.Title),
new SqlParameter("@Author", book.Author),
new SqlParameter("@PublishDate", book.PublishDate),
new SqlParameter("@ISBN", book.ISBN),
new SqlParameter("@WordsCount", book.WordsCount),
new SqlParameter("@UnitPrice", book.UnitPrice),
new SqlParameter("@ContentDescription", book.ContentDescription),
new SqlParameter("@AurhorDescription", book.AurhorDescription),
new SqlParameter("@EditorComment", book.EditorComment),
new SqlParameter("@TOC", book.TOC),
new SqlParameter("@Clicks", book.Clicks),
new SqlParameter("@Votes", book.Votes),
new SqlParameter("@TotalRating", book.TotalRating)
};
DBHelper.ExecuteCommand(sql, para);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
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;
}
}
public static void ModifyBook(Book book)
{
BookService.ModifyBook(book);
}
public static void DeleteBook(Book book)
{
BookService.DeleteBook(book);
}
