Add, remove and modify condition in where clause(.NET version)

This article shows how to use General SQL Parser to add, remove and modify condition in where clause, this feature enables your .NET(C#, VB.NET) application manipulate expression in where clause dynamically and create your own filter to fetch desired data set from database.

Add a condition

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3)

After executing this C# code, we can add a new condition “ClientID > 2″ to where clause.

TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
whereCondition w = new whereCondition(select.WhereClause);
w.add(conditionType.and, "ClientID > 2");

Now, the SQL become this, easy and clean:

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3) AND ClientID > 2

Remove a condition

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3) AND ClientID > 2

After executing this C# code, we can remove condition “ClientID > 2″ from the where clause.

TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
whereCondition w = new whereCondition(select.WhereClause);
w.remove("ClientID > 2");

Now, the SQL becomes this, easy and clean:

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3)

Replace/modify a condition

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3) AND ClientID > 2

After executing this C# code, we can replace condition “ClientID > 2″ with “ClientID > 3″ in the where clause.

TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
whereCondition w = new whereCondition(select.WhereClause);
w.replace("ClientID > 2", "ClientID > 3");

Now, the SQL becomes this, easy and clean:

SELECT clientid,
       phone
FROM   client
WHERE  clientid IN ( 1, 2, 3) AND ClientID > 3

expressions in where clause was organized as a binary tree structure, and can be visited in preorder/inorder/postorder. you can anything you like by iterate this expression binary tree.

Here is another article had a detailed explanation about the expression structure.

Download this demo: C# version, Java version

sqlparser.com