C# SqlStringBuilder.Add方法代码示例

        public override SqlString Render(IList args, ISessionFactoryImplementor factory)
        {
            bool hasArgs = args.Count > 0;

            SqlStringBuilder buf = new SqlStringBuilder();
            buf.Add(name);
            if (hasArgs)
            {
                buf.Add("(");
                for (int i = 0; i < args.Count; i++)
                {
                    object arg = args[i];
                    if (arg is Parameter || arg is SqlString)
                    {
                        buf.AddObject(arg);
                    }
                    else
                    {
                        buf.Add(arg.ToString());
                    }
                    if (i < (args.Count - 1)) buf.Add(", ");
                }
                buf.Add(")");
            }
            return buf.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 GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
		{
			var tokenEnum = new SqlTokenizer(querySqlString).GetEnumerator();
			if (!tokenEnum.TryParseUntilFirstMsSqlSelectColumn()) return null;

			var result = new SqlStringBuilder(querySqlString);
			if (!tokenEnum.TryParseUntil("order"))
			{
				result.Add(" ORDER BY CURRENT_TIMESTAMP");
			}

			result.Add(" OFFSET ");
			if (offset != null)
			{
				result.Add(offset).Add(" ROWS");
			}
			else
			{
				result.Add("0 ROWS");
			}

			if (limit != null)
			{
				result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS ONLY");
			}

			return result.ToSqlString();
		}

        public SqlString Render(IList args, ISessionFactoryImplementor factory)
        {
            if (args.Count < 2)
            {
                throw new QueryException("coalesce() requires at least two arguments");
            }
            
            var sb = new SqlStringBuilder();

            sb.Add("Switch");
            sb.Add("(");
            for (int i = 0; i < args.Count; i++)
            {
                if (i>0)
                {
                    sb.Add(",");  
                }
                var arg = args[i];
                sb.Add("not IsNull(");
                sb.AddObject(arg);
                sb.Add(")");
                sb.Add(",");
                sb.AddObject(arg); 
            }

            sb.Add(",");  
            sb.Add("1=1");
            sb.Add(",");
            sb.AddObject(args[args.Count - 1]);

            sb.Add(")");

            return sb.ToSqlString();
        }

		protected SqlStringBuilder WhereString(string alias, string[] columnNames, IType type, SqlString subselect,
		                                       int batchSize)
		{
			if (subselect == null)
			{
				return base.WhereString(alias, columnNames, type, batchSize);
			}
			else
			{
				SqlStringBuilder buf = new SqlStringBuilder();
				if (columnNames.Length > 1)
				{
					buf.Add("(");
				}
				buf.Add(StringHelper.Join(", ", StringHelper.Qualify(alias, columnNames)));
				if (columnNames.Length > 1)
				{
					buf.Add(")");
				}
				buf.Add(" in ")
					.Add("(")
					.Add(subselect)
					.Add(")");
				return buf;
			}
		}

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

			IType propertyType = AbstractCriterion.GetType( factory, persistentClass, _propertyName, aliasClasses );
			string[ ] columnNames = AbstractCriterion.GetColumns( factory, persistentClass, _propertyName, alias, aliasClasses );
			Parameter[ ] parameters = Parameter.GenerateParameters( factory, columnNames, propertyType );

			if( columnNames.Length != 1 )
			{
				throw new HibernateException( "insensitive like may only be used with single-column properties" );
			}

			if( factory.Dialect is PostgreSQLDialect )
			{
				sqlBuilder.Add( columnNames[ 0 ] );
				sqlBuilder.Add( " ilike " );
			}
			else
			{
				sqlBuilder.Add( factory.Dialect.LowercaseFunction )
					.Add( "(" )
					.Add( columnNames[ 0 ] )
					.Add( ")" )
					.Add( " like " );
			}

			sqlBuilder.Add( parameters[ 0 ] );

			return sqlBuilder.ToSqlString();
		}

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

			if (offset != null)
			{
				result.Add(" OFFSET ").Add(offset).Add(" ROWS");
			}

			if (limit != null)
			{
				if (offset == null)
				{
					result.Add(" OFFSET 0 ROWS");

					// According to Oracle Docs: 
					// http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html
					// the 'limit' param must be 1 or higher, but we have situations with limit=0.
					// This leads to undetermined behaviour of Oracle DBMS. It seems that the query
					// was executed correctly but the execution takes pretty long time.
					//
					// Empirically estimated that adding 'OFFSET 0 ROWS' to these types of queries 
					// ensures much faster execution. Stated above is a kind of hack to fix 
					// described situation.
				}
				result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS ONLY");
			}

			return result.ToSqlString();
		}

		public SqlString Render(IList args, ISessionFactoryImplementor factory)
		{
			// TODO: QueryException if args.Count<2 (not present in H3.2) 
			bool threeArgs = args.Count > 2;
			object pattern = args[0];
			object orgString = args[1];
			object start = threeArgs ? args[2] : null;

			SqlStringBuilder buf = new SqlStringBuilder();
			buf.Add("charindex(")
				.AddObject(pattern)
				.Add(", ");
			if (threeArgs)
			{
				buf.Add("right(");
			}
			buf.AddObject(orgString);
			if (threeArgs)
			{
				buf.Add(", char_length(")
					.AddObject(orgString)
					.Add(")-(")
					.AddObject(start)
					.Add("-1))");
			}
			buf.Add(")");
			return buf.ToSqlString();
		}

		public SqlString Render(IList args, ISessionFactoryImplementor factory)
		{
			// TODO: QueryException if args.Count<2 (not present in H3.2) 
			bool threeArgs = args.Count > 2;
			object pattern = args[0];
			object orgString = args[1];
			object start = threeArgs ? args[2] : null;

			SqlStringBuilder buf = new SqlStringBuilder();
			if (threeArgs)
			{
				buf.Add("(case ");
				RenderPositionInSubstring(buf, pattern, orgString, start);
				buf.Add(" when 0 then 0 else (");
				RenderPositionInSubstring(buf, pattern, orgString, start);
				buf.Add("+(")
				   .AddObject(start)
				   .Add("-1)) end)");
			}
			else
			{
				buf.Add("charindex(")
				.AddObject(pattern)
				.Add(", ")
				.AddObject(orgString)
				.Add(")");
			}
			return buf.ToSqlString();
		}

		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
		{
			if (this.criteria.Count == 0)
			{
				return EmptyExpression;
			}

			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();

			sqlBuilder.Add("(");

			for (int i = 0; i < this.criteria.Count - 1; i++)
			{
				sqlBuilder.Add(this.criteria[i].ToSqlString(criteria, criteriaQuery, enabledFilters));
				sqlBuilder.Add(Op);
			}

			sqlBuilder.Add(this.criteria[this.criteria.Count - 1].ToSqlString(criteria, criteriaQuery, enabledFilters));


			sqlBuilder.Add(")");

			return sqlBuilder.ToSqlString();
		}

		public SqlString Render(IList args, ISessionFactoryImplementor factory)
		{
			// DONE: QueryException if args.Count<2 (not present in H3.2) 
			if (args.Count < 2)
			{
				throw new QueryException("position(): Not enough parameters (attended from 2 to 3).");
			}
			bool threeArgs = args.Count > 2;
			object pattern = args[0];
			object orgString = args[1];
			object start = threeArgs ? args[2] : null;

			SqlStringBuilder buf = new SqlStringBuilder();
			if (threeArgs)
			{
				buf.Add("(case ");
				RenderPositionInSubstring(buf, pattern, orgString, start);
				buf.Add(" when 0 then 0 else (");
				RenderPositionInSubstring(buf, pattern, orgString, start);
				buf.Add("+")
				   .AddObject(start)
				   .Add("-1) end)");
			}
			else
			{
				buf.Add("position(")
				.AddObject(pattern)
				.Add(" in ")
				.AddObject(orgString)
				.Add(")");
			}
			return buf.ToSqlString();
		}

		public override SqlString ToSqlString( ISessionFactoryImplementor factory, System.Type persistentClass, string alias, IDictionary aliasClasses )
		{
			if( _criteria.Count == 0 )
			{
				return new SqlString( "1=1" );
			}

			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();

			sqlBuilder.Add( "(" );

			for( int i = 0; i < _criteria.Count - 1; i++ )
			{
				sqlBuilder.Add(
					( ( ICriterion ) _criteria[ i ] ).ToSqlString( factory, persistentClass, alias, aliasClasses ) );
				sqlBuilder.Add( Op );
			}

			sqlBuilder.Add(
				( ( ICriterion ) _criteria[ _criteria.Count - 1 ] ).ToSqlString( factory, persistentClass, alias, aliasClasses ) );


			sqlBuilder.Add( ")" );

			return sqlBuilder.ToSqlString();
		}

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

		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);

			if (columnNames.Length != 1)
			{
				throw new HibernateException("insensitive like may only be used with single-column properties");
			}

			if (criteriaQuery.Factory.Dialect is PostgreSQLDialect)
			{
				sqlBuilder.Add(columnNames[0]);
				sqlBuilder.Add(" ilike ");
			}
			else
			{
				sqlBuilder.Add(criteriaQuery.Factory.Dialect.LowercaseFunction)
					.Add("(")
					.Add(columnNames[0])
					.Add(")")
					.Add(" like ");
			}

			sqlBuilder.AddParameter();

			return sqlBuilder.ToSqlString();
		}

		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
		{
			//Implementation changed from H3.2 to use SqlString
			string[] columns = criteriaQuery.GetIdentifierColumns(criteria);
			SqlStringBuilder result = new SqlStringBuilder(4 * columns.Length + 2);
			if (columns.Length > 1)
			{
				result.Add(StringHelper.OpenParen);
			}

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

				result.Add(columns[i])
					.Add(" = ");

				AddValueOrProjection(criteria, criteriaQuery, enabledFilters, result);
			}

			if (columns.Length > 1)
			{
				result.Add(StringHelper.ClosedParen);
			}
			return result.ToSqlString();
		}