Adding a Database Project to a Visual Studio Solution

By:   |   Updated: 2022-12-13   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | More > 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.

visual studio 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.

visual studio add new project

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

visual studio configure new project

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

visual studio solution explorer

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

visual studio import database objects

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.

visual studio import database objects

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

visual studio import database objects

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.

visual studio solution explorer

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:

  1. Create a Microsoft SQL Server Database
  2. Create a Table in Microsoft SQL Server
  3. Create SQL Server Tables Using Foreign Keys for Referential Integrity
  4. How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application
  5. Create, Read, Update and Delete Data in SQL Server Tables
  6. Indexing in SQL with Clustered and Non-Clustered Indexes
  7. Create SQL Server Stored Procedures, Views and Functions
  8. Setup Security for a SQL Server Database via SSMS and T-SQL
  9. Develop a Simple Web Application in ASP.NET with SQL Server


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jan Potgieter 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

Comments For This Article