protected static string ApplyWhereSubOperations(esComparison.esComparisonData comparisonData)
{
string sql = string.Empty;
if (comparisonData.HasExpression)
{
sql += GetMathmaticalExpressionColumn(comparisonData.Expression);
if (comparisonData.SubOperators != null && comparisonData.SubOperators.Count > 0)
{
sql = BuildSubOperationsSql(sql, comparisonData.SubOperators);
}
return(sql);
}
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return(sql);
}
protected static string ApplyWhereSubOperations(StandardProviderParameters std, esDynamicQuerySerializable query, esComparison.esComparisonData comparisonData)
{
string sql = string.Empty;
if (comparisonData.HasExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, comparisonData.Expression);
if (comparisonData.SubOperators != null && comparisonData.SubOperators.Count > 0)
{
sql = BuildSubOperationsSql(std, sql, comparisonData.SubOperators);
}
return(sql);
}
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(std, delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return(sql);
}
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuerySerializable query, List <esComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
DbType paramType = DbType.String;
string compareTo = String.Empty;
foreach (esComparison comparisonItem in items)
{
paramType = DbType.String;
esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
esDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
std.needsIntegerParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == esParenthesis.Open)
{
sql += "(";
}
else
{
sql += ")";
}
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case esConjunction.And: sql += " AND "; break;
case esConjunction.Or: sql += " OR "; break;
case esConjunction.AndNot: sql += " AND NOT "; break;
case esConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary <string, SqlParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as esDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case esComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case esComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case esComparisonOperand.Equal:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
}
else
{
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
}
else
{
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
}
else
{
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
}
else
{
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
}
else
{
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
}
else
{
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
}
break;
case esComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")";
paramType = DbType.AnsiStringFixedLength;
needsStringParameter = true;
break;
case esComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case esComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case esComparisonOperand.In:
case esComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == esComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
{
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
}
else
{
sql += comma + Convert.ToString(o);
}
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case esComparisonOperand.Between:
SqlCommand sqlCommand = std.cmd as SqlCommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
SqlParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = paramType;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
}
else
{
p = new SqlParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return(sql);
}
protected static string GetJoinStatement(StandardProviderParameters std, esDynamicQuerySerializable query)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (esJoinItem joinItem in iQuery.InternalJoinItems)
{
esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem;
switch (joinData.JoinType)
{
case esJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case esJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case esJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case esJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName((esProviderSpecificMetadata)iSubQuery.ProviderMetadata);
sql += " " + iSubQuery.JoinAlias + " ON ";
foreach (esComparison comparisonItem in joinData.WhereItems)
{
esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == esParenthesis.Open)
{
sql += "(";
}
else
{
sql += ")";
}
continue;
}
if (comparisonData.IsConjunction)
{
if (comparisonData.Conjunction == esConjunction.And)
{
sql += " AND ";
}
else
{
sql += " OR ";
}
continue;
}
sql += GetColumnName(comparisonData.Column);
switch (comparisonData.Operand)
{
case esComparisonOperand.Equal:
sql += " = ";
break;
case esComparisonOperand.NotEqual:
sql += " <> ";
break;
case esComparisonOperand.GreaterThan:
sql += " > ";
break;
case esComparisonOperand.LessThan:
sql += " < ";
break;
case esComparisonOperand.LessThanOrEqual:
sql += " <= ";
break;
case esComparisonOperand.GreaterThanOrEqual:
sql += " >= ";
break;
case esComparisonOperand.IsNull:
sql += " IS NULL ";
break;
case esComparisonOperand.IsNotNull:
sql += " IS NOT NULL ";
break;
case esComparisonOperand.In:
sql += " IN ";
break;
case esComparisonOperand.NotIn:
sql += " NOT IN ";
break;
}
if (comparisonData.ComparisonColumn.Name != null)
{
sql += GetColumnName(comparisonData.ComparisonColumn);
}
else
{
esDynamicQuerySerializable subQuery = comparisonData.Value as esDynamicQuerySerializable;
if (subQuery != null)
{
sql += GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ")";
}
else if (comparisonData.Value != null)
{
// Handle Literals
sql += comparisonData.Value.ToString();
}
}
}
sql += ")";
}
}
return(sql);
}
protected static string ApplyWhereSubOperations(StandardProviderParameters std, esDynamicQuerySerializable query, esComparison.esComparisonData comparisonData)
{
string sql = string.Empty;
string delimitedColumnName = GetColumnName(comparisonData.Column);
if (comparisonData.SubOperators != null)
{
sql = BuildSubOperationsSql(std, delimitedColumnName, comparisonData.SubOperators);
}
else
{
sql = delimitedColumnName;
}
return(sql);
}
