/// <summary>
/// Returns a Select with aggregate functions
/// </summary>
/// <param name="aggegateSelectFields">
/// Aggregate fields definitions
/// </param>
/// <param name="table">
/// Table to query
/// </param>
/// <param name="filters">
/// Filters to apply on Select
/// </param>
/// <param name="columnsToGroup">
/// Columns for group by
/// </param>
/// <returns>
/// Select Sentence with aggregate functions
/// </returns>
public virtual Command Select(SelectAggregate select)
{
//Validating arguments
if (select == null)
{
throw new ArgumentNullException("select");
}
//Building the select sentence
Command command = "SELECT ";
//Crossing the fields definitions
foreach (var column in select.AggregateColumns)
{
command.Script += SelectAggregateColumn(column) + ", ";
}
//Removing the last commas
command.Script = command.Script.TrimEnd(',', ' ') + " ";
command.Append(FromClause(select));
command.Append(WhereClause(select.Where, LogicalOperator.And));
command.Append(GroupByClause(select.GroupBy));
command.Append(OrderByClause(select.OrderBy));
command.Append(LimitClause(select.Limit));
command.Script += ScriptSeparator;
//Returning the result
return(command);
}
protected virtual Command Filter(InFilter filter)
{
//Validating if there are filters defined
if (filter == null)
{
return(null);
}
Command command = new Command();
command.Append(ColumnFullName(filter));
command.Append(InClause(filter.Values.ToList()));
return(command);
}
protected virtual Command Filter(IEnumerable <FilterBase> filters, LogicalOperator logicalOperator)
{
//Local Vars
Command command = new Command();
//Validating if there are filters defined
if (filters == null || filters.Count() == 0)
{
return(null);
}
//Getting the logical operator for merge filters
string logicalOpr = Operator(logicalOperator);
//Crossing the filters
foreach (FilterBase f in filters)
{
//Adding filter condition
var c = Filter(f);
command.Append(c);
command.Script += Operator(logicalOperator);
}
//Removing last logic operator
command.Script = command.Script.Remove(command.Script.Length - logicalOpr.Length, logicalOpr.Length);
return(command);
}
/// <summary>
/// Returns the Sql sentence for select all the object's
/// of the specified Table, applying a filter collection
/// </summary>
/// <param name="table">
/// Table that you desire to select
/// </param>
/// <param name="filters">
/// Collection with the filters to apply on the query
/// </param>
/// <param name="orderName">
/// Name of the order definition used to sort results,
/// or null if not used
/// </param>
/// <returns>
/// Sql sentence for select all the object's of the
/// specified Table, applying a filter collection
/// </returns>
public virtual Command Select(Select select)
{
//Validating if the table argument is null
if (select == null)
{
throw new ArgumentNullException("select");
}
if (select is SelectAggregate)
{
return(Select((SelectAggregate)select));
}
//fix column filters tables names, if they come from a join, we should use the join alias if it exist
foreach (ColumnFilter filter in select.Where.Where(f => f is ColumnFilter))
{
//this filter is using a join table's column, so we need to the join's table alias
if (filter.Column.Table != select.Table)
{
foreach (SelectJoin join in select.Joins)
{
//this filter is unsong this join's table, so we assign it the table alias from the join
if (filter.Column.Table == join.Table && !string.IsNullOrWhiteSpace(join.Alias) && join.Table.Name != join.Alias)
{
filter.TableAlias = join.Alias;
}
}
}
}
//Creating Select sentence
Command command = SelectClause(select);
command.Append(FromClause(select));
command.Append(WhereClause(select.Where, LogicalOperator.And));
command.Append(OrderByClause(select.OrderBy));
command.Append(LimitClause(select.Limit));
command.Script += ScriptSeparator;
//Returning the sql sentence
return(command);
}
protected virtual Command OnClause(IEnumerable <FilterBase> filters, LogicalOperator logicalOperator)
{
//Validating if there are filters defined
if (filters == null || filters.Count() == 0)
{
return(null);
}
//Adding ON clause
Command command = " ON ";
command.Append(Filter(filters, logicalOperator));
return(command);
}
/// <summary>
/// Creates the From Clause for a Select Sql Sentence
/// </summary>
/// <param name="table">
/// Table for From Clause creation
/// </param>
/// <returns>
/// From Clause from the Select Sql Sentence
/// </returns>
protected virtual Command FromClause(Select select)
{
//Validating if table argument is null
if (select == null)
{
throw new ArgumentNullException("select");
}
//Creating the From Clause
Command command = " From " + EncloseName(select.Table.Name);
//resolve joins
foreach (SelectJoin join in select.Joins)
{
command.Script += " " + join.JoinType.ToString().ToUpper() + " JOIN " + EncloseName(join.Table.Name) + (string.IsNullOrWhiteSpace(join.Alias)? string.Empty : " " + join.Alias) + " ON ";
command.Append(Filter(join.On, LogicalOperator.And));
}
//Returning the From sql Clause
return(command);
}
protected virtual Command Filter(RangeFilter filter)
{
//Validating if there are filters defined
if (filter == null)
{
return(null);
}
Command command = new Command();
CommandParameter minParam = new CommandParameter(filter.MinValue, null, filter.Column.DbType);
command.Parameters.Add(minParam);
CommandParameter maxParam = new CommandParameter(filter.MaxValue, null, filter.Column.DbType);
command.Parameters.Add(maxParam);
command.Append
(
LogicalExpression
(
EqualityComparison
(
ColumnFullName(filter),
minParam.Name,
CompareOperator.GreaterThanEqual
),
EqualityComparison
(
ColumnFullName(filter),
maxParam.Name,
CompareOperator.LessThanEqual
),
LogicalOperator.And,
false
)
);
return(command);
}
protected virtual Command Filter(IEnumerable<FilterBase> filters, LogicalOperator logicalOperator)
{
//Local Vars
Command command = new Command();
//Validating if there are filters defined
if (filters == null || filters.Count() == 0) return null;
//Getting the logical operator for merge filters
string logicalOpr = Operator(logicalOperator);
//Crossing the filters
foreach (FilterBase f in filters)
{
//Adding filter condition
var c = Filter(f);
command.Append(c);
command.Script += Operator(logicalOperator);
}
//Removing last logic operator
command.Script = command.Script.Remove(command.Script.Length - logicalOpr.Length, logicalOpr.Length);
return command;
}
protected virtual Command Filter(RangeFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
CommandParameter minParam = new CommandParameter(filter.MinValue, null, filter.Column.DbType);
command.Parameters.Add(minParam);
CommandParameter maxParam = new CommandParameter(filter.MaxValue, null, filter.Column.DbType);
command.Parameters.Add(maxParam);
command.Append
(
LogicalExpression
(
EqualityComparison
(
ColumnFullName(filter),
minParam.Name,
CompareOperator.GreaterThanEqual
),
EqualityComparison
(
ColumnFullName(filter),
maxParam.Name,
CompareOperator.LessThanEqual
),
LogicalOperator.And,
false
)
);
return command;
}
protected virtual Command Filter(InFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
command.Append(ColumnFullName(filter));
command.Append(InClause(filter.Values.ToList()));
return command;
}