How do I accurately handle a batch separator for SQL from C#

For Data Explorer I would like to add support for a Batch separator.

So for example if users type in:

 
select 'GO' go select 1 as go 
Go 
select 100

I would like to return the three result sets.

Its clear that I need some sort of parser here, my hope is that this is a solved problem and I can just plug it in. (writing a full T-SQL parser is not something I would like to do)

What component / demo code could achieve splitting this ba开发者_如何学JAVAtch into its 3 parts?

---

It's not often that I say this, but this is a case where I'd definitely advocate bending the user input to conform to computer rules rather than trying to solve the problem of having a computer understand massively varied user input

Impose a simple rule of: The word "go" must appear on its own line in order to be interpreted as a command to proceed

If your users can't adhere to a rule like that, should they really be engaged in the far more complex task of writing SQL queries?

---

I was looking for solution of the same problem, but didn't found any suitable (use of SMO was not acceptable in my case). So, I had to write own parser. Here it is:

static IEnumerable<string> ParseSqlBatch(Stream s)
{
    if (s == null)
        throw new ArgumentNullException();

    StringBuilder sbSqlStatement = new StringBuilder();
    Stack<string> state = new Stack<string>();
    StreamReader sr = new StreamReader(s);

    //initially search for "GO" or open tag of strings ('), comments (--, /*) or identifiers ([)
    string pattern = @"(?>(?<=^\s*)go(?=\s*(--.*)?$)|''(?!')|(?<!')'|(?<!\[)\[|--(?=.*)?|/\*)";
    //if open tag found search for close tag, then continue search
    string patternCloseString = @"(?>''|'(?!'))";
    string patternCloseIdentifier = @"(?>\]\]|\](?!\]))";
    string patternComments = @"(?>\*/|/\*)";

    Regex rx = new Regex(pattern, RegexOptions.IgnoreCase);

    while (!sr.EndOfStream)
    {
        string line = sr.ReadLine();

        int ix = 0;
        bool bBreak = false;
        while (ix < line.Length && !bBreak)
        {
            Match m = rx.Match(line, ix);

            if (!m.Success)
            {
                sbSqlStatement.Append(line.Substring(ix));
                break;
            }

            int ix2 = m.Index;
            string word = m.Value;

            sbSqlStatement.Append(line.Substring(ix, ix2 - ix));

            if (state.Count == 0)
            {
                if (string.Compare(word, "GO", true) == 0)
                {
                    if (sbSqlStatement.Length > 0)
                    {
                        yield return sbSqlStatement.ToString();
                        sbSqlStatement = new StringBuilder();
                        break;
                    }
                }
                else
                {
                    switch (word)
                    {
                        case "'":
                            rx = new Regex(patternCloseString);
                            break;
                        case "[":
                            rx = new Regex(patternCloseIdentifier);
                            break;
                        case "/*":
                            rx = new Regex(patternComments);
                            break;
                        case "--":
                            sbSqlStatement.Append(line.Substring(ix2));
                            bBreak = true;
                            continue;
                    }

                    if (word != "''")
                        state.Push(word);
                }
            }
            else
            {
                string st = state.Peek();

                switch (st)
                {
                    case "'":
                        if (st == word)
                            state.Pop();
                        break;
                    case "[":
                        if (word == "]")
                            state.Pop();
                        break;
                    case "/*":
                        if (word == "*/")
                            state.Pop();
                        else if (word == "/*")
                            state.Push(word);
                        break;
                }

                if (state.Count == 0)
                    rx = new Regex(pattern, RegexOptions.IgnoreCase);
            }

            ix = ix2 + word.Length;
            sbSqlStatement.Append(word);
        }

        sbSqlStatement.AppendLine();
    }

    if (sbSqlStatement.Length > 0)
        yield return sbSqlStatement.ToString();
}

It correctly handles "GO" within strings, identifiers and comments. Perhaps not ideal one, but tested successfully over hundreds of various .sql scripts.

And then, for example:

using (FileStream fs = new FileStream("SampleBatch.sql", FileMode.Open, FileAccess.Read))
{
    foreach (string statement in ParseSqlBatch(fs))
    {
        //execute statement here, or do something with it
    }

    fs.Close();
}

I hope it will help someone.

---

I'm not aware of an existing solution to this (though I agree that there probably is one out there). I just want to point out that you probably don't need to write a full T-SQL parser: all you really need to find is the word "go" outside of quotes. That is, look for <word boundary>GO<word boundary> and keep track of opening and closing quotes along the way. If you find a match and it's not after an opening quote (before its matching closing one) then it's the batch separator. It should be fairly easy to do this without writing anything that you'd call a proper "parser".

---

In your case above, can't you just split on newlines, test each line if it begins with the word "go", then split the script on that?

After re-reading this a couple of times, this is a really ugly problem. Looking at the first line in your script, there are actually no command delimiters (semicolons or newlines). I don't think you have much of a choice but to actually parse the whole thing.

But, somewhere along the line this has to get parsed anyway, right? Perhaps there's something you can do inside or using the existing parser for this. Depending on how much access you have to it, you could:

• Change the code for the existing parser to understand the "go" command to execute and return what it has, then run again.

• Take a copy of the existing parsing code, adapt it to understand the "go" command, strip out the interpreter part, then just use it to split blocks and feed to the real parser?

---

You can change the current database for an open SqlConnection very easily:

 connection.ChangeDatabase("YourDB");

An example:

private static void ConctDatabase(string connectionString)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        MessageBox.Show("Database: {0}", conn.Database);
        conn.ChangeDatabase("Northwind");
        MessageBox.Show("Database: {0}", conn.Database);
    }
}