public void SaveLinksOfContent(string typeIndent, int relatedId, IList <ILink> list) { if (list.Count == 0) { return; } SqlQuery[] querys = new SqlQuery[list.Count]; int i = 0; foreach (ILink link in list) { querys[i++] = SqlQueryHelper.Format( (link.LinkId <= 0 ? DbSql.Link_InsertRelatedLink:DbSql.Link_UpdateRelatedLink), new object[, ] { { "@typeIndent", typeIndent }, { "@relatedId", relatedId }, { "@id", link.LinkId }, { "@name", link.LinkName }, { "@uri", link.LinkUri }, { "@title", link.LinkTitle }, { "@enabled", link.Enabled } }); } base.ExecuteNonQuery(querys); }
/************ 分页 ****************/ /// <summary> /// 获取栏目分页文档 /// </summary> /// <param name="siteId"></param> /// <param name="lft"></param> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="rgt"></param> /// <param name="recordCount"></param> /// <param name="pages"></param> /// <returns></returns> public DataTable GetPagedArchives( int siteId, int lft, int rgt, int pageSize, ref int currentPageIndex, out int recordCount, out int pages) { object[,] data = new object[, ] { { "@siteId", siteId }, { "@lft", lft }, { "@rgt", rgt } }; const string condition = " "; //数据库为ACCESS,页码为1时调用SQL const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.ID AS ID,* FROM $PREFIX_archive INNER JOIN $PREFIX_category ON $PREFIX_archive.[CID]=$PREFIX_category.[ID] WHERE $PREFIX_category.siteId=@siteId AND (lft>=@lft AND rgt<=@rgt) AND flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ORDER BY sort_number DESC,$PREFIX_archive.id"; //获取记录条数 recordCount = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format(DbSql.Archive_GetPagedArchivesCountSql_pagerqurey, data)).ToString()); pages = recordCount / pageSize; if (recordCount % pageSize != 0) { pages++; } //验证当前页数 if (currentPageIndex > pages && currentPageIndex != 1) { currentPageIndex = pages; } if (currentPageIndex < 1) { currentPageIndex = 1; } //计算分页 int skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ? sql1 : DbSql.Archive_GetPagedArchivesByCategoryID_pagerquery; sql = SQLRegex.Replace(sql, m => { switch (m.Groups[1].Value) { case "skipsize": return(skipCount.ToString()); case "pagesize": return(pageSize.ToString()); } return(null); }); return(base.GetDataSet(SqlQueryHelper.Format(sql, data)).Tables[0]); }
public void InsertDataExtendFields(ExtendRelationType relationType, int relationId, IDictionary <int, string> extendData) { if (extendData.Count == 0) { return; } string sql = base.OptimizeSql(DbSql.DataExtend_InsertOrUpdateFieldValue); IList <SqlQuery> querys = new List <SqlQuery>(); querys.Add(SqlQueryHelper.Format(DbSql.DataExtend_ClearupExtendFielValue, new object[, ] { { "@relationType", relationType }, { "@relationId", relationId } } )); foreach (int key in extendData.Keys) { if (!String.IsNullOrEmpty(extendData[key])) { querys.Add(new SqlQuery(sql, new object[, ] { { "@relationType", relationType }, { "@relationId", relationId }, { "@fieldId", key }, { "@fieldValue", extendData[key] } })); } } base.ExecuteNonQuery(querys.ToArray()); }
public int UpdateSite(ISite site) { return(base.ExecuteNonQuery( SqlQueryHelper.Format(DbSql.Site_EditSite, new object[, ] { { "@siteId", site.Id }, { "@name", site.Name }, { "@dirname", site.DirName }, { "@tpl", site.Tpl }, { "@domain", site.Domain }, { "@location", site.Location }, { "@language", site.Language }, { "@note", site.Note }, { "@seotitle", site.SeoTitle }, { "@seokeywords", site.SeoKeywords }, { "@seodescription", site.SeoDescription }, { "@state", site.State }, { "@protel", site.Tel }, { "@prophone", site.Phone }, { "@profax", site.Fax }, { "@proaddress", site.Address }, { "@proemail", site.Email }, { "@im", site.Im }, { "@postcode", site.PostCode }, { "@pronotice", site.Notice }, { "@proslogan", site.Slogan } }))); }
public void GetExtendValuesList(int siteId, int relationType, IList <int> idList, DataReaderFunc func) { if (idList == null || idList.Count == 0) { return; } string ids = ""; int tmpInt = 0; foreach (int i in idList) { if (tmpInt++ != 0) { ids += ","; } ids += i.ToString(); } base.ExecuteReader(SqlQueryHelper.Format(DbSql.DataExtend_GetExtendValuesList, new object[, ] { { "@siteId", siteId }, { "@relationType", relationType } }, ids ), func); }
/// <summary> /// 获取所有的友情链接 /// </summary> /// <returns></returns> public void GetAllSiteLinks(int siteId, SiteLinkType type, DataReaderFunc func) { base.ExecuteReader( SqlQueryHelper.Format(DbSql.Link_GetSiteLinksByLinkType, new object[, ] { { "@siteId", siteId }, { "@linkType", (int)type } }), func); }
public void GetSiteLinkById(int siteId, int linkId, DataReaderFunc func) { base.ExecuteReader( SqlQueryHelper.Format(DbSql.Link_GetSiteLinkById, new object[, ] { { "@siteId", siteId }, { "@linkId", linkId } }), func); }
public void ReadLinksOfContent(string typeIndent, int relatedId, DataReaderFunc func) { base.ExecuteReader( SqlQueryHelper.Format(DbSql.Link_GetRelatedLinks, new object[, ] { { "@typeIndent", typeIndent }, { "@relatedId", relatedId } }), func); }
public void RemoveRelatedLinks(string typeIndent, int relatedId, string ids) { base.ExecuteNonQuery( SqlQueryHelper.Format(DbSql.Link_RemoveRelatedLinks, new object[, ] { { "@typeIndent", typeIndent }, { "@relatedId", relatedId } }, ids)); }
public int GetMaxSortNumber(int siteId) { return(int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(DbSql.Archive_GetMaxSortNumber, new object[, ] { { "@siteId", siteId }, })).ToString())); }
public int GetCategoryExtendRefrenceNum(int siteId, int categoryId, int extendFieldId) { return(int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(DbSql.DataExtend_GetCategoryExtendRefrenceNum, new object[, ] { { "@siteId", siteId }, { "@categoryId", categoryId }, { "@fieldId", extendFieldId } }) ).ToString())); }
public void SaveSortNumber(int archiveId, int sortNumber) { base.ExecuteNonQuery( SqlQueryHelper.Format(DbSql.Archive_UpdateSortNumber, new object[, ] { { "@archiveId", archiveId }, { "@sort_number", sortNumber }, })); }
public void GetArchivesExtendValues(int siteId, int relationType, string categoryTag, int number, DataReaderFunc func) { base.ExecuteReader( SqlQueryHelper.Format(DbSql.Archive_GetArchivesExtendValues, new object[, ] { { "@siteId", siteId }, { "@tag", categoryTag }, { "@relationType", relationType } }, number.ToString() ), func); }
public void GetSelftAndChildArchiveExtendValues(int siteId, int relationType, int lft, int rgt, int number, DataReaderFunc func) { /* * base.ExecuteReader( * new SqlQuery(String.Format(base.OptimizeSQL(SP.Archive_GetSelfAndChildArchives), number), * new object[,]{ * }), func * ); */ base.ExecuteReader( SqlQueryHelper.Format(DbSql.Archive_GetSelfAndChildArchiveExtendValues, new object[, ] { { "@siteId", siteId }, { "@lft", lft }, { "@rgt", rgt }, { "@relationType", relationType } }, number.ToString() ), func); }
public void UpdateCategoryExtendsBind(int categoryId, int[] extendIds) { SqlQuery[] querys = new SqlQuery[extendIds.Length + 1]; const string insertSql = "INSERT INTO $PREFIX_categoryExtend (categoryId,extendId,enabled) VALUES (@categoryId, @extendId,1)"; const string delSql = "DELETE FROM $PREFIX_categoryExtend WHERE categoryId=@categoryId"; querys[0] = SqlQueryHelper.Format(delSql, new object[, ] { { "@categoryId", categoryId } }); for (int i = 0; i < extendIds.Length; i++) { querys[i + 1] = SqlQueryHelper.Format(insertSql, new object[, ] { { "@categoryId", categoryId }, { "@extendId", extendIds[i] } } ); } base.ExecuteNonQuery(querys); }
public int CreateSite(ISite site) { base.ExecuteNonQuery(SqlQueryHelper.Format(DbSql.Site_CreateSite, new object[, ] { { "@name", site.Name }, { "@dirname", site.DirName }, { "@domain", site.Domain }, { "@location", site.Location }, { "@tpl", site.Tpl }, { "@language", site.Language }, { "@note", site.Note }, { "@seotitle", site.SeoTitle }, { "@seokeywords", site.SeoKeywords }, { "@seodescription", site.SeoDescription }, { "@state", site.State }, { "@protel", site.Tel }, { "@prophone", site.Phone }, { "@profax", site.Fax }, { "@proaddress", site.Address }, { "@proemail", site.Email }, { "@im", site.Im }, { "@postcode", site.PostCode }, { "@pronotice", site.Notice }, { "@proslogan", site.Slogan } })); int siteId = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format( "SELECT MAX(siteid) FROM $PREFIX_site")).ToString()); //初始化Root数据 base.ExecuteNonQuery( SqlQueryHelper.Format(@"INSERT INTO $PREFIX_category (siteid,pagetitle,moduleid,tag,name,keywords,description,lft,rgt,orderindex) VALUES(" + siteId + ",'ROOT',1,'root','根栏目','','',1,2,1)") ); return(siteId); }
/// <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 sort_number DESC"); } //数据库为OLEDB,且为第一页时 const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.[ID] AS ID,* FROM $PREFIX_archive INNER JOIN $PREFIX_category ON $PREFIX_archive.[CID]=$PREFIX_category.[ID] WHERE $[condition] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.[ID]"; //记录数 recordCount = int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(DbSql.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 : DbSql.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); }
/// <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 sort_number DESC"); } object[,] data = new object[, ] { { "@siteId", siteId }, { "@lft", categoryLft }, { "@rgt", categoryRgt } }; //为第一页时 const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.[ID] AS ID,* FROM $PREFIX_archive INNER JOIN $PREFIX_category ON $PREFIX_archive.[CgID]=$PREFIX_category.[ID] WHERE $[condition] AND $PREFIX_category.siteid=@siteId AND ($PREFIX_category.lft>=@lft AND $PREFIX_category.rgt<=@rgt) AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.[ID]"; //记录数 recordCount = int.Parse(base.ExecuteScalar( SqlQueryHelper.Format(DbSql.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 : DbSql.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); }