/// <summary> Apply s limit clause to the query. </summary>
/// <param name="querySqlString">The query to which to apply the limit. </param>
/// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <returns> the modified SQL </returns>
/// <remarks>
/// Typically dialects utilize <see cref="SupportsVariableLimit"/>
/// limit caluses when they support limits. Thus, when building the
/// select command we do not actually need to know the limit or the offest
/// since we will just be using placeholders.
/// <p/>
/// Here we do still pass along whether or not an offset was specified
/// so that dialects not supporting offsets can generate proper exceptions.
/// In general, dialects will override one or the other of this method and
/// <see cref="GetLimitString(SqlString,int,int)"/>.
/// </remarks>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
/*
* "SELECT [SKIP x] FIRST y rest-of-sql-statement"
*/
int insertIndex = GetAfterSelectInsertPoint(querySqlString);
if (offset > 0)
{
return(querySqlString.Insert(insertIndex, " skip " + offset + " first " + limit));
}
return(querySqlString.Insert(insertIndex, " first " + limit));
}
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);
var 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()));
}
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 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 OnPrepareStatement(SqlString sql)
{
Match match = Regex.Match(sql.ToString());
String tableName = match.Groups[1].Value;
String tableAlias = match.Groups[2].Value;
sql = sql.Substring(match.Groups[2].Index);
sql = sql.Replace(tableAlias, tableName);
sql = sql.Insert(0, "delete from ");
Int32 orderByIndex = sql.IndexOfCaseInsensitive(" order by ");
if (orderByIndex > 0)
{
sql = sql.Substring(0, orderByIndex);
}
int limitIndex = sql.IndexOfCaseInsensitive(" limit ");
if (limitIndex > 0)
{
sql = sql.Substring(0, limitIndex);
}
return(sql);
}
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));
}
public override SqlString OnPrepareStatement(SqlString sql)
{
sql = sql.Insert(0, String.Format("{0};", this.sqlBefore()));
if (this.sqlAfter != null)
{
sql = sql.Append(String.Format(";{0}", this.sqlAfter()));
}
return(base.OnPrepareStatement(sql));
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
using (var tokenEnum = new SqlTokenizer(querySqlString).GetEnumerator())
{
if (!tokenEnum.TryParseUntilFirstMsSqlSelectColumn())
{
return(null);
}
var insertPoint = tokenEnum.Current.SqlIndex;
return(querySqlString.Insert(insertPoint, new SqlString("top ", limit, " ")));
}
}
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()));
}
/// <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));
}
private SqlString PageByLimitOnly(SqlString limit)
{
var tokenEnum = new SqlTokenizer(_sourceQuery).GetEnumerator();
if (!tokenEnum.TryParseUntilFirstMsSqlSelectColumn())
{
return(null);
}
int insertPoint = tokenEnum.Current.SqlIndex;
return(_sourceQuery.Insert(insertPoint, new SqlString("TOP (", limit, ") ")));
}
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());
}
/// <summary>
/// MS Access and SQL Server support limit. This implementation has been made according the MS Access syntax
/// </summary>
/// <param name="querySqlString">The original query</param>
/// <param name="offset">Specifies the number of rows to skip, before starting to return rows from the query expression.</param>
/// <param name="limit">Is used to limit the number of results returned in a SQL statement</param>
/// <returns>Processed query</returns>
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
// int insertIndex = GetAfterSelectInsertPoint(queryString);
//
// var builder = new SqlStringBuilder(queryString);
/*
* Where 15 is the StartPos + PageSize, and 5 is the PageSize. https://stackoverflow.com/a/1900668/100863
* SELECT*
* FROM(
* SELECT TOP 3 sub.`SMP_GLOBALID`
* FROM(
* SELECT TOP 3 `tblSamples`.`SMP_GLOBALID`
* FROM `tblSamples`
* ORDER BY `SMP_GLOBALID`
* ) sub
* ORDER BY `SMP_GLOBALID` DESC
* ) subOrdered
* ORDER BY subOrdered.`SMP_GLOBALID`
* var builder = new SqlStringBuilder(queryString);
* builder.
*/
// var returnList = new List<string>();
//
// DataTable dt = _jetDbConnection.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, "Employee" });
// int columnOrdinalForName = dt.Columns["COLUMN_NAME"].Ordinal;
//
// foreach (DataRow r in dt.Rows)
// {
// returnList.Add(r.ItemArray[columnOrdinalForName].ToString());
// }
var top = new SqlString(" top ", limit);
return(queryString.Insert(GetAfterSelectInsertPoint(queryString), 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()));
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
var top = new SqlString(" top (", limit, ")");
return(querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), top));
}
static void Main(string[] args)
{
//insert itemlist valuelist
var insertSql = new SqlString();
insertSql.Insert("userlist", new string[] { "username", "password", "enable", "time" }, new object[] { "admin", "a_secret", false, DateTime.Now });
Console.WriteLine(insertSql.result + "\n\r");
//delete
var deleteSql = new SqlString();
deleteSql.Delete("userlist");
deleteSql.Where("username", "hacker");
deleteSql.WhereAnd("password", "<script..");
Console.WriteLine(deleteSql.result + "\n\r");
//update
var updateSql = new SqlString();
updateSql.Update("userlist", new string[] { "username", "password", "enable", "time" }, new object[] { "admin", "a_secret", false, DateTime.Now });
updateSql.Where("username", "godman");
updateSql.WhereAnd("password", "bequiet0123");
Console.WriteLine(updateSql.result + "\n\r");
//select -> where ->whereand -> order
var selectSql0 = new SqlString();
selectSql0.Select(null, "userlist");
selectSql0.Where("username", "admin");
selectSql0.WhereAnd("password", "sercetX");
selectSql0.WhereAnd("isreal", true);
selectSql0.OrderBy(new OrderBy[] { new OrderBy("SignUpTime", Order.ASC), new OrderBy("followers", Order.DESC) });
Console.WriteLine(selectSql0.result + "\n\r");
//select -> where ->whereand -> order
var selectSql = new SqlString();
selectSql.Select(new string[] { "sex", "age" }, "userlist");
selectSql.Where("username", "admin");
selectSql.WhereAnd("password", "sercetX");
selectSql.WhereAnd("isreal", true);
selectSql.OrderBy(new OrderBy[] { new OrderBy("SignUpTime", Order.ASC), new OrderBy("followers", Order.DESC) });
Console.WriteLine(selectSql.result + "\n\r");
//select -> where ->whereand -> order
var selectSql2 = new SqlString();
selectSql2.SelectTop(10, new string[] { "sex", "age" }, "userlist");
selectSql2.Where("username", "admin");
selectSql2.WhereAnd("password", "sercetX");
selectSql2.WhereAnd("isreal", true);
selectSql2.OrderBy(new OrderBy[] { new OrderBy("SignUpTime", Order.ASC), new OrderBy("followers", Order.DESC) });
Console.WriteLine(selectSql2.result + "\n\r");
Console.Read();
}