Exemplo n.º 1
0
        protected override string GetQueryText(SqlQuery query)
        {
            var sql = query.ToString();

            var limit = " LIMIT {0}";
            if (query.Skip > 0) {
                limit = string.Format(limit, query.Skip, "{0}");
                if (query.Take > 0) {
                    limit = string.Format("{0}, {1}", limit, query.Take);
                }
                sql += limit;
            } else if (query.Take > 0) {
                sql += string.Format(limit, string.Format("0, {0}", query.Take));
            }

            return sql;
        }
Exemplo n.º 2
0
        protected override string GetQueryText(SqlQuery query)
        {
            var sql = query.ToString();

            // custom settings
            if (query.Skip > 0) {
                // Paging
                var sql2 = "SELECT * FROM ({0}) AS Limit WHERE {1}";
                var rownumber = "SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS Row,";

                string where;
                if (query.Take > 0) {
                    where = string.Format("Row BETWEEN {0} AND {1}", query.Skip + 1, query.Take + query.Skip);
                } else {
                    where = string.Format("Row > {0}", query.Skip);
                }

                if (query.OrderBy.Count > 0) {
                    var list = new List<string>();
                    foreach (var column in query.OrderBy) {
                        list.Add(column.ToString());
                    }
                    rownumber = string.Format(rownumber, string.Join(",", list.ToArray()));
                } else {
                    rownumber = string.Format(rownumber, "(SELECT 1)");
                }
                sql = string.Format(sql2, rownumber + sql.Remove(0, 6), where);
            } else if (query.Take > 0) {
                // top
                sql = string.Format("SELECT TOP {0}", query.Take) + sql.Remove(0, 6);
            }

            return sql;
        }