How to get reference to SSDT Database Project memory model from a Visual Studio Extension?

Question

I'm trying to write a simple extension to the Visual Studio 2013, which should be able to list all tables in a SQL Server Database Project selected by an user of the extension. In order to do that, I need to access in-memory model of the DB project(s). But the only way I found is to load compiled .dacpac file produced by the DB project to a new TSqlModel instance and query it, but I would rather be able work with "live" in-memory representation. I've found this tutorial to SSDT API: http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx, but there is not an example how to connect to the in-memory model within the Visual Studio. I also found a tutorial solving this issue by collecting all files with .sql extension from the current solution and added their content into the TSqlModel instance, but this is not the way I prefer.

Is such thing possible at all? If so, how to do that?

Thank you, Michal

Answer 1

Database projects don't provide a Visual Studio service with access to the model right now. However you can simulate this by hooking into project system events and using the APIs to have your own model. Dave Ballantyne shows a static way to do this in this blog post. If you with to make this more dynamic you can hook up to project events such as project/file added, deleted, changed etc. and update your model based on these events. That is what happens internally in the database project. Note that you should also use the AddOrUpdateObjects API call instead of the one he uses, since this allows you to update a file when it changes:

    public void ProcessProjectModels()
    {
        var hostServiceProvider = (IServiceProvider)this.Host;
        var dte = (DTE)hostServiceProvider.GetService(typeof(DTE));

        using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { }))
        {
            foreach (Project project in dte.Solution)
            {
                IterateThroughProject(project.ProjectItems, model);
            }

            List<TSqlObject> allTables = GetAllTables(model);
            foreach (var table in allTables)
            {
                // Do processing 
            }
        }
    }

    public List<TSqlObject> GetAllTables(TSqlModel model)
    {
        List<TSqlObject> allTables = new List<TSqlObject>();

        var tables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table);
        if (tables != null)
        {
            allTables.AddRange(tables);
        }
        return allTables;
    }

    private void IterateThroughProject(ProjectItems PrjItems, TSqlModel model)
    {
        foreach (ProjectItem PrjItem in PrjItems)
        {
            if (PrjItem.ProjectItems != null)
            {
                IterateThroughProject(PrjItem.ProjectItems, model);
            }
            if (//PrjItem.Object.GetType().ToString() == "Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode" && 
                PrjItem.Name.EndsWith(".sql", StringComparison.OrdinalIgnoreCase))
            {
                // This is a sql file and will be processed
                // Note: There should be a separate API to read the live contents of this item, to avoid the need to save it
                if (!PrjItem.Saved)
                {
                    PrjItem.Save();
                }
                StreamReader Reader = new StreamReader(PrjItem.FileNames[0]);

                string Script = Reader.ReadToEnd();
                model.AddOrUpdateObjects(Script, PrjItem.Name, null);
            }
        }
    }

Note: The reason this feature doesn't have in-build support is that to do it right, the project should either provide a readonly model (no ability to add/update scripts) or ensure that add/update object calls actually affect the relevant script files in the project. It should also support event notifications if you with to do processing on change events (just as the project system supports). If this is something that would be useful to you (as I can imagine it would in this case), then opening a Connect bug or raising it on Visual Studio user voice is the way to get this on the product team radar.