/// <summary> /// 在指定栏目下搜索关键词相关的内容 /// </summary> /// <param name="categoryRgt"></param> /// <param name="keyword"></param> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="recordCount"></param> /// <param name="pageCount"></param> /// <param name="orderby"></param> /// <param name="siteId"></param> /// <param name="categoryLft"></param> /// <param name="func"></param> /// <returns></returns> public void SearchArchivesByCategory(int siteId, int categoryLft, int categoryRgt, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func) { const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' "; if (String.IsNullOrEmpty(orderby)) { orderby = String.Intern("ORDER BY CreateDate DESC"); } object[,] data = new object[, ] { { "@siteId", siteId }, { "@lft", categoryLft }, { "@rgt", categoryRgt } }; //为第一页时 const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_Archives.[ID] AS ID,* FROM $PREFIX_Archives INNER JOIN $PREFIX_categories ON $PREFIX_Archives.[CgID]=$PREFIX_categories.[ID] WHERE $[condition] AND $PREFIX_categories.siteid=@siteId AND ($PREFIX_categories.lft>=@lft AND $PREFIX_categories.rgt<=@rgt) AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_Archives.[ID]"; //记录数 recordCount = int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(SP.Archive_GetSearchRecordCountByCategoryID, data, keyword, condition )).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 int skipCount = pageSize * (currentPageIndex - 1); string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ? sql1: SP.Archive_GetPagedSearchArchivesByCategoryID; sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "siteid": return(siteId.ToString()); case "condition": return(condition); case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); case "keyword": return(keyword); case "orderby": return(orderby); } return(null); }); base.ExecuteReader(SqlQueryHelper.Format(sql, data), func); }
/// <summary> /// 搜索关键词相关的内容 /// </summary> /// <param name="keyword"></param> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="recordCount"></param> /// <param name="pageCount"></param> /// <param name="orderby"></param> /// <returns></returns> public void SearchArchives(int siteID, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func) { /* * string condition = ArchiveFlag.GetSQLString(new string[,]{ * {"st","0"}, * {"v","1"} * }); */ const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' "; //排序规则 if (String.IsNullOrEmpty(orderby)) { orderby = String.Intern("ORDER BY Createdate DESC"); } //数据库为OLEDB,且为第一页时 const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_Archives.[ID] AS ID,* FROM $PREFIX_Archives INNER JOIN $PREFIX_categories ON $PREFIX_Archives.[CID]=$PREFIX_categories.[ID] WHERE $[condition] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_Archives.[ID]"; //记录数 recordCount = int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(SP.Archive_GetSearchRecordCount, keyword, siteID.ToString(), condition) ).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 int skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB? sql1: SP.Archive_GetPagedSearchArchives; sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "condition": return(condition); case "siteid": return(siteID.ToString()); case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); case "keyword": return(keyword); case "orderby": return(orderby); } return(null); }); base.ExecuteReader(new SqlQuery(base.OptimizeSQL(sql)), func); }