How to avoid deleting charts from the database when publishing in Visual Studio 2015

Question

I have a SQL Server 2014 database project in VS 2015. I also created a couple of schematic diagrams in SSMS (since VS does not have the diagram diagrams that I know of). This is an early stage of the project life cycle and a lot of refactoring. SSMS schemes are good at updating themselves in the face of change - which is really nice. I would like to save diagrams in (re) publish in dev environment.

The problem is to keep up with refactoring, I would need to delete things in the target database that are no longer in the project (or just recreate the target db in the dev environment) ... but it will be so that my diagrams are deleted. I did not understand a clean way to avoid this, and this is a kick in the lower leg to rebuild the diagrams. Has anyone come up with a reasonably clean way of gradual publishing when deleting a project, but keeping the diagrams?

Answer 1

Configure the charts on a global temp table or table in another database before you recreate the dev database. You can do this by doing something like this:

IF (OBJECT_ID(N'tempdb..##TempTable') IS NOT NULL) DROP TABLE ##TempTable ; SELECT * INTO ##TempTable FROM sysdiagrams 

Unfortunately, you cannot successfully run this from a pre-deployment script if you delete and recreate the dev database. A pre-deployed script runs after the drop. Therefore, I would look at creating a powershell script that publishes dacpac, and before the dacpac deployment step executes the above statement, complete the diagram step.

After deploying dacpac, run something like this to recreate the diagrams:

 INSERT INTO dbo.sysdiagrams (name, principal_id, version, definition) SELECT name, principal_id, version, definition FROM ##TempTable AS tt 

Answer 2

I don't have much experience developing Visual Studio, so this is more of an idea than a real answer.

If you created a materialized (indexed) view of dbo.sysdiagrams and included it in your VS project, would this prevent dbo.sysdiagrams from being deleted during deployment?

 create view test with schemabinding as select name, principal_id,diagram_id,version, definition from dbo.sysdiagrams CREATE UNIQUE CLUSTERED INDEX CIX_test_diag ON dbo.test(name, principal_id,diagram_id,version)