Post
Ask Question

Database Project Tutorial Using Visual Studio 2012

Database Projects

You can use database projects to create new databases and to update existing databases and data-tier applications using Visual Studio just like any web or Windows desktop application.


Why a Database Project

We have our Windows desktop or web application's source code all under version control so that we can keep track of all the changes going on. However the database scripts, table changes, procedures and function updates and so on are ignored. Keeping the database in sync and managing all the changes being done is a challenging task. Also if we have multiple servers then the task of managing the database becomes even tougher.

A database project enables you to apply version control and project management techniques to your database development efforts in much the same way you apply those techniques to managed or native code. The development team manages changes to databases by putting it under version control using Team Foundation Server. Members of your team can then check out files to make, build, and test changes in an isolated development environment before sharing them with the team. To help ensure code quality, your team can finish and test all the changes for a specific release of the database in a staging environment before you deploy the changes into production.

Database Project

Content List
  1. Getting Prepared
  2. Creating Database Project
  3. Creating various database objects, like tables, functions, procedures, triggers and so on
  4. Pre-deployment and post-deployment script
  5. Creating a Publish Profile and publishing the Database Project
  6. Importing an existing database to a new Database Project

1. Getting Prepared

So what do we need before creating a Database Project? First the SQL Server Database, obviously, and secondly Visual Studio.

However to create a Database Project you also need to check if your Visual Studio has SQL Server Data Tools installed.

If you have this installed then you can proceed otherwise you must install it first.

Open Visual Studio > Help > About Visual Studio. You can see SQL Server Data Tools in the list.

Visual Studio

If Yes go ahead else you can download and install the SQL Server Data Tools.

2. Creating Database Project

Create a new project by clicking "File" -> "New" -> "Project..." then seelct "SQL Server" > "SQL Server Database Project".

If you don't find this item then it means SQL Server Data Tools is not installed. Refer to the preceding section for details.

Creating Database Project

3. Creating various Database Objects

Before creating a database object, get the project organised by creating various folders for multiple objects. Like create a "Tables" folder for keeping tables, "Functions" folder for functions, "View" for views, "Procedures" for Stored Procedures and so on.

You can also create a folder for multiple modules like "Product", "Sales" and under this create "Tables", "Procedures" and "Functions" folders.

Procedures

Create a sample Table "Product" and "Order" by right-clicking on the tables folder then selecting Add > Table.

You get the Table Designer and Code view. Enter add fields from Designer or type in Code view.

The following is the "Product" table.

Product

The following is the "Orders" Table

Orders

Create a sample function by right-clicking on the Functions folder then selecting Add > Scalar-Valued Function.

Create a sample Function

Create a sample Stored Procedure by right-clicking on the Procedures folder then selecting Add > Stored Procedure.

Create a sample Stored Procedure

Create a sample Stored Procedure by right-clicking on the Triggers folder then selecting Add > New Item > Tables and Views > Trigger.

Tables and Views

4. Pre-deployment and Post-deployment Script

Pre-deployment and Post-deployment scripts execute Transact-SQL statements before and after the main deployment script.

Like a pre-deployment script can copy data from a table that is being changed into a temporary table before re-formatting and applying the data to the changed table in a post-deployment script.

I included a Post-Deployment script to add few records to the "Product" table.

Post Deployment Script

Deployment

5. Creating a Publish Profile and Publishing the Database Project

    5.1 Code Analysis

    Click on SQL Menu > Static Code Analysis > Run to check for any errors or warning and fix it by clicking on the list shown.

    This will keep the code clean from errors.

    5.2 Build Project

    Once you are done with all the creation and changes in Database Project, right-click the Project folder and Build.

    Build Project

    Once the build is successful we are ready to create a publish profile.

    Before creating it, check if the Database Project SQL Server Target Platform version matches your SQL Server Version.

    5.3 Setting Right Database Target

    Right-click the Project folder then select Properties and correctly set the SQL Server Version on the Target.

    Also Advanced Database Settings can be done by clicking on Database Settings below.

    Database Settings

    5.4 Create a Publish Profile

    Publish the Profile .xml file that contains a collection of all the property key-value pairs necessary to deploy a database model (a .dacpac) to a target database.

    Right-click the Project folder then select Publish then do the following:

    1. Enter the Database Name. With this name the database will be created.
    2. Click Edit to open the Connection Properties window.
    3. Select your Server.
    4. Save the Profile.
    5. Click Publish to publish you Database Project.

    There is also an Advanced button. You can click on the Advanced button and do make more settings, like "Always Re-Create the Database", "Back-up before Deployment" and so on.

    Advance button

    Once the Publish Profile is created you can modify by opening and editing it manually.
    1. <?xml version="1.0" encoding="utf-8"?>    
    2. <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">    
    3.   <PropertyGroup>    
    4.     <IncludeCompositeObjects>True</IncludeCompositeObjects>    
    5.     <TargetDatabaseName>SalesDemo</TargetDatabaseName>    
    6.     <DeployScriptFileName>SalesDemo.sql</DeployScriptFileName>    
    7.     <ProfileVersionNumber>1</ProfileVersionNumber>    
    8.     <TargetConnectionString>Data Source=WIN-1J0QGUUL45M\SQLEXPRESS;Integrated Security=True;Pooling=False</TargetConnectionString>    
    9.   </PropertyGroup>    
    10. </Project>  
    5.5 Checking for Publishing Errors and Status

    Open Tools > Other Windows > Data Tool Operations to view the results.

    If you get errors when Publishing click on View Results to fix them.

    Publishing Error

    Publishing Error

    Publishing Success

    Publishing Success

    5.7 Check the resulting Physical Database from SQL Server Management Studio

    Verify that all the database objects are properly created.

    Check the resulting Physical Database

    If the Database Project is kept under Source Control then when database changes happen in the future, we can simply open the Database Project, update the required database object .sql and then Build and Publish. The physical database will be updated accordingly.

    This way all the changes will be under control and history maintained and the database will be in sync.

6. Importing an Existing Database to a new Database Project

If the scenario is that we already have a database with all the tables, procedures, views, functions and so on and we want to bring that into the Database Project and keep it under source control then we obviously don't want to create all the matching database objects manually in our project. That would be a tedious job.

This is want we can do.

    6.1 Create a .dacpac file

    Open SQL Server Management Studio then right-click on the database you to import then select Tasks >Extract Data-tier Application.

    Then follow the simple procedure and a .dacpac file is created. Remember the location where the file is created.

    A DACPAC file contains all of the information necessary to build the db objects specified in the SQL DB project.

    SQL DB

    6.2. Import the .dacpac file into the Database Project

    Open Visual Studio and create a new Database Project.

    Right-click on the project then click on Import > Data-tier Application (*.dacpac).

    The "Import Data-tier Application File" dialog will open.

    Select the .dacpac file you created previously and click Start.

    All your database objects will be imported into your Database Project.

    mported to your Database

     
     




Similar Articles