public List<SCL.Book> RecentBooks(int n)
{
if (n > 0)
{
string query = "select top " + n.ToString() + " * from BooksTable where BookId<>24 order by BookId desc";
sqlConnection.Open();
SqlCommand cmd = new SqlCommand(query, sqlConnection);
SqlDataReader dataReader;
dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
List<SCL.Book> _list = new List<SCL.Book>();
while (dataReader.Read())
{
SCL.Book _b = new SCL.Book();
_b.Author = Convert.ToString(dataReader["BookAuthor"]);
_b.Category = Convert.ToString(dataReader["BookCategory"]);
_b.Date = Convert.ToDateTime(dataReader["BookAddDate"]);
_b.Description = Convert.ToString(dataReader["BookDescription"]);
_b.Id = Convert.ToInt32(dataReader["BookId"]);
_b.Mark = Convert.ToDecimal(dataReader["BookMark"]);
if (dataReader["BookImage"] != System.DBNull.Value)
{
_b.Photo = (byte[])dataReader["BookImage"];
}
else
{
_b.Photo = null;
_b.PhotoPath = "";
}
_b.Price = Convert.ToDecimal(dataReader["BookPrice"]);
_b.Title = Convert.ToString(dataReader["BookTitle"]);
_list.Add(_b);
}
sqlConnection.Close();
dataReader.Dispose();
return _list;
}
else
{
sqlConnection.Close();
dataReader.Dispose();
return null;
}
}
else
{
return ReadSome();
}
}
public List<SCL.Book> ReadSome()
{
List<SCL.Book> _books = new List<SCL.Book>();
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("select * from BooksTable where BookId<>24", sqlConnection);
SqlDataReader dataReader;
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
SCL.Book b = new SCL.Book();
b.Id = Convert.ToInt32(dataReader["BookId"]);
b.Title = dataReader["BookTitle"].ToString();
b.Author = dataReader["BookAuthor"].ToString();
b.Mark = Convert.ToDecimal(dataReader["BookMark"]);
if (dataReader["BookPrice"]!=System.DBNull.Value)
{
b.Price = Convert.ToDecimal(dataReader["BookPrice"]);
}
if (dataReader["BookImage"] != System.DBNull.Value)
{
b.Photo = (byte[])dataReader["BookImage"];
}
else
{
b.Photo = null;
}
if (dataReader["BookDescription"]!=System.DBNull.Value)
{
b.Description = Convert.ToString(dataReader["BookDescription"]);
}
else
{
b.Description = "There isn't any description";
}
b.Date = Convert.ToDateTime(dataReader["BookAddDate"]);
b.Category = dataReader["BookCategory"].ToString();
_books.Add(b);
}
return _books;
}
public List<SCL.Book> FindBookByString(string searchString)
{
List<SCL.Book> _returnList = new List<SCL.Book>();
string query = "select * from BooksTable where BookAuthor like @SearchString or BookCategory like @SearchString or BookTitle like @SearchString or BookDescription like @SearchString";
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.CommandText = query;
cmd.Parameters.Add("@SearchString", System.Data.SqlDbType.VarChar).Value = "%"+searchString+"%";
SqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
SCL.Book _book = new SCL.Book();
_book.Id = Convert.ToInt32(dataReader["BookId"]);
_book.Title = Convert.ToString(dataReader["BookTitle"]);
_book.Author = Convert.ToString(dataReader["BookAuthor"]);
_book.Mark = Convert.ToDecimal(dataReader["BookMark"]);
if (dataReader["BookPrice"] != System.DBNull.Value)
{
_book.Price = Convert.ToDecimal(dataReader["BookPrice"]);
}
if (dataReader["BookImage"] != System.DBNull.Value)
{
_book.Photo = (byte[])dataReader["BookImage"];
}
else
{
_book.Photo = null;
_book.PhotoPath = "";
}
if (dataReader["BookDescription"]!=System.DBNull.Value)
{
_book.Description = Convert.ToString(dataReader["BookDescription"]);
}
else
{
_book.Description = "There isn't any description";
}
_book.Date = Convert.ToDateTime(dataReader["BookAddDate"]);
_book.Category = Convert.ToString(dataReader["BookCategory"]);
_returnList.Add(_book);
}
return _returnList;
}
else
{
return null;
}
}
public List<SCL.Book> PopularThisMonth()
{
string query = "select top 3 * from BooksTable where BookId in (select BookId from BooksTable where MONTH(BookAddDate) = MONTH(GETDATE())) order by BookMark desc";
sqlConnection.Open();
SqlCommand cmd = new SqlCommand(query, sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
List<SCL.Book> _list = new List<SCL.Book>();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
SCL.Book _b = new SCL.Book();
_b.Author = dataReader["BookAuthor"].ToString();
_b.Category = dataReader["BookCategory"].ToString();
_b.Date = Convert.ToDateTime(dataReader["BookAddDate"]);
_b.Description = dataReader["BookDescription"].ToString();
_b.Id = Convert.ToInt32(dataReader["BookId"]);
_b.Mark = Convert.ToDecimal(dataReader["BookMark"]);
_b.Photo = (byte[])dataReader["BookImage"];
_b.Price = Convert.ToDecimal(dataReader["BookPrice"]);
_b.Title = dataReader["BookTitle"].ToString();
_list.Add(_b);
}
sqlConnection.Close();
return _list;
}
else
{
return null;
}
}
public SCL.Book FindBookById(int id)
{
string query = "select * from BooksTable where BookId = @id";
sqlConnection.Open();
SqlCommand cmd = new SqlCommand(query, sqlConnection);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
SqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
dataReader.Read();
SCL.Book b = new SCL.Book();
b.Id = Convert.ToInt32(dataReader["BookId"]);
b.Title = Convert.ToString(dataReader["BookTitle"]);
b.Author = Convert.ToString(dataReader["BookAuthor"]);
b.Mark = Convert.ToDecimal(dataReader["BookMark"]);
if (dataReader["BookPrice"]!=System.DBNull.Value)
{
b.Price = Convert.ToDecimal(dataReader["BookPrice"]);
}
if (dataReader["BookImage"] != System.DBNull.Value)
{
b.Photo = (byte[])dataReader["BookImage"];
}
else
{
b.Photo = null;
}
if (dataReader["BookDescription"] != System.DBNull.Value)
{
b.Description = Convert.ToString(dataReader["BookDescription"]);
}
else
{
b.Description = "There isn't any description";
}
b.Date = Convert.ToDateTime(dataReader["BookAddDate"]);
b.Category = Convert.ToString(dataReader["BookCategory"]);
sqlConnection.Close();
cmd.Dispose();
dataReader.Dispose();
return b;
}
else {
sqlConnection.Close();
cmd.Dispose();
dataReader.Dispose();
return null;
}
}
