For this blog I’ve developed an add-in for General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition projects (better known as “Data Dude”) which will generate Create, Read, Update and Delete (CRUD) stored procedures for a particular table in your model. I wanted to provide the ability to affect the content of those procedures without recompiling my add-in so I’m using Visual Studio’s built in, template based, code generation tool - known as T4. It’s not well known but extremely powerful. There’s a good MSDN article here. I’ll go through the key pieces of the code, but there’s nothing like running it for yourself, so I’ve provided the entire source in this zip.
Overview of the Add-In
The zip reference above contains an MSI called InstallCRUDGenerator.msi. Once you install, make sure the add-in is loaded by selecting the “Tools.Add-in Manager” menu button:
Once loaded any scripts containing tables will display a “CRUDGenerator” button in your Solution Explorer context menu:
If you select this the CRUD Generation Dialog will appear:
This dialog allows you to select your source table script and then view the generated CRUD stored procedures. If you’re happy with the generated procedures select “Save Procedures to Project” and 4 new stored procedures will be created in your project.
Now for the details! The text box in the middle of the CRUD Generation Dialog contains your T4 template. At the start of this blog I gave you a good T4 link so I won’t go into details on the scripting language itself, but let’s adjust the Create procedure seen in the image above. First let’s add my company name to the comments for this stored procedure. The comment is generated on lines 10 through 13 of the T4 Template:
1: /*
2: CREATE procedure for <#= tableFullName #>
3: Generated <#= DateTime.Now.ToString() #>
4: */
Let’s change this to
1: /*
2: Copyright (c) Microsoft Corporation. All rights reserved.
3: CREATE procedure for <#= tableFullName #>
4: Generated <#= DateTime.Now.ToString() #>
5: */
.. and hit the “Generate” button:
Notice now that our copyright appears in the generated stored procedure T-SQL and we did this without modifying our add-in. Very cool!
Feel free to experiment and modify the T4 templates I provide in this add-in. There are 5 templates installed into “%ProgramFiles%\Microsoft Visual Studio 9.0 CRUD Template AddIn”:
· CrudCreate.tt
o Responsible for generated the ‘Create’ stored procedure
· CrudDelete.tt
o Responsible for generated the ‘Delete’ stored procedure
· CrudRead.tt
o Responsible for generated the ‘Read’ stored procedure
· CrudUpdate.tt
o Responsible for generated the ‘Update’ stored procedure
· CrudToolbox.tt
o Useful utilities for walking the Data Dude model. This is where the columns are analyzed to determine how they are used in the templates above.
CrudToolbox.tt contains the most code. It’s here where I walk the Data Dude object model to determine the column and column types for the selected table. If you wish to change this file remember that it too can be altered without having to restart Visual Studio. Just open it in a text editor or another Visual Studio instance, edit and then hit the ‘Generate’ button as we did before.
Overview of the Add-In Code
In this section I’ll provide you with an overview of the CRUD Generator Add-in code. For anyone that’s ever generated a Visual Studio Add-in project you’ll know that Connect.cs is where the add-in initializes. It’s there where I add the ‘CRUD Generator’ button to the Solution Explorer context menu and handle the button click. My handling is a simple pass-off to a class I call the CRUDEngine:
1: public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled)
2: {
3: handled = false;
4: if(executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
5: {
6: if(commandName == "CRUDGenerator.Connect.CRUDGenerator")
7: {
8: CRUDEngine engine = new CRUDEngine(ProjectFinder.GetFirstDatabaseProject(_applicationObject));
9: engine.Run();
10: handled = true;
11: return;
12: }
13: }
14: }
This engine creates an instance of my WinForms dialog and provides that dialog with a class I call the CRUDGenerator:
1: public void Run()
2: {
3: CRUDDialog dialog = new CRUDDialog();
4: dialog.Generator = new CRUDGenerator(_project);
5: dialog.ShowDialog();
6: }
I won’t go over my CRUDDialog since most of it is simple WinForms programming. The CRUDGenerator is where we’ll focus next. This class is responsible for executing the T4 scripting engine and returning the results. For more information on executing T4 programmatically see this msdn page.
The most important thing to note when walking the Data Dude object model is that it is not Serializable (although it is thread-safe). This means you cannot access the object model from another process (i.e. through out of process DTE) or from another AppDomain. Fortunately during the process of executing the T4 template engine you have the opportunity to provide a class which must derive from ITextTemplatingEngineHost. My instance of that host is called VSDBCustomHost and it it I provide the current AppDomain
1: public AppDomain ProvideTemplatingAppDomain(string content)
2: {
3: return AppDomain.CurrentDomain;
4: }
The core of this class is it’s RunTemplate method. In that method I create an instance of a VSDBCustomHost and process the template:
1: public string RunTemplate(ISqlTable selectedTable,
2: string templateFullPath,
3: out CompilerErrorCollection errors)
4: {
5: string tableScript = GetTableText(selectedTable);
6: errors = null;
7: if (string.IsNullOrEmpty(templateFullPath))
8: return string.Empty;
9:
10: VSDBCustomHost host = new VSDBCustomHost();
11: Microsoft.VisualStudio.TextTemplating.Engine engine = new Microsoft.VisualStudio.TextTemplating.Engine();
12: host.TemplateFile = templateFullPath;
13: host.IncludePath = Path.Combine(VsUtils.GetVSTSDBDirectory(), @"Extensions\SqlServer\");
14: host.VsUtils = _vsUtils;
15: AppDomain domain = host.ProvideTemplatingAppDomain(string.Empty);
16: string output = string.Empty;
17: try
18: {
19: // Set the table to be generated into the app domain
20: domain.SetData("TT_TABLE", selectedTable);
21: //Read the text template.
22: string input = File.ReadAllText(templateFullPath);
23: //Transform the text template.
24: output = engine.ProcessTemplate(input, host);
25: errors = host.Errors;
26: }
27: finally
28: {
29: domain.SetData("TT_TABLE", null);
30: }
31: return output;
32: }
At the beginning of this blog is the link to the zip file containing all the source for this add-in. From here I’ll leave you to paruse the source.
Conclusion
Hopefully you’ve now got an appreciation of the power of T4 templates! Remember that if you want to use these templates with Data Dude projects you’ll have to provide your own templating engine host since the Data Dude model cannot cross process or AppDomain boundaries since it is not serializable. Feel free to ping me if you’ve got questions! Have a good 2009!
Patrick Sirr
Data Dude Programmer
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
In this blog I’ll introduce you to the Import Scripts add-in to the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition. With this add-in Visual Studio can import multiple scripts to populate a target database project. 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).
To install run InstallImportScripts.msi (available in this zip). This install will create these files on your filesystem:
%VSINSTALLDIR%\Common7\Ide\PublicAssemblies\ImportScriptsRecursively.dll
C:\ProgramData\Microsoft\MSEnvShared\Addins\ImportScriptsRecursively.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 Script Import” is available and selected to load.
A new menu will appear in the Data menu called “Import Scripts…” whenever at least one Database Project is open in the solution explorer.
Selecting this menu button will bring up the Scripts Selector dialog. Use this dialog to
· Select the project which will receive the imported database schema objects (the target project)
· Select the directory to search for .sql files
· Select or deselect individual files or folders from the import process. Note that, by default any scripts named Script.PostDeployment.sql, Script.PreDeployment.sql or IgnoredOnImport.sql will not be selected.
· Select the default encoding for the .sql files. If you run the addin and no database schema objects are imported verify the encoding of your files match the encoding you’ve selected in the Scripts Selector dialog.
· Choose among the various import script options available on the Import Script Wizard.
· If you choose to concatenate your scripts into one large file memory pressure will increase but performance will be vastly increased.
Once you select ‘Run Import’ the Output Window will be activated and messages will appear providing you with feedback on the import script process. Each file will produce output similar to the one below. The output will include 1) a progress indicator (in the case below 4/700) and 2) the DTE command which can be executed into the Command Window to re-import that particular script (in the case below Project.ImportScript /FileName "C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql" /Encoding UTF8).
| Executing 4/700 : Project.ImportScript /FileName "C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql" /Encoding UTF8 Started importing file: C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql File name C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql (size: 270) Parsing SQL script Total number of batches in script: 2 Total number of statements in script: 2 Finished importing file: C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql |
Conclusion
The Import Scripts Add-in allows you to import multiple scripts at once. This should be a useful addin when upgrading from CTPs (which do not support upgrade) or importing from a script archive into a database project for the first time.
- Patrick Sirr
- DataDude Programmer
In this blog I’ll lead you through the various parameters for the “Data.NewDataComparison” command available in the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition. This command launches the Visual Studio Data Compare editor.
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.NewDataComparison”. The New Data Comparison dialog will appear. This is the expected result when executing “Data.NewDataComparison” without parameters. To avoid this dialog and immediately produce a data comparison result the following parameters are available:
Data.NewDataComparison /SrcServerName srcServer /SrcDatabaseName srcDatabaseName /SrcDisplayName displayName [/SrcUserName username] [/SrcPassword password] /TargetServerName targetServer /TargetDatabaseName targetDatabaseName /TargetDisplayName displayName [/TargetUserName username] [/TargetPassword password]
The command will have the general form is “Data.NewDataComparison <source options> <target options>”
| /SrcServerName /TargetServerName | This identifies the type of the source or target provider in the schema compare. |
| /SrcDatabaseName /TargetDatabaseName | For ConnectionBased providers, this is the connection string |
| /SrcDisplayName /TargetDisplayName | For ConnectionBased providers, this is the name of the database |
| /SrcUserName /TargetUserName | This is the title used in the schema compare editor for this source or target |
| /SrcPassword /TargetPassword | For ProjectBased providers, this is the name of the project. This project must be open in the Solution Explorer. |
I have two named SQL Server 2008 instances on my box – SQL2008_1 and SQL2008_2. The first instance has a database named ‘Drake’ and the other ‘Josh’. A Data Compare session can be launched by typing the following command into the Command Window
Data.NewDataComparison /SrcServerName .\SQL2008_1 /SrcDatabaseName Drake /SrcDisplayName Drake /TargetServerName .\SQL2008_2 /TargetDatabaseName Josh /TargetDisplayName Josh
If differences are available, I can now use the command “Data.DataCompareExportToFile [filename]” to create a data update script. When I execute “Data.DataCompareExportToFile C:\JoshUpgrade.sql" in the Command Window a data upgrade script will be created.
Conclusion
At this point I’ve illustrated how to use the Import Script, Schema Compare and Data Compare DTE commands. I hope you’re starting to see the flexibility this provides for authoring your own addins, packages or applications tailored to your specific requirements.
- Patrick Sirr
“DataDude” Programmer
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>”
| /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 | For ConnectionBased providers, this is the data source guid. For SQL Server providers this guid is “067EA0D9-BA62-43f7-9106-34930C60C528” |
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
We have had a lot of requests from folks that would rather not go through the Import Script Wizard when they have multiple T-SQL source scripts. Fortunately Visual Studio provides the ability to execute parameterized commands by using the Design Time Extensibility (DTE) framework. For a typical list of such commands visit this MSDN Library entry. In this blog I’ll lead you through a useful command provided by General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition which allows you to bypass the Import Script Wizard and import your scripts directly into your project system.
Step 1: Create a script
Create a script as c:\myScript.txt with the following contents:
| CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) ) GO EXEC sp_addextendedproperty @name='TableWithComputedColumn', @value ='column_3', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Table1', @level2type = NULL, @level2name = NULL GO CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1] GO CREATE USER [User1] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo; GO GRANT UPDATE ON [dbo].[Table1] TO [User1] |
Create another script “c:\myScript2.txt” with the following contents:
| CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0 |
Step 2: Create a Project
Launch Visual Studio and create a SQL Server 2008 project:
Step 3: Select the new project in the Solution Explorer
When you execute DTE commands, they execute in the context of the project that is selected in the Solution Explorer. You should back up your project or, better yet, check in your project to version control before you run this DTE command.
Step 4: Display the Command Window
By using the Command Window, you can run DTE commands and provide parameters to those commands that allow them. To display the Command Window, open the View menu, point to Other Windows, and click Command Window.
Step 5: Import the Script
The import script DTE command is “Project.ImportScript”. Now you are at the point where you can run the command and have some fun.
First try the command without any parameters (“Project.ImportScript”):
You’ll notice that the Import Script wizard appears. This is the command that is executed when you right-click on the project node and click “Import Script”. Not so interesting. Dismiss this dialog box. Next you will parameterize the command.
The parameters for “Project.ImportScript” are as follows.
Project.ImportScript /FileName [filename] [/encoding Unicode|UTF32|UTF8|UTF7] [/Overwrite] [/IgnoreExtendedProperties] [/IgnorePermissions] [/AddImportedPermissionsToModel]
| /FileName [filename] | This is the file that you want import. Place quotes around the file name if it has embedded spaces. |
| /encoding Unicode|UTF32|UTF8|UTF7 | The encoding of the file |
| [/Overwrite] | If duplicate objects are discovered should they be overwritten? |
| [/IgnoreExtendedProperties] | Suppress importing extended properties |
| [/IgnorePermissions] | Suppress importing permissions |
| [/AddImportedPermissionsToModel] | If provided, any imported permissions will not be added to the model. This means your permissions XML file will have a Build Action of “Not In Build” |
Now import your myScript.sql file. You can ignore extended properties and permissions. Run the script below (“Project.ImportScript /FileName “C:\myScript.sql” /IgnoreExtendedProperties /IgnorePermissions”):
Notice that your project now has the following files:
MyProject\Schema Objects\Schemas\dbo\Tables\Table1.table.sql
| CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) ) |
MyProject \Schema Objects\Schemas\dbo\Views\View1.view.sql
| CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1] |
If you now change the command to “Project.ImportScript /FileName "C:\myScript.sql" /Overwrite /IgnorePermissions” the table script will be altered to:
| CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) ) GO EXEC sp_addextendedproperty @name='TableWithComputedColumn', @value ='column_3', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Table1', @level2type = NULL, @level2name = NULL |
To import your second script, change the command in the Command Window to ““Project.ImportScript /FileName "C:\myScript2.sql”. After you run the command, the project system contains the following file:
MyProject \Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql
| CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0 |
Obviously you can now repeat this process for all the SQL files that make up your schema.
Conclusion
Note that any DTE command is available through DTE automation and is accessible through EnvDTE80.DTE2.ExecuteCommand or EnvDTE80.DTE2.Commands.Raise. You can use the DTE commands to create Add-Ins, external processes, or packages to automate your specific system. GDR for Visual Studio Team System 2008 Database Edition GDR provides other parameterized commands. I will blog about these soon.
- Patrick Sirr
“DataDude” Programmer
