Patrick Sirr's Blog

Schema Compare DTE Commands

In this blog I’ll lead you through the various parameters for the “Data.NewSchemaComparison” command available in the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition. This command launches the Visual Studio Schema Compare editor and can, optionally begin a comparison between database projects, .dbschema files or SQL Server databases.

The source code is available here.

If you have the Visual Studio Team System 2008 GDR installed, open it and navigate to the Command Window. To display the Command Window, open the View menu, point to Other Windows, and click Command Window. At the prompt type “Data.NewSchemaComparison”. The New Schema Comparison dialog will appear. This is the expected result when executing “Data.NewSchemaComparison” without parameters. To avoid this dialog and immediately produce a schema comparison result the following parameters are available:

Data.NewSchemaComparison [/ProviderType ConnectionBased | ProjectBased | FileBased] [/ConnectionString connection] | [/DatabaseName databaseName] | [/ConnectionName name] | [ProjectName proj] | [/FileName fileName] [/DataSourceGuid dataSource][ProviderType ConnectionBased | ProjectBased | FileBased] [/ConnectionString connection] | [/DatabaseName databaseName] | [/ConnectionName name] | [ProjectName proj] | [/FileName fileName] [/DataSourceGuid dataSource]

The command parameters may look daunting, but the general form is “Data.NewSchemaComparision /ProviderType <source options> /ProviderType <target options>”

Example 1: Connection-Based Providers

I have a local SQL Server 2008 named instance called “SQL2008”. On that server I have two databases – “Larry” and “Moe”. The following command will launch a schema compare session between these two databases when entered into the Command Window:

Data.NewSchemaComparison /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2008;Integrated Security=True;Pooling=False" /ConnectionName Larry /DatabaseName Larry /DataSourceGuid 067EA0D9-BA62-43f7-9106-34930C60C528 /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2008;Integrated Security=True;Pooling=False" /ConnectionName Moe /DatabaseName Moe /DataSourceGuid 067EA0D9-BA62-43f7-9106-34930C60C528

Example 2: Project-Based Providers

I have a solution with two database projects. The solution is currently open in Visual Studio and both projects are loaded. I can schema compare the projects by typing the following command into the Command Window.

Data.NewSchemaComparison /ProviderType ProjectBased /ProjectName Abbott /ProviderType ProjectBased /ProjectName Costello

Example 3: File-Based Providers

I have two .dbschema files located at “C:\Laurel.dbschema” and “C:\Hardy.dbschema”. I can schema compare the files by typing the following command into the Command Window.

Data.NewSchemaComparison /ProviderType FileBased /FileName c:\Laurel.dbschema /ProviderType FileBased /FileName c:\Hardy.dbschema

Example 4: Mixed Providers

At this point I have done schema comparisons between connections, projects and files. Any mix of source or target providers is also allowed. For example, I can compare my project Abbott.dbproj with my database Larry located in my local SQL Server 2008 instance through this command:

Data.NewSchemaComparison /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2005;Integrated Security=True;Pooling=False" /ConnectionName Larry /DatabaseName Larry /DataSourceGuid {067EA0D9-BA62-43f7-9106-34930C60C528} /ProviderType ProjectBased /ProjectName Abbott

Example 5: Driving Schema Compare through Automation

I’ve used the Command Window to launch the schema compare editor. In this example I will control Visual Studio from another process, launch the schema compare editor, and save the change script to an editor. When Visual Studio launches it places a DTE object on the Running Object Table (ROT). Other applications can use the ROT entry to control Visual Studio using the DTE object model. Each DTE entry on the ROT has a unique name beginning with “!VisualStudio” and ending with the process id. During the development of this application I used “irotview.exe” to view the various Visual Studio instance names. Below is a screenshot of irotview.exe (I have outlined the Visual Studio instances).

I first launch Visual Studio and wait for it to start responding

   1: // Launch Visual Studio 

   2: Process vs = new Process(); 

   3: vs.StartInfo.FileName =   

   4:  Environment.ExpandEnvironmentVariables(@"%VS90COMNTOOLS%..\IDE\devenv.exe"); 

   5: vs.Start(); 

   6: while (!vs.Responding) 

   7: { 

   8:    System.Threading.Thread.Sleep(1000); 

   9: } 

The next step is to get the EnvDTE._DTE instance from the ROT. Accessing the ROT is documented elsewhere so I won’t spend time with it here. I’ve authored a class that, given a Process object, will return either null or an EnvDTE._DTE instance. Note that there is some delay between when Visual Studio is responding and when it places the _DTE instance on the ROT. This is why I’ve placed the ROT query in a while loop.

   1: EnvDTE._DTE dteInstance = null;

   2:  

   3: while (vs.HasExited == false &&

   4:        dteInstance == null)

   5: {

   6:    dteInstance = ROTHelper.GetVisualStudioInstance(vs);

   7: }

Once the EnvDTE._DTE instance is available I use the ExecuteCommand method to launch a Schema Compare editor.

   1: // Create a schema compare session which compares 

   2: // two databases

   3: string commandArg = string.Format(CultureInfo.InvariantCulture,

   4:             "/ProviderType ConnectionBased "+

   5:             "/ConnectionString {0} " +

   6:             "/ConnectionName {1} "+

   7:             "/DatabaseName {1}" +

   8:             "/DataSourceGuid {2} " +

   9:             "/ProviderType ConnectionBased " +

  10:             "/ConnectionString {3} " +

  11:             "/ConnectionName {4} " +

  12:             "/DatabaseName {4} " +

  13:             "/DataSourceGuid {5}",

  14:             "\"Data Source=.\\sql2008;Integrated Security=True;Pooling=False\"",

  15:             "Larry",

  16:             guidSqlServerDataSource.ToString(),

  17:             "\"Data Source=.\\sql2008;Integrated Security=True;Pooling=False\"",

  18:             "Larry",

  19:             guidSqlServerDataSource.ToString());

  20:  

  21: // Execute the command

  22: dteInstance.ExecuteCommand("Data.NewSchemaComparison", commandArg);

  23:  

The Schema Compare editor has no event telling me when it has finished comparing. In order to export the change script I use ExecuteCommand and catch COMExceptions until it succeeds or the Visual Studio process exits. The COMExceptions are expected if the command I am executing is currently disabled in the Visual Studio IDE.

   1: bool done = false;

   2: string outputFile = @"C:\Export.sql";

   3:  

   4: while (!done &&

   5:        vs.HasExited == false)

   6: {

   7:    try

   8:    {

   9:       Console.WriteLine("Ouputting script to " + outputFile);

  10:       dteInstance.ExecuteCommand("Data.SchemaCompareExportToFile", 

  11:           outputFile); 

  12:       done = true;

  13:    }

  14:    catch (COMException)

  15:    {

  16:       Console.WriteLine("Waiting for message to become available");

  17:       Thread.Sleep(1000);

  18:    }

  19: }

Finally, I close Visual Studio

   1: if (vs.HasExited == false)

   2: {

   3:    // Close VS

   4:    dteInstance.Application.Quit();

   5: }

Conclusion:

I’ve exercised the Schema Compare editor using files, projects and databases and had some fun controlling Visual Studio through the ROT! In future blogs I’ll introduce more of the DTE commands available to you in the GDR for Visual Studio Team System 2008 Database Edition.

- Patrick Sirr

“DataDude” Programmer