Schema Compare DTE Commands

[アーティクル]
11/22/2008

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 and Visual Studio 2010 Beta 2. 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 C# console application source code for Visual Studio Team System 2008 GDR is available here.

The C# console application source code for Visual Studio 2010 Beta 2 is available here.

If you have the Visual Studio Team System 2008 GDR or Visual Studio 2010 Beta 2 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:

Visual Studio Team System 2008 GDR :

Visual Studio 2010 Beta 2 :

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

/ProviderType ConnectionBased \| ProjectBased \| FileBased	This identifies the type of the source or target provider in the schema compare.
/ConnectionString connection	For ConnectionBased providers, this is the connection string
/DatabaseName	For ConnectionBased providers, this is the name of the database
/ConnectionName name	This is the title used in the schema compare editor for this source or target
/ProjectName proj	For ProjectBased providers, this is the name of the project. This project must be open in the Solution Explorer.
/FileName fileName	For FileBased providers, this is the full path to the .dbschema file
/DataSourceGuid dataSource NOTE: This option has been deprecated in Visual Studio 2010 Beta 2	For ConnectionBased providers, this is the data source guid. For SQL Server providers this guid is “067EA0D9-BA62-43f7-9106-34930C60C528”
/DspFamily NOTE: This option is only available in Visual Studio 2010 Beta 2	Specifies the family of database schema providers which should be used to model the database. For SQL Server use “sql”.

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:

Visual Studio Team System 2008 GDR :

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

Visual Studio 2010 Beta 2 :

Data.NewSchemaComparison /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2008;Integrated Security=True;Pooling=False" /ConnectionName Larry /DatabaseName Larry /DspFamily sql /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2008;Integrated Security=True;Pooling=False" /ConnectionName Moe /DatabaseName Moe /DspFamily sql

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.

Visual Studio Team System 2008 GDR & Visual Studio 2010 Beta 2 :

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.

Visual Studio Team System 2008 GDR & Visual Studio 2010 Beta 2 :

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 the command below.

Visual Studio Team System 2008 GDR :

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

Visual Studio 2010 Beta 2 :

Data.NewSchemaComparison /ProviderType ConnectionBased /ConnectionString "Data Source=.\sql2005;Integrated Security=True;Pooling=False" /ConnectionName Larry /DatabaseName Larry /DspFamily sql /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 (the following example is for Visual Studio 2008)

    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.

- Patrick Sirr

Comments

Anonymous
November 22, 2008
Patrick Sirr , one of the key developers in our team started his own blog. If you want to learn about
Anonymous
November 25, 2008
Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition
Anonymous
November 25, 2008
The VS Database edition team has just released the golden version of the Visual Studio Team System 2008
Anonymous
January 13, 2009
I've uploaded a new .zip file with some fixes. Thanks Tracy!!
Anonymous
January 13, 2009
Patrick - Thank you very much for thus. This will be most helpful in our automated development / testing environment. It is working as expected now. Tracy
Anonymous
February 10, 2009
I've updated the zip file to include a block of code which verifies that any projects you may be comparing have their database schema models fully resolved. This code is unnecessary when comparing two databases as the reverse engineering process will resolve the models as they built. // Just in case you're comparing two projects you'll need to build the solution // to make sure the project's object models are fully built. dteInstance.ExecuteCommand("Build.BuildSolution", commandArg); bool done = false; while (!done && vs.HasExited == false) { if (dteInstance.Solution.SolutionBuild.BuildState == EnvDTE.vsBuildState.vsBuildStateDone) { done = true; } Console.WriteLine("Waiting for solution build to finish..."); Thread.Sleep(5000); }

次の方法で共有

Schema Compare DTE Commands

Comments

その他のリソース