/// <summary> /// 生成 SQL 分页查询语句 /// </summary> /// <param name="skip">要跳过记录数量</param> /// <param name="take">要获取记录数</param> /// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param> /// <param name="args">SQL 查询用的参数</param> /// <returns>最终可以执行的 SQL 分页查询语句</returns> /// <exception cref="System.Exception">Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id</exception> public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { if(parts.sqlSelectRemoved.StartsWith("*")) throw new Exception(Localization.Resource.PageQueryAliasException); // Same deal as SQL Server return Singleton<SqlServerDatabaseType>.Instance.BuildPageQuery(skip, take, parts, ref args); }
/// <summary> /// 生成 SQL 分页查询语句 /// </summary> /// <param name="skip">要跳过记录数量</param> /// <param name="take">要获取记录数</param> /// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param> /// <param name="args">SQL 查询用的参数</param> /// <returns>最终可以执行的 SQL 分页查询语句</returns> public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1); if(PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved)) { parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner"; } var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}", parts.sqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.sqlOrderBy, parts.sqlSelectRemoved, args.Length, args.Length + 1); args = args.Concat(new object[] { skip, skip + take }).ToArray(); return sqlPage; }
/// <summary> /// 生成 SQL 分页查询语句 /// </summary> /// <param name="skip">要跳过记录数量</param> /// <param name="take">要获取记录数</param> /// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param> /// <param name="args">SQL 查询用的参数</param> /// <returns>最终可以执行的 SQL 分页查询语句</returns> public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { var sql = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1); args = args.Concat(new object[] { take, skip }).ToArray(); return sql; }
/// <summary> /// 生成 SQL 分页查询语句 /// </summary> /// <param name="skip">要跳过记录数量</param> /// <param name="take">要获取记录数</param> /// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param> /// <param name="args">SQL 查询用的参数</param> /// <returns>最终可以执行的 SQL 分页查询语句</returns> public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.sql, args.Length, args.Length + 1); args = args.Concat(new object[] { skip, take }).ToArray(); return sqlPage; }