public static SqlString Apply(SqlString sql)
{
if (text == null)
return sql;
var s = "from " + theTable;
var index = sql.IndexOfCaseInsensitive(s);
var indexOfSpaceAfterAlias = sql.IndexOf(" ", index + s.Length +1, sql.Length, StringComparison.InvariantCultureIgnoreCase);
if (indexOfSpaceAfterAlias == -1)
return sql;
return sql.Insert(indexOfSpaceAfterAlias, text);
}
private static SqlString PrependComment(SqlString sql, QueryParameters parameters)
{
string comment = parameters.Comment;
if (string.IsNullOrEmpty(comment))
{
return sql;
}
else
{
return sql.Insert(0, "/* " + comment + " */");
}
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
/*
* "SELECT TOP limit rest-of-sql-statement"
*/
SqlStringBuilder topFragment = new SqlStringBuilder();
topFragment.Add(" top ");
topFragment.Add(limit);
return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), topFragment.ToSqlString());
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
var top = new SqlStringBuilder()
.Add(" top (")
.Add(limit)
.Add(")")
.ToSqlString();
return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), top);
}
/// <summary>
/// Add a <c>LIMIT (TOP)</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <param name="offset">Offset of the first row to process in the result set</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (offset > 0)
{
throw new NotSupportedException("SQL Server does not support an offset");
}
/*
* "SELECT TOP limit rest-of-sql-statement"
*/
return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + limit);
}
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
int intSelectInsertPoint = GetAfterSelectInsertPoint(querySqlString);
string strLimit = string.Format(" top {0} start at {1}", limit, offset + 1);
return querySqlString.Insert(intSelectInsertPoint, strLimit);
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
// FIXME - This should use the ROWS syntax in Firebird to avoid problems with subqueries metioned here:
// http://www.firebirdsql.org/refdocs/langrefupd20-select.html#langrefupd20-first-skip
/*
* "SELECT FIRST x [SKIP y] rest-of-sql-statement"
*/
int insertIndex = GetAfterSelectInsertPoint(queryString);
SqlStringBuilder limitFragment = new SqlStringBuilder();
if (limit != null)
{
limitFragment.Add(" first ");
limitFragment.Add(limit);
}
if (offset != null)
{
limitFragment.Add(" skip ");
limitFragment.Add(offset);
}
return queryString.Insert(insertIndex, limitFragment.ToSqlString());
}
/// <summary>
/// Add a <c>FIRST x [SKIP] y</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <param name="offset">Offset of the first row to process in the result set</param>
/// <returns>A new SqlString that contains the <c>FIRST</c> clause.</returns>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
/*
* "SELECT FIRST x [SKIP y] rest-of-sql-statement"
*/
int insertIndex = GetAfterSelectInsertPoint(querySqlString);
if (offset > 0)
{
return querySqlString.Insert(insertIndex, " first " + limit.ToString() + " skip " + offset.ToString());
}
else
{
return querySqlString.Insert(insertIndex, " first " + limit.ToString());
}
}
public void Insert()
{
SqlString sql = new SqlString(new object[] { "begin ", Parameter.Placeholder, " end" });
Assert.AreEqual("beginning ? end", sql.Insert(5, "ning").ToString());
Assert.AreEqual("begin middle? end", sql.Insert(6, "middle").ToString());
Assert.AreEqual("begin ?middle end", sql.Insert(7, "middle").ToString());
Assert.AreEqual("beg|in ? end", sql.Insert(3, "|").ToString());
Assert.AreEqual("begin ? ending", sql.Insert(11, "ing").ToString());
Assert.AreEqual("begin ? enXd", sql.Insert(10, "X").ToString());
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
var tokenEnum = new SqlTokenizer(querySqlString).GetEnumerator();
if (!tokenEnum.TryParseUntilFirstMsSqlSelectColumn()) return null;
int insertPoint = tokenEnum.Current.SqlIndex;
return querySqlString.Insert(insertPoint, new SqlString("top ", limit, " "));
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
/*
* "SELECT [SKIP x] FIRST y rest-of-sql-statement"
*/
// TODO - Check support for cases where only the offset is specified, but the limit is not. Might need to use int.MaxValue.
int insertIndex = GetAfterSelectInsertPoint(queryString);
SqlStringBuilder limitFragment = new SqlStringBuilder();
if (offset != null)
{
limitFragment.Add(" skip ");
limitFragment.Add(offset);
}
if (limit != null)
{
limitFragment.Add(" first ");
limitFragment.Add(limit);
}
return queryString.Insert(insertIndex, limitFragment.ToSqlString());
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
if (offset != null && offset.Length != 0)
throw new NotSupportedException("Jet Engine 4.0 does not support an offset");
int insertIndex = GetAfterSelectInsertPoint(queryString);
var limitFragment = new SqlStringBuilder();
if (limit != null && limit.Length != 0)
{
limitFragment.Add(" TOP ");
limitFragment.Add(limit);
}
return queryString.Insert(insertIndex, limitFragment.ToSqlString());
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
var top = new SqlString(" top (", limit, ")");
return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), top);
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
int intSelectInsertPoint = GetAfterSelectInsertPoint(queryString);
SqlStringBuilder limitFragment = new SqlStringBuilder();
limitFragment.Add(" top ");
if (limit != null)
limitFragment.Add(limit);
else
limitFragment.Add(int.MaxValue.ToString());
if (offset != null)
{
limitFragment.Add(" start at ");
limitFragment.Add(offset);
}
return queryString.Insert(intSelectInsertPoint, limitFragment.ToSqlString());
}