0

This is a follow up question from here. I'm trying to use DACExtensions to retrieve the SelectStatement objects from a Procedure so that I can generate wrapper functions from my SSDT project using a T4 template. The problem is that the Nodes property of my Visitor object doesn't have any nodes in it. What am I missing?

Here's my Visitor:

public class SelectVisitor : TSqlFragmentVisitor
{
    public SelectVisitor() { this.Nodes = new List<SelectStatement>(); }
    public List<SelectStatement> Nodes { get; private set; }
    public override void Visit(SelectStatement node)
    {
        base.Visit(node);
        this.Nodes.Add(node);
    }
}

And here is how I'm trying to use it:

// Create the model
var procFiles = Directory.GetFiles(sqlPath, "*.sql", SearchOption.AllDirectories);
var model = new TSqlTypedModel(SqlServerVersion.Sql100, new TSqlModelOptions());
foreach(var procFile in procFiles)
{
    model.AddObjects(File.ReadAllText(procFile));
}

// Loop through the procs
var procs = model.GetObjects<TSqlProcedure>(DacQueryScopes.UserDefined);
foreach(var proc in procs){ 
    var selectVisitor = new SelectVisitor();
    var ast = proc.GetAst();
    ast.Accept(selectVisitor);
    foreach(var node in selectVisitor.Nodes){
        // Nodes has Count=0 :(
    }
}
Community
  • 1
  • 1
adam0101
  • 29,096
  • 21
  • 96
  • 174
  • Even though it says [here](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.model.tsqlmodel.isscriptbacked.aspx#P:Microsoft.SqlServer.Dac.Model.TSqlModel.IsScriptBacked) that empty models are script-backed by default, I thought I'd try creating a dacpac from the model with `loadAsScriptBackedModel` set to true as seen [here](https://github.com/GoEddie/DacpacExplorer/commit/be821f2505aab4e5a6c2510db0cb505323bfe534), but that also did not work. – adam0101 Apr 11 '15 at 03:56

1 Answers1

1

Using TSqlModelUtils.TryGetFragmentForAnalysis should ensure you get the original AST inside the model - hopefully that will have what you need. You may want to debug in and view your AST and what actually gets created - that's how we do things internally and often you may be surprised about what actually gets generated. Finally note that the DacpacExplorer tool might make this easier to visualize - it should now have support for showing the AST behind an object such as a procedure.

Kevin Cunnane
  • 8,020
  • 1
  • 32
  • 35
  • Ah, thank you so much. That is exactly what I needed to get those darn SelectStatement's. This is going to be a very cool framework when I'm done with it. Thanks again. – adam0101 Apr 13 '15 at 18:39
  • Thanks again Kevin. Here's my first release if you're curious: https://github.com/aeslinger0/sqlsharpener – adam0101 Apr 21 '15 at 02:16
  • Brilliant! I'll check this out for sure, great work. And thanks for using MIT license, that makes it easier for me to be allowed to view the code :-) – Kevin Cunnane Apr 21 '15 at 03:43
  • No problem! It's still a little rough, but it has only been a week. :) I have some more things I want to add yet - including your idea for EF code-first entities. – adam0101 Apr 21 '15 at 13:30