SQL Server Data Tools to Visual Studio 2013 - Database Reverse Engineering

  • Article

Introduction

Database Cycle Management is Hard.

Microsoft created SQL Server Data Tools for Visual Studio 2013, to  make development easier.

  • Single Tool to support developer’s needs.
  • Can build, debug, test, maintain, and refactor databases.
  • Developers can use familiar Visual Studio tools for database development.
    • Code navigation, IntelliSense, C# language, platform-specific validation,
      • debugging, and declarative editing in the Transact-SQL editor.
  • Works connected or disconnected (Tools work on top of design time)
  • Have Schema Model differencing capabilities (Compare and Update Model)
  • Schema and app under TFS control
  • Publish to all supported SQL platforms

STEP1 - Create Project

Select SQL Server Database Project Template and call it DemoSSDT

A empty project will be created.

                          https://code.msdn.microsoft.com/site/view/file/129761/1/1.png

STEP2 - Import Database

After the project creation we need to import from our SQL Server database.

Select the project and with the right mouse button select the option Import. The import could be made from three different options. On this demo we will choose Database, since we have an existent SQL Server Database.

                          https://code.msdn.microsoft.com/site/view/file/129762/1/2.png

A new screen will appear to select existent database. On this demo we use the AdventureWorks2012.

Select the option Start

                          https://code.msdn.microsoft.com/site/view/file/129763/1/3.png 

The import will start.

                          https://code.msdn.microsoft.com/site/view/file/129764/1/4.png 

After the import finished, as we can see, all the schemas from our SQL Server database were imported to our Visual Studio project.

                          https://code.msdn.microsoft.com/site/view/file/129765/1/5.png

STEP3 - Create Table

Create new table on DBO Schema call Currency.

Select Add New Item on project solution.

This option will open a new screen with several items divided from categories.

Select the category Tables and Views and choose the option Table. Call it Currency.

                          https://code.msdn.microsoft.com/site/view/file/129766/1/6.png 

Create new table on DBO Schema an name it Currency.

The table was created with Id field pre generated.

                           https://code.msdn.microsoft.com/site/view/file/129767/1/7.png 

Create two new fields (Code and Description).

For that on the table designer add two new fields, like in the SQL Server. As you see, the script is automatically created, when we change the designer.

STEP4 - Refactoring

One of the new features, it's the possibility to use refactoring.

Select one field on the script tab and with the right mouse button select the option refactor like on the image below.  One of the options is the Refactor option. This option like on the other Visual Studio Project, allow the user to rename in all solution (Functions, Store Procedures…) the field of our table.

                              https://code.msdn.microsoft.com/site/view/file/129768/1/8.png 

STEP5 - Publish

After all our changes on the project, we could in an easy way Publish to SQL Server database the changes made on our project.

Select the project and select the option Publish

                              https://code.msdn.microsoft.com/site/view/file/129769/1/9.png 

Select the target database. On our case will be the AdventureWorks2012 existent on SQL Server database.

Choose the Publish button.

                               https://code.msdn.microsoft.com/site/view/file/129770/1/10.png 

On the output window, we have access to result of our publish. One of the option give access to the script generated. If we click on it, we will see the script generated like we see on the image below.

                               https://code.msdn.microsoft.com/site/view/file/129773/1/11.png 

On the left tab, as we can see the Currency table was published to our database, with the three fields created.

                               https://code.msdn.microsoft.com/site/view/file/129774/1/12.png 

References

Microsoft SQL Server Data tools
VS and SSDT versions
Team Blog and Forums