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