Transforms a simple SQL Server SELECT statement into a pageable statement.

sql-server-paging.cs

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