c# extract sql query sections from string

Question

Hey all I am looking for a clean way of extracting sections of my SQL query string.

What I mean is this:

Say I have a query like:

SELECT 
    poc, event, network, vendor 
FROM 
    bLine 
WHERE 
    network = 'something' 
AND 
    event = 'simple' 
OR 
    event != 'hard' 
ORDER BY 
    poc ASC

I'm looking for splitting each up in its own section (using Dictionary as a way of storing the 2 values each):

Key      |Value
-------------------------------------
SELECT   |poc, event, network, vendor
FROM     |bLine
WHERE    |network = 'something' 
AND      |event = 'simple' 
OR       |event != 'hard' 
ORDER BY |poc 
??       |ASC

Anyone happen to have something like this already? I've tried THIS example on SO since it seemed to be what I am looking to do but the code does not seem to work:

with errors saying:

Error CS0117 'TokenInfo' does not contain a definition for 'Start'

Error CS0117 'TokenInfo' does not contain a definition for 'End'

Error CS0117 'TokenInfo' does not contain a definition for 'IsPairMatch'

ETC ETC....

UPDATE

Seems that this SO example HERE does what I need it to do and has no errors! :)

A few different issues - What about more complex statements such as `Select into...` or `Group By`, multiple `Joins`, nested SQLs, etc. But, given your example above, what about multiple `And`s / `Or`s or nested (bracketed) criteria in your `Where` clause?.... This may not be the right way to go about this.... — John Bustos, Jun 07 '18 at 15:21
it wont have **INTO, GROUP, JOINS, etc** It will only have what I listed in the OP query. — StealthRT, Jun 07 '18 at 15:29

Sandeep Ingale · Answer 1 · 2018-06-07T20:02:52.277

if you have a fixed string all the time you can use something like this.This is my attempt to solve the problem I can say this is not the most efficient or advance way to do it but it's one way to do it.

        string[] Params = { "SELECT", "FROM", "WHERE", "AND", "OR", "ORDER BY" };
        Dictionary<string, string> sqlSource = new Dictionary<string, string>();
        string sqlString = "SELECT poc, event, network, vendor FROM bLine WHERE network = 'something'";
        int i, j;

        //iterate through all the items from Params array
        for (i = 0; i < Params.Length; i++)
        {

            string result = "";

            //take next element from Params array for SELECT next will be FROM
            for (j = i + 1; j < Params.Length; j++)
            {
                int ab = sqlString.IndexOf(Params[j]);
                //Get the substring between SELECT and FROM
                if (ab > 0)
                {
                    int pFrom = sqlString.IndexOf(Params[i]) + Params[i].Length;
                    int pTo = sqlString.LastIndexOf(Params[j]);
                    result = sqlString.Substring(pFrom, (pTo - pFrom));
                }


                //if there is a string then break the loop  
                if (result != "")
                {
                    sqlSource.Add(Params[i], result);
                    break;
                }
            }

            //if result is empty which is possible after FROM clause if there are no WHERE/AND/OR/ORDER BY then get substring between FROM and end of string
            if (result == "")
            {
                int pFrom = sqlString.IndexOf(Params[i]) + Params[i].Length;
                int pTo = sqlString.Length;
                result = sqlString.Substring(pFrom, (pTo - pFrom));
                sqlSource.Add(Params[i], result);
                break;
            }

        }

c# extract sql query sections from string

1 Answers1