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