/// <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(); }