예제 #1
0
파일: TableDAL.cs 프로젝트: wangjiec1/cms
        /// <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]);
        }
예제 #2
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /************ 分页 ****************/

        /// <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)
        {
            object[,] data = new object[, ] {
                { "@siteId", siteId },
            };
            String catIdArrayString = this.IntArrayToString(catIdArray);

            String sql = SQLRegex.Replace(DbSql.ArchiveGetPagedArchivesCountSqlPagerqurey, m =>
            {
                switch (m.Groups[1].Value)
                {
                case "catIdArray": return(catIdArrayString);
                }
                return(String.Empty);
            });

            //获取记录条数
            recordCount = int.Parse(base.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;
            }

            //计算分页
            int 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(base.GetDataSet(SqlQueryHelper.Format(sql, data)).Tables[0]);
        }
예제 #3
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]";

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

            recordCount = int.Parse(
                base.ExecuteScalar(
                    new SqlQuery(
                        String.Format(base.OptimizeSql(DbSql.Operation_GetOperationsCountByAvailable), condition), DalBase.EmptyParameter)
                    ).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, DalBase.EmptyParameter)).Tables[0]);
        }
예제 #4
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(
                    base.ExecuteScalar(base.NewQuery(DbSql.Operation_GetOperationCount, DalBase.EmptyParameter))
                    .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_GetPagedOperations);


            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, DalBase.EmptyParameter)
                       ).Tables[0]);
        }
예제 #5
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), DalBase.EmptyParameter)
                                                       ).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]);
        }
예제 #6
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(
                base.ExecuteScalar(base.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;
            }
            //计算分页
            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]);
        }
예제 #7
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /// <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)
        {
            String sql = DbSql.Archive_GetSpecialArchiveList;

            sql = SQLRegex.Replace(sql, m =>
            {
                switch (m.Groups[1].Value)
                {
                case "catIdArray":
                    return(this.IntArrayToString(catIdArray));
                }
                return(null);
            });

            IDictionary <String, object> paramters = new Dictionary <string, object>();

            paramters.Add("@siteId", siteId);
            SqlQuery query = new SqlQuery(String.Format(base.OptimizeSql(sql), skipSize, number), paramters);

            base.ExecuteReader(query, func);
        }
예제 #8
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /// <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)
        {
            string 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(base.ExecuteScalar(
                                        base.NewQuery(String.Format(DbSql.ArchiveGetSearchRecordCountByModuleId, moduleId,
                                                                    keyword, condition), DalBase.EmptyParameter)
                                        ).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.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(base.GetDataSet(base.NewQuery(sql, null)).Tables[0]);
        }
예제 #9
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /// <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)
        {
            string condition = SqlConst.Archive_NotSystemAndHidden;


            if (String.IsNullOrEmpty(orderby))
            {
                orderby = String.Intern("ORDER BY $PREFIX_archive.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.site_id=@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.ArchiveGetSearchRecordCountByCategoryId,
                                                              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.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);
            });


            base.ExecuteReader(SqlQueryHelper.Format(sql, data), func);
        }
예제 #10
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /// <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, int categoryLft, int categoryRgt, bool onlyMatchTitle,
                                   string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount,
                                   string orderby, DataReaderFunc func)
        {
            base.CheckSqlInject(keyword, orderby);
            StringBuilder sb = new StringBuilder(SqlConst.Archive_NotSystemAndHidden);

            if (siteId > 0)
            {
                sb.Append(" AND $PREFIX_category.site_id=").Append(siteId.ToString());
            }

            if (categoryLft > 0 && categoryRgt > 0)
            {
                sb.Append(" AND ($PREFIX_category.lft>=").Append(categoryLft.ToString())
                .Append(" AND $PREFIX_category.rgt<=").Append(categoryRgt).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("%')");
            }

            string condition = sb.ToString();

            //排序规则
            if (String.IsNullOrEmpty(orderby))
            {
                orderby = String.Intern("ORDER BY $PREFIX_archive.sort_number DESC");
            }


            //记录数
            recordCount = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format(DbSql.Archive_GetSearchRecordCount, 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 = 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);
            });

            base.ExecuteReader(base.NewQuery(sql, null), func);
        }
예제 #11
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
        /// <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, //Order filed ( CreateDate | ViewCount | Agree | Disagree )
                   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})", this.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(base.ExecuteScalar(
                                        base.NewQuery(String.Format(DbSql.ArchiveGetpagedArchivesCountSql, condition), DalBase.EmptyParameter)).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);


            string 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(base.GetDataSet(base.NewQuery(sql, null)).Tables[0]);
        }
예제 #12
0
파일: ArchiveDAL.cs 프로젝트: wangjiec1/cms
 public int GetCategoryArchivesCount(string id)
 {
     return(int.Parse(base.ExecuteScalar(
                          base.NewQuery(SQLRegex.Replace(DbSql.Archive_GetCategoryArchivesCount, (c) => { return id; }), null)).ToString()));
 }
예제 #13
0
파일: TableDAL.cs 프로젝트: wangjiec1/cms
        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(
                                        base.NewQuery(DbSql.Table_GetRowsCount,
                                                      base.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;
            }

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

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


            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]);
        }