Introduction: Crystal Syntax
In order to understand why operators perfectly fit this usage scenario we need to have a short glimpse at the Crystal
- Column References
References to Columns of the underlying data source are written as {TableName.ColumnName}. So if you have a dataset with a table called Customer and a Column called name, you would write {Customer.Name}.
- Comparison Operators
You can use comparison operators in order to compare a column value to any given value. Beneath the standard comparison operators like ‘less than’ or ‘greater than’ or ‘equals’ you also have some string operators like ‘starts with’ or ‘like’. So you could write something like the following: {Customer.Name} startswith ‘A’.
- Boolean Operators
Simple Expressions like the one shown above can be concatenated by using Boolean operators. Crystal Reports supports Not, And, Or, Xor, Eqv and Imp. Here’s an example: {Customer.Name} startsWith ‘A’ and {Customer.Age} < 18.
I think that’s enough for the moment to get started. If you want to see more operators or want to play around with selection formulas use the formula editor of the Crystal Reports designer in Visual Studio.
Crystal Syntax Converter
I don’t feel comfortable writing magic strings in code without getting them checked by some instance. Selection formulas can become very complex and error prone. So I searched for a solution to write down C# code and get the string generated out of it. My first approach was some kind of fluent API, so I could write something like the following:
Customer.NameColumn.StartsWith(“A”).And(Customer.AgeColumn.LessThan(18))
The result of this expression is equivalent to the Boolean Operators example above. But it’s much longer and more difficult to read. So I dropped that approach and turned to operator overloading. As stated at the beginning, operators are very powerful when used in appropriate situations. For our example we need to overload binary operators in order to get boolean operators and of course the comparison operators.
Let’s start with the very simplest expression. We want to use a column of our data source and compare it to any other value (e.g. a string or number). I start by writing a simple extension method, because I don’t want to write down DataSet.Table.Column every time and I need a custom object where I can define the overloaded operators. The custom class will be called CrystalColumn and the extension method looks like this:
public static CrystalColumn ToCrystalColumn(this DataColumn dc)
{
var sb = new StringBuilder("{");
sb.Append(dc.Table.TableName);
sb.Append(".");
sb.Append(dc.ColumnName);
sb.Append("}");
return new CrystalColumn(sb.ToString());
}
The method simply takes in a DataColumn and creates a string representation of it according to the Crystal Syntax. So now we a are able to write:
var firstname = dataSet.Customer
.FirstNameColumn.ToCrystalColumn();
The next step would be to overload the needed operators on the CrystalColumn class. Here’s the example of the ‘less than’ operator:
public static SingleExpression operator <(CrystalColumn column, int value)
{
return new SingleExpression(column.ToString() + " < " + value);
}
The method needs to be static and the first parameter depicts the object on which the operator is defined. The provided objects are converted to the according string representation and passed to a new instance of the SingleExpression class. This class encapsulates a simple expression. With the code so far we can write the following:
var age = dataSet.Customer.AgeColumn.ToCrystalColumn();
var singleExpression = age < 18;
That feels much more natural and even looks better, doesn’t it? Here’s another example of the ‘starts with’ operator, which is of course no operator in the sense of C# operators but we need it for our expression:
public SingleExpression StartsWith(params string[] s)
{
var startsWithExpression = "'" + s[0] + "'";
if (s.Length > 1)
{
startsWithExpression = "[";
for (int i = 0; i < s.Length; i++)
{
startsWithExpression += "'" + s[i] + "'";
if (i < s.Length - 1)
startsWithExpression += ",";
}
startsWithExpression += "]";
}
var expression = columnExpression + " startswith " + startsWithExpression;
return new SingleExpression(expression);
}
The ’starts with’ operator in Crystal Reports acceppts an array of strings. That’s why the method acceppts a string array as parameter. Again a string represenation of the provided objects is created and passed to a new instance of the SingleExpression class. This method allows us to write:
var firstname = dataSet.Customer.FirstNameColumn.ToCrystalColumn();
var singleExpression = firstname.StartsWith("Mike", "John");
What’s missing now is the ability to combine two or more simple expressions by employing boolean operators to form complex expressions. In order to achieve this, we can now overload the needed binary operators on the SingleExpression class. For example the ‘And’ operator can be overloaded as follows:
public static Expression operator &(SingleExpression ex1, SingleExpression ex2)
{
return ex1.And(ex2);
}
private Expression And(SingleExpression singleExpression)
{
return new Expression(this,
singleExpression, LogicalOperator.and);
}
The operator overload takes a second SingleExpression instance and calls the private ‘And’ method. This method in turn creates a new Expression instance that encapsulates two or more simple expressions and finally generates the needed string representation. As another example I will show you the implementation of the ‘Not’ operator:
public static SingleExpression operator !(SingleExpression ex)
{
return new SingleExpression(
"not (" + ex.ToString() + ")");
}
The method takes only one argument, because the ’Not’ operator is an unary operator acting on a single argument. According to this only a simple expression is returned.
Putting it all together
With the classes and method we have so far, we can now write our example expression using operators:
var firstname = dataSet.Customer.FirstNameColumn.ToCrystalColumn();
var age = dataSet.Customer.AgeColumn.ToCrystalColumn();
var expression = firstname.StartsWith("A", "B") & age < 18;
When you compare that statement to the first approach, I think it’s easy to see that the readability improved a lot. We can also compare it to the resulting formula in Crystal Syntax and see that it is as nearly as short as the formula:
{Customer.FirstName} startswith ['A','B'] and {Customer.Age} < 18
The Expression class overwrites its ToString method in order to write out the complete selection formula, so you can now easily pass it to the report viewer:
crystalViewer.SelectionFormula = expression.ToString();
Summary
This was only a short example of operator overloading in C# but I hope you noticed the great benefit of using them. If you need more information about C# operators just search the internet. It’s not a new concept. Some words of wisdom: Operators are not reasonable in every situation. Additionally when you use them be sure to use them the correct way and implement only operations that act the way a user would expect them to do. If you for example implement the ‘+’ operator for a custom number class, don’t return the difference. People are anticipating the results of common operations and get confused if something strange happens.
No comments:
Post a Comment