private void WriteSelect(ParserWriter writer, APSqlSelectCommand command) { writer.WriteDirect("SELECT"); if (command.SelectMode == APSqlSelectMode.DISTINCT) { writer.Write("DISTINCT"); } APSqlSelectPhrase phrase = command.SelectExprClause.Next as APSqlSelectPhrase; bool isFirst = true; while (phrase != null) { if (!isFirst) { writer.Write(','); } else { isFirst = false; } WriteSelectExpression(writer, phrase.Expr); if (!string.IsNullOrEmpty(phrase.Alias)) { writer.Write("AS " + JudgeAliasName(phrase.Alias)); } phrase = phrase.Next as APSqlSelectPhrase; } }
protected override DbCommand ParseSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); OracleCommand dbCmd = new OracleCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { if ((command.Skip ?? 0) > 0) { writer.WriteDirect("SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM ("); writer.WriteLine(); ParseSelectInternal(command, dbCmd, writer); writer.Idented = 0; writer.WriteLine(); writer.WriteDirect(String.Format(") query_alias {0} ) WHERE query_rownum > {1}", (command.Take ?? 0) > 0 ? "WHERE ROWNUM <= " + (command.Take.Value + command.Skip.Value) : "", command.Skip.Value)); } else { ParseSelectInternal(command, dbCmd, writer, command.Take); } } dbCmd.CommandText = sb.ToString(); return(dbCmd); }
private void WriteSelect(ParserWriter writer, APSqlSelectCommand command, int size) { writer.WriteDirect("SELECT"); if (command.SelectMode == APSqlSelectMode.DISTINCT) writer.Write("DISTINCT"); if (size > 0) writer.Write("TOP " + size); if (command.SelectExprClause != null) { APSqlSelectPhrase phrase = command.SelectExprClause.Next as APSqlSelectPhrase; bool isFirst = true; while (phrase != null) { if (!isFirst) writer.Write(','); else isFirst = false; WriteSelectExpression(writer, phrase.Expr); if (!string.IsNullOrEmpty(phrase.Alias)) writer.Write("AS " + JudgeAliasName(phrase.Alias)); phrase = phrase.Next as APSqlSelectPhrase; } } }
/// <summary> /// Build 'SELECT COUNT(*)' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public DbCommand BuildSizeOfSelectCommand(APSqlSelectCommand command) { if (command == null) throw new ArgumentNullException("command"); return ParseSizeOfSelect(command); }
/// <summary> /// Executes the CommandText against the Connection and builds an IDataReader. Return the count of SELECT command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>Count of SELECT command records.</returns> public virtual int ExecuteSizeOfSelect(APSqlSelectCommand command) { DbCommand dbCmd = _provider.BuildSizeOfSelectCommand(command); dbCmd.Connection = Connection; dbCmd.Transaction = _transaction; return((int)dbCmd.ExecuteScalar()); }
/// <summary> /// Create view. /// </summary> /// <param name="name">View name.</param> /// <param name="command">The command to create view.</param> public virtual void CreateView(string name, APSqlSelectCommand command) { DbCommand dbCmd = _provider.BuildCreateViewCommand(name, command); dbCmd.Connection = Connection; dbCmd.Transaction = _transaction; dbCmd.ExecuteNonQuery(); }
/// <summary> /// SQL 'GROUP BY' clause extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The Expression phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand group_by(this APSqlSelectCommand command, params APSqlExprPhrase[] phrases) { if (phrases != null && phrases.Length != 0) { command.GroupByClause = new APSqlGroupByClause(phrases); } return(command); }
/// <summary> /// SQL 'GROUP BY' clause extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The Expression phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand group_by(this APSqlSelectCommand command, IEnumerable <APSqlExprPhrase> phrases) { if (phrases != null) { command.GroupByClause = new APSqlGroupByClause(phrases); } return(command); }
/// <summary> /// SQL 'HAVING' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'HAVING' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand having(this APSqlSelectCommand command, APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (phrases != null && phrases.Length != 0) { command.HavingClause = new APSqlWhereClause(joinType, phrases); } return(command); }
/// <summary> /// SQL 'HAVING' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'HAVING' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand having(this APSqlSelectCommand command, APSqlConditionJoinType joinType, IEnumerable <APSqlWherePhrase> phrases) { if (phrases != null) { command.HavingClause = new APSqlWhereClause(joinType, phrases); } return(command); }
/// <summary> /// Create a 'INSERT' command. /// </summary> /// <param name="tableDef">Table definition.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="clause">The 'SELECT' clause.</param> public APSqlInsertCommand(APTableDef tableDef, APSqlSelectCommand subQuery, APSqlSelectClause clause) { if (tableDef == null) throw new ArgumentNullException("tableDef"); _tableDef = tableDef; _subQuery = subQuery; _selectClause = clause; }
/// <summary> /// Create a 'INSERT' command. /// </summary> /// <param name="tableDef">Table definition.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="phrases">The 'SELECT' phrases.</param> public APSqlInsertCommand(APTableDef tableDef, APSqlSelectCommand subQuery, IEnumerable<APSqlSelectPhrase> phrases) { if (tableDef == null) throw new ArgumentNullException("tableDef"); _tableDef = tableDef; _subQuery = subQuery; _selectClause = new APSqlSelectClause(phrases); }
/// <summary> /// Build 'SELECT COUNT(*)' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public DbCommand BuildSizeOfSelectCommand(APSqlSelectCommand command) { if (command == null) { throw new ArgumentNullException("command"); } return(ParseSizeOfSelect(command)); }
private void ParseSelectInternal(APSqlSelectCommand command, OracleCommand dbCmd, ParserWriter writer, int?maxReturnCount = null) { WriteSelect(writer, command); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd, maxReturnCount); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); }
/// <summary> /// Create a 'INSERT' command. /// </summary> /// <param name="tableDef">Table definition.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="clause">The 'SELECT' clause.</param> public APSqlInsertCommand(APTableDef tableDef, APSqlSelectCommand subQuery, APSqlSelectClause clause) { if (tableDef == null) { throw new ArgumentNullException("tableDef"); } _tableDef = tableDef; _subQuery = subQuery; _selectClause = clause; }
/// <summary> /// Create a 'INSERT' command. /// </summary> /// <param name="tableDef">Table definition.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="phrases">The 'SELECT' phrases.</param> public APSqlInsertCommand(APTableDef tableDef, APSqlSelectCommand subQuery, IEnumerable <APSqlSelectPhrase> phrases) { if (tableDef == null) { throw new ArgumentNullException("tableDef"); } _tableDef = tableDef; _subQuery = subQuery; _selectClause = new APSqlSelectClause(phrases); }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand where (this APSqlSelectCommand command, params APSqlWherePhrase[] phrases) { if (phrases != null && phrases.Length != 0) { command.WhereClause = new APSqlWhereClause(APSqlConditionJoinType.AND, phrases); } else { command.WhereClause = null; } return(command); }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand where (this APSqlSelectCommand command, IEnumerable <APSqlWherePhrase> phrases) { if (phrases != null) { command.WhereClause = new APSqlWhereClause(APSqlConditionJoinType.AND, phrases); } else { command.WhereClause = null; } return(command); }
/// <summary> /// Build 'SELECT' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public DbCommand BuildSelectCommand(APSqlSelectCommand command) { if (command == null) throw new ArgumentNullException("command"); if (command.Take != null && command.Take <= 0) throw new APDataException(APResource.APData_QueryTakeError); if (command.Skip != null) { if (command.Skip < 0) throw new APDataException(APResource.APData_QuerySkipError); } return ParseSelect(command); }
/// <summary> /// SQL 'FROM' clause extensions. Add new 'FROM' in clause. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The 'FROM' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand from_add(this APSqlSelectCommand command, params APSqlFromPhrase[] phrases) { if (command.FromClause == null || command.FromClause.Next == null) { command.FromClause = new APSqlFromClause(phrases); } else { APSqlFromPhrase exist = command.FromClause.Last as APSqlFromPhrase; exist.SetNext(phrases); } return(command); }
/// <summary> /// SQL 'GROUP BY' clause extensions. Add new 'GROUP BY' in clause. /// </summary> /// <param name="command">The command.</param> /// <param name="phrases">The Expression phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand group_by_add(this APSqlSelectCommand command, IEnumerable <APSqlExprPhrase> phrases) { if (command.GroupByClause == null || command.GroupByClause.Next == null) { command.GroupByClause = new APSqlGroupByClause(phrases); } else { APSqlExprPhrase exist = command.FromClause.Last as APSqlExprPhrase; exist.SetNext(phrases); } return(command); }
protected override DbCommand ParseSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); SqlCommand dbCmd = new SqlCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { WriteSelect(writer, command, command.Take ?? 0); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); if (command.Skip != null && command.Skip > 0) { writer.WriteLine(); if (command.WhereClause == null || command.WhereClause.Next == null) { writer.WriteDirect("WHERE"); } else { writer.WriteDirect("AND"); } writer.Write(command.PrimeryKeyExpr.SelectExpr); writer.Write("NOT IN ("); { // sub query WriteSelect(writer, new APSqlSelectCommand(command.PrimeryKeyExpr), command.Skip.Value); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); writer.Idented--; } writer.Write(")"); } WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); } dbCmd.CommandText = sb.ToString(); return(dbCmd); }
protected override DbCommand ParseSizeOfSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); OracleCommand dbCmd = new OracleCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { writer.Write("SELECT COUNT(*)"); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); //WriteOrderBy(writer, command.OrderByClause); } dbCmd.CommandText = sb.ToString(); return(dbCmd); }
/// <summary> /// Build 'SELECT' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public DbCommand BuildSelectCommand(APSqlSelectCommand command) { if (command == null) { throw new ArgumentNullException("command"); } if (command.Take != null && command.Take <= 0) { throw new APDataException(APResource.APData_QueryTakeError); } if (command.Skip != null) { if (command.Skip < 0) { throw new APDataException(APResource.APData_QuerySkipError); } } return(ParseSelect(command)); }
/// <summary> /// SQL 'HAVING' phrase extensions. Add new condition join 'OR' with exist condition. /// </summary> /// <param name="command">The command.</param> /// <param name="phrase">The new 'WHERE' phrase.</param> /// <returns>The command.</returns> public static APSqlSelectCommand having_or(this APSqlSelectCommand command, APSqlWherePhrase phrase) { if (command.HavingClause == null || command.HavingClause.Next == null) { command.HavingClause = new APSqlWhereClause(phrase); } else { APSqlWherePhrase exist = command.HavingClause.Next as APSqlWherePhrase; if (exist is APSqlConditionOrPhrase) { (exist as APSqlConditionOrPhrase).Child.Last.SetNext(phrase); } else { command.HavingClause = new APSqlWhereClause(APSqlConditionJoinType.OR, exist, phrase); } } return(command); }
private void ParseSelectInternal(APSqlSelectCommand command, int maxReturnCount, SqlCommand dbCmd, ParserWriter writer) { WriteSelect(writer, command, maxReturnCount); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); }
/// <summary> /// Executes the CommandText against the Connection and builds an IDataReader. Return the count of SELECT command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>Count of SELECT command records.</returns> public virtual int ExecuteSizeOfSelect(APSqlSelectCommand command) { DbCommand dbCmd = _provider.BuildSizeOfSelectCommand(command); dbCmd.Connection = Connection; dbCmd.Transaction = _transaction; return (int)dbCmd.ExecuteScalar(); }
protected override DbCommand ParseSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); OracleCommand dbCmd = new OracleCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { if ((command.Skip ?? 0) > 0) { writer.WriteDirect("SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM ("); writer.WriteLine(); ParseSelectInternal(command, dbCmd, writer); writer.Idented = 0; writer.WriteLine(); writer.WriteDirect(String.Format(") query_alias {0} ) WHERE query_rownum > {1}", (command.Take ?? 0) > 0 ? "WHERE ROWNUM <= " + (command.Take.Value + command.Skip.Value) : "", command.Skip.Value)); } else { ParseSelectInternal(command, dbCmd, writer, command.Take); } } dbCmd.CommandText = sb.ToString(); return dbCmd; }
protected override DbCommand ParseSizeOfSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); SqlCommand dbCmd = new SqlCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { writer.Write("SELECT COUNT(*)"); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); //WriteOrderBy(writer, command.OrderByClause); } dbCmd.CommandText = sb.ToString(); return dbCmd; }
/// <summary> /// Create a new condition phrase where sub query when use '[NOT] EXISTS' /// </summary> /// <param name="subQuery">Sub Query.</param> /// <param name="conditionOperator">'EXISTS' or 'NOT EXISTS'</param> public APSqlConditionPhrase(APSqlSelectCommand subQuery, APSqlConditionOperator conditionOperator) { if (subQuery == null) throw new ArgumentNullException("subQuery"); if (conditionOperator != APSqlConditionOperator.Exists && conditionOperator != APSqlConditionOperator.NotExists) throw new ArgumentException(APResource.APDB_OperatorExists, "conditionOperator"); _expr = null; _conditionOperator = conditionOperator; _value = subQuery; }
/// <summary> /// Create a new condition phrase where sub query when use 'ANY | SOME | ALL'. /// </summary> /// <param name="expr">SQL Expression.</param> /// <param name="conditionOperator">Condition operator.</param> /// <param name="subQuery">Sub Query.</param> /// <param name="subQueryScalarRestrict">Sub query scalar restrict.</param> public APSqlConditionPhrase(APSqlOperateExpr expr, APSqlConditionOperator conditionOperator, APSqlSelectCommand subQuery, APSqlSubQueryScalarRestrict subQueryScalarRestrict) : this(expr, conditionOperator, subQuery, null) { _subQueryScalarRestrict = subQueryScalarRestrict; }
/// <summary> /// Build 'SELECT COUNT(*)' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public abstract DbCommand BuildSizeOfSelectCommand(APSqlSelectCommand command);
public override DbCommand BuildCreateViewCommand(string name, APSqlSelectCommand command) { DbCommand dbCmd = BuildSelectCommand(command); dbCmd.CommandText = string.Format("create view {0}{1}as{1}", name, Environment.NewLine) + dbCmd.CommandText; return dbCmd; }
/// <summary> /// SQL SubQuery clause extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="clause">The 'SELECT' clause.</param> /// <returns>The command.</returns> public static APSqlInsertCommand subQuery(this APSqlInsertCommand command, APSqlSelectCommand subQuery, APSqlSelectClause clause) { command.SubQuery = subQuery; command.SelectClause = clause; return command; }
/// <summary> /// SQL SubQuery clause extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="phrases">The 'SELECT' phrases.</param> /// <returns>The command.</returns> public static APSqlInsertCommand subQuery(this APSqlInsertCommand command, APSqlSelectCommand subQuery, params APSqlSelectPhrase[] phrases) { command.SubQuery = subQuery; command.SelectClause = new APSqlSelectClause(phrases); return command; }
/// <summary> /// Parse 'SELECT COUNT(*)' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> protected abstract DbCommand ParseSizeOfSelect(APSqlSelectCommand command);
/// <summary> /// SQL SubQuery clause extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="subQuery">SubQuery.</param> /// <param name="phrases">The 'SELECT' phrases.</param> /// <returns>The command.</returns> public static APSqlInsertCommand subQuery(this APSqlInsertCommand command, APSqlSelectCommand subQuery, IEnumerable<APSqlSelectPhrase> phrases) { command.SubQuery = subQuery; command.SelectClause = new APSqlSelectClause(phrases); return command; }
protected override DbCommand ParseSelect(APSqlSelectCommand command) { StringBuilder sb = new StringBuilder(); SqlCommand dbCmd = new SqlCommand(); using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable)) { WriteSelect(writer, command, command.Take ?? 0); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); if (command.Skip != null && command.Skip > 0) { writer.WriteLine(); if (command.WhereClause == null || command.WhereClause.Next == null) { writer.WriteDirect("WHERE"); } else { writer.WriteDirect("AND"); } writer.Write(command.PrimeryKeyExpr.SelectExpr); writer.Write("NOT IN ("); { // sub query WriteSelect(writer, new APSqlSelectCommand(command.PrimeryKeyExpr), command.Skip.Value); writer.Idented++; WriteFrom(writer, command.FromClause, dbCmd); WriteWhere(writer, command.WhereClause, dbCmd); WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); writer.Idented--; } writer.Write(")"); } WriteGroupBy(writer, command.GroupByClause, dbCmd); WriteHaving(writer, command.HavingClause, dbCmd); WriteOrderBy(writer, command.OrderByClause); } dbCmd.CommandText = sb.ToString(); return dbCmd; }
/// <summary> /// Build 'SELECT' command. /// </summary> /// <param name="command">The 'SELECT' command.</param> /// <returns>The DbCommand.</returns> public override DbCommand BuildSelectCommand(APSqlSelectCommand command) { return _parser.BuildSelectCommand(command); }
/// <summary> /// Build DB command to create a view. /// </summary> /// <param name="name">View name.</param> /// <param name="command">The command to create view.</param> /// <returns>The DbCommand.</returns> public abstract DbCommand BuildCreateViewCommand(string name, APSqlSelectCommand command);