/// <summary>
/// Return customized limit string (for paging queries)
/// For performance reason the limit string is ommitted when querying the first page.
/// </summary>
/// <param name="querySqlString"></param>
/// <param name="offset"></param>
/// <param name="last"></param>
/// <returns></returns>
public override NHibernate.SqlCommand.SqlString GetLimitString(NHibernate.SqlCommand.SqlString querySqlString, int offset, int last)
{
if (!querySqlString.StartsWithCaseInsensitive("select "))
{
throw new ArgumentException("querySqlString should start with select", "querySqlString");
}
SqlString sqlString = querySqlString.Substring(6);
string orderSql = querySqlString.SubstringStartingWithLast("order by").ToString();
if (orderSql.Length != 0)
{
sqlString = sqlString.Substring(0, (sqlString.Length - orderSql.Length) - 1);
}
SqlStringBuilder builder = new SqlStringBuilder();
int num = offset + 1;
builder.Add("SELECT TOP ").Add(last.ToString()).Add(" ").Add(sqlString);
if (offset > 0)
{
builder.Add(" WITH FIRSTROW ").Add(num.ToString());
}
if (orderSql.Length > 0)
{
builder.Add(" ").Add(orderSql);
}
return(builder.ToSqlString());
}
private void InitStatementString(IOuterJoinLoadable elementPersister, string alias, int batchSize, SqlString subquery)
{
int joins = CountEntityPersisters(associations);
Suffixes = BasicLoader.GenerateSuffixes(joins + 1);
int collectionJoins = CountCollectionPersisters(associations) + 1;
CollectionSuffixes = BasicLoader.GenerateSuffixes(joins + 1, collectionJoins);
SqlStringBuilder whereString = WhereString(alias, oneToManyPersister.KeyColumnNames, subquery, batchSize);
string filter = oneToManyPersister.FilterFragment(alias, EnabledFilters);
whereString.Insert(0, StringHelper.MoveAndToBeginning(filter));
JoinFragment ojf = MergeOuterJoins(associations);
SqlSelectBuilder select =
new SqlSelectBuilder(Factory).SetSelectClause(
oneToManyPersister.SelectFragment(null, null, alias, Suffixes[joins], CollectionSuffixes[0], true)
+ SelectString(associations)).SetFromClause(elementPersister.FromTableFragment(alias)
+ elementPersister.FromJoinFragment(alias, true, true)).SetWhereClause(
whereString.ToSqlString()).SetOuterJoins(ojf.ToFromFragmentString,
ojf.ToWhereFragmentString
+ elementPersister.WhereJoinFragment(alias, true, true));
select.SetOrderByClause(OrderBy(associations, oneToManyPersister.GetSQLOrderByString(alias)));
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("load one-to-many " + oneToManyPersister.Role);
}
SqlString = select.ToSqlString();
}
public void GetLimitString_NoOffsetSpecified_UsesFetchFirstOnly()
{
// arrange
DB2Dialect dialect = new DB2Dialect();
SqlString sql = new SqlString(
new object[]
{
"select a, b, c ",
"from d",
" where X = ",
Parameter.Placeholder,
" and Z = ",
Parameter.Placeholder,
" order by a, x"
});
// act
SqlString limited = dialect.GetLimitString(sql, null, new SqlString("222"));
// assert
Assert.AreEqual(
"select a, b, c from d where X = ? and Z = ? order by a, x fetch first 222 rows only",
limited.ToString());
Assert.AreEqual(2, limited.GetParameterCount());
}
/// <summary>
/// Converts the given SQLException into Exception hierarchy, as well as performing
/// appropriate logging.
/// </summary>
/// <param name="converter">The converter to use.</param>
/// <param name="sqlException">The exception to convert.</param>
/// <param name="message">An optional error message.</param>
/// <param name="sql">The SQL executed.</param>
/// <returns> The converted <see cref="ADOException"/>.</returns>
public static Exception Convert(ISQLExceptionConverter converter, Exception sqlException, string message,
SqlString sql)
{
return Convert(converter,
new AdoExceptionContextInfo
{SqlException = sqlException, Message = message, Sql = sql != null ? sql.ToString() : null});
}
public BasicCollectionJoinWalker(
IQueryableCollection collectionPersister,
int batchSize,
SqlString subquery,
ISessionFactoryImplementor factory,
IDictionary enabledFilters)
: base(factory, enabledFilters)
{
this.collectionPersister = collectionPersister;
string alias = GenerateRootAlias(collectionPersister.Role);
WalkCollectionTree(collectionPersister, alias);
IList allAssociations = new ArrayList();
ArrayHelper.AddAll(allAssociations, associations);
allAssociations.Add(new OuterJoinableAssociation(
collectionPersister.CollectionType,
null,
null,
alias,
JoinType.LeftOuterJoin,
Factory,
enabledFilters
));
InitPersisters(allAssociations, LockMode.None);
InitStatementString(alias, batchSize, subquery);
}
public override void AddJoin(string tableName, string alias, string[] fkColumns, string[] pkColumns, JoinType joinType,
SqlString on)
{
string joinString;
switch (joinType)
{
case JoinType.InnerJoin:
joinString = " inner join ";
break;
case JoinType.LeftOuterJoin:
joinString = " left outer join ";
break;
case JoinType.RightOuterJoin:
joinString = " right outer join ";
break;
case JoinType.FullJoin:
joinString = " full outer join ";
break;
default:
throw new AssertionFailure("undefined join type");
}
_fromFragment.Add(joinString + tableName + ' ' + alias + " on ");
for (int j = 0; j < fkColumns.Length; j++)
{
_fromFragment.Add(fkColumns[j] + "=" + alias + StringHelper.Dot + pkColumns[j]);
if (j < fkColumns.Length - 1)
{
_fromFragment.Add(" and ");
}
}
AddCondition(_fromFragment, on);
}
public static string ExtendMessage(string message, SqlString sql, object[] parameterValues, IDictionary namedParameters)
{
StringBuilder sb = new StringBuilder();
sb.Append(message).Append(Environment.NewLine).
Append("[ ").Append(sql).Append(" ]");
if (parameterValues != null && parameterValues.Length > 0)
{
sb.Append(Environment.NewLine).Append("Positional parameters: ");
int index = 0;
foreach (object parameterValue in parameterValues)
{
object value = parameterValue;
if (value == null)
value = "null";
sb.Append(" #").Append(index).Append(">").Append(value);
}
}
if (namedParameters != null && namedParameters.Count > 0)
{
sb.Append(Environment.NewLine);
foreach (DictionaryEntry namedParameter in namedParameters)
{
object value = namedParameter.Value;
if (value == null)
value = "null";
sb.Append(" ").Append("Name:").Append(namedParameter.Key)
.Append(" - Value:").Append(value);
}
}
sb.Append(Environment.NewLine);
return sb.ToString();
}
public SequenceStructure(Dialect.Dialect dialect, string sequenceName, int initialValue, int incrementSize)
{
_sequenceName = sequenceName;
_initialValue = initialValue;
_incrementSize = incrementSize;
_sql = new SqlString(dialect.GetSequenceNextValString(sequenceName));
}
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;
}
}
protected void InitAll(
SqlString whereString,
string orderByString,
LockMode lockMode)
{
WalkEntityTree(persister, Alias);
IList allAssociations = new ArrayList();
foreach (object obj in associations)
{
allAssociations.Add(obj);
}
allAssociations.Add(
new OuterJoinableAssociation(
persister.EntityType,
null,
null,
alias,
JoinType.LeftOuterJoin,
Factory,
CollectionHelper.EmptyMap
));
InitPersisters(allAssociations, lockMode);
InitStatementString(whereString, orderByString, lockMode);
}
public override IDataReader GetReader(int? commandTimeout)
{
var batcher = Session.Batcher;
SqlType[] sqlTypes = Commands.SelectMany(c => c.ParameterTypes).ToArray();
ForEachSqlCommand((sqlLoaderCommand, offset) => sqlLoaderCommand.ResetParametersIndexesForTheCommand(offset));
_sqlString = _sqlString.Insert(0, "\nBEGIN\n").Append("\nEND;\n");
var command = batcher.PrepareQueryCommand(CommandType.Text, _sqlString, sqlTypes);
if (commandTimeout.HasValue)
{
command.CommandTimeout = commandTimeout.Value;
}
BindParameters(command);
for (int cursorIndex = 0; cursorIndex < _cursorCount; cursorIndex++)
{
IDbDataParameter outCursor = command.CreateParameter();
_oracleDbType.SetValue(outCursor, _oracleDbTypeRefCursor, null);
outCursor.ParameterName = ":cursor" + Convert.ToString(cursorIndex);
outCursor.Direction = ParameterDirection.Output;
command.Parameters.Add(outCursor);
}
return new BatcherDataReaderWrapper(batcher, command);
}
/// <summary>
/// Returns a TSQL SELECT statement that will - when executed - return a 'page' of results.
/// </summary>
/// <param name="offset"></param>
/// <param name="limit"></param>
/// <returns></returns>
public SqlString PageBy(SqlString offset, SqlString limit)
{
if (offset == null)
return PageByLimitOnly(limit);
return PageByLimitAndOffset(offset, limit);
}
public override void AddJoin(string tableName, string alias, string[] fkColumns, string[] pkColumns, JoinType joinType,
SqlString on)
{
//arbitrary on clause ignored!!
AddJoin(tableName, alias, fkColumns, pkColumns, joinType);
AddCondition(on);
}
/// <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 SubselectOneToManyLoader(IQueryableCollection persister, SqlString subquery, ICollection<EntityKey> entityKeys,
QueryParameters queryParameters,
ISessionFactoryImplementor factory, IDictionary<string, IFilter> enabledFilters)
: base(persister, BatchSizeForSubselectFetching, factory, enabledFilters)
{
keys = new object[entityKeys.Count];
int i = 0;
foreach (EntityKey entityKey in entityKeys)
{
keys[i++] = entityKey.Identifier;
}
// NH Different behavior: to deal with positionslParameter+NamedParameter+ParameterOfFilters
namedParameters = new Dictionary<string, TypedValue>(queryParameters.NamedParameters);
parametersSpecifications = queryParameters.ProcessedSqlParameters.ToList();
var processedRowSelection = queryParameters.ProcessedRowSelection;
SqlString finalSubquery = subquery;
if (queryParameters.ProcessedRowSelection != null && !SubselectClauseExtractor.HasOrderBy(queryParameters.ProcessedSql))
{
// when the original query has an "ORDER BY" we can't re-apply the pagination.
// This is a simplification, we should actually check which is the "ORDER BY" clause because when the "ORDER BY" is just for the PK we can re-apply "ORDER BY" and pagination.
finalSubquery = GetSubSelectWithLimits(subquery, parametersSpecifications, processedRowSelection, namedParameters);
}
InitializeFromWalker(persister, finalSubquery, BatchSizeForSubselectFetching, enabledFilters, factory);
types = queryParameters.PositionalParameterTypes;
values = queryParameters.PositionalParameterValues;
}
public void Format(SqlString text)
{
DetermineNumberOfPreceedingParametersForEachQuery(text);
foundReturnParameter = false;
parameterIndex = text.Parts.OfType<Parameter>().Select(x => x.ParameterPosition ?? -1).Union(new[] { -1 }).Max();
text.Visit(this);
}
protected void InitProjection(SqlString projectionString, SqlString whereString,
string orderByString, string groupByString, LockMode lockMode)
{
WalkEntityTree(persister, Alias);
Persisters = new ILoadable[0];
InitStatementString(projectionString, whereString, orderByString, groupByString, lockMode);
}
private void InitStatementString(SqlString projection,SqlString condition,
string orderBy,string groupBy,LockMode lockMode)
{
int joins = CountEntityPersisters(associations);
Suffixes = BasicLoader.GenerateSuffixes(joins + 1);
JoinFragment ojf = MergeOuterJoins(associations);
SqlString selectClause = projection
??
new SqlString(persister.SelectFragment(alias, Suffixes[joins]) + SelectString(associations));
SqlSelectBuilder select = new SqlSelectBuilder(Factory)
.SetLockMode(lockMode)
.SetSelectClause(selectClause)
.SetFromClause(Dialect.AppendLockHint(lockMode, persister.FromTableFragment(alias)) +persister.FromJoinFragment(alias, true, true))
.SetWhereClause(condition)
.SetOuterJoins(ojf.ToFromFragmentString,ojf.ToWhereFragmentString + WhereFragment)
.SetOrderByClause(OrderBy(associations, orderBy))
.SetGroupByClause(groupBy);
if (Factory.Settings.IsCommentsEnabled)
select.SetComment(Comment);
SqlString = select.ToSqlString();
}
public static ADOException Convert(ISQLExceptionConverter converter, Exception sqle, string message, SqlString sql,
object[] parameterValues, IDictionary namedParameters)
{
string extendMessage = ExtendMessage(message, sql, parameterValues, namedParameters);
ADOExceptionReporter.LogExceptions(sqle, extendMessage);
return new ADOException(extendMessage, sqle, sql);
}
public SqlCommandImpl(SqlString query, ICollection<IParameterSpecification> specifications, QueryParameters queryParameters, ISessionFactoryImplementor factory)
{
this.query = query;
this.specifications = specifications;
this.queryParameters = queryParameters;
this.factory = factory;
}
public object Generate(ISessionImplementor session, object obj)
{
var sql = new SqlString(session.Factory.Dialect.SelectGUIDString);
try
{
IDbCommand st = session.Batcher.PrepareCommand(CommandType.Text, sql, SqlTypeFactory.NoTypes);
IDataReader reader = null;
try
{
reader = session.Batcher.ExecuteReader(st);
object result;
try
{
reader.Read();
result = IdentifierGeneratorFactory.Get(reader, identifierType, session);
}
finally
{
reader.Close();
}
log.Debug("GUID identifier generated: " + result);
return result;
}
finally
{
session.Batcher.CloseCommand(st, reader);
}
}
catch (Exception sqle)
{
throw ADOExceptionHelper.Convert(session.Factory.SQLExceptionConverter, sqle, "could not retrieve GUID", sql);
}
}
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 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 static ADOException Convert(Exception sqle, string message, SqlString sql, object[] parameterValues,
IDictionary namedParameters)
{
StringBuilder sb = new StringBuilder();
sb.Append(message).Append(Environment.NewLine).
Append("[ ").Append(sql).Append(" ]")
.Append(Environment.NewLine);
if (parameterValues != null && parameterValues.Length > 0)
{
sb.Append("Positional parameters: ");
int index = 0;
foreach (object parameterValue in parameterValues)
{
object value = parameterValue;
if (value == null)
value = "null";
sb.Append(" ").Append(index).Append(" ").Append(value).Append(Environment.NewLine);
}
}
if (namedParameters != null && namedParameters.Count > 0)
{
foreach (DictionaryEntry namedParameter in namedParameters)
{
object value = namedParameter.Value;
if (value == null)
value = "null";
sb.Append(" ").Append("Name: ").Append(namedParameter.Key)
.Append(" - Value: ").Append(value).Append(Environment.NewLine);
}
}
ADOExceptionReporter.LogExceptions(sqle, sb.ToString());
return new ADOException(sb.ToString(), sqle, sql);
}
public static Exception Convert(ISQLExceptionConverter converter, Exception sqle, string message, SqlString sql,
object[] parameterValues, IDictionary<string, TypedValue> namedParameters)
{
sql = TryGetActualSqlQuery(sqle, sql);
string extendMessage = ExtendMessage(message, sql != null ? sql.ToString() : null, parameterValues, namedParameters);
ADOExceptionReporter.LogExceptions(sqle, extendMessage);
return Convert(converter, sqle, extendMessage, sql);
}
public void GetLimitStringWithOffsetAndLimitAndTableStartingWithSelectKeywordAndDifferentCasing()
{
var dialect = new Oracle8iDialect();
var sqlString = new SqlString(@"sElEct selectlimi0_.""Id"" as column1_2_,selectlimi0_.""FirstName"" as column2_2_,selectlimi0_.""LastName"" As column3_2_ fRom ""SelectLimit"" selectlimi0_");
var expected = new SqlString(@"select column1_2_,column2_2_,column3_2_ from ( select row_.*, rownum rownum_ from ( sElEct selectlimi0_.""Id"" as column1_2_,selectlimi0_.""FirstName"" as column2_2_,selectlimi0_.""LastName"" As column3_2_ fRom ""SelectLimit"" selectlimi0_ ) row_ where rownum <=1) where rownum_ >1");
var limited = dialect.GetLimitString(sqlString, new SqlString("1"), new SqlString("1"));
Assert.AreEqual(limited, expected);
}
/// <summary>
/// Appends the SqlString parameter to the end of the current SqlString to create a
/// new SqlString object.
/// </summary>
/// <param name="rhs">The SqlString to append.</param>
/// <returns>A new SqlString object.</returns>
/// <remarks>
/// A SqlString object is immutable so this returns a new SqlString. If multiple Appends
/// are called it is better to use the SqlStringBuilder.
/// </remarks>
public SqlString Append( SqlString rhs )
{
object[ ] temp = new object[rhs.SqlParts.Length + sqlParts.Length];
Array.Copy( sqlParts, 0, temp, 0, sqlParts.Length );
Array.Copy( rhs.SqlParts, 0, temp, sqlParts.Length, rhs.SqlParts.Length );
return new SqlString( temp );
}
public override void Append(ISqlCommand command)
{
Commands.Add(command);
_sqlString = _sqlString.Append("\nOPEN :cursor")
.Append(Convert.ToString(_cursorCount++))
.Append("\nFOR\n")
.Append(command.Query).Append("\n;\n");
}
public SimpleEntityLoader( ILoadable persister, SqlString sql, LockMode lockMode )
{
this.persister = new ILoadable[ ] {persister};
this.idType = persister.IdentifierType;
this.sql = sql;
this.lockMode = new LockMode[ ] {lockMode};
PostInstantiate();
}
public void GetLimitStringWithTableStartingWithSelectKeyword()
{
var dialect = new Oracle8iDialect();
var sqlString=new SqlString(@"select selectlimi0_.""Id"" as column1_2_,selectlimi0_.""FirstName"" as column2_2_,selectlimi0_.""LastName"" as column3_2_ from ""SelectLimit"" selectlimi0_");
var expected = new SqlString(@"select column1_2_,column2_2_,column3_2_ from ( select selectlimi0_.""Id"" as column1_2_,selectlimi0_.""FirstName"" as column2_2_,selectlimi0_.""LastName"" as column3_2_ from ""SelectLimit"" selectlimi0_ ) where rownum <=1");
var limited=dialect.GetLimitString(sqlString, null, new SqlString("1"));
Assert.AreEqual(limited, expected);
}
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
return new SqlStringBuilder(querySqlString)
.Add(" fetch first ")
.Add(limit.ToString())
.Add(" rows only ")
.ToSqlString();
}
public static IList executeSQL(string sql)
{
IList result = new ArrayList();
ISessionFactoryImplementor sf;
IDbCommand cmd;
IDbConnection conn;
try
{
Configuration cfg = new Configuration();
cfg.Configure();
sf = (ISessionFactoryImplementor)cfg.BuildSessionFactory();
SqlString queryString = new NHibernate.SqlCommand.SqlString(sql);
NHibernate.SqlTypes.SqlType[] Types = new NHibernate.SqlTypes.SqlType[] { };
cmd = sf.ConnectionProvider.Driver.GenerateCommand(CommandType.Text, queryString, Types);
cmd.CommandText = sql;
conn = sf.OpenConnection();
}
catch (HibernateException ex)
{
throw new HibernateException("Fatal error occured, Can not connect the database!", ex);
}
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
Array subList = new object[rs.FieldCount];
for (int i = 0; i < rs.FieldCount; i++)
{
subList.SetValue(rs.GetValue(i), i);
}
result.Add(subList);
}
}
catch (HibernateException ex)
{
throw new HibernateException(ex);
}
finally
{
sf.CloseConnection(conn);
}
return(result);
}
public abstract void AddJoin(string tableName, string alias, string[] fkColumns, string[] pkColumns, JoinType joinType,
SqlString on);
/// <summary>
/// Appends the SqlString parameter to the end of the current SqlString to create a
/// new SqlString object.
/// </summary>
/// <param name="rhs">The SqlString to append.</param>
/// <returns>A new SqlString object.</returns>
/// <remarks>
/// A SqlString object is immutable so this returns a new SqlString. If multiple Appends
/// are called it is better to use the SqlStringBuilder.
/// </remarks>
public SqlString Append(SqlString rhs)
{
return(new SqlString(ArrayHelper.Join(sqlParts, rhs.sqlParts)));
}
public abstract void AddJoins(SqlString fromFragment, SqlString whereFragment);
/// <summary>
/// Adds an existing SqlString to this SqlStringBuilder
/// </summary>
/// <param name="sqlString">The SqlString to add to this SqlStringBuilder</param>
/// <param name="prefix">String to put at the beginning of the combined SqlString.</param>
/// <param name="op">How these Statements should be junctioned "AND" or "OR"</param>
/// <param name="postfix">String to put at the end of the combined SqlString.</param>
/// <returns>This SqlStringBuilder</returns>
/// <remarks>
/// This calls the overloaded Add method with an array of SqlStrings and wrapStatment=false
/// so it will not be wrapped with a "(" and ")"
/// </remarks>
public SqlStringBuilder Add(SqlString sqlString, string prefix, string op, string postfix)
{
return(Add(new SqlString[] { sqlString }, prefix, op, postfix, false));
}
/// <summary>
/// Sets the prebuilt SqlString to the Where clause
/// </summary>
/// <param name="whereSqlString">The SqlString that contains the sql and parameters to add to the WHERE</param>
/// <returns>This SqlSelectBuilder</returns>
public SqlSelectBuilder SetWhereClause(SqlString whereSqlString)
{
whereClause = whereSqlString;
return(this);
}
public override void AddJoins(SqlString fromFragment, SqlString whereFragment)
{
_fromFragment.Add(fromFragment);
//where fragment must be empty!
}
/// <summary>
/// Sets the prebuilt SqlString to the Having clause
/// </summary>
/// <param name="havingSqlString">The SqlString that contains the sql and parameters to add to the HAVING</param>
/// <returns>This SqlSelectBuilder</returns>
public SqlSelectBuilder SetHavingClause(SqlString havingSqlString)
{
havingClause = havingSqlString;
return(this);
}
public override bool AddCondition(SqlString condition)
{
return(AddCondition(afterWhere, condition));
}
public SqlSelectBuilder SetOuterJoins(string outerJoinsAfterFrom, SqlString outerJoinsAfterWhere)
{
return(this.SetOuterJoins(new SqlString(outerJoinsAfterFrom), outerJoinsAfterWhere));
}
public SqlString ToQuerySqlString()
{
var builder = new SqlStringBuilder();
builder.Add("select ");
if (distinct)
{
builder.Add("distinct ");
}
SqlString from = joins.ToFromFragmentString;
if (from.StartsWithCaseInsensitive(","))
{
from = from.Substring(1);
}
else if (from.StartsWithCaseInsensitive(" inner join"))
{
from = from.Substring(11);
}
builder.Add(selectBuilder.ToSqlString())
.Add(" from")
.Add(from);
SqlString part1 = joins.ToWhereFragmentString.Trim();
SqlString part2 = whereBuilder.ToSqlString();
bool hasPart1 = part1.Count > 0;
bool hasPart2 = part2.Count > 0;
if (hasPart1 || hasPart2)
{
builder.Add(" where ");
}
if (hasPart1)
{
builder.Add(part1.Substring(4));
}
if (hasPart2)
{
if (hasPart1)
{
builder.Add(" and (");
}
builder.Add(part2);
if (hasPart1)
{
builder.Add(")");
}
}
if (groupBy.Count > 0)
{
builder.Add(" group by ").Add(groupBy.ToSqlString());
}
if (having.Count > 0)
{
builder.Add(" having ").Add(having.ToSqlString());
}
if (orderBy.Count > 0)
{
builder.Add(" order by ").Add(orderBy.ToSqlString());
}
return(builder.ToSqlString());
}
/// <summary>
/// Sets the text that should appear after the FROM
/// </summary>
/// <param name="fromClause">The fromClause in a SqlString</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetFromClause(SqlString fromClause)
{
// it is safe to do this because a fromClause will have no
// parameters
return(SetFromClause(fromClause.ToString()));
}
/// <summary>
/// Sets the text for the SELECT
/// </summary>
/// <param name="selectClause">The selectClause to set</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetSelectClause(string selectClause)
{
this.selectClause = new SqlString(selectClause);
return(this);
}
/// <summary>
/// Sets the SqlString for the OUTER JOINs.
/// </summary>
/// <remarks>
/// All of the Sql needs to be included in the SELECT. No OUTER JOINS will automatically be
/// added.
/// </remarks>
/// <param name="outerJoinsAfterFrom">The outerJoinsAfterFrom to set</param>
/// <param name="outerJoinsAfterWhere">The outerJoinsAfterWhere to set</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetOuterJoins(SqlString outerJoinsAfterFrom, SqlString outerJoinsAfterWhere)
{
this.outerJoinsAfterFrom = outerJoinsAfterFrom;
this.outerJoinsAfterWhere = outerJoinsAfterWhere;
return(this);
}
public override void AddFromFragmentString(SqlString fromFragmentString)
{
afterFrom.Add(fromFragmentString);
}
public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
{
Trace.WriteLine(sql.ToString());
return(sql);
}
/// <summary>
/// Adds an existing SqlString to this SqlStringBuilder. It does NOT add any
/// prefix, postfix, operator, or wrap around this. It is equivalent to just
/// adding a string.
/// </summary>
/// <param name="sqlString">The SqlString to add to this SqlStringBuilder</param>
/// <returns>This SqlStringBuilder</returns>
/// <remarks>This calls the overloaded Add(sqlString, null, null, null, false)</remarks>
public SqlStringBuilder Add(SqlString sqlString)
{
sqlString.Visit(AddingVisitor);
return(this);
}
public abstract bool AddCondition(SqlString condition);
public override void AddJoins(SqlString fromFragment, SqlString whereFragment)
{
afterFrom.Add(fromFragment);
afterWhere.Add(whereFragment);
}
public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
{
Debug.WriteLine(sql);
return(base.OnPrepareStatement(sql));
}
/// <summary>
/// Sets the text that should appear after the ORDER BY.
/// </summary>
/// <param name="orderByClause">The orderByClause to set</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetOrderByClause(SqlString orderByClause)
{
this.orderByClause = orderByClause;
return(this);
}
public abstract void AddFromFragmentString(SqlString fromFragmentString);
public static bool IsEmpty(SqlString str)
{
return(str == null || str.Count == 0);
}
public SqlCommandInfo ToSqlCommandInfo()
{
SqlString text = ToSqlString();
return(new SqlCommandInfo(text, GetParametersTypeArray()));
}
/// <summary>
/// Create a StringBuilder to modify the SqlString
/// </summary>
/// <param name="sqlString">The SqlString to modify.</param>
public SqlStringBuilder(SqlString sqlString)
{
sqlParts = new List <object>(sqlString.Count);
Add(sqlString);
}
/// <summary>
/// Sets the text for the SELECT
/// </summary>
/// <param name="selectClause">The selectClause to set</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetSelectClause(SqlString selectClause)
{
this.selectClause = selectClause;
return(this);
}
public void String(SqlString sqlString)
{
parent.Add(sqlString);
}
public static bool IsNotEmpty(SqlString str)
{
return(!IsEmpty(str));
}
