C# SqlStringBuilder.Insert方法代码示例

		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
		{
			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();

			SqlString[] columnNames =
				CriterionUtil.GetColumnNames(_propertyName, _projection, criteriaQuery, criteria, enabledFilters);

			bool opNeeded = false;

			for (int i = 0; i < columnNames.Length; i++)
			{
				if (opNeeded)
				{
					sqlBuilder.Add(" or ");
				}
				opNeeded = true;

				sqlBuilder.Add(columnNames[i])
					.Add(" is not null");
			}

			if (columnNames.Length > 1)
			{
				sqlBuilder.Insert(0, "(");
				sqlBuilder.Add(")");
			}

			return sqlBuilder.ToSqlString();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="factory"></param>
		/// <param name="persistentClass"></param>
		/// <param name="alias"></param>
		/// <returns></returns>
		public override SqlString ToSqlString( ISessionFactoryImplementor factory, System.Type persistentClass, string alias, IDictionary aliasClasses )
		{
			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();

			string[ ] columnNames = AbstractCriterion.GetColumns( factory, persistentClass, _propertyName, alias, aliasClasses );

			for( int i = 0; i < columnNames.Length; i++ )
			{
				if( i > 0 )
				{
					sqlBuilder.Add( " and " );
				}

				sqlBuilder.Add( columnNames[ i ] )
					.Add( " is null" );
			}

			if( columnNames.Length > 1 )
			{
				sqlBuilder.Insert( 0, "(" );
				sqlBuilder.Add( ")" );
			}

			return sqlBuilder.ToSqlString();
		}

		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters)
		{
			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();

			string[] columnNames = criteriaQuery.GetColumnsUsingProjection(criteria, _propertyName);

			for (int i = 0; i < columnNames.Length; i++)
			{
				if (i > 0)
				{
					sqlBuilder.Add(" and ");
				}

				sqlBuilder.Add(columnNames[i])
					.Add(" is null");
			}

			if (columnNames.Length > 1)
			{
				sqlBuilder.Insert(0, "(");
				sqlBuilder.Add(")");
			}

			return sqlBuilder.ToSqlString();
		}

		public void InsertAndAdd()
		{
			SqlStringBuilder builder = new SqlStringBuilder();

			builder.Add("col1, col2 ");
			builder.Insert(0, "select ");
			builder.Add("from table ");

			Assert.AreEqual("select col1, col2 from table ", builder.ToSqlString().ToString());
		}

		public void RemoveAt() 
		{
			SqlStringBuilder builder = new SqlStringBuilder();

			builder.Add("   select * ");
			builder.Add("from table");
			Assert.AreEqual( "   select * from table", builder.ToSqlString().ToString() );

			builder.RemoveAt(0);
			Assert.AreEqual( "from table", builder.ToSqlString().ToString(), "Removed the first element in the SqlStringBuilder" );

			builder.Insert(0, "SELECT * ");
			Assert.AreEqual( "SELECT * from table", builder.ToSqlString().ToString() );
		}

		/// <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 ?"
			 */
			SqlStringBuilder pagingBuilder = new SqlStringBuilder();
			bool isInOrderBy = false;
			StringBuilder orderByStringBuilder = new StringBuilder();

			// build a new query and extract the order by part
			foreach( object sqlPart in querySqlString.SqlParts )
			{
				string sqlPartString = sqlPart as string;
				if( sqlPartString != null )
				{
					if( sqlPartString.ToLower( System.Globalization.CultureInfo.InvariantCulture ).TrimStart().StartsWith( "order by" ) )
					{
						isInOrderBy = true;
					}
				}

				if( isInOrderBy && sqlPart is string )
				{
					orderByStringBuilder.Append( ( string ) sqlPart );
				}
				else
				{
					pagingBuilder.AddObject( sqlPart );
				}
			}

			string rownumClause = "rownumber() over(" + orderByStringBuilder.ToString() + ") as rownum, ";
			// Add the rownum clause first, right after the original select
			pagingBuilder.Insert( 1, rownumClause );
			// Add the rest
			pagingBuilder.Insert( 0, "select * from (" );
			pagingBuilder.Add( ") as tempresult " );
			
			// Add the where clause
			pagingBuilder.Add( "where rownum " );

			if( hasOffset )
			{
				pagingBuilder.Add( "between " );
				pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) ).Add("+1");
				pagingBuilder.Add( " and " );
				pagingBuilder.Add( new Parameter( "p2", new Int32SqlType() ) );
			}
			else
			{
				pagingBuilder.Add( "<= " );
				pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) );
			}

			return pagingBuilder.ToSqlString();
		}

		/// <summary></summary>
		public SqlString ToFragmentString()
		{
			SqlStringBuilder buf = new SqlStringBuilder( values.Count * 5 );
			buf.Add( columnName );

			if( values.Count > 1 )
			{
				// is a comma needed before the value that's about to be added - it
				// defaults to false because we don't need a comma right away.
				bool commaNeeded = false;

				// if a "null" is in the list of values then we need to manipulate
				// the SqlString a little bit more at the end.
				bool allowNull = false;

				buf.Add( " in (" );
				for( int i = 0; i < values.Count; i++ )
				{
					if( Null.Equals( values[ i ] ) )
					{
						allowNull = true;
					}
					else if ( NotNull.Equals( values[ i ] ) )
					{
						throw new ArgumentOutOfRangeException( "not null makes no sense for in expression" ) ;
					}
					else
					{
						if( commaNeeded )
						{
							buf.Add( StringHelper.CommaSpace );
						}
						buf.Add( ( string ) values[ i ] );

						// a value has been added into the IN clause so the next
						// one needs a comma before it
						commaNeeded = true;
					}
				}

				buf.Add( StringHelper.ClosedParen );

				// if "null" is in the list of values then add to the beginning of the
				// SqlString "is null or [column] (" + [rest of sqlstring here] + ")"
				if( allowNull )
				{
					buf.Insert( 0, " is null or " )
						.Insert( 0, columnName )
						.Insert( 0, StringHelper.OpenParen )
						.Add( StringHelper.ClosedParen );
				}
			}
			else
			{
				object value = values[ 0 ];
				if( Null.Equals( value ) )
				{
					buf.Add( " is null" );
				}
				else if ( NotNull.Equals( value ) )
				{
					buf.Add( " is not null " );
				}
				else
				{
					buf.Add( "=" + values[ 0 ] );
				}
			}
			return buf.ToSqlString();
		}