public string BuildWhereStatement(bool useCommandObject, ref DbCommand usedDbCommand, ContainsClause _contains, string relation = "")
{
var whereGroupDictionary = new Dictionary <string, string>();
string Result = "";
foreach (List <WhereClause> WhereStatement in this) // Loop through all statement levels, OR them together
{
string LevelWhere = "";
foreach (WhereClause Clause in WhereStatement) // Loop through all conditions, AND them together
{
// if(Clause.TableRelations!=string.Empty && !Clause.TableRelations.Equals(relation)) continue;
if (!string.IsNullOrEmpty(relation) && Clause.TableRelations != string.Empty && !Clause.TableRelations.Equals(relation))
{
continue;
}
string WhereClause = "";
if (useCommandObject)
{
// Create a parameter
string parameterName = string.Format(
"@p{0}_{1}",
usedDbCommand.Parameters.Count + 1,
Clause.FieldName.Replace('.', '_')
);
DbParameter parameter = usedDbCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = Clause.Value;
usedDbCommand.Parameters.Add(parameter);
// Create a where clause using the parameter, instead of its value
WhereClause += CreateComparisonClause(Clause.FieldName, Clause.ComparisonOperator, new SqlLiteral(parameterName));
}
else
{
WhereClause = CreateComparisonClause(Clause.FieldName, Clause.ComparisonOperator, Clause.Value);
}
foreach (WhereClause.SubClause SubWhereClause in Clause.SubClauses) // Loop through all subclauses, append them together with the specified logic operator
{
switch (SubWhereClause.LogicOperator)
{
case LogicOperator.And:
WhereClause += " AND "; break;
case LogicOperator.Or:
WhereClause += " OR "; break;
}
if (useCommandObject)
{
// Create a parameter
string parameterName = string.Format(
"@p{0}_{1}",
usedDbCommand.Parameters.Count + 1,
Clause.FieldName.Replace('.', '_')
);
DbParameter parameter = usedDbCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = SubWhereClause.Value;
usedDbCommand.Parameters.Add(parameter);
// Create a where clause using the parameter, instead of its value
WhereClause += CreateComparisonClause(Clause.FieldName, SubWhereClause.ComparisonOperator, new SqlLiteral(parameterName));
}
else
{
WhereClause += CreateComparisonClause(Clause.FieldName, SubWhereClause.ComparisonOperator, SubWhereClause.Value, SubWhereClause.CompareParam);
}
}
// LevelWhere += "(" + WhereClause + ") AND "; --------------------------------------------------- added for demo need to change this portion... .... \\\\\\\\\\\\\\\\\\\\\\\\
if (!string.IsNullOrEmpty(Clause.WhereGroup))
{
whereGroupDictionary.Add(Clause.FieldName, WhereClause);
}
else
{
LevelWhere += "(" + WhereClause + ") AND ";
}
}
LevelWhere = LevelWhere.Substring(0, LevelWhere.Length - 5); // Trim de last AND inserted by foreach loop
if (_contains.containsList != null && _contains.containsList.Any())
{
LevelWhere += "AND CONTAINS((Name, Color), 'Red')";
}
if (WhereStatement.Count > 1)
{
Result += " (" + LevelWhere + ") ";
}
else
{
Result += " " + LevelWhere + " ";
}
Result += " OR";
}
Result = Result.Substring(0, Result.Length - 2); // Trim de last OR inserted by foreach loop
if (whereGroupDictionary.Count > 0)
{
Result = Result.TrimStart();
Result = Result.TrimEnd();
Result = Result.Substring(1, Result.Length - 2); /// Trim first and second brace
var groupRes = "";
foreach (var item in whereGroupDictionary)
{
groupRes += "(" + item.Value + ") OR";
}
groupRes = groupRes.Substring(0, groupRes.Length - 2); /// remove last or
Result = "(" + Result + " AND " + "(" + groupRes + ") )";
}
return(Result);
}
public string BuildWhereStatement(ContainsClause _contains, string relation = "")
{
DbCommand dummyCommand = null; // = DataAccess.UsedDbProviderFactory.CreateCommand();
return(BuildWhereStatement(false, ref dummyCommand, _contains, relation));
}
