Rusty Divine

Live, Love, Learn, Teach

How to create a Visual Studio 2013 Database Project in 10 Minutes!

Watch my Pluralsight Author Audition video to learn how to create a Visual Studio 2013 Database Project from an existing SQL Server database and populate it with data from that database.

After you’ve created your database project, you can publish it to your development environment for a consistent development database that reduces the risk of data integrity bugs sneaking in.

Improve Team Collaboration with Visual Studio 2013 Database Projects

 

Approximate transcript:

Have you ever been part of a team who has had problems coordinating changes to the database during development or while publishing your work to production? Wouldn't it be nice to have some reliable and consistent test data that you can use while developing and debugging a new feature?

Hi, I'm Rusty Divine and in this course on improving team collaboration with Visual Studio 2013 database projects I am going to show you how to create a consistent data set for each member of your team and overcome database conflicts by creating a database project that can be published on your local machine at the click of the button. After watching this course you will be ready to create a new Visual Studio Database Project with test data so that you can develop your new features in isolation of any model or data changes by other team members.

************

On many software development teams more than one developer makes changes to the database. A team might use a shared development database on a server and have worked out a system to handle breaking database changes and buggy data that creep in. Some teams can script each change they make to the database model and seed data, then update or rebuild the database to any version they need to by running each script in order.

************

An option that has worked well for teams that I have been part of is to use a database project to manage the model and test-data changes. Database projects work well when the development of the solution will take months to years to build and the team of three or more will each be helping make changes to the database. The database project is a good option because it:

  • Gives developers a local database sandbox
  • Provides consistent data that can be wiped and reloaded when data integrity bugs creep in
  • Makes merging into your version control system easier than having to merge scripts that are dependent on the order they are ran
  • Allows reuse on automatic builds to publish a consistent database that automated tests can be written against

***********

Some potential drawbacks to database projects are:

  • Setting it up the first time takes time
  • On a new project where you are working to define the database and it is changing rapidly, it can feel like this takes you out of the develop-test flow
  • When moving to QA and Production you need to do a model comparison to generate change scripts and create any data motion scripts you need to change data in each environment

Let's take a look at what it will take to get a database project added to your solution.

***********

We'll be using this Northwind database to represent a backup of your development database where you've taken the time to get just the data you need into it - the less data the better because it will take less time to publish that way.

I've created a database project using the default settings from rt-clicking on this database and selecting create new project. You can see it has brought in all of the tables, views, and other schema, but it did not script the data.

Here I've added a scripts folder and a post-deployment folder to organize the data load scripts. Now, there are several ways to get the data scripts generated, but the way I find works best for a set of data that isn't reaching into the hundreds of thousands of rows is to use SQL Management Studio's script generation; for larger data sets I would recommend SSMS tools (pop-up a link).

In management studio, I will right-click on the database and choose to generate scripts. I want to select just the tables and then choose to generate a separate file for each. In the advanced settings, I choose to not generate the use database commands and change the export from schema to data. Now I'll export it to the post-deployment folder we created in our project.

I've shown all the files here in the project and want to include our new scripts. Now I need to set the build action to none for these so that they aren't included in the publish because I want to control the order of them.

Next, I will add a post-deployment script that will execute each of these data loads in the correct order that does not conflict with their foreign key relationships.

The format for running these is to use this colon-r followed by the script location to run the script. The script uses command syntax, so I will turn on sql command mode.

Now, I'll paste in the rest of the tables. When I try to build, I see an error with the order details table. Looking at it, I see that I need to add some quotation marks around it, and now it builds fine.

Let's publish this database to see our data. Rt-click on the project and choose publish. In the advanced mode, I want to choose recreate the database every time. There are ways you can merge this data into an existing database, but I want to make sure I wipe out any data related bugs that may have crept in and start clean each time. I'll save the profile for later use, and then click on publish.

There was a problem publishing the database, so I'll click this link to view the results. Here, I can see the script it generated and I could copy/paste this into SQL management studio to troubleshoot, but looking at the messages, I see there was a problem with the self-referential key on the employee table. I know that's going to be a problem to load, so I'll just drop the key before I load the table, then re-add it after.

Now, let's publish from the profile we saved. You can see it published successfully, and if I go into management studio and refresh the databases, there is our new database and you can see it did bring over our data.

*************

Summary

In this course we covered the benefits of having a consistent development data set for your team so that they develop and test their work in isolation.

We covered how a database project can

  • Help your medium to large team coordinate model and test-data changes
  • Provide an isolated sandbox to develop new features
  • Wipe out any data-related bugs that creep in during development

There are a lot of extra features that you can explore with database projects, and I'd encourage you to watch these other Pluralsight courses to learn more.