CODEWITHSUNDEEP
c#sql-server
Ali_dotNet
Ali_dotNet

Reputation: 3279

detecting SQL queries from string in C#

I've googled a lot about my question and even looked at many QAs here in SO, but couldn't find what I was looking for, anyway sorry if it is a duplicate question. This is my scenario: I have a string in C#, and it should be used as a SQL Server query. But this string can be anything, from a simple (select A from B) to a complicated SP (which may contain several commands). How can I explicitly DETECT each query in this string? For instance let's say we have following string:

select columnA from tblB
delete tblD where ID='100'
insert into tblF (name) values ('test')

above text is clearly consisted of 3 different queries, each of which should be executed in its own specific way, i.e. the first one (select) needs ExecuteReader, and I should display a table of results, while the others require ExecuteNonQuery. So internally I should run 3 SQL queries, but the problem is how can I differentiate between these 3 queries? I cannot simply split string with '\n' as in SQL, a query can be written in multiple lines. What is the best/correct way of detecting different queries. (SQL Server Management Studio is a perfect example of what I'm looking for) Should I use SQL Parsers? If so, what should I be looking for? I've seen a lot of parsers also, but am really confused as I don't know what should I exactly expect from them.

I did my best to describe the problem! Thanks in advance

Upvotes: 3

Views: 1154

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

You can add a reference to Microsoft.SqlServer.TransactSql.ScriptDom then this is quite straight forward (at least for the happy path)

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.IO;

namespace MyApp
{
    class Program
    {
        static void Main(string[] args)
        {

            var sql = @"
SELECT columnA
FROM   tblB
WHERE  ColumnB = 1

DELETE tblD
WHERE  ID = '100'

INSERT INTO tblF
            (NAME)
VALUES      ('test'),
            ('foo'),
            ('bar') 
";

            var parser = new TSql100Parser(true); //Or TSql120Parser or whatever
            IList<ParseError> errors = new List<ParseError>();

            TSqlScript script = (TSqlScript)parser.Parse(new StringReader(sql), out errors);

            //TODO: Don't ignore errors
            foreach (var batch in script.Batches)
            {
                foreach (var st in batch.Statements)
                {
                    Console.WriteLine(st.GetType().Name);
                    Console.WriteLine(sql.Substring(st.StartOffset, st.FragmentLength));
                    Console.WriteLine();
                }
            }
        }
    }
}

Returns

enter image description here

Upvotes: 6

Related Questions