コード例 #1
0
        /************ 分页 ****************/

        /// <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]);
        }
コード例 #2
0
        /// <summary>
        /// 获取行数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public DataTable GetRowsData(int[] ids)
        {
            string rowRange = String.Empty;
            int    i        = 0;

            Array.ForEach(ids, a =>
            {
                if (++i != 1)
                {
                    rowRange += ",";
                }
                rowRange += a.ToString();
            });

            string sql = base.OptimizeSql(DbSql.table_GetRowData);

            sql = SQLRegex.Replace(sql, (match) =>
            {
                switch (match.Groups[1].Value)
                {
                case "range": return(rowRange);
                }
                return(null);
            });

            return(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
        }
コード例 #3
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]";

            string condition = String.Format(typeID == 1 ? "[receiveuid]={0}" : "[senduid]={0}", uid);


            recordCount = int.Parse(base.ExecuteScalar(new SqlQuery(
                                                           String.Format(base.OptimizeSql(DbSql.Message_GetPagedMessagesCount), condition), null)
                                                       ).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 ?
                         base.OptimizeSql(sql1) :
                         base.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(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
        }
コード例 #4
0
ファイル: UserDAL.cs プロジェクト: jeason0813/AtNet.Cms
        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]";

            string condition = available ? "available" : "available=false";

            recordCount = int.Parse(
                base.ExecuteScalar(
                    new SqlQuery(String.Format(base.OptimizeSql(DbSql.Operation_GetOperationsCountByAvailable), condition), null)
                    ).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 ?
                         base.OptimizeSql(sql1) :
                         base.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(base.GetDataSet(new SqlQuery(sql, null)).Tables[0]);
        }
コード例 #5
0
ファイル: MemberDAL.cs プロジェクト: jeason0813/AtNet.Cms
        /// <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(
                base.ExecuteScalar(new SqlQuery(base.OptimizeSql(DbSql.Member_GetMemberCount))).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 ?
                         base.OptimizeSql(sql1) :
                         base.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(base.GetDataSet(
                       new SqlQuery(sql)
                       ).Tables[0]);
        }
コード例 #6
0
 public int GetCategoryArchivesCount(string id)
 {
     return(int.Parse(base.ExecuteScalar(
                          new SqlQuery(base.OptimizeSql(SQLRegex.Replace(DbSql.Archive_GetCategoryArchivesCount, (c) => { return id; })), null)).ToString()));
 }
コード例 #7
0
        /// <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)
        {
            const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ";

            if (String.IsNullOrEmpty(orderby))
            {
                orderby = String.Intern("ORDER BY 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]=$[moduleid] 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(
                                        new SqlQuery(base.OptimizeSql(String.Format(DbSql.Archive_GetSearchRecordCountByModuleID, moduleId,
                                                                                    keyword, condition)), null)
                                        ).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_GetPagedSearchArchivesByModuleID;

            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 "moduleid": return(moduleId.ToString());

                case "orderby": return(orderby);
                }
                return(null);
            });

            return(base.GetDataSet(new SqlQuery(base.OptimizeSql(sql))).Tables[0]);
        }
コード例 #8
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,
                                             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);
        }
コード例 #9
0
        /// <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);
        }
コード例 #10
0
        /// <summary>
        /// 获取栏目分页文档
        /// </summary>
        /// <param name="lft"></param>
        /// <param name="rgt"></param>
        /// <param name="author">指定作者,NULL表示不限,可显示所有作者发布的文档</param>
        /// <param name="siteId"></param>
        /// <param name="moduleId">参数暂时不使用为-1</param>
        /// <param name="flags"></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 lft, int rgt, string author,
                                          string[,] flags, string orderByField, bool orderAsc,
                                          int pageSize, int currentPageIndex,
                                          out int recordCount, out int pages)
        {
            //SQL Condition Template
            const string conditionTpl = "$[siteid]$[module]$[category]$[author]$[flags]";


            //Get records count
            //{0}==$[condition]

            const string sql1 = @"SELECT TOP $[pagesize] a.id AS id,alias,title,
                                    c.name as CategoryName,cid,flags,author,content,source,
                                    createdate,viewcount,location,sort_number as sortNumber FROM $PREFIX_archive a
                                    INNER JOIN $PREFIX_category c ON a.cid=c.id
                                    WHERE $[condition] ORDER BY $[orderByField] $[orderASC],a.id";


            string condition,                                                                     //SQL where condition
                   order     = String.IsNullOrEmpty(orderByField) ? "sort_number" : orderByField, //Order filed ( CreateDate | ViewCount | Agree | Disagree )
                   orderType = orderAsc ? "ASC" : "DESC";                                         //ASC or DESC


            string flag = ArchiveFlag.GetSQLString(flags);

            condition = SQLRegex.Replace(conditionTpl, match =>
            {
                switch (match.Groups[1].Value)
                {
                case "siteid": return(String.Format(" c.siteid={0}", siteId.ToString()));

                case "category": return(lft <= 0 || rgt <= 0 ? ""
                        : String.Format(" AND lft>={0} AND rgt<={1}", lft.ToString(), rgt.ToString()));

                case "module": return(moduleId <= 0 ? ""
                        : String.Format(" AND m.id={0}", moduleId.ToString()));

                case "author": return(String.IsNullOrEmpty(author) ? null
                        : String.Format(" AND author='{0}'", author));

                case "flags": return(String.IsNullOrEmpty(flag) ? "" : " AND " + flag);
                }
                return(null);
            });

            // throw new Exception(new SqlQuery(base.OptimizeSQL(String.Format(SP.Archive_GetpagedArchivesCountSql, condition)));

            //获取记录条数
            recordCount = int.Parse(base.ExecuteScalar(
                                        new SqlQuery(base.OptimizeSql(String.Format(DbSql.Archive_GetpagedArchivesCountSql, condition)), null)).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;

            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(base.GetDataSet(new SqlQuery(base.OptimizeSql(sql))).Tables[0]);
        }
コード例 #11
0
        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 tableid=$[tableid] ORDER BY submittime DESC";


            //记录数
            recordCount = int.Parse(base.ExecuteScalar(
                                        new SqlQuery(base.OptimizeSql(DbSql.Table_GetRowsCount),
                                                     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;
            }

            //跳过记录数
            int skipCount = pageSize * (currentPageIndex - 1);

            //如果调过记录为0条,且为OLEDB时候,则用sql1
            string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ?
                         base.OptimizeSql(sql1) :
                         base.OptimizeSql(DbSql.Table_GetPagedRows);


            sql = SQLRegex.Replace(sql, (match) =>
            {
                switch (match.Groups[1].Value)
                {
                case "tableid": return(tableID.ToString());

                case "pagesize": return(pageSize.ToString());

                case "skipsize": return(skipCount.ToString());
                    // case "keyword": return keyword;
                }
                return(null);
            });
            return(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
        }