HomeActive Query BuilderReplacing some or all condition string rexpressions with a parameter

Replacing some or all condition string rexpressions with a parameter


I need to alter the conditionstrings based on user selection.
IE.:
Consider the following statement :

Select * from Table WHERE Field1='x' and field2<>'y' or field1='y' and field2<>'x'

User has selected that the condition "Field1='x'" should be a parameter - the query is to be exported to Excel and thus the query that is injected into an Excel workbook should look like :

Select * from Table WHERE Field1=? and field2<>'y' or field1='y' and field2<>'x'

How can I accomplish that task?

I can iterate through all the CriteriaList.Items and find the corresponding CriteriaItem, and I need to convert that CriteriaItem to an SQLExpressionItem, but the ExpressionItem that comes from it is a SQLExpressionColumn, not a SQLExpressionBinary....

I am really flustered about this... is there som easy way to alter the RExpression values of a CriteriaItem?


Martin Moesby | email
June 1 2010, 06:49 AM
---------------------CODESNIPPET ON ----------------------------
.
.
.
.
if (useAsParameter[i])
{
foreach (CriteriaItem itm in queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.CriteriaList.Items)
{
if (itm.ConditionCount != 0)
{
for (int j = 0; j < itm.ConditionStrings.Count; j++)
{
SQLExpressionItem condition = new SQLExpressionItem(newSQL.SQLContext);
condition = itm.GetExpression();
itm.ConditionStrings[j] = replaceConditionWithParameter(newItem);
}
}
}
.
.
.
.

FUNCTION replaceConditionWithParameter :

private string replaceConditionWithParameter(SQLExpressionItem condition)
{
if (condition is SQLExpressionBetween)
{
SQLExpressionBetween obj = condition as SQLExpressionBetween;
if (obj.HaveNot)
return obj.LExpression.SimpleSQL(plainTextSQLBuilder1.SQLBuilder).ToString() + " not between ? and ?";
else
return obj.LExpression.SimpleSQL(plainTextSQLBuilder1.SQLBuilder).ToString()+ " between ? and ?";
}
else if (condition is SQLExpressionOperatorBinary)
{
SQLExpressionOperatorBinary obj = condition as SQLExpressionOperatorBinary;
return obj.LExpression.SimpleSQL(plainTextSQLBuilder1.SQLBuilder).ToString() + " " + obj.OperatorObj.ToString() + " ?";
}
else if (condition is SQLExpressionLikeTemplate)
{
SQLExpressionLikeTemplate obj = condition as SQLExpressionLikeTemplate;
if (obj.HaveNot)
return obj.LExpression.SimpleSQL(plainTextSQLBuilder1.SQLBuilder) + " not like ?";
else
return obj.LExpression.SimpleSQL(plainTextSQLBuilder1.SQLBuilder) + " like ?";
}
return "";
}

-------------------- CODESNIPPET OFF ------------------------------


I hope this makes sense.....
Martin Moesby | email
6 mins since original post
The above snippet is my current code, but it doesn't work.

The line "condition = itm.getExpression()" returns a SQLExpressionCOlumn, not a SQLExpressionBinary....

So...is there a way to easily replace some or all conditions with parameters?
Martin Moesby | email
9 mins since original post
This topic is closed