Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Transforms a simple SQL Server SELECT statement into a pageable statement.
using System;
using System.Text.RegularExpressions;
namespace Raoni {
public static class Utils {
private string queryLimit(string query, uint limit, uint? offset) {
if(limit > 0) {
offset = offset == null ? 0 : offset;
if(offset < 0)
throw new Exception("LIMIT argument offset=" + offset + " is not valid");
if(offset == 0) {
Regex rx = new Regex(@"SELECT\s", RegexOptions.IgnoreCase);
query = rx.Replace(query, "SELECT TOP " + limit + " ", 1);
}
else {
int lastOrder = query.LastIndexOf("ORDER BY");
string e = "", orderby = "", over = "";
if(lastOrder > -1) {
e = query.Substring(lastOrder);
orderby = e.Substring("ORDER BY".Length);
}
else
lastOrder = query.Length;
over = orderby.Length > 0 ? "(SELECT 0)" : Regex.Replace(orderby, @"[^,]+\.([^,]+)", @"__temp__.$1", RegexOptions.Singleline);
string i = query.Substring(0, lastOrder) + Regex.Replace(e, @"\s*ORDER BY(.*)", "", RegexOptions.Singleline);
uint start = (uint)offset + 1, end = (uint)offset + limit;
query = @"
WITH __table__ AS (
SELECT ROW_NUMBER() OVER (ORDER BY " + over + ") AS __row__, * FROM (" + i + ") AS __temp__" + @"
)
SELECT * FROM __table__ WHERE __row__ BETWEEN " + start + " AND " + end;
}
}
return query;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment