C# SqlCommand.SqlStringBuilder類代碼示例

        public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            var sqlBuilder = new SqlStringBuilder();
            SqlString[] columnNames = null;

            if (_propertyName != "*")
            {
                columnNames = CriterionUtil.GetColumnNames(_propertyName, _projection, criteriaQuery, criteria, enabledFilters);

                if (columnNames.Length != 1)
                {
                    throw new HibernateException("Contains may only be used with single-column properties");
                }
            } else
            {
                columnNames = new SqlString[]
                                  {
                                      new SqlString("*")
                                  };
            }

            sqlBuilder.Add("contains(")
              .Add(columnNames[0])
              .Add(",");

            sqlBuilder.Add(criteriaQuery.NewQueryParameter(GetParameterTypedValue(criteria, criteriaQuery)).Single());
            sqlBuilder.Add(")");

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

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

		public SqlString ToSubselectString(string ukname)
		{
			string[] joinColumns = ukname == null
			                       	? StringHelper.Qualify(alias, loadable.IdentifierColumnNames)
			                       	: ((IPropertyMapping) loadable).ToColumns(alias, ukname);

			SqlString sqlString = new SqlStringBuilder()
				.Add("select ")
				.Add(StringHelper.Join(", ", joinColumns))
				.Add(queryString)
				.ToSqlString();

			RowSelection selection = queryParameters.RowSelection;

			bool useLimit = Loader.Loader.UseLimit(selection, dialect);
			bool hasFirstRow = Loader.Loader.GetFirstRow(selection) > 0;
			bool useOffset = hasFirstRow && useLimit && dialect.SupportsLimitOffset;

			if ((useLimit || hasFirstRow) == false)
				return sqlString;

			sqlString = AppendOrderByIfNeeded(sqlString);

			return dialect.GetLimitString(sqlString.Trim(),
										  useOffset ? Loader.Loader.GetFirstRow(selection) : 0,
										  Loader.Loader.GetMaxOrLimit(dialect, selection));
		}

		/// <summary>
		/// Converts the ColumnNames and ColumnValues to a WhereFragment
		/// </summary>
		/// <param name="tableAlias">The Alias for the Table.</param>
		/// <param name="columnNames">The names of the Columns to Add to the WhereFragment</param>
		/// <param name="op">The operator to use between the names &amp; values.  For example " = " or "!="</param>
		/// <returns>A SqlString that contains the WhereFragment</returns>
		protected SqlString ToWhereString(string tableAlias, string[] columnNames, string op)
		{
			SqlStringBuilder sqlBuilder = new SqlStringBuilder((columnNames.Length * 2) + 5);

			bool andNeeded = false;

			for (int i = 0; i < columnNames.Length; i++)
			{
				if (string.IsNullOrEmpty(columnNames[i])) continue;// prevent empty column name

				if (andNeeded)
				{
					sqlBuilder.Add(" AND ");
				}
				andNeeded = true;

				string columnName;
				if (tableAlias != null && tableAlias.Length > 0)
				{
					columnName = tableAlias + StringHelper.Dot + columnNames[i];
				}
				else
				{
					columnName = columnNames[i];
				}

				sqlBuilder
					.Add(columnName)
					.Add(op)
					.AddParameter();
			}

			return sqlBuilder.ToSqlString();
		}

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

            //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(criteria, criteriaQuery, enabledFilters));
                sqlBuilder.Add(Op);
            }

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

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

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

 /// <summary>
 /// Applies the template to passed in arguments.
 /// </summary>
 /// <param name="args">args function arguments</param>
 /// <param name="factory">generated SQL function call</param>
 /// <returns></returns>
 public SqlString Render(IList args, ISessionFactoryImplementor factory)
 {
     SqlStringBuilder buf = new SqlStringBuilder();
     foreach (TemplateChunk tc in chunks)
     {
         if (tc.ArgumentIndex != InvalidArgumentIndex)
         {
             int adjustedIndex = tc.ArgumentIndex - 1; // Arg indices are one-based
             object arg = adjustedIndex < args.Count ? args[adjustedIndex] : null;
             // TODO: if (arg == null) QueryException is better ?
             if (arg != null)
             {
                 if (arg is Parameter || arg is SqlString)
                 {
                     buf.AddObject(arg);
                 }
                 else
                 {
                     buf.Add(arg.ToString());
                 }
             }
         }
         else
         {
             buf.Add(tc.Text);
         }
     }
     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, int position, ICriteriaQuery criteriaQuery,
		                                      IDictionary<string, IFilter> enabledFilters)
		{
			ISQLFunction sqlFunction = GetFunction(criteriaQuery);
			List<string> tokens = new List<string>();
			string replacemenToken = Guid.NewGuid().ToString("n");
			for (int i = 0; i < args.Length; i++)
			{
				tokens.Add(replacemenToken);
			}
			string functionStatement = sqlFunction.Render(tokens, criteriaQuery.Factory).ToString();
			string[] splitted = functionStatement.Split(new string[] {replacemenToken}, StringSplitOptions.RemoveEmptyEntries);

			SqlStringBuilder sb = new SqlStringBuilder();
			for (int i = 0; i < splitted.Length; i++)
			{
				sb.Add(splitted[i]);
				if (i < args.Length)
				{
					int loc = (position + 1) * 1000 + i;
					SqlString projectArg = GetProjectionArgument(criteriaQuery, criteria, args[i], loc, enabledFilters);
					sb.Add(projectArg);
				}
			}
			sb.Add(" as ");
			sb.Add(GetColumnAliases(position)[0]);
			return sb.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 SqlString Render(IList args, ISessionFactoryImplementor factory)
		{
			//ANSI-SQL92 definition
			//<general set function> ::=
			//<set function type> <leftparen> [ <setquantifier> ] <value expression> <right paren>
			//<set function type> : := AVG | MAX | MIN | SUM | COUNT
			//<setquantifier> ::= DISTINCT | ALL

			if (args.Count < 1 || args.Count > 2)
			{
				throw new QueryException(string.Format("Aggregate {0}(): Not enough parameters (attended from 1 to 2).", name));
			}
			else if ("*".Equals(args[args.Count - 1]) && !acceptAsterisk)
			{
				throw new QueryException(string.Format("Aggregate {0}(): invalid argument '*'.", name));
			}
			SqlStringBuilder cmd = new SqlStringBuilder();
			cmd.Add(name)
				.Add("(");
			if (args.Count > 1)
			{
				object firstArg = args[0];
				if (!StringHelper.EqualsCaseInsensitive("distinct", firstArg.ToString()) &&
				    !StringHelper.EqualsCaseInsensitive("all", firstArg.ToString()))
				{
					throw new QueryException(string.Format("Aggregate {0}(): token unknow {1}.", name, firstArg));
				}
				cmd.AddObject(firstArg).Add(" ");
			}
			cmd.AddObject(args[args.Count - 1])
				.Add(")");
			return cmd.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();
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="SubselectClauseExtractor"/> class.
		/// </summary>
		/// <param name="sql">The <see cref="SqlString" /> to extract the subselect clause from.</param>
		public SubselectClauseExtractor(SqlString sql)
		{
			builder = new SqlStringBuilder(sql.Count);
			this.sql = sql;
			lastOrderByIndex = -1;
			lastOrderByPartIndex = -1;
		}

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