MSSQL Extension For Visual Studio Code

Introduction

Visual Studio Code is a graphical code editor for Linux, macOS and Windows that supports various extensions. Personally, I think in current scenarios, Visual Studio Code is the best code editor tool. In Visual Studio Code, you can get an extension about AngularJS, NodeJS, TypeScript, C#, Python and also for other languages. In this article, I will explain how can we use Visual Studio Code for SQL Server. We will create a connection to SQL Server and afterwards, we run various queries in the code editor.

If you don’t have Visual Studio Code, download Visual Studio Code.

Now, start your Visual Studio Code and type “mssql”. You will find an extension for “mssql” and download it.

This extension installs the files required for Microsoft SQL Server, Azure SQL and SQL Data Warehouse development .

After successful installation, create a “data.sql” file in your editor and ensure that language mode for your file is “SQL”.

In the current state, we are in disconnected mode. If we write any query, we will not get any result. First, we will create a connection to SQL Server. Press “Ctrl+Shift+P” or F1 and type “sql”. You will get a dropdown list. From this list, click on “MS SQL: Connect” option and select “Create Connection Profile”.

When you enter, then it will ask you about your “Server Name”, “database” (it is optional), user name, password and name for your profile. After answering all the points given above, Visual Studio makes the connection for your SQL Server instance. If the connection is successfully established, then you will find your server name and database name in status bar. You will get Master database, if you don’t define the database name during the connection establishment, otherwise you will get your database name.

 

Create database

During the connection, we don’t define the database, so we create a database. Write SQL in your SQL file, which you will create, when you type SQL, then you will get a list of code snippets.

Click on “sqlCreateDatabase” option. When you enter on this, you will get a code snippet to create a database. Write “testDatabase” for this exercise.

To execute the query given above, create the database and now press “Ctrl+ Shift +E”. You will get a message of the connection establishment, if the database is created successfully.

Use database

After creating the database, we use this database, so press “F1”, type “SQL” and select “Use Database” option.

 Now, select the “testDatabase” from the list.

Create a table

Now, type “sql” in your file and select the “sqlCreateTable” code snippets from this list. Create an Employee table, as shown below.

After writing all the column names and constraints, when you press “Ctrl+Shift+E”, an Employee table will be created. If you again press the “Ctrl+Shift+E”, then you will get an error that the table name already exists. Now, our table is ready. Let’s insert some values into Employee table.

Insert Data

Now, write some insert queries for employee table and press Ctrl+Shift +E.

INSERT Into Employee
values(1, 'Pankaj', 22, 'Alwar');
INSERT Into Employee
values(2, 'Sandeep', 23, 'Jaipur');
INSERT Into Employee
values(3, 'Rahul', 24, 'Delhi');
INSERT Into Employee
values(4, 'Sanjeev', 22, 'Alwar');

Select data from tables

Now, write “Select * From Employee” query into your terminal and execute this query. When you execute the query, you will get the data into grid format.

One of the great features of Visual Studio code is that we can save our table result into JSON and XML format. For this, right click on your table and select.

Save data into JSON format

Right click on the table and select “Save as JSON” option. Using this option, you can save your table data into JSON file.

CSV data into CSV format

Right click on the table and select “Save as CSV” option. Using this option, you can save your table data into a CSV file.

Copy and Copy with Header

When you right click on grid result, you will find option to Save, and Save with header. Using this option, you can copy the data without or with the data. You can select a single value, single row, single column or combination of all these.

Conclusion

Visual Studio Code is a great IDE for SQL Server and Azure, if you need to perform some SQL queries and don’t want to open SQL Server Management Studio for some simple queries, then you should prefer Visual Studio Code, as it will provide a great intelliSense for SQL queries.