Esempio n. 1
0
 public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null)
 {
     parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1);
     if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved))
     {
         parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner";
     }
     string text = primaryKey ?? string.Empty;
     if (primaryKey.Contains('.') && !primaryKey.EndsWith("."))
     {
         text = primaryKey.Substring(primaryKey.LastIndexOf(".") + 1);
     }
     string result = string.Format("SELECT peta_paged.{4} FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}", new object[]
     {
         (parts.sqlOrderBy == null) ? "ORDER BY (SELECT NULL)" : parts.sqlOrderBy,
         parts.sqlSelectRemoved,
         args.Length,
         args.Length + 1,
         text
     });
     args = args.Concat(new object[]
     {
         skip,
         skip + take
     }).ToArray<object>();
     return result;
 }
Esempio n. 2
0
 public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null)
 {
     if (parts.sqlSelectRemoved.StartsWith("*"))
     {
         throw new System.Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
     }
     return Singleton<SqlServerDatabaseType>.Instance.BuildPageQuery(skip, take, parts, ref args, null);
 }
Esempio n. 3
0
 public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null)
 {
     string result = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1);
     args = args.Concat(new object[]
     {
         take,
         skip
     }).ToArray<object>();
     return result;
 }
Esempio n. 4
0
        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;
        }
Esempio n. 5
0
 public static bool SplitSQL(string sql, out PagingHelper.SQLParts parts)
 {
     parts.sql = sql;
     parts.sqlSelectRemoved = null;
     parts.sqlCount = null;
     parts.sqlOrderBy = null;
     Match match = PagingHelper.rxColumns.Match(sql);
     if (!match.Success)
     {
         return false;
     }
     Group group = match.Groups[1];
     parts.sqlSelectRemoved = sql.Substring(group.Index);
     if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved))
     {
         parts.sqlCount = string.Concat(new string[]
         {
             sql.Substring(0, group.Index),
             "COUNT(",
             match.Groups[1].ToString().Trim(),
             ") ",
             sql.Substring(group.Index + group.Length)
         });
     }
     else
     {
         parts.sqlCount = sql.Substring(0, group.Index) + "COUNT(*) " + sql.Substring(group.Index + group.Length);
     }
     match = PagingHelper.rxOrderBy.Match(parts.sqlCount);
     if (!match.Success)
     {
         parts.sqlOrderBy = null;
     }
     else
     {
         group = match.Groups[0];
         parts.sqlOrderBy = group.ToString();
         parts.sqlCount = parts.sqlCount.Substring(0, group.Index) + parts.sqlCount.Substring(group.Index + group.Length);
     }
     return true;
 }
Esempio n. 6
0
 /// <summary>
 /// Builds an SQL query suitable for performing page based queries to the database
 /// </summary>
 /// <param name="skip">The number of rows that should be skipped by the query</param>
 /// <param name="take">The number of rows that should be retruend by the query</param>
 /// <param name="parts">The original SQL query after being parsed into it's component parts</param>
 /// <param name="args">Arguments to any embedded parameters in the SQL query</param>
 /// <returns>The final SQL query that should be executed.</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;
 }
Esempio n. 7
0
 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;
 }