Database Project Extensibility – Merge Table and View Scripts

Article
01/05/2011

In this blog I’ve created a project feature extension to combine Table and View child scripts into the script that defines the parent object. For example, constraints or extended properties for a table. Some developers like to have all the T-SQL scripts defining a particular object in one .sql file. Typically you’d run this after using Import Schema to populate your database project. The package is built using a vsix file as the deployment mechanism and can be downloaded here. The source for this project is located here.

Overview of the Package

Adding the button to Schema View

The package contributes a ‘Merge scripts into parent schema object’ menu button onto the schema view context menu for Tables and Views. When pressed the selected Tables and Views will gather their hierarchical statements into the parent script. If the child script is emptied of content then it is excluded from the project . If your project is under version control you may wish to review the output window and delete these files from your SCC provider.

Overview of the Code
One of our requirements is to add a button to the Schema View context menu. In order to do that we’ll need to create a Visual Studio package. To create Visual Studio packages you’ll need to install the SDK located here. Once installed, you’ll have the option to create a new “Visual Studio Integration Package” project based on the vsix format. See the file ‘MergeScripts.vsct’ for details on how I added this button on the schema view context menu.

Implementing the database project feature extension

I also need to hook into the Visual Studio 2010 database project extensibility. To create extensions to a Visual Studio 2010 database project you must

Create a class which implements one of the extension point interfaces. All these interfaces ultimately derive from Microsoft.Data.Schema.Extensibility.IExtension.
Publish your extension via a file named <MyFile>.Extensions.xml file which can be placed in the %ProgramFiles%\Microsoft Visual Studio 10.0\VSTSDB, %ProgramFiles%\Microsoft Visual Studio 10.0\Common7\IDE or the %LocalAppData%\Microsoft\VisualStudio\10.0<hive>\Extensions directory.
Your extensions file must correspond to the following schema:

 <?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns="urn:Microsoft.Data.Schema.Extensions" elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="urn:Microsoft.Data.Schema.Extensions">
  <xsd:element name="extensions">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="extension" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:attribute name="assembly" type="xsd:string" use="optional"/>
            <xsd:attribute name="type" type="xsd:string" use="required"/>
            <xsd:attribute name="enabled" type="xsd:boolean" use="required"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="assembly" type="xsd:string" use="required"/>
      <xsd:attribute name="version" type="xsd:unsignedByte" use="required"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The database extension created for this project is called MergeScriptsFeature and is published in the MergeScripts.Extensions.xml:

 <?xml version="1.0" encoding="utf-8"?>
<extensions assembly="" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">
  <extension type="MergeScripts.MergeScriptsFeature" assembly="MergeScripts, Version=1.0.0.0, Culture=neutral, PublicKeyToken=0cae59df434c3018"  enabled="true" />
</extensions>

MergeScriptsFeature has the following declaration:

[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]

class MergeScriptsFeature : IDatabaseProjectFeature, IOleCommandTarget

The DatabaseSchemaProviderCompatibility attribute identifies the database schema provider affinity. In this case “SqlDatabaseSchemaProvider” is used indicating that this feature will work with all SQL database projects.

When a database project is opened it queries for all extensions compatible with the current database schema provider which also implement IDatabaseProjectFeature. Instances of each of those features are instantiated and initialized with the IDatabaseProjectNode for that project. With this node, features can listen to events – in my case I listen to ProjectClosed and FileIconRequest.

The MergeScriptsFeature will need to chain into the IOleCommandTarget implemented by the Database Schema View. To do this the feature retrieves the ‘SchemaModelViewerService’ from the agnostic database package – called ‘DataPackage’. Once that service raises the DatabaseSchemaViewInitialized event (or if the Schema View is already displayed) the MergeScriptsFeature IOleCommandTarget is chained through the method IDatabaseSchemaView.RegisterForLimitedCommandRouting.

When the merge button is pressed MergeScriptsFeature.DoMerge() is called. This routine creates a CommandFactory and proceeds to buffer up the source code control and file operations necessary to do the actual merge. Once all these commands are buffered they are ordered and executed in CommandFactory.Execute().

Installing the Package

Installing a vsix package can be done on a per-user basis or for all users on the machine. To install as a specific user just double-click on the .vsix file. This will unzip the contents and place them into “%LocalAppData%\Microsoft\VisualStudio\10.0Exp\Extensions\Microsoft Corporation\MergeScripts\1.0”. The next time Visual Studio is launched the contents of this package (like menu definitions) will be merged into the Visual Studio shell. To verify the vsix extension is installed bring up the Extension Manager available under the main menu “Tools.Extension Manager”. You should see the MergeScripts package.

To install the vsix package for all users (called a Trusted Extension) you need to have administrator rights on the machine. You can then install using the “VSIXInstaller /admin” command. This will install the extension into %VSInstallDir%\Common7\IDE\Extensions.

Conclusion

I know an extension like this (or an option during Import Script) has been requested multiple times on our forums. Until we get around to adding the option into Visual Studio I hope this extension will service your needs. If you have any questions please visit our forums here or contact me via this blog. Thanks!

-- Patrick

Comments

Anonymous
May 16, 2011
its really very nice & useful , www.train4job.com

Share via