C# SqlString.Substring方法代码示例

        public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
        {
            SqlStringBuilder result = new SqlStringBuilder();

            if (offset == null)
            {
                int insertPoint = GetAfterSelectInsertPoint(queryString);

                return result
                    .Add(queryString.Substring(0, insertPoint))
                    .Add(" TOP (")
                    .Add(limit)
                    .Add(") ")
                    .Add(queryString.Substring(insertPoint))
                    .ToSqlString();
            }

            return base.GetLimitString(queryString, offset, limit);
        }

		public void Split()
		{
			SqlString sql = new SqlString(new string[] { "select", " alfa, beta, gamma", " from table" });
			var parts1 = sql.Split(",").Select(s => s.ToString()).ToArray();
			var expectedParts1 = new[] { "select alfa" , " beta", " gamma from table" };
			Assert.That(parts1, Is.EqualTo(expectedParts1));

			SqlString sql2 = sql.Substring(6);
			sql2.Compact();
			var parts2 = sql2.Split(",").Select(s => s.ToString()).ToArray();
			var expectedParts2 = new[] { " alfa", " beta", " gamma from table" };
			Assert.That(parts2, Is.EqualTo(expectedParts2));
		}

		public void HashcodeEqualForEqualStringsWithDifferentHistory()
		{
			// Verify that sql strings that are generated in different ways, but _now_ have
			// equal content, also have equal hashcodes.

			SqlString sql = new SqlString(new string[] { "select", " from table" });
			sql = sql.Substring(6);

			SqlString sql2 = new SqlString(new string[] { " from table" });

			Assert.That(sql, Is.EqualTo(sql2));
			Assert.That(sql.GetHashCode(), Is.EqualTo(sql2.GetHashCode()));
		}

		public void Substring2Complex()
		{
			SqlString str =
				new SqlString(new object[] { "select ", Parameter.Placeholder, " from table where x = ", Parameter.Placeholder });

			SqlString substr7_10 = str.Substring(7, 10);
			Assert.AreEqual(new SqlString(new object[] { Parameter.Placeholder, " from tab" }), substr7_10);

			SqlString substr10_200 = str.Substring(10, 200);
			Assert.AreEqual(new SqlString(new object[] { "rom table where x = ", Parameter.Placeholder }), substr10_200);

			SqlString substr200_10 = str.Substring(200, 10);
			Assert.AreEqual(SqlString.Empty, substr200_10);
		}

		/// <summary>
		/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
		/// </summary>
		/// <param name="querySqlString">A Query in the form of a SqlString.</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>
		/// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param>
		/// <param name="limitParameterIndex">Optionally, the Limit parameter index</param>
		/// <returns>A new <see cref="SqlString"/> that contains the <c>LIMIT</c> clause.</returns>
		public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex)
		{
			/*
		     * "select * from (select row_number() over(orderby_clause) as rownum, "
			 * querySqlString_without select
			 * " ) as tempresult where rownum between ? and ?"
			 */
			string rownumClause = GetRowNumber(querySqlString);

			SqlStringBuilder pagingBuilder = new SqlStringBuilder();
			pagingBuilder
				.Add("select * from (select ")
				.Add(rownumClause)
				.Add(querySqlString.Substring(7))
				.Add(") as tempresult where rownum ");

			if (offset > 0)
			{
				pagingBuilder
					.Add("between ")
					.Add(Parameter.WithIndex(offsetParameterIndex.Value))
					.Add("+1 and ")
					.Add(Parameter.WithIndex(limitParameterIndex.Value));
			}
			else
			{
				pagingBuilder
					.Add("<= ")
					.Add(Parameter.WithIndex(limitParameterIndex.Value));
			}

			return pagingBuilder.ToSqlString();
		}

		public static SqlString RemoveAsAliasesFromSql(SqlString sql)
		{
			return sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as "));
		}

		/// <summary>
		/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
		/// </summary>
		/// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off.</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>
		/// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param>
		/// <param name="limitParameterIndex">Optionally, the Limit parameter index</param>
		/// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
		/// <remarks>
		/// Note that we need to explicitly specify the columns, because we need to be able to use them in a paged subselect [NH-1155]
		/// </remarks>
		public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex)
		{
			SqlStringBuilder result = new SqlStringBuilder();
						
			if (offset == 0)
			{
				int insertPoint = this.GetAfterSelectInsertPoint(querySqlString);
				
				return result
					.Add(querySqlString.Substring(0, insertPoint))
					.Add(" TOP (")
					.Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
					.Add(")")
					.Add(querySqlString.Substring(insertPoint))
					.ToSqlString();
			}

			int fromIndex = GetFromIndex(querySqlString);
			SqlString select = querySqlString.Substring(0, fromIndex);

			List<SqlString> columnsOrAliases;
			Dictionary<SqlString, SqlString> aliasToColumn;
			ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
		
			int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
			SqlString fromAndWhere;
			SqlString[] sortExpressions;

			//don't use the order index if it is contained within a larger statement(assuming 
			//a statement with non matching parenthesis is part of a larger block)
			if (orderIndex > 0 && HasMatchingParens(querySqlString.Substring(orderIndex).ToString()))
			{
				fromAndWhere = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
				SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
				sortExpressions = orderBy.Substring(9).Split(",");
			}
			else
			{
				fromAndWhere = querySqlString.Substring(fromIndex).Trim();
				// Use dummy sort to avoid errors
				sortExpressions = new[] {new SqlString("CURRENT_TIMESTAMP"),};
			}				
				
			result
				.Add("SELECT TOP (")
				.Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
				.Add(") ")
				.Add(StringHelper.Join(", ", columnsOrAliases))
				.Add(" FROM (")
				.Add(select)
				.Add(", ROW_NUMBER() OVER(ORDER BY ");
			
			AppendSortExpressions(aliasToColumn, sortExpressions, result);

			result
				.Add(") as __hibernate_sort_row ")
				.Add(fromAndWhere)
				.Add(") as query WHERE query.__hibernate_sort_row > ")
				.Add(offsetParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(offsetParameterIndex.Value))
				.Add(" ORDER BY query.__hibernate_sort_row");
			
			return result.ToSqlString();
		}

		public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
		{
			// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
			// for LIMIT/OFFSET support.  Does not support a limit of zero.

			// FIXME - Add support for where offset is set, but limit is not.

			int insertionPoint = GetAfterSelectInsertPoint(sql);

			if (insertionPoint > 0)
			{
				SqlStringBuilder limitBuilder = new SqlStringBuilder();
				limitBuilder.Add("select");
				if (insertionPoint > 6)
				{
					limitBuilder.Add(" distinct ");
				}
				limitBuilder.Add(" top ");
				limitBuilder.Add(limit);
				if (offset != null)
				{
					limitBuilder.Add(" start at ");
					limitBuilder.Add(offset);
				}
				limitBuilder.Add(sql.Substring(insertionPoint));
				return limitBuilder.ToSqlString();
			}
			else
			{
				return sql; // unchanged
			}
		}

		/// <summary>
		/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
		/// </summary>
		/// <param name="querySqlString">A Query in the form of a SqlString.</param>
		/// <param name="hasOffset">Offset of the first row is not zero</param>
		/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
		public override SqlString GetLimitString(SqlString querySqlString, bool hasOffset)
		{
			/*
		     * "select * from (select row_number() over(orderby_clause) as rownum, "
			 * querySqlString_without select
			 * " ) as tempresult where rownum between ? and ?"
			 */
			string rownumClause = GetRowNumber(querySqlString);

			SqlStringBuilder pagingBuilder = new SqlStringBuilder();
			pagingBuilder
				.Add("select * from (select ")
				.Add(rownumClause)
				.Add(querySqlString.Substring(7))
				.Add(") as tempresult where rownum ");

			if (hasOffset)
			{
				pagingBuilder
					.Add("between ")
					.Add(Parameter.Placeholder)
					.Add("+1 and ")
					.Add(Parameter.Placeholder);
			}
			else
			{
				pagingBuilder
					.Add("<= ")
					.Add(Parameter.Placeholder);
			}

			return pagingBuilder.ToSqlString();
		}

		public static SqlString RemoveAsAliasesFromSql(SqlString sql)
		{
			int index = sql.LastIndexOfCaseInsensitive(" as ");
			if (index < 0) return sql;
			return sql.Substring(0, index);
		}

		public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
		{
			sql = sql.Trim();
			bool isForUpdate = false;
			if (sql.EndsWithCaseInsensitive(" for update"))
			{
				sql = sql.Substring(0, sql.Length - 11);
				isForUpdate = true;
			}

			string selectColumns = ExtractColumnOrAliasNames(sql);

			var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10);
			if (offset != null)
			{
				pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( ");
			}
			else
			{
				pagingSelect.Add("select " + selectColumns + " from ( ");
			}
			pagingSelect.Add(sql);
			if (offset != null && limit != null)
			{
				pagingSelect.Add(" ) row_ where rownum <=").Add(limit).Add(") where rownum_ >").Add(offset);
			}
			else if (limit != null)
			{
				pagingSelect.Add(" ) where rownum <=").Add(limit);
			}
			else
			{
				// offset is specified, but limit is not.
				pagingSelect.Add(" ) row_ ) where rownum_ >").Add(offset);
			}

			if (isForUpdate)
			{
				pagingSelect.Add(" for update");
			}

			return pagingSelect.ToSqlString();
		}

		/// <summary>
		/// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
		/// for LIMIT/OFFSET support.
		///
		/// Produce a parametertized SQL query using positional parameters for
		/// TOP and START AT (if specified).
		/// </summary>
		public override SqlString GetLimitString(SqlString sql, bool hasOffset)
		{
			int insertionPoint = GetAfterSelectInsertPoint(sql);
			
			if (insertionPoint > 0)
			{
				SqlStringBuilder limitBuilder = new SqlStringBuilder();
				limitBuilder.Add("select");
				if (insertionPoint > 6)
				{
					limitBuilder.Add(" distinct ");
				}
				limitBuilder.Add(" top ");
				limitBuilder.Add(Parameter.Placeholder);
				if (hasOffset)
				{
					limitBuilder.Add(" start at ");
					limitBuilder.Add(Parameter.Placeholder);
				}
				limitBuilder.Add(sql.Substring(insertionPoint));
				return limitBuilder.ToSqlString();
			}
			else
			{
				return sql; // unchanged
			}
		}

      /// <summary>
      /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
      /// </summary>
      /// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
      /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
      /// <param name="last">Maximum number of rows to be returned by the query</param>
      /// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
      /// <remarks>
      /// The <c>LIMIT</c> SQL will look like
      /// <code>
      ///
      /// SELECT TOP last * FROM (
      /// SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ {sort direction 1} [, __hibernate_sort_expr_2__ {sort direction 2}, ...]) as row, query.* FROM (
      ///      {original select query part}, {sort field 1} as __hibernate_sort_expr_1__ [, {sort field 2} as __hibernate_sort_expr_2__, ...]
      ///      {remainder of original query minus the order by clause}
      /// ) query
      /// ) page WHERE page.row > offset
      ///
      /// </code>
      /// </remarks>
      public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
      {

          int parenthesis = 0;

          int fromIndex = 0;

          string querystring = querySqlString.ToString();

          string currentchar;

          int querystringlength = querystring.Length;

          for (int i = 0; i < querystringlength; i++)
          {
              currentchar = querystring.Substring(i, 1);

              //if ( ((i + 6) <= querystring.Length) && (querystring.Substring(i, 6).ToLower() == " from ") && (parenthesis == 0))
              if (((i + 6) <= querystringlength) && (querystring.Substring(i, 6).IndexOf(" from ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
              {
                  fromIndex = i;
                  break;
              }
              if (currentchar == "(")
                  parenthesis++;
              if (currentchar == ")")
                  parenthesis--;

          }


         SqlString select = querySqlString.Substring(0, fromIndex);

         int orderIndex = 0;

         querystringlength = querystring.Length;

         for (int i = querystringlength - 1; i >= 0; i--)
         {
             currentchar = querystring.Substring(i, 1);
             if (((i + 10) <= querystringlength) && (querystring.Substring(i, 10).IndexOf(" order by ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
             {
                 orderIndex = i;
                 break;
             }
             if (currentchar == "(")
                 parenthesis++;
             if (currentchar == ")")
                 parenthesis--;

         }


         SqlString from;
         string[] sortExpressions;
         if (orderIndex > 0)
         {
            from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
            string orderBy = querySqlString.Substring(orderIndex).ToString().Trim();
            //sortExpressions = orderBy.Substring(9).Split(',');
            sortExpressions = getSortExpression(orderBy.Substring(9));
         }
         else
         {
            from = querySqlString.Substring(fromIndex).Trim();
            // Use dummy sort to avoid errors
            sortExpressions = new string[] { "CURRENT_TIMESTAMP" };
         }

         SqlStringBuilder result = new SqlStringBuilder()
            .Add("SELECT TOP ")
            .Add(last.ToString())
            .Add(" * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ");

         for (int i = 1; i <= sortExpressions.Length; i++)
         {
            if (i > 1)
               result.Add(", ");

            result.Add("__hibernate_sort_expr_")
//.........这里部分代码省略.........

		public override SqlString GetLimitString(SqlString sql, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex)
		{
			sql = sql.Trim();
			bool hasOffset = offset > 0;
			bool isForUpdate = false;
			if (sql.EndsWithCaseInsensitive(" for update"))
			{
				sql = sql.Substring(0, sql.Length - 11);
				isForUpdate = true;
			}

			var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10);
			if (hasOffset)
			{
				pagingSelect.Add("select * from ( select row_.*, rownum rownum_ from ( ");
			}
			else
			{
				pagingSelect.Add("select * from ( ");
			}
			pagingSelect.Add(sql);
			if (hasOffset)
			{
				pagingSelect.Add(" ) row_ where rownum <=").AddParameter(limitParameterIndex.Value).Add(") where rownum_ >").AddParameter(offsetParameterIndex.Value);
			}
			else
			{
				pagingSelect.Add(" ) where rownum <=").AddParameter(limitParameterIndex.Value);
			}

			if (isForUpdate)
			{
				pagingSelect.Add(" for update");
			}

			return pagingSelect.ToSqlString();
		}

		/// <summary>
		/// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
		/// for LIMIT/OFFSET support.
		///
		/// Produce a parametertized SQL query using positional parameters for
		/// TOP and START AT (if specified).
		/// </summary>
		public override SqlString GetLimitString( SqlString sql, bool hasOffset )
		{
			int InsertionPoint = GetAfterSelectInsertPoint( sql );
			
			if ( InsertionPoint > 0 )
			{
				SqlStringBuilder LimitBuilder = new SqlStringBuilder();
				LimitBuilder.Add( "SELECT" );
				if ( InsertionPoint > 6 ) 
				{
					LimitBuilder.Add( " DISTINCT " );
				}
				LimitBuilder.Add( " TOP ");
				LimitBuilder.Add( Parameter.Placeholder );
				if ( hasOffset )
				{
					LimitBuilder.Add( " START AT ");
					LimitBuilder.Add( Parameter.Placeholder );
				}
				LimitBuilder.Add( sql.Substring( InsertionPoint ) );
				return LimitBuilder.ToSqlString();
			}
			else
			{
				return sql; // unchanged
			}
		}