public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (querySqlString.IndexOfCaseInsensitive(" ORDER BY ") < 0)
{
querySqlString = querySqlString.Append(" ORDER BY GETDATE()");
}
return(querySqlString.Append(string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offset, limit)));
}
public void Append_Test()
{
var query = new SqlString();
query.Append("Hello");
query.Append("World");
Assert.AreEqual("HelloWorld", query);
}
private void UpdateTranslate(IModel valueObject, List <OracleParameter> parameters)
{
foreach (PropertyInfo prop in valueObject.GetType().GetProperties())
{
if (prop.GetValue(valueObject, null) != null)
{
if (CheckBrowsable(valueObject, prop.Name))
{
//SqlStringBuilder.Append(prop.Name.ToUpper() + " = :" + prop.Name.ToUpper() + ",");
SqlString.Append(prop.Name.ToUpper() + " = " + DbSymbolize + prop.Name.ToUpper() + ",");
var propertyType = prop.PropertyType;
if (prop.PropertyType.IsGenericType &&
prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
propertyType = prop.PropertyType.GetGenericArguments()[0];
}
//如果obj value非null但長度為0, 代表需為NULL, 以DBnull.Value傳值
parameters.Add(new OracleParameter(DbSymbolize + prop.Name.ToUpper(), (OracleDbType)GetDbType(propertyType.Name), prop.GetValue(valueObject, null).ToString().Length > 0 ? prop.GetValue(valueObject, null) : DBNull.Value, ParameterDirection.Input));
}
}
}
SqlString.Remove(SqlString.Length - 1, 1);
}
public AncestorResult Query <T>(IModel objectModel) where T : class, IModel, new()
{
var isSuccess = false;
var sqlString = string.Empty;
var returnResult = new AncestorResult();
var parameters = new List <OracleParameter>();
var dataTable = new DataTable();
try
{
SqlString.Clear();
// 2015-08-31
//sqlString = QueryStringGenerator(objectModel, parameters);
var tableName = objectModel.GetType().Name;
SqlString.Append("SELECT " + tableName + ".*, ROWID FROM " + tableName);
var sqlWhereCondition = ParseWhereCondition(objectModel, parameters);
SqlString.Append(sqlWhereCondition);
isSuccess = DB.Query(SqlString.ToString(), parameters, ref dataTable);
returnResult.Message = DB.ErrorMessage;
returnResult.DataList = dataTable.ToList <T>();
}
catch (Exception exception)
{
returnResult.Message = exception.ToString();
isSuccess = false;
}
returnResult.IsSuccess = isSuccess;
return(returnResult);
}
/// <summary>
/// Generates an SqlString that selects a row by id
/// </summary>
/// <param name="forUpdateFragment">SQL containing <c>FOR UPDATE</c> clauses
/// to append at the end of the query (optional)</param>
/// <returns></returns>
protected virtual SqlString GenerateSelectString(string forUpdateFragment)
{
SqlSimpleSelectBuilder builder = new SqlSimpleSelectBuilder(factory);
// set the table name and add the columns to select
builder.SetTableName(TableName)
.AddColumns(IdentifierColumnNames)
.AddColumns(subclassColumnClosure, subclassColumnAliasClosure)
.AddColumns(subclassFormulaClosure, subclassFormulaAliasClosure);
if (HasSubclasses)
{
builder.AddColumn(DiscriminatorColumnName, DiscriminatorAlias);
}
// add the parameters to use in the WHERE clause
builder.SetIdentityColumn(IdentifierColumnNames, IdentifierType);
// Ok, render the SELECT statement
SqlString selectSqlString = builder.ToSqlString();
// add any special text that is contained in the forUpdateFragment
if (forUpdateFragment != null && forUpdateFragment.Length > 0)
{
selectSqlString = selectSqlString.Append(forUpdateFragment);
}
return(selectSqlString);
}
public void Append()
{
SqlString sql = new SqlString(new string[] { "select", " from table" });
SqlString postAppendSql = sql.Append(" where A=B");
Assert.IsFalse(sql == postAppendSql, "should be a new object");
Assert.AreEqual(3, postAppendSql.Count);
sql = postAppendSql;
postAppendSql = sql.Append(new SqlString(" and C=D"));
Assert.AreEqual(4, postAppendSql.Count);
Assert.AreEqual("select from table where A=B and C=D", postAppendSql.ToString());
}
/// <summary>
/// Render the SQL fragment
/// </summary>
public virtual SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
{
if (projection != null)
{
SqlString sb = new SqlString();
SqlString produced = this.projection.ToSqlString(criteria, 0, criteriaQuery, new Dictionary <string, IFilter>());
SqlString truncated = NHibernate.Util.StringHelper.RemoveAsAliasesFromSql(produced);
sb = sb.Append(truncated);
sb = sb.Append(ascending ? " asc" : " desc");
return(sb);
}
string[] columns = criteriaQuery.GetColumnAliasesUsingProjection(criteria, propertyName);
Type.IType type = criteriaQuery.GetTypeUsingProjection(criteria, propertyName);
StringBuilder fragment = new StringBuilder();
ISessionFactoryImplementor factory = criteriaQuery.Factory;
for (int i = 0; i < columns.Length; i++)
{
bool lower = ignoreCase && IsStringType(type.SqlTypes(factory)[i]);
if (lower)
{
fragment.Append(factory.Dialect.LowercaseFunction)
.Append("(");
}
fragment.Append(columns[i]);
if (lower)
{
fragment.Append(")");
}
fragment.Append(ascending ? " asc" : " desc");
if (i < columns.Length - 1)
{
fragment.Append(", ");
}
}
return(new SqlString(fragment.ToString()));
}
public void Count()
{
SqlString sql =
new SqlString(
new object[] { "select", " from table where a = ", Parameter.Placeholder, " and b = ", Parameter.Placeholder });
Assert.AreEqual(5, sql.Count, "Count with no nesting failed.");
sql = sql.Append(new SqlString(new object[] { " more parts ", " another part " }));
Assert.AreEqual(7, sql.Count, "Added a SqlString to a SqlString");
}
public override SqlString OnPrepareStatement(SqlString sql)
{
sql = sql.Insert(0, String.Format("{0};", this.sqlBefore()));
if (this.sqlAfter != null)
{
sql = sql.Append(String.Format(";{0}", this.sqlAfter()));
}
return(base.OnPrepareStatement(sql));
}
public override SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery, IDictionary <string, IFilter> enabledFilters)
{
SqlString sqlStringSubquery = _subQuery.ToSqlString(criteria, criteriaQuery, enabledFilters);
return(sqlStringSubquery.Append(new SqlString(new object[]
{
" as y",
loc.ToString(),
"_"
})));
}
protected SqlString MergeOrderings(SqlString ass, SqlString orderBy)
{
if (ass.Length == 0)
{
return(orderBy);
}
if (orderBy.Length == 0)
{
return(ass);
}
return(orderBy.Append(StringHelper.CommaSpace, ass));
}
protected override SqlString ApplyLocks(SqlString sqlSelectString, IDictionary lockModes, Dialect.Dialect dialect)
{
if (lockModes == null || lockModes.Count == 0)
{
return(sqlSelectString);
}
else
{
ForUpdateFragment fragment = new ForUpdateFragment(lockModes);
return(sqlSelectString.Append(fragment.ToSqlStringFragment(dialect)));
}
}
public void AddManyToManyJoin(JoinFragment outerjoin, IQueryableCollection collection)
{
string manyToManyFilter = collection.GetManyToManyFilterFragment(rhsAlias, enabledFilters);
SqlString condition = string.Empty.Equals(manyToManyFilter)
? on
: SqlStringHelper.IsEmpty(on) ? new SqlString(manyToManyFilter) :
on.Append(" and ").Append(manyToManyFilter);
outerjoin.AddJoin(joinable.TableName, rhsAlias, lhsColumns, rhsColumns, joinType, condition);
outerjoin.AddJoins(joinable.FromJoinFragment(rhsAlias, false, true),
joinable.WhereJoinFragment(rhsAlias, false, true));
}
public override SqlString OnPrepareStatement(SqlString sql)
{
if (sql.ToString().EndsWith("FROM \"User\" user0_ WHERE user0_.Id=?"))
{
var filter = _session.GetEnabledFilter("Tenant") as FilterImpl;
if (filter != null)
{
var filterValue = filter.Parameters["name"];
sql = sql.Append(string.Format(" And user0_.Tenant = '{0}'", filterValue));
}
}
return(base.OnPrepareStatement(sql));
}
/// <summary>
/// 追加字符串
/// </summary>
/// <param name="objValue"></param>
internal WhereHelper <T> StringAppend(params object[] objValue)
{
if (objValue == null || objValue.Count() == 0)
{
return(this);
}
foreach (var val in objValue)
{
SqlString.Append(val);
}
return(this);
}
private void CombineCriteriaQueries()
{
foreach (CriteriaLoader loader in loaders)
{
CriteriaQueryTranslator translator = loader.Translator;
translators.Add(translator);
QueryParameters queryParameters = translator.GetQueryParameters();
parameters.Add(queryParameters);
SqlCommandInfo commandInfo = loader.GetQueryStringAndTypes(session, queryParameters);
sqlString = sqlString.Append(commandInfo.Text)
.Append(session.Factory.ConnectionProvider.Driver.MultipleQueriesSeparator)
.Append(Environment.NewLine);
types.AddRange(commandInfo.ParameterTypes);
}
}
public OuterJoinableAssociation(IAssociationType joinableType, String lhsAlias, String[] lhsColumns, String rhsAlias,
JoinType joinType, SqlString withClause, ISessionFactoryImplementor factory,
IDictionary <string, IFilter> enabledFilters)
{
this.joinableType = joinableType;
this.lhsAlias = lhsAlias;
this.lhsColumns = lhsColumns;
this.rhsAlias = rhsAlias;
this.joinType = joinType;
joinable = joinableType.GetAssociatedJoinable(factory);
rhsColumns = JoinHelper.GetRHSColumnNames(joinableType, factory);
on = new SqlString(joinableType.GetOnCondition(rhsAlias, factory, enabledFilters));
if (SqlStringHelper.IsNotEmpty(withClause))
{
on = on.Append(" and ( ").Append(withClause).Append(" )");
}
this.enabledFilters = enabledFilters; // needed later for many-to-many/filter application
}
private void AggregateQueriesInformation()
{
sqlString = new SqlString();
foreach (AbstractQueryImpl query in queries)
{
QueryParameters queryParameters = query.GetQueryParameters();
queryParameters.ValidateParameters();
query.VerifyParameters();
IQueryTranslator[] queryTranslators =
session.GetQueries(query.ExpandParameterLists(queryParameters.NamedParameters), false);
foreach (IQueryTranslator translator in queryTranslators)
{
translators.Add(translator);
parameters.Add(queryParameters);
queryParameters = GetFilteredQueryParameters(queryParameters, translator);
SqlCommandInfo commandInfo = translator.Loader.GetQueryStringAndTypes(session, queryParameters);
sqlString = sqlString.Append(commandInfo.Text).Append(session.Factory.ConnectionProvider.Driver.MultipleQueriesSeparator).Append(Environment.NewLine);
types.AddRange(commandInfo.ParameterTypes);
}
}
}
public virtual void Append(ISqlCommand command)
{
Commands.Add(command);
sqlString = sqlString.Append(command.Query).Append(_statementTerminator).Append(Environment.NewLine);
}
public override SqlString ApplyOptions(SqlString queryString, string hints)
{
var sql = queryString.Append($" OPTION({hints})");
return(sql);
}
public override SqlString AddIdentifierOutParameterToInsert(SqlString insertString, string identifierColumnName, string parameterName)
{
return(insertString.Append(" returning ", identifierColumnName));
}
public override SqlString AppendIdentitySelectToInsert(SqlString insertSql)
{
return(insertSql.Append("; " + IdentitySelectString));
}
public override SqlString AppendIdentitySelectToInsert(SqlString insertString, string identifierColumnName)
{
return(insertString.Append(" returning ", identifierColumnName));
}
private static void ExtractColumnOrAliasNames(SqlString select, out List <SqlString> columnsOrAliases,
out Dictionary <SqlString, SqlString> aliasToColumn)
{
columnsOrAliases = new List <SqlString>();
aliasToColumn = new Dictionary <SqlString, SqlString>();
IList <SqlString> tokens = new QuotedAndParenthesisStringTokenizer(select).GetTokens();
int index = 0;
while (index < tokens.Count)
{
SqlString token = tokens[index];
int nextTokenIndex = index += 1;
if (token.StartsWithCaseInsensitive("select"))
{
continue;
}
if (token.StartsWithCaseInsensitive("distinct"))
{
continue;
}
if (token.StartsWithCaseInsensitive(","))
{
continue;
}
if (token.StartsWithCaseInsensitive("from"))
{
break;
}
// handle composite expressions like "2 * 4 as foo"
while ((nextTokenIndex < tokens.Count) && (tokens[nextTokenIndex].StartsWithCaseInsensitive("as") == false && tokens[nextTokenIndex].StartsWithCaseInsensitive(",") == false))
{
SqlString nextToken = tokens[nextTokenIndex];
token = token.Append(nextToken);
nextTokenIndex = index += 1;
}
// if there is no alias, the token and the alias will be the same
SqlString alias = token;
bool isFunctionCallOrQuotedString = token.IndexOfCaseInsensitive("'") >= 0 || token.IndexOfCaseInsensitive("(") >= 0;
// this is heuristic guess, if the expression contains ' or (, it is probably
// not appropriate to just slice parts off of it
if (isFunctionCallOrQuotedString == false)
{
// its a simple column reference, so lets set the alias to the
// column name minus the table qualifier if it exists
int dot = token.IndexOfCaseInsensitive(".");
if (dot != -1)
{
alias = token.Substring(dot + 1);
}
}
// notice! we are checking here the existence of "as" "alias", two
// tokens from the current one
if (nextTokenIndex + 1 < tokens.Count)
{
SqlString nextToken = tokens[nextTokenIndex];
if (nextToken.IndexOfCaseInsensitive("as") >= 0)
{
SqlString tokenAfterNext = tokens[nextTokenIndex + 1];
alias = tokenAfterNext;
index += 2; //skip the "as" and the alias
}
}
columnsOrAliases.Add(alias);
aliasToColumn[alias] = token;
}
}
protected SqlString GenerateIdInsertSelect(IQueryable persister, string tableAlias, IASTNode whereClause)
{
var select = new SqlSelectBuilder(Factory);
SelectFragment selectFragment = new SelectFragment(Factory.Dialect)
.AddColumns(tableAlias, persister.IdentifierColumnNames, persister.IdentifierColumnNames);
select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));
string rootTableName = persister.TableName;
SqlString fromJoinFragment = persister.FromJoinFragment(tableAlias, true, false);
SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false);
select.SetFromClause(rootTableName + ' ' + tableAlias + fromJoinFragment);
if (whereJoinFragment == null)
{
whereJoinFragment = SqlString.Empty;
}
else
{
whereJoinFragment = whereJoinFragment.Trim();
if (whereJoinFragment.StartsWithCaseInsensitive("and "))
{
whereJoinFragment = whereJoinFragment.Substring(4);
}
}
SqlString userWhereClause = SqlString.Empty;
if (whereClause.ChildCount != 0)
{
// If a where clause was specified in the update/delete query, use it to limit the
// returned ids here...
try
{
var nodes = new CommonTreeNodeStream(whereClause);
var gen = new SqlGenerator(Factory, nodes);
gen.whereClause();
userWhereClause = gen.GetSQL().Substring(7);
}
catch (RecognitionException e)
{
throw new HibernateException("Unable to generate id select for DML operation", e);
}
if (whereJoinFragment.Length > 0)
{
whereJoinFragment.Append(" and ");
}
}
select.SetWhereClause(whereJoinFragment + userWhereClause);
var insert = new InsertSelect();
if (Factory.Settings.IsCommentsEnabled)
{
insert.SetComment("insert-select for " + persister.EntityName + " ids");
}
insert.SetTableName(persister.TemporaryIdTableName);
insert.SetSelect(select);
return(insert.ToSqlString());
}
public override SqlString OnPrepareStatement(SqlString sql)
{
TotalCalls++;
return(sql.Append("/* TEST */"));
}
public virtual void Append(ISqlCommand command)
{
Commands.Add(command);
sqlString = sqlString.Append(command.Query, _statementTerminator);
}
public override SqlString AppendIdentitySelectToInsert(SqlString insertString)
{
return(insertString.Append("\nselect @@identity"));
}
public virtual void Append(ISqlCommand command)
{
Commands.Add(command);
sqlString = sqlString.Append(command.Query).Append(";").Append(Environment.NewLine);
}
public static SqlString Desc(this SqlString s)
{
return(s.Append(" desc "));
}