Geek asks and answers

SQL Server ScriptDom Parsing

The development team I'm working with is using SQL Data Projects for the great work that we have to do against an existing database. We went through several weeks and there were several mistakes, but the experience was generally good.

However, when we connect to production, the dba team refuses to accept DACPAC as a deployment method. Instead, they want to see a traditional script for each DML or DDL statement.

Current thinking is to create a script difference between the finished SQL project and the production environment, and then parse this into separate scripts. I don’t know well.

To parse the difference script two options are possible:

  • Parse the script based on the batch separator command, GO. Pretty simple solutions, but promise.
  • Or use Microsoft.SqlServer.TransactSql.ScriptDom. It looks more promising, but it seems much more complicated.

I'm currently testing ScriptDom, but it's hard for me to figure this out. My current, but not only problems, are as follows.

I am trying to parse the following SQL using ScriptDOM in C #:

CREATE TABLE dbo.MyTable
(
    MyColumn VARCHAR(255)
)

But I don’t see how to access the VARCHAR size, in this case 255.

The code I use is as follows:

TSqlFragment sqlFragment = parser.Parse(textReader, out errors);

SQLVisitor myVisitor = new SQLVisitor();
sqlFragment.Accept(myVisitor);

public override void ExplicitVisit(CreateTableStatement node)
{
    // node.SchemaObjectName.Identifiers to access the table name
    // node.Definition.ColumnDefinitions to access the column attributes
}

From each column definition, I expected to find a length property or the like. However, I also have a suspicion that you might use the visitor template I come across to re-analyze each column definition. Any ideas?

+1
source share
3 answers

Great that you are using ssdt!

, , dacpacs, , deloyment script sqlpackage.exe.

...

  • t-sql
  • ssdt
  • ci
  • sqlpackage.exe/action: script, dacpac QA, PROD .. script.

DBA script ( ) - script.

:

CI, sqlpackage.exe script :)

, !

+3

, . , TSQL, SSDT, SQLDOM . :

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

namespace ScriptDomDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            TSql120Parser parser = new TSql120Parser(false);
            IList<ParseError> errors;
            using (StringReader sr = new StringReader(@"create table t1 (c1 int primary key)
GO
create table t2 (c1 int primary key)"))
            {
                TSqlFragment fragment = parser.Parse(sr, out errors);
                IEnumerable<string> batches = GetBatches(fragment);
                foreach (var batch in batches)
                {
                    Console.WriteLine(batch);
                }
            }
        }

        private static IEnumerable<string> GetBatches(TSqlFragment fragment)
        {
            Sql120ScriptGenerator sg = new Sql120ScriptGenerator();
            TSqlScript script = fragment as TSqlScript;
            if (script != null)
            {
                foreach (var batch in script.Batches)
                {
                    yield return ScriptFragment(sg, batch);
                }
            }
            else
            {
                // TSqlFragment is a TSqlBatch or a TSqlStatement
                yield return ScriptFragment(sg, fragment);
            }
        }

        private static string ScriptFragment(SqlScriptGenerator sg, TSqlFragment fragment)
        {
            string resultString;
            sg.GenerateScript(fragment, out resultString);
            return resultString;
        }
    }
}

, , Visual Studio , node . , TSQL .

+3
#reference Microsoft.SqlServer.BatchParser
#reference Microsoft.SqlServer.BatchParserClient

using System;
using System.Collections.Specialized;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;

namespace ScriptParser
{
   class Program
   {
      static void Main(string[] args)
      {
         ExecuteBatch batcher = new ExecuteBatch();
         string text = File.ReadAllText("ASqlFile.sql");
         StringCollection statements = batcher.GetStatements(text);
         foreach (string statement in statements)
         {
            Console.WriteLine(statement);
         }
      }
   }
}
0
source

More articles:


All Articles