Adding a Database Project to a Visual Studio Solution
By: Jan Potgieter | Updated: 2022-12-13 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | More > Application Development
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.
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.
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).
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:
- Create a Microsoft SQL Server Database
- Create a Table in Microsoft SQL Server
- Create SQL Server Tables Using Foreign Keys for Referential Integrity
- How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application
- Create, Read, Update and Delete Data in SQL Server Tables
- Indexing in SQL with Clustered and Non-Clustered Indexes
- Create SQL Server Stored Procedures, Views and Functions
- Setup Security for a SQL Server Database via SSMS and T-SQL
- Develop a Simple Web Application in ASP.NET with SQL Server
About the author
View all my tips
Article Last Updated: 2022-12-13