I’ve been working with Microsoft SharePoint, on and off, for about 3 months, so don’t bite me if something in that ball of yarn already does this :-)
If you’ve been working with SharePoint you would have come across it’s lists. Lists are like tables in a database, they have columns/records etc. These list are queried via an xml based language called CAML.
A typical query would look like this:
<Where>
<Eq>
<FieldRef Name="SomeColumn1" />
<Value Type="Text" >SomeValue1</Value>
</Eq>
</Where>
This would return all the records which have a value in column “SomeColumn1” equal to “SomeValue1”. Simple, isn’t it.
The agonizing pain only surfaces when the queries get more complex eg,
<Where>
<Or>
<And>
<Eq>
<FieldRef Name="SomeColumn1" />
<Value Type="Text" >SomeValue1</Value>
</Eq>
<Eq>
<FieldRef Name="SomeColumn2" />
<Value Type="Text" >SomeValue2</Value>
</Eq>
</And>
<And>
<Eq>
<FieldRef Name="SomeColumn2" />
<Value Type="Text" >SomeValue2</Value>
</Eq>
<Eq>
<FieldRef Name="SomeColumn3" />
<Value Type="Text" >SomeValue3</Value>
</Eq>
</And>
</Or>
</Where>
Logically it is equivalent to this:
((SomeColumn1 == “SomeValue1”) AND (SomeColumn2 == “SomeValue2”)) OR ((SomeColumn3 == “SomeValue2”) AND (SomeColumn3 == “SomeValue3”))
Clearly writing this by hand will become unmanageable very quickly, especially if you want to exclude parts of the query based on, say, if a textbox have been left blank.
A simple framework
The solution I choose works like this. All the query keywords are represented by classes and implement the IQueryInterface interface:
interface IQueryElement
{
// The output of the element
string Output
{
get;
}
// Must return true if the element is to be included
bool Evaluate
{
get;
}
}
Thus something simple like equals is then implemented like this:
class QEq : ITestOperator
{
IQueryElement qe;
// Constructor
public QEq(IQueryElement queryElement)
{
qe = queryElement;
}
public string Output
{
get
{
return "<Eq>"+qe.Output+"</Eq>";
}
}
public bool Evaluate
{
get
{
if (qe == null)
return false;
return qe.Evaluate;
}
}
}
Note that it implements ITestOperator and not IQueryElement, infact ITestOperator itself implements IQueryElement. This is just to help classify Eq as something that does a test (for equality in this case), why would we want to do this ? For compile time type checking of course, lets take a look at where this comes into play.
The And keyword is a bit more complex, have a look at the downloadable code.
class QAnd : IBinaryOperator, ITestOperator
{
public QAnd(ITestOperator _qe1,ITestOperator _qe2)
.
.
}
The And operator is a binary operator, as you can see it takes two ITestOperator parameters in its constructor. If a developer tries to use anything but an ITestOperator he/she would get a compile time error.
Ok, that’s nice and all, but lets have a look at complete example:
// Define our tests
QEq someColumn1 = new QEq(new QFieldRefValue("SomeColumn1","SomeValue1",SPQueryTypes.Text));
// Specify the combination of our tests
QQuery query = new QQuery(new QWhere(someColumn1));
// Get the query
System.Console.WriteLine(query.Output);
Query 1
Yes, you guessed it, it’s the first query right at the top of this page. What’s that QFieldRefValue you ask ? Well, its:
class QFieldRefValue : IQueryElement
{
public QFieldRefValue(string _fieldname, string _qvalue, SPQueryTypes _type)
.
.
}
The QFieldRefValue class takes a column name a value and a type at it’s constructor, this represents the column and what it will be tested against. The QEq class wraps the QFieldRefValue class to test the “SomeColumn1” column for equality against “SomeValue1” which is of type Text, an enum (Add some more, I haven’t included them all).
The second xml query would look like this:
// Define our tests
QEq someColumn1 = new QEq(new QFieldRefValue("SomeColumn1","SomeValue1",SPQueryTypes.Text));
QEq someColumn2 = new QEq(new QFieldRefValue("SomeColumn2","SomeValue2",SPQueryTypes.Text));
QEq someColumn3 = new QEq(new QFieldRefValue("SomeColumn3","SomeValue3",SPQueryTypes.Text));
// Specify the combination of our tests
QQuery query = new QQuery(
new QWhere(
new QOr(
new QAnd(someColumn1,someColumn2),
new QAnd(someColumn2,someColumn3)
)));
// Get the query
System.Console.WriteLine(query.Output);
Query 2
That’s quite a bit more maintainable.
An added bonus
I can hear you cry, “It would never stay that simple because I have a dynamic beast of a query!”, fear not, the evaluate method you saw in the IQueryElement interface has a purpose.
So you have a query which lets the user search on 3 columns, name, surname and hobby. The query uses ANDs like in query 2 to search for records which have all three the fields, but what happens when the name textbox is left empty ? If you decide to exclude the name for the query all you need to change (just taking the name for now) is :
// Define our tests
QEq name = new QEq(
new QFieldRefValue("Name",
txtName.Text.Equals(“”)? null : txtName.Text,
SPQueryTypes.Text));
Query 3
This will set the value of the test to null if the textbox’s text is empty, and guess what ? The query will sort itself out. That means that you don’t have to pay special attention to the final query you build with all the composite ANDs and ORs.
Give the downloadable code a try(http://www.reyn.co.za/index.php?pr=SP_Query_Builder). If you find a bug, found a better way of doing this or start using this for your work/own project, drop a comment.