Exemple #1
0
        private string GetSetSql(string sql, int firstResult, int maxResults, string orderby)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }
            sql = sql.TrimStart();
            int    selectIndex   = SqlDialectBase.GetSelectEnd(sql) + 1;
            string orderByClause = string.IsNullOrEmpty(orderby) ? "CURRENT_TIMESTAMP" : orderby;

            string projectedColumns = SqlDialectBase.GetColumnNames(sql).Aggregate(new StringBuilder(), (sb, s) => (sb.Length == 0 ? sb : sb.Append(", ")).Append(GetColumnName("_proj", s, null)), sb => sb.ToString());
            string newSql           = sql.Insert(selectIndex, string.Format("ROW_NUMBER() OVER({0}) AS {1}, ", orderByClause, "_row_number"));

            string result = string.Format("SELECT TOP({0}) {1} FROM ({2}) [_proj] WHERE {3} >= {4}",
                                          maxResults, projectedColumns.Trim(), newSql, "_proj._row_number", firstResult);

            return(result);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">类型参数</typeparam>
        /// <param name="context">上下文<see cref="context"/></param>
        /// <param name="sqlNoteInfo">SQL注释<see cref="SqlNoteInfo"/></param>
        /// <param name="sqlSearchInfo">内容查询SQL</param>
        /// <param name="sqlOrderby">Orderby</param>
        /// <param name="nCount">记录数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static IEnumerable <T> QueryPager <T>(this DbContext context, string sqlSearchInfo,
                                                     string sqlOrderby, ref int nCount, int pageIndex = 1, int pageSize = 10,
                                                     dynamic param = null)
        {
            if (string.IsNullOrEmpty(sqlSearchInfo))
            {
                throw new ArgumentNullException("参数sqlSearchInfo不能为空");
            }

            if (string.IsNullOrEmpty(sqlOrderby))
            {
                throw new ArgumentNullException("sqlOrderby", "分页函数必须有order by");
            }


            var    dbFactory      = new DataBaseFactory(context.DatabaseType);
            string strSql         = dbFactory.GetSqlPages(sqlSearchInfo, pageIndex, pageSize, sqlOrderby);
            string sqlSearchCount = SqlDialectBase.GetSqlCount(sqlSearchInfo);

            nCount = (int)SqlMapper.ExecuteScalar(context.Connection, sqlSearchCount.ToString(), param);
            return(context.Connection.Query <T>(strSql.ToString(), param as object));
        }