/// <summary> /// 获取行数据 /// </summary> /// <param name="ids"></param> /// <returns></returns> public DataTable GetRowsData(int[] ids) { var rowRange = string.Empty; var i = 0; Array.ForEach(ids, a => { if (++i != 1) { rowRange += ","; } rowRange += a.ToString(); }); var sql = OptimizeSql(DbSql.table_GetRowData); sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "range": return(rowRange); } return(null); }); return(GetDataSet(new SqlQuery(sql)).Tables[0]); }
public DataTable GetPagedAvailableOperationList(bool available, int pageSize, int currentPageIndex, out int recordCount, out int pageCount) { const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_operation WHERE $[condition]"; var condition = available ? "available" : "available=false"; recordCount = int.Parse( ExecuteScalar( new SqlQuery( string.Format(OptimizeSql(DbSql.Operation_GetOperationsCountByAvailable), condition), EmptyParameter) ).ToString()); //计算页码 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? OptimizeSql(sql1) : OptimizeSql(DbSql.Archive_GetPagedOperationsByAvialble); sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "pagesize": return(pageSize.ToString()); case "skipsize": return((pageSize * (currentPageIndex - 1)).ToString()); case "condition": return(condition); } return(null); }); return(GetDataSet(new SqlQuery(sql, EmptyParameter)).Tables[0]); }
/// <summary> /// 获取分页消息 /// </summary> /// <param name="uid">用户ID</param> /// <param name="type">消息类型</param> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="recordCount"></param> /// <param name="pageCount"></param> /// <returns></returns> public DataTable GetPagedMessage(int uid, int typeID, int pageSize, ref int currentPageIndex, out int recordCount, out int pageCount) { const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_Message WHERE Recycle=0 AND $[condition]"; var condition = string.Format(typeID == 1 ? "[receiveuid]={0}" : "[senduid]={0}", uid); recordCount = int.Parse(ExecuteScalar(new SqlQuery( string.Format(OptimizeSql(DbSql.Message_GetPagedMessagesCount), condition), EmptyParameter) ).ToString()); pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //当前页数 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //计算分页 var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? OptimizeSql(sql1) : OptimizeSql(DbSql.Message_GetPagedMessages); sql = SQLRegex.Replace(sql, match => { switch (match.Groups[1].Value) { case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); case "condition": return(condition.ToString()); } return(null); }); return(GetDataSet(new SqlQuery(sql)).Tables[0]); }
/// <summary> /// 获取会员列表 /// </summary> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="recordCount"></param> /// <param name="pageCount"></param> /// <param name="time"></param> /// <returns></returns> public DataTable GetPagedMembers(int pageSize, ref int currentPageIndex, out int recordCount, out int pages, DateTime?time) { //ACCESS时候第一页执行 const string sql1 = @"SELECT TOP $[pagesize] [id],[username],[avatar],[nickname],[RegIp],[RegTime],[LastLoginTime] FROM $PREFIX_member INNER JOIN $PREFIX_MemberDetails ON $PREFIX_member.[ID]=$PREFIX_MemberDetails.[UID]"; recordCount = int.Parse( ExecuteScalar(NewQuery(DbSql.Member_GetMemberCount, null)).ToString() ); pages = recordCount / pageSize; if (recordCount % pageSize != 0) { pages++; } //验证当前页数 if (currentPageIndex > pages && currentPageIndex != 1) { currentPageIndex = pages; } if (currentPageIndex < 1) { currentPageIndex = 1; } //计算分页 var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? OptimizeSql(sql1) : OptimizeSql(DbSql.Member_GetPagedMembers); sql = SQLRegex.Replace(sql, match => { switch (match.Groups[1].Value) { case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); } return(null); }); return(GetDataSet( new SqlQuery(sql) ).Tables[0]); }
public DataTable GetPagedOperationList(int pageSize, int currentPageIndex, out int recordCount, out int pageCount) { const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_operation"; //计算页码 recordCount = int.Parse( ExecuteScalar(NewQuery(DbSql.Operation_GetOperationCount, EmptyParameter)) .ToString()); pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? OptimizeSql(sql1) : OptimizeSql(DbSql.Archive_GetPagedOperations); sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); } return(null); }); return(GetDataSet( new SqlQuery(sql, EmptyParameter) ).Tables[0]); }
/// <summary> /// 获取指定数量和栏目的特殊文档 /// </summary> /// <param name="siteId"></param> /// <param name="catIdArray"></param> /// <param name="number"></param> /// <param name="skipSize"></param> /// <param name="func"></param> public void GetSpecialArchives(int siteId, int[] catIdArray, int number, int skipSize, DataReaderFunc func) { var sql = DbSql.Archive_GetSpecialArchiveList; sql = SQLRegex.Replace(sql, m => { switch (m.Groups[1].Value) { case "catIdArray": return(IntArrayToString(catIdArray)); } return(null); }); IDictionary <string, object> paramters = new Dictionary <string, object>(); paramters.Add("@siteId", siteId); var query = new SqlQuery(string.Format(OptimizeSql(sql), skipSize, number), paramters); ExecuteReader(query, func); }
/// <summary> /// 在指定模块下搜索关键词相关的内容 /// </summary> /// <param name="moduleId"></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> /// <returns></returns> public DataTable SearchByModule(int moduleId, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby) { var condition = SqlConst.Archive_NotSystemAndHidden; if (string.IsNullOrEmpty(orderby)) { orderby = string.Intern("ORDER BY $PREFIX_archive.sort_number DESC"); } //为第一页时 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 $PREFIX_category.[ModuleID]=$[module_id] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.id"; //记录数 recordCount = int.Parse(ExecuteScalar( NewQuery(string.Format(DbSql.ArchiveGetSearchRecordCountByModuleId, moduleId, keyword, condition), EmptyParameter) ).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 var skipCount = pageSize * (currentPageIndex - 1); var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? sql1 : DbSql.ArchiveGetPagedSearchArchivesByModuleId; sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "condition": return(condition); case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); case "keyword": return(keyword); case "module_id": return(moduleId.ToString()); case "orderby": return(orderby); } return(null); }); return(GetDataSet(NewQuery(sql, null)).Tables[0]); }
/// <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, string catPath, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func) { var condition = SqlConst.Archive_NotSystemAndHidden; if (string.IsNullOrEmpty(orderby)) { orderby = string.Intern("ORDER BY $PREFIX_archive.sort_number DESC"); } var data = new object[, ] { { "@siteId", siteId }, { "@catPath", catPath + "/%" }, }; //为第一页时 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_archive.path LIKE @catPath AND $PREFIX_archive.site_id=@siteId AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.id"; //记录数 recordCount = int.Parse(ExecuteScalar( SqlQueryHelper.Format( String.Format(DbSql.ArchiveGetSearchRecordCountByCategoryId, keyword, condition), data)).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 var skipCount = pageSize * (currentPageIndex - 1); var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? sql1 : DbSql.ArchiveGetPagedSearchArchivesByCategoryId; 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); }); ExecuteReader(SqlQueryHelper.Format(sql, data), func); }
/// <summary> /// 搜索关键词相关的内容 /// </summary> /// <param name="siteId"></param> /// <param name="categoryRgt"></param> /// <param name="onlyMatchTitle"></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="func"></param> /// <param name="categoryLft"></param> /// <returns></returns> public void SearchArchives(int siteId, string catPath, bool onlyMatchTitle, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func) { CheckSqlInject(new[] { keyword, orderby }); var sb = new StringBuilder(SqlConst.Archive_NotSystemAndHidden); if (siteId > 0) { sb.Append(" AND $PREFIX_category.site_id=").Append(siteId.ToString()); } if (!string.IsNullOrEmpty(catPath)) { sb.Append(" AND $PREFIX_archive.path LIKE '").Append(catPath).Append("/%'"); } if (onlyMatchTitle) { sb.Append(" AND title LIKE '%").Append(keyword).Append("%'"); } else { sb.Append(" AND ( title LIKE '%").Append(keyword).Append("%' OR outline LIKE '%").Append(keyword) .Append("%' OR content LIKE '%").Append(keyword).Append("%')"); } var condition = sb.ToString(); //排序规则 if (string.IsNullOrEmpty(orderby)) { orderby = string.Intern("ORDER BY $PREFIX_archive.sort_number DESC"); } var query = SqlQueryHelper.CreateQuery(String.Format(DbSql.Archive_GetSearchRecordCount, condition)); //记录数 recordCount = int.Parse(ExecuteScalar(query).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = DbSql.Archive_GetPagedSearchArchives; sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "condition": return(condition); case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); case "orderby": return(orderby); } return(null); }); ExecuteReader(NewQuery(sql, null), func); }
/// <summary> /// 获取栏目分页文档 /// </summary> /// <param name="lft"></param> /// <param name="rgt"></param> /// <param name="siteId"></param> /// <param name="moduleId">参数暂时不使用为-1</param> /// <param name="publisherId"></param> /// <param name="includeChild"></param> /// <param name="flag"></param> /// <param name="keyword"></param> /// <param name="orderByField"></param> /// <param name="orderAsc"></param> /// <param name="pageSize"></param> /// <param name="currentPageIndex"></param> /// <param name="recordCount"></param> /// <param name="pages"></param> /// <returns></returns> public DataTable GetPagedArchives(int siteId, int moduleId, int[] catIdArray, int publisherId, bool includeChild, int flag, string keyword, string orderByField, bool orderAsc, int pageSize, int currentPageIndex, out int recordCount, out int pages) { //SQL Condition Template const string conditionTpl = "$[siteid]$[module]$[category]$[author_id]$[flag]$[keyword]"; string condition, //SQL where condition order = string.IsNullOrEmpty(orderByField) ? "a.sort_number" : orderByField, orderType = orderAsc ? "ASC" : "DESC"; //ASC or DESC condition = SQLRegex.Replace(conditionTpl, match => { switch (match.Groups[1].Value) { case "siteid": return(string.Format(" c.site_id={0}", siteId.ToString())); case "category": if (catIdArray.Length > 0) { if (includeChild && catIdArray.Length > 1) { return(string.Format(" AND cat_id IN({0})", IntArrayToString(catIdArray))); } return(string.Format(" AND cat_id = {0}", catIdArray[0])); } return(string.Empty); case "module": return(moduleId <= 0 ? "" : string.Format(" AND m.id={0}", moduleId.ToString())); case "author_id": return(publisherId == 0 ? null : string.Format(" AND author_id='{0}'", publisherId)); case "flag": return(flag > 0 ? "$PREFIX_archive.flag & " + flag : ""); case "keyword": if (string.IsNullOrEmpty(keyword)) { return(""); } return(string.Format(" AND (title LIKE '%{0}%' OR Content LIKE '%{0}%')", keyword)); } return(null); }); // throw new Exception(base.OptimizeSql(String.Format(DbSql.Archive_GetpagedArchivesCountSql, condition))); //获取记录条数 recordCount = int.Parse(ExecuteScalar( NewQuery(string.Format(DbSql.ArchiveGetpagedArchivesCountSql, condition), EmptyParameter)).ToString()); pages = recordCount / pageSize; if (recordCount % pageSize != 0) { pages++; } //验证当前页数 if (currentPageIndex > pages && currentPageIndex != 1) { currentPageIndex = pages; } if (currentPageIndex < 1) { currentPageIndex = 1; } //计算分页 var skipCount = pageSize * (currentPageIndex - 1); var sql = DbSql.Archive_GetPagedArchivesByCategoryId; sql = SQLRegex.Replace(sql, m => { switch (m.Groups[1].Value) { case "skipsize": return(skipCount.ToString()); case "pagesize": return(pageSize.ToString()); case "condition": return(condition); case "orderByField": return(order); case "orderASC": return(orderType); } return(null); }); //throw new Exception(new SqlQuery(base.OptimizeSQL(sql)); //throw new Exception(sql+"-"+DbHelper.DbType.ToString()+"-"+new SqlQuery(base.OptimizeSQL(SP.ToString()); //System.Web.HttpContext.Current.Response.Write(sql); //throw new Exception(sql); return(GetDataSet(NewQuery(sql, null)).Tables[0]); }
/************ 分页 ****************/ /// <summary> /// 获取栏目分页文档 /// </summary> /// <param name="siteId"></param> /// <param name="lft"></param> /// <param name="pageSize"></param> /// <param name="skipSize"></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[] catIdArray, int pageSize, int skipSize, ref int currentPageIndex, out int recordCount, out int pages) { var data = new object[, ] { { "@siteId", siteId }, }; var catIdArrayString = IntArrayToString(catIdArray); var sql = SQLRegex.Replace(DbSql.ArchiveGetPagedArchivesCountSqlPagerqurey, m => { switch (m.Groups[1].Value) { case "catIdArray": return(catIdArrayString); } return(string.Empty); }); //获取记录条数 recordCount = int.Parse(ExecuteScalar(SqlQueryHelper.Format(sql, data)).ToString()); pages = recordCount / pageSize; if (recordCount % pageSize != 0) { pages++; } //验证当前页数 if (currentPageIndex > pages && currentPageIndex != 1) { currentPageIndex = pages; } if (currentPageIndex < 1) { currentPageIndex = 1; } //计算分页 var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 sql = DbSql.ArchiveGetPagedArchivesByCategoryIdPagerquery; sql = SQLRegex.Replace(sql, m => { switch (m.Groups[1].Value) { case "catIdArray": return(catIdArrayString); case "skipsize": return((skipCount + skipSize).ToString()); case "pagesize": return(pageSize.ToString()); } return(null); }); return(GetDataSet(SqlQueryHelper.Format(sql, data)).Tables[0]); }
public int GetCategoryArchivesCount(string id) { return(int.Parse(ExecuteScalar( NewQuery(SQLRegex.Replace(DbSql.Archive_GetArchivesCountByPath, (c) => { return id; }), null)) .ToString())); }
public DataTable GetPagedRows(int tableID, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount) { /* * string condition = ArchiveFlag.GetSQLString(new string[,]{ * {"st","0"}, * {"v","1"} * }); */ // string condition = String.Empty ; // if (!String.IsNullOrEmpty(keyword)) // { // condition = String.Format(" AND value LIKE '%{0}%'", keyword.Replace("'", "''")); // } //数据库为OLEDB,且为第一页时 const string sql1 = @"SELECT TOP $[pagesize] * FROM $PREFIX_table_row WHERE table_id=$[tableid] ORDER BY submittime DESC"; //记录数 recordCount = int.Parse(ExecuteScalar( NewQuery(DbSql.Table_GetRowsCount, Db.CreateParametersFromArray( new object[, ] { { "@tableId", tableID } })) ).ToString()); //页数 pageCount = recordCount / pageSize; if (recordCount % pageSize != 0) { pageCount++; } //对当前页数进行验证 if (currentPageIndex > pageCount && currentPageIndex != 1) { currentPageIndex = pageCount; } if (currentPageIndex < 1) { currentPageIndex = 1; } //跳过记录数 var skipCount = pageSize * (currentPageIndex - 1); //如果调过记录为0条,且为OLEDB时候,则用sql1 var sql = skipCount == 0 && DbType == DataBaseType.OLEDB ? OptimizeSql(sql1) : OptimizeSql(DbSql.TableGetPagedRows); sql = SQLRegex.Replace(sql, (match) => { switch (match.Groups[1].Value) { case "table_id": return(tableID.ToString()); case "pagesize": return(pageSize.ToString()); case "skipsize": return(skipCount.ToString()); // case "keyword": return keyword; } return(null); }); return(GetDataSet(new SqlQuery(sql)).Tables[0]); }