Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

SQL Server Database Project in Visual Studio – Querychat

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)

Visual Studio new project

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.

Create SQL Server database project

Figure 2. Create SQL Server database project

Now, see the MyDB database was created in the Solution Explorer.

MyDB solution

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.

Add a new 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.

Naming the MyTable1

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:

MyTable1 design

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.

Add columns in MyTable1

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.

MyTable1.sql was created

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.

  1. Open the Properties of the MyDB database in Solution Explorer.

MyDB database project properties

Figure 9. MyDB database project properties

Click the Debug in the left side menu. Go to “Target Connection String” then click the Edit button.

MyDB debug

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.

Connect to MYSQLSERVER local Instance

Figure 11. Connect to MYSQLSERVER local Instance

The connection string configuration will look like this:

The connection string target was created

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.

Publish the MyDB database project

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.

Create a new database name as MyDB2

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.

 The MyDB is accessed in SQL Server as MyDB2

Figure 15. The MyDB is accessed in SQL Server as MyDB2

 

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc