using Microsoft.SqlServer.TransactSql.ScriptDom for parse query with errors

Question

I use the following code to get a list of statements in the query:

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

namespace SqlTokenazer
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Tokenaze();
        }

        private void Tokenaze()
        {
            rtbLog.Clear();

            string script = "select * from dbo.Mytable where columnName = 0 delete from dbo.Mytable where columnName = 0";
            var sqlScript = ParseScript(script);

            PrintStatements(sqlScript);
        }

        public TSqlScript ParseScript(string script){
            IList<ParseError> parseErrors;
            TSql100Parser tsqlParser = new TSql100Parser(true);
            TSqlFragment fragment;
            using (StringReader stringReader = new StringReader(script))
            {
                fragment = (TSqlFragment)tsqlParser.Parse(stringReader, out parseErrors);
            }
            if (parseErrors.Count > 0)
            {
                var retMessage = string.Empty;
                foreach (var error in parseErrors)
                {
                    retMessage += error.Number + " - " + error.Message + " - position: " + error.Offset + ";\r\n";
                }
                rtbLog.Text += retMessage;
            }
            return (TSqlScript)fragment;

        }

        public void PrintStatements(TSqlScript tsqlScript)
        {

            if (tsqlScript != null)
            {
                foreach (TSqlBatch batch in tsqlScript.Batches)
                {
                    if (batch.Statements.Count == 0) continue;

                    foreach (TSqlStatement statement in batch.Statements)
                    {
                        rtbLog.Text += string.Format("{0}\r\n", statement.GetType().ToString());
                    }
                }

            }
        }
    }

}

Results:

Microsoft.SqlServer.TransactSql.ScriptDom.SelectStatement
Microsoft.SqlServer.TransactSql.ScriptDom.DeleteStatement

But when I make a mistake in query, a list of statements is empty :(

string script = "select * from dbo.Mytable where ... delete from dbo.Mytable where columnName = 0";

how can I get a list of statements, if the query is wrong?

Thanks!

Answer 1

I know this is an old question, but I came across it while Googling so I figured I'd answer it.

If your question is, how to get a list of statements if the SQL can't be parsed, the short answer is that you can't - the parser has no idea what the list of statements would be. You'd have to look at the errors and figure it out.

If your question is, what's wrong with the input code, it's that the select and delete statements are all on the same line. If you separate them with a semicolon or break them into two lines, it'll work and you can get your two statements.