///<summary> ///construct where query based on the conditions ///</summary> ///<param name="criterion">conditions</param> ///<returns>where query</returns> private static string buildWhereString(SearchBookCriterion criterion) { string sql = " "; string temp = null; if (!string.IsNullOrEmpty(criterion.title)) { temp = string.Format(" and (BookTitle like '%{0}%') ", criterion.title); sql += temp; } if (!string.IsNullOrEmpty(criterion.author)) { temp = string.Format(" and (Author1 like '%{0}%' " + " or Author2 like '%{0}%' " + " or Author3 like '%{0}%' ) ", criterion.author); sql += temp; } if (criterion.date1.HasValue && criterion.date2.HasValue) { temp = string.Format(" and (PublishDate between '{0}' and '{1}') ", criterion.date1.Value, criterion.date2.Value); sql += temp; } if (criterion.price1.HasValue && criterion.price2.HasValue) { temp = string.Format(" and (Price between '{0}' and '{1}') ", criterion.price1, criterion.price2); sql += temp; } if (!string.IsNullOrEmpty(criterion.publisher)) { temp = string.Format(" and (PublisherName like '%{0}%') ", criterion.publisher); sql += temp; } return(sql); }
/// <summary> /// search for books based on certain condition, allow paging /// </summary> /// <param name="criterion">condition</param> /// <param name="pageIndex">PageIndex</param> /// <param name="pageSize">size of the page</param> /// <param name="count">number of books that matches the condition</param> /// <returns>the certain books' information</returns> public static DataTable search(SearchBookCriterion criterion, int pageIndex, int pageSize, out int count) { string where = " "; if (criterion != null) { where = buildWhereString(criterion); } SqlHelper db = new SqlHelper(); DbCommand command = db.GetSqlStringCommond("select count(*) from Book where 1=1 " + where); count = Convert.ToInt32(db.ExecuteScalar(command)); string sql = "SELECT BookID,BookTitle,Price,Discount,RealPrice,Author1,Author2,Author3," + " SmallImage,PublisherName,PublishDate," + " Row_Number() over (order by BookID desc) as rownum " + " FROM Book where (1=1) "; sql += where; string temp = null; sql = "WITH tempTable AS (" + sql + ") "; temp = string.Format(" select * from tempTable where rownum between {0} and {1} ", (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); sql += temp; command = db.GetSqlStringCommond(sql); return(db.ExecuteDataTable(command)); }