MS Visual Studio provides a Database Project feature that allows us to build, develop, manage, and compare databases. It also gives us the flexibility to create a project from the existing database, a new database, table, view, functions, or stored procedure.
In this article, we can learn step-by-step on how to create a new SQL Server database and table in Visual Studio and publish it into the SQL Server database.
Create a new Database Project
The following steps show us how to create a new database project in Visual Studio:
In the Visual Studio menu, click File → New → Project (short-key: CTRL + Shift + N)
Figure 1. Visual Studio new project
From the Installed project templates, select SQL Server → SQL Server Database Project. Name the project as MyDB, then click the OK button.
Figure 2. Create SQL Server database project
Now, see the MyDB database was created in the Solution Explorer.
Figure 3. MyDB solution
Create a database table in Visual Studio
Create a table in the following steps below:
In the Solution Explorer, right-click the MyDB database project, then select Add → Table.
Figure 4. Add a new table
In the Installed menu, expand SQL Server then select Table and Views → Table. Name the table as MyTable1 then click the OK button.
Figure 5. Naming the MyTable1
Once the MyTable1 table was created, it will allow us to deploy the changes on it, such as adding columns. The following figure shows the design and the CREATE TABLE syntax of the MyTable1 table:
Figure 6. MyTable1 design
Now we can add more columns, whether directly through the design or using Transact-SQL. Let’s add 2 columns named first_name and last_name.
Figure 7. Add columns in MyTable1
When we add a column through the design, Visual Studio also adds that column in the CREATE TABLE command, which we can see on the T-SQL tab. The final SQL command is as follows:
CREATE TABLE [dbo].[MyTable1] ( [Id] INT NOT NULL PRIMARY KEY, [first_name] VARCHAR(50) NULL, [last_name] VARCHAR(50) NULL )
Save the table changes by using the CTRL + S short-key. The table will end up with an SQL file named as MyTable1.sql in the Solution Explorer like this.
Figure 8. MyTable1.sql was created
Publish into SQL Server database
The steps below demonstrate how we publish the database and table that we have created into SQL Server Management Studio.
- Open the Properties of the MyDB database in Solution Explorer.
Figure 9. MyDB database project properties
Click the Debug in the left side menu. Go to “Target Connection String” then click the Edit button.
Figure 10. MyDB debug
Click the Show Connection Properties and ensure the values are correct. Click the Test Connection button to check whether the connection is successful or not. If succeed, click the OK button.
Figure 11. Connect to MYSQLSERVER local Instance
The connection string configuration will look like this:
Figure 12. The connection string target was created
Do not forget to save these changes by pressing CTRL + S short-key.
Back to the Solution Explorer, right-click on the MyDB then select Publish.
Figure 13. Publish the MyDB database project
Click the Edit button on the Target database connection. Choose the connection we have created (local)\MYSQLSERVER then click the OK button.
Figure 14. Create a new database name as MyDB2
In this example, we will use a database named MyDB2 and click the Publish button.
Let’s access the MyTable1 table in the MyDB2 database from the SQL Server Management Studio. Expand the MyDB2 database to access the MyTable1 table that has successfully created as shown in the following figure.
Figure 15. The MyDB is accessed in SQL Server as MyDB2