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