The "Entity Designer Database Generation Power Pack" is a downloadable addition to Visual Studio 2010 Beta 2 which includes some new database generation technologies. The original Database Generation feature in the Entity Designer in VS 2010 is extensible via Windows Workflows and T4 Templates. This Power Pack builds on these extensibility mechanisms and introduces the following:
In addition, there are a few other things:
In this walkthrough, I will demonstrate how to install the Database Generation Power Pack 1.0.1, some of the key features in the new user interface, and how to achieve data and schema migration.
The only pre-requisite is Visual Studio 2010 Beta 2. Note that this extension will not work with Express editions. The Database Generation Power Pack is an MSI package that consists of a VSIX extension, a set of XAML flies, and T4 templates.
You can download the package from here: http://visualstudiogallery.msdn.microsoft.com/en-us/df3541c3-d833-4b65-b942-989e7ec74c87
Click on the “Download” link which will download the MSI. Launch the MSI, note the pre-release license and finish installing the setup package.
1. Open BlogModel.edmx in your project. Click on an area of the designer surface, look at the “Database Generation Workflow” property in the Properties window and note that we’ve added six new workflows. We can select a new one from here but for now, keep the default workflow: “TablePerTypeStrategy.xaml (VS)”
2. Right-click on the designer surface and select “Generate Database from Model…”. You will be presented with the new user interface:
3. You can think of this user interface as a more advanced version of the property window selector. On the left pane is a list of all workflows available which we aggregate from three locations:
4. All of the workflows in the left pane are installed in the VS directory. We discourage you from editing these workflows since we may update them in the future. Therefore, the only operation available to you for these default workflows is cloning. The two buttons on the right of the toolbar allow you to clone the workflow to your user directory and to your project, respectively:
5. Click on the “Generate Migration T-SQL and Deploy” workflow in the list and click “Select Workflow for Database Generation”. Notice the green tick mark in the left pane now indicates that the “Generate Migration T-SQL and Deploy” workflow is selected. If your properties window is open, also notice that “Generate Migration T-SQL and Deploy.xaml” is selected in the “Database Generation Workflow” property.
6. Hit the “Next >” button at the bottom, and now you’re presented with the data connection page that you’re familiar with in basic Model First.
7. Select a ‘New Connection’, choose ‘.\sqlexpress’ as your Server name, and type in ‘BlogDb’ as the database name. Hit OK. VS will prompt you to create this database – select Yes. If you run into an error like “Permission denied in master”, make sure you have CREATE DATABASE permission (see the link above in ‘Setup’). Click ‘Next’ out of the data connection page.
8. You will see the Summary Page, but the “DDL” textbox has been replaced by a new progress bar which will track the progress of the overall workflow. If everything proceeds successfully, the progress bar should finish.
9. Hit Finish out of the Summary page, and the required store model (SSDL) and mappings (MSL) are created. Note that the T-SQL was never shown in the database generation process and it never opened in the editor afterwards. If you would like to tweak this workflow so that it follows the usual database generation process (not directly deploy, but open the migration script in the T-SQL editor) then using the new ‘Workflow Manager’, you can clone the workflow into your user directory, rename it, set the ‘Script Generation’ option to ‘Migration T-SQL’, set the ‘Deployment’ option to ‘None’, and select it for database generation.
10. Now open the Server Explorer (View -> Server Explorer) and notice that we’ve directly deployed to the database (if it’s already open, then right-click on the ‘Tables’ folder and select ‘Refresh’)
11. Now let’s insert some data – open up the [blogmodel.sql] script in Visual Studio and choose Data -> Transact SQL Editor -> Execute SQL. Connect to ‘.\sqlexpress’, and execute the script. You should see ‘Query executed successfully.’ Under the ‘Messages’ pane in the T-SQL editor.
12. Go back to the model – now let’s say we want to add comments to the posts in our blog and add a description to each post in our blog.
13. Right-click on the designer surface again, Generate Database from Model. Click ‘Next’ past the Workflow Manager. Since the connection string is now present in app.config, you will not see the data connection dialog, and the workflow should immediately directly deploy the new T-SQL. Select ‘Finish’ out of the Summary and Settings page. Go back to Server Explorer, right-click on the ‘Tables’ folder and select ‘Refresh’. Notice that we’ve added the ‘Comments’ table.
14. Right-click the Posts table and select “Show Table Data”:
We have preserved the existing data and added a new, nullable column for the description. If you do not see the ‘Comments’ table and the new ‘Description’ column, check to make sure that the ‘Description’ property in your ‘Post’ entity is Nullable. A script that is generated with a non-nullable Description property will look to see if there is any data in the ‘Post’ and halt the script to prevent data loss.
In this walkthrough we:
You can expect more functionality in future version such as the ability to handle “renames” (although this Power Pack provides migration, it cannot identify a rename of an EntityType, for example – this will translate into a drop/create), a new Workflow Manager, the ability to manage T4 templates, better database project support, and more.
Finally, we’re presenting these options and new UI affordances as a way of gauging what is most important to you for the next version of our tools so we value your feedback greatly!
Thanks,
Adi Unnithan Software Design Engineer