Examining the "Where" clause of a SQL in C#

Question

1.00/5 (1 vote)

See more:

Hi,

I got stuck up in validating the SQL which has been received as a Text Box input from the User, here we go; User is entering the complex SQL with lot of Conditions, sub queries in a "Where" clause, and my portion is to extract the count of Conditions in the "Where" clause of a input SQL.

for the below SQL, the value should return as "4", though I have mulitple conditions used in the Sub Query, I need to get the count of Conditions that have been used in the Base SQL.(Sub Queries in the Where clause should not be taken into account)

SQL

Select *
from table_1
where c1=232 and c2=44 and c3 in (Select test from table_2 where table_2.col='Con_val") and rownum<=100;

In order to achieve the exact count of Where conditions in a SQL, is there any predefined Method or function available in C#.

Awaiting for the response from C# experts.

Thanks in advance,
Priya

Posted 5-Mar-13 6:29am

Priyavadhanam

Updated 5-Mar-13 7:15am

Maciej Los

v3

Add a Solution

Comments

Anurag Sinha V 5-Mar-13 12:44pm

Hey,
i dont think there is any pre-defined method or function as such, but may be you can write a sp, where you might wana count the number of 'and' and 'or' keywords, after the where clause.
You would have to do some customizations so that the sp doesnt take into account the sub-query and stuffs.
And in the end if you are bale to make out the correct number of 'and' and 'or' keywords, then add one to them and you would get the count of conditions in the where clause.
You might wait for some experts opinion too.
Hope the above helps.

Regards
Anurag

Priyavadhanam 5-Mar-13 12:49pm

Anurag,
Thanks for your suggestions. I tried up writing a complex for loop processing the AND, OR Key words using SubString, IndexOf methods. but this again failed when I have a "Where" clause inside the Sub Query with a multiple AND, OR key words.

So, should try for some other approach...
Anyways, thanks for your comment..

Sergey Alexandrovich Kryukov 5-Mar-13 13:30pm

Why?!
—SA

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-03-05T06:48:00

Solution 1

Statement parsing is quite complicated. Since you need to follow the syntax of that specific implementation. Even those implementation claim to be SQL:2001 compliant for example, for sure there will be considerable differences in the syntax. Thus for a complex and really robust solution, you need to implement yourself the query parser of that RDBMS specific implementation. The full syntax of Oracle query language in BNF notation can be found here: http://docs.oracle.com/javaee/5/tutorial/doc/bnbuf.html[^]

This one might be a good starting point: SQL Parser[^]

Posted 5-Mar-13 6:48am

Zoltán Zörgő

Comments

Maciej Los 5-Mar-13 13:19pm

Exactly!
+5!

Zoltán Zörgő 5-Mar-13 16:16pm

Thank you.

Sergey Alexandrovich Kryukov 5-Mar-13 13:31pm

My 5, but... don't you think that the root reason is that OP does not know how to use SQL properly. She never explained the real goal of it. Parsing the query may be just a huge abuse based in lack of understanding of technology...
—SA

Zoltán Zörgő 5-Mar-13 16:16pm

Thank you. Yappp... can happen...

recoverinfotech · Answer 2 · 2013-03-05T13:13:00

Solution 2

use Substring and string.count for IN , OR , AND , and all the sql key words witch are using in where case .
but there is no seance to count Conditions in the "Where" . cos if you pre know the query use Sql Management Studio for this kind of validation

Posted 5-Mar-13 13:13pm

recoverinfotech

Examining the "Where" clause of a SQL in C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Examining the "Where" clause of a SQL in C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us