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!

DomF · Accepted Answer · 2018-03-28 19:30:28Z

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.

answered Mar 28, 2018 at 19:30

DomF

211 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related