public List<Book> AuthorBookSearch(string query)
{
List<Book> BookList = null;
SqlParameter[] Params = { new SqlParameter("@Search", query) };
using (DataTable table = DataProvider.ExecuteParamatizedSelectCommand("sp_AuthorBookSearch", CommandType.StoredProcedure, Params))
{
if (table.Rows.Count > 0)
{
BookList = new List<Book>();
foreach (DataRow row in table.Rows)
{
Book book = new Book();
book.BookID = (int)row["BookID"];
book.ProductID = (int)row["ProductID"];
book.BookTitle = row["BookTitle"].ToString();
book.ISBN = row["ISBN"].ToString();
book.SellingPrice = Convert.ToDouble(row["SellingPrice"]);
book.BookCategoryID = (int)row["BookCategoryID"];
book.CoverImage = row["CoverImage"].ToString();
book.PublisherID = (int)row["PublisherID"];
book.BookTitle = book.BookTitle.Substring(0, Math.Min(11, book.BookTitle.Length));
book.BookTitle += "...";
BookList.Add(book);
}
}
}
return BookList;
}
public Book AddExperimentBook(Book book)
{
BookHandler myHandler = new BookHandler();
Book b = new Book();
b = myHandler.experimentalBook(book);
return b;
}
public BookAuthor TrialInsertBook(Book book)
{
BookHandler myHandler = new BookHandler(); return myHandler.TrailInsertBook(book);
}
public bool RestoreBook(Book book)
{
BookHandler myHandler = new BookHandler();
return myHandler.RestoreBook(book);
}
public bool AddBook(Book book)
{
BookHandler myHandler = new BookHandler();
return myHandler.InsertBook(book);
}
public bool DeleteBook(Book book)
{
BookHandler myHandler = new BookHandler();
return myHandler.DeleteBook(book);
}
public bool InsertBookProduct(Book book)
{
SqlParameter[] Params = new SqlParameter[]
{
new SqlParameter("@CostPrice", book.CostPrice),
new SqlParameter("@SellingPrice", book.SellingPrice),
new SqlParameter("@DateTime", book.DateAdded)
};
return DataProvider.ExecuteNonQuery("sp_InsertProduct", CommandType.StoredProcedure,
Params);
}
public BookAuthor TrailInsertBook(Book book)
{
BookAuthor bookAuthor = new BookAuthor();
SqlParameter[] Params = new SqlParameter[]
{
new SqlParameter("@ProductID", book.ProductID),
new SqlParameter("@BookTitle", book.BookTitle),
new SqlParameter("@Synopsis", book.Synopsis),
new SqlParameter("@ISBN", book.ISBN),
new SqlParameter("@BookCategoryID", book.BookCategoryID),
new SqlParameter("@PublisherID", book.PublisherID),
new SqlParameter("@SupplierID", book.SupplierID),
new SqlParameter("@CoverImage", book.CoverImage)
};
using (DataTable table = DataProvider.ExecuteParamatizedSelectCommand("sp_Insert_Book", CommandType.StoredProcedure, Params))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
bookAuthor.BookID = Convert.ToInt32(row["BookID"]);
}
}
return bookAuthor;
}
public List<Book> GetNewBookList()
{
List<Book> BookList = null;
using (DataTable table = DataProvider.ExecuteSelectCommand("sp_ViewAllNewBooks",
CommandType.StoredProcedure))
{
if (table.Rows.Count > 0)
{
BookList = new List<Book>();
foreach (DataRow row in table.Rows)
{
Book book = new Book();
BookCategory bc = new BookCategory();
book.BookID = (int)row["BookID"];
book.ProductID = (int)row["ProductID"];
book.BookTitle = row["BookTitle"].ToString();
book.Synopsis = row["Synopsis"].ToString();
book.SupplierID = Convert.ToInt32(row["SupplierID"].ToString());
book.ISBN = row["ISBN"].ToString();
book.CostPrice = Convert.ToDouble(row["CostPrice"]);
book.SellingPrice = Convert.ToDouble(row["SellingPrice"]);
book.BookCategoryID = (int)row["BookCategoryID"];
book.CoverImage = row["CoverImage"].ToString();
book.BookTitle = book.BookTitle.Substring(0, Math.Min(11, book.BookTitle.Length));
book.BookTitle += "...";
BookList.Add(book);
}
}
}
return BookList;
}
public Book GetBookDetails(int ProductID)
{
Book book = null;
SqlParameter[] Params = { new SqlParameter("@ProductID", ProductID) };
using (DataTable table = DataProvider.ExecuteParamatizedSelectCommand("sp_ViewSpecificBookAdmin",
CommandType.StoredProcedure, Params))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
book = new Book();
book.BookID = Convert.ToInt32(row["BookID"]);
book.ProductID = Convert.ToInt32(row["ProductID"]);
book.BookTitle = row["BookTitle"].ToString();
book.Synopsis = row["Synopsis"].ToString();
book.CostPrice = Convert.ToDouble(row["CostPrice"]);
book.SellingPrice = Convert.ToDouble(row["SellingPrice"]);
book.SupplierID = Convert.ToInt32(row["SupplierID"]);
book.ISBN = row["ISBN"].ToString();
book.BookCategoryID = Convert.ToInt32(row["BookCategoryID"]);
book.PublisherID = Convert.ToInt32(row["PublisherID"]);
book.CoverImage = row["CoverImage"].ToString();
}
}
return book;
}
public Book experimentalBook(Book book)
{
Book bk;
SqlParameter[] Params = { new SqlParameter("@CostPrice", book.CostPrice),
new SqlParameter("@SellingPrice", book.SellingPrice),
new SqlParameter("@DateAdded", book.DateAdded),
new SqlParameter("@IsBook", book.IsBook = true)
};
using (DataTable table = DataProvider.ExecuteParamatizedSelectCommand("sp_ManhattanProject", CommandType.StoredProcedure, Params))
{
bk = new Book();
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
bk.ProductID = Convert.ToInt32(row["ProductID"]);
}
}
return bk;
}
public bool DeleteBook(Book book)
{
SqlParameter[] Params = new SqlParameter[]
{
new SqlParameter("@ProductID", book.ProductID),
new SqlParameter("@Status", book.Status = true)
};
return DataProvider.ExecuteNonQuery("sp_DeleteBook", CommandType.StoredProcedure, //procedure
Params);
}
public List<Book> CheckDuplicateBook(string isbn)
{
List<Book> bookList = null;
SqlParameter[] Params = { new SqlParameter("@ISBN", isbn) };
using (DataTable table = DataProvider.ExecuteParamatizedSelectCommand("sp_CheckDuplicateBook", CommandType.StoredProcedure, Params))
{
if (table.Rows.Count > 0)
{
bookList = new List<Book>();
foreach (DataRow row in table.Rows)
{
Book book = new Book();
book.ISBN = row["ISBN"].ToString();
bookList.Add(book);
}
}
}
return bookList;
}
public bool UpdateBookProduct(Book book)
{
SqlParameter[] Params = new SqlParameter[]
{
new SqlParameter("@ProductID", book.ProductID ),
new SqlParameter("@CostPrice", book.CostPrice),
new SqlParameter("@SellingPrice", book.SellingPrice),
new SqlParameter("@IsBook", book.IsBook = true),
//new SqlParameter("@DateAdded", book.DateAdded = DateTime.Now),
};
return DataProvider.ExecuteNonQuery("sp_UpdateProduct", CommandType.StoredProcedure,
Params);
}
public bool UpdateBook(Book book)
{
BookHandler myHandler = new BookHandler();
return myHandler.UpdateBook(book);
}
public bool InsertBook(Book book)
{
SqlParameter[] Params = new SqlParameter[]
{
new SqlParameter("@ProductID", book.ProductID),
new SqlParameter("@BookTitle", book.BookTitle),
new SqlParameter("@Synopsis", book.Synopsis),
new SqlParameter("@ISBN", book.ISBN),
new SqlParameter("@BookCategoryID", book.BookCategoryID),
new SqlParameter("@PublisherID", book.PublisherID),
new SqlParameter("@SupplierID", book.SupplierID),
new SqlParameter("@CoverImage", book.CoverImage)
};
return DataProvider.ExecuteNonQuery("sp_InsertBook", CommandType.StoredProcedure,
Params);
}
public bool UpdateBookProduct(Book book)
{
BookHandler myHandler = new BookHandler();
Book bk = new Book();
return myHandler.UpdateBookProduct(book);
}
public bool RestoreBook(Book book)
{
SqlParameter[] Params = {
new SqlParameter("@ProductID", book.ProductID),
new SqlParameter("@Status", book.Status = false)
};
return DataProvider.ExecuteNonQuery("sp_RestoreTech", CommandType.StoredProcedure, Params);
}
