/// <summary>
/// Return customized limit string (for paging queries)
/// For performance reason the limit string is ommitted when querying the first page.
/// </summary>
/// <param name="querySqlString"></param>
/// <param name="offset"></param>
/// <param name="last"></param>
/// <returns></returns>
public override NHibernate.SqlCommand.SqlString GetLimitString(NHibernate.SqlCommand.SqlString querySqlString, int offset, int last)
{
if (!querySqlString.StartsWithCaseInsensitive("select "))
{
throw new ArgumentException("querySqlString should start with select", "querySqlString");
}
SqlString sqlString = querySqlString.Substring(6);
string orderSql = querySqlString.SubstringStartingWithLast("order by").ToString();
if (orderSql.Length != 0)
{
sqlString = sqlString.Substring(0, (sqlString.Length - orderSql.Length) - 1);
}
SqlStringBuilder builder = new SqlStringBuilder();
int num = offset + 1;
builder.Add("SELECT TOP ").Add(last.ToString()).Add(" ").Add(sqlString);
if (offset > 0)
{
builder.Add(" WITH FIRSTROW ").Add(num.ToString());
}
if (orderSql.Length > 0)
{
builder.Add(" ").Add(orderSql);
}
return(builder.ToSqlString());
}
public void SubstringStartingWithLastMultiplePossibilities()
{
SqlString sql = new SqlString(new string[] { " order by x", " order by z" });
Assert.AreEqual("order by z", sql.SubstringStartingWithLast("order by").ToString());
}
public void SubstringStartingWithLastAndParameters()
{
SqlString sql =
new SqlString(
new object[] { "select x from y where z = ", Parameter.Placeholder, " order by ", Parameter.Placeholder });
Assert.AreEqual(new SqlString(new object[] { "order by ", Parameter.Placeholder }),
sql.SubstringStartingWithLast("order by"));
}
public void NoSubstringStartingWithLast()
{
SqlString sql = new SqlString(new object[] { "select x from y where z = ", Parameter.Placeholder, " order by t" });
Assert.AreEqual("", sql.SubstringStartingWithLast("zzz").ToString());
}
private static string GetRowNumber(SqlString sql)
{
return new StringBuilder()
.Append("rownumber() over(")
.Append(sql.SubstringStartingWithLast("order by"))
.Append(") as rownum, ")
.ToString();
}