{"id":849,"date":"2011-03-25T02:30:03","date_gmt":"2011-03-25T07:30:03","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=849"},"modified":"2012-01-29T02:44:13","modified_gmt":"2012-01-29T07:44:13","slug":"add-remove-and-modify-condition-in-where-clause-net-version","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/add-remove-and-modify-condition-in-where-clause-net-version\/","title":{"rendered":"Add, remove and modify condition in where clause(.NET version)"},"content":{"rendered":"

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. <\/p>\n

#### Add a condition<\/strong><\/h4>\n

####

####

    \r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3)\r\n<\/pre>\nAfter executing this C# code, we can add a new condition “ClientID > 2” to where clause.<\/p>\n\r\nTSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];\r\nwhereCondition w = new whereCondition(select.WhereClause);\r\nw.add(conditionType.and, \"ClientID > 2\");\r\n<\/pre>\nNow, the SQL become this, easy and clean:<\/p>\n\r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3) AND ClientID > 2\r\n<\/pre>\nRemove a condition<\/strong><\/h4>\n\r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3) AND ClientID > 2\r\n<\/pre>\nAfter executing this C# code, we can remove condition “ClientID > 2” from the where clause.<\/p>\n\r\nTSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];\r\nwhereCondition w = new whereCondition(select.WhereClause);\r\nw.remove(\"ClientID > 2\");\r\n<\/pre>\nNow, the SQL becomes this, easy and clean:<\/p>\n\r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3)\r\n<\/pre>\nReplace\/modify a condition<\/strong><\/h4>\n\r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3) AND ClientID > 2\r\n<\/pre>\nAfter executing this C# code, we can replace condition “ClientID > 2” with “ClientID > 3” in the where clause.<\/p>\n\r\nTSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];\r\nwhereCondition w = new whereCondition(select.WhereClause);\r\nw.replace(\"ClientID > 2\", \"ClientID > 3\");\r\n<\/pre>\nNow, the SQL becomes this, easy and clean:<\/p>\n\r\nSELECT clientid,\r\n       phone\r\nFROM   client\r\nWHERE  clientid IN ( 1, 2, 3) AND ClientID > 3\r\n<\/pre>\nexpressions in where clause was organized as a binary tree structure, and can be visited in preorder\/inorder\/postorder<\/a>. you can anything you like by iterate this expression binary tree.<\/p>\nHere is another article had a detailed explanation about the expression structure<\/a>.<\/p>\nDownload this demo: C# version<\/a>, Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n