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; } }