The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, April 19, 2024 Login
Public

Remove SMO - Parse for GO 11/29/2022 7:25:14 AM

SMO = SQL Management Objects.

GO is not a TSQL command, but is often used in many scripts, the client must parse for go and submit in batches.

SSMS = SQL Server Management Studio.

SSMS Does this automatically.

Take the same script and execute it via submitting the command to the DBMS (java, c#, powershell etc) and it will fail.

Generally, the solution for this is to implement SMO in the code.

Long term this has always been an issue as SMO libraries are a pain too install, upgrade, patch.  As the years go by even upgrading projects in visual studio become difficult fighting the GAC and other .NET fun.

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Finally, I just removed SMO from my project(s) and life is soo much easier.

Below is the REGEX Function I use to parse a command into the different statements based on the GO operator.

using System.Collections;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Collections.Specialized;

    public static class SQLFunctions
    {

        public static ArrayList ParseForGo(String query)
        {
            // If 'GO' keyword is present, separate each subquery, so they can be run separately.
            // Use Regex class, as we need a case insensitive match.

            string separator = "GO";
            Regex r = new Regex(string.Format(@"^\s*{0}\s*$", separator), RegexOptions.IgnoreCase | RegexOptions.Multiline);
            MatchCollection mc = r.Matches(query);
            ArrayList queries = new ArrayList();
            int pos = 0;
            foreach (Match m in mc)
            {
                string sub = query.Substring(pos, m.Index - pos).Trim();
                if (sub.Length > 0) queries.Add(sub);
                pos = m.Index + m.Length + 1;
            }

            if (pos < query.Length)
            {
                string finalQuery = query.Substring(pos).Trim();
                if (finalQuery.Length > 0) queries.Add(finalQuery);
            }

            return queries;

        }
}
To call this:
 // If the user has selected text within the query window, just execute the
                // selected text.  Otherwise, execute the contents of the whole textbox.
                string allquerytext = txtQuery.SelectedText.Length == 0 ? txtQuery.Text : txtQuery.SelectedText;
                //                if (query.Trim() == "") return;

                // now parse for the go operatory
                ArrayList queries = new ArrayList();
                if (chkAdhocParseForGo.Checked)
                {
                    queries = parseForGo(allquerytext);
                }
                else
                {
                    queries.Add(allquerytext);
                }



Blog Home