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