Navigating the Data Dude Object Model

  • [アーティクル]

In this blog I’ll introduce you to the basics of navigating the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition object model. I’ve wrapped it all into an add-in so you can step through the code and extend it as you wish. I hope you find it useful! The first part of this blog will describe the basics for navigating the model. After we’ve covered that ground I’ll introduce you to the add-in.

Basic Navigation

I’m assuming you are writing an add-in. If so then your add-in will be initialized with a DTE2 object in its OnConnection method. This provides you with all the open projects in your solution. To find the database projects loop over all projects finding those with a EnvDTE.Project.Kind of “C8D11400-126E-41CD-887F-60BD40844F9E”.

Now that you’ve identified the EnvDTE.Project instance you can gain access to the DataDude model through the properties collection of this project. Get the “DataSchemaModel” property and cast to a Microsoft.Data.Schema.SchemaModel.DataSchemaModel. Note that you’ll need to add references to the DataDude assemblies Microsoft.Data.Schema.dll and Microsoft.Data.Schema.Sql.dll.

    1: public static DataSchemaModel GetModel(EnvDTE.Project project)

    2: {

    3:     object obj = project.Properties.Item("DataSchemaModel");

    4:     return ((EnvDTE.Property)obj).Value as DataSchemaModel;

    5: }

Note that the DataSchemaModel is thread-safe but is not XmlSerializable. So if you’re using DTE from an external process the DataSchemaModel property will return null.

Let’s continue and query the model for all the tables. Use the member DataSchemaModel.GetElements querying for ISqlTable. Note that we are choosing to use ElementQueryFilter.Internal as the argument because we only want those tables which have been defined by the user – not those brought in through an external reference to a .dbschema file. Here’s an example of how to retrieve all the tables in the project:

    1: public static IList<ISqlTable> GetTables(DataSchemaModel model)

    2: {

    3:     return model.GetElements< ISqlTable >(ElementQueryFilter.Internal); 

    4: }

Great! Now we have all the tables. Note that the objects returned by this query support other interfaces. For instance, ISqlTable supports ISqlColumnSource which makes navigating the table columns much easier.

Even though you have access to the model changes to it are prohibited and will throw an exception. This is because the project system’s model is controled through scripts. It is only through these scripts that the model can be modified. Ok, so the next logical question is – “where is this table defined? What script? Where in that script?” Good question! Cast your object (i.e. the ISqlTable instance) to IScriptSourcedElement. If this interface returns an ElementSource then it does indeed live in a source file in the project system. The CacheIdentifier field will be the full path filename in the project system and and the length/offset the location, within that file, of this particular object. Note also, that IScriptSourcedElement gives you access to the ScriptDom allowing you to walk the ASTs if your needs include walking our parse tree. If you download the add-in source below and look at VsUtils.GetSelectedModelElements you’ll see where I use the ElementSource.

Add-In Example

Now I’ll introduce you to a General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition Add-in which will alter the script in your project to comment out selected schema-owned elements. I’ve included the source code but, in order to compile the install project you’ll need to install Votive 3.0 (I used 3.0.4707.0). This add-in uses the DataSchemaModel features I talked about in the previous section.

To install run CommentOutAddIn.msi (available in this zip). This install will create these files on your filesystem:

%VSINSTALLDIR%\Common7\Ide\PublicAssemblies\VSTSDBCommentAddIn.dll

C:\ProgramData\Microsoft\MSEnvShared\Addins\CommentOutElementAddIn.AddIn

After installing run Visual Studio 2008 and bring up the Add-in Manager by selecting Tools.Add-in Manager from the main toolbar. Verify that “Database Project Comment Out Add-in” is available and selected to load.

A new menu will appear in the Data menu called “Comment Out…” whenever at least one Database Project is open in the solution explorer.

Selecting this menu button will bring up the Comment Out dialog. Select the Database Project to search and click the “Find Schema Objects” button. This will search the project for all Tables, Views and Procedures and populate the tree control you see in the image below.

At this point select the schema objects you’d like to comment out and select “Command Out Objects”. At this point the dialog will close and all the selected elements will be commented out from their respective files.

For each file you should see a message similar to this in the output window:

C:\TEMP\DATABASE3\DATABASE3\SCHEMA OBJECTS\SCHEMAS\SALES\TABLES\SPECIALOFFERPRODUCT.TABLE.SQL is being modified so selected schema objects may be commented out.

Here’s the result of the commenting out a table:

    1: /*CREATE TABLE [Sales].[SpecialOfferProduct] (

    2:     [SpecialOfferID] INT              NOT NULL,

    3:     [ProductID]      INT              NOT NULL,

    4:     [rowguid]        UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,

    5:     [ModifiedDate]   DATETIME         NOT NULL

    6: );*/

Conclusion

The Comment Out Add-in is capable of commenting out Tables, Views and Procs in your script. But more than that, I hope the source code provides some help to for those wishing to write add-ins to tailor to their specific needs. Enjoy!

- Patrick Sirr

“DataDude” Senior Programmer

Comments

  • Anonymous
    December 27, 2008
    Is it possible to get IModelElement reference from the selected file from SolutionExplorer ProjectItem projItem = Dte.SelectedItems.Item(1).ProjectItem; OAFileItem fileItem = projItem as OAFileItem; IList<IModelElement> elements = fileItem.GetElements(); fileItem.GetElements() is not exist in current model. Instead of loading Tables, SPs, Views add-in works on selected file from Solution Explorer

  • Anonymous
    December 28, 2008
    There could be many model elements in a particular file.  To retrieve them you'll need to query the model for the particular element type you're interested (IModelElement if you don't know) and then cast them to IScriptSourcedElement.  That object has an ElementSource field which identifies the file where the object lives (as well as the offset/size in that file).  Download the Zip associated with this blog and look at the routine VsUtils.GetSelectedModelElements. Good luck!