Database Projects in Visual Studio Solution

By: Jan Potgieter | Updated: 2022-12-13 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | > Application Development

Problem

Over the past 10 tutorials, we developed a database and a simple website using the database we developed. To better manage the database and application code it is useful to store the code, i.e., the scripts to create tables, the Login and User, the foreign keys for the relationships between the tables, and the stored procedures to do CRUD operations on the data in a Visual Studio project. In this article, we will look at how to add the database code to the same solution (as the HR website created in the prior tip) to keep the related projects together.

Solution

Visual Studio is an IDE developed by Microsoft used to create software. It includes all the tools needed to build software, such as writing code, editing, debugging, and testing functionality with the help of automated testing frameworks.

In this tutorial, we will add a SQL Server database project for the SQL objects to the same solution as the website code.

Step 1: Set Up the Database Environment from Scratch

To create the database, use this script to recreate the complete HR Database with all the tables, data, and stored procedures.

Step 2: Set Up the HR Solution from Scratch

In a previous tutorial, we created a simple website to view all the data, add new records, do updates, and delete data from the Companies and Employees tables. You can download and use the solution that was created in the previous tutorial and we will add the database objects to this VS solution.

Step 3: Add a SQL Server Database Project

This tutorial will add a SQL Server Project to keep all the SQL Server objects in the solution.

After completing the above steps, open the HR Solution with Visual Studio. Right-click the Solution and select Add, New Project.

Select the SQL Server Database Project template in the Add a new project window. If the template is not visible, search for SQL Server Database.

Select the SQL Server Database Project and click Next.

On the Configure your new project window, name the project (in this case: HRDatabase) and click Create.

Your new project will now appear in the Solution Explorer, as in the image below.

Now you must import the database into the new project. Right-click on the newly created project and select: Import > Database.

The Import Database window will appear. Click on the Select Connection button and complete the Connection Properties to connect to the SQL Server and the database. The HRDBLogin created in the setup script can be used as the User Name.

After you have set up the connection information and clicked on the Connect button, click on the Start button on the Import Database window.

When the process is finished, click Finish.

visual studio import database objects summary

Your database will now be imported to the Project you created, and the tables and stored procedures should be available in the dbo folder. See the image below. Under the Security folder you can see the HRDBLogin and HRDBUser created in the previous tutorial.

After working through the above tutorial, you now have a solution with two projects:

The Website application created in the previous tutorial, and
The Database project as created above.

This tip includes a zip file (HRSolution-Web-DB.zip) of the solution you can download (note: the file is 35MB).

Next Steps

In the next tip we will create a Reporting Services project. It will demonstrate how to create reports that show a list of companies and a list of Employees by Company name.

Check out the related articles from this series below:

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-12-13

Adding a Database Project to a Visual Studio Solution