public List <BookShowDTO> GetListBook() { List <BookShowDTO> bookShowDTOs = new List <BookShowDTO>(); string query = string.Format("SELECT dbo.Book.Id,dbo.Book.Title,BookCategory.Name,dbo.Book.Author , Publisher.Name AS Publisher, " + "dbo.Book.YearPublisher, dbo.Book.Price, dbo.BookItem.NumberCount, dbo.Book.Status " + "FROM dbo.Book, dbo.BookCategory, " + "dbo.Publisher, dbo.BookItem " + "WHERE dbo.Book.PublisherId = dbo.Publisher.Id AND dbo.Book.BookCategoryId = dbo.BookCategory.Id AND dbo.BookItem.BookId = dbo.Book.Id"); DataTable dataTable = DataProvider.Instance.ExcuteQuery(query); foreach (DataRow item in dataTable.Rows) { BookShowDTO bookShowDTO = new BookShowDTO(item); bookShowDTOs.Add(bookShowDTO); } return(bookShowDTOs); }
public List <BookShowDTO> GetBookShowDTOByName(string name) { List <BookShowDTO> bookShowDTOs = new List <BookShowDTO>(); string query = string.Format("" + "SELECT * FROM (SELECT dbo.Book.Id,dbo.Book.Title,BookCategory.Name,dbo.Book.Author , "+ "Publisher.Name AS Publisher, dbo.Book.YearPublisher, dbo.Book.Price, dbo.BookItem.NumberCount, " + "dbo.Book.Status " + "FROM dbo.Book, dbo.BookCategory,dbo.Publisher, dbo.BookItem " + "WHERE dbo.Book.PublisherId = dbo.Publisher.Id AND dbo.Book.BookCategoryId = dbo.BookCategory.Id " + "AND dbo.BookItem.BookId = dbo.Book.Id) AS A " + "WHERE dbo.fuConvertToUnsign1(title) LIKE N'%' + dbo.fuConvertToUnsign1(N'{0}') + '%'", name); DataTable dataTable = DataProvider.Instance.ExcuteQuery(query); foreach (DataRow item in dataTable.Rows) { BookShowDTO bookShowDTO = new BookShowDTO(item); bookShowDTOs.Add(bookShowDTO); } return(bookShowDTOs); }