Azure SQL Database: Use Visual Studio Code to connect and query data

Visual Studio Code is a graphical code editor for Linux, macOS, and Windows that supports extensions, including the mssql extension for querying Microsoft SQL Server, Azure SQL Database, and SQL Data Warehouse. This quick start demonstrates how to use Visual Studio Code to connect to an Azure SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

This quick start uses as its starting point the resources created in one of these quick starts:

Before you start, make sure you have installed the newest version of Visual Studio Code and loaded the mssql extension. For installation guidance for the mssql extension, see Install VS Code and see mssql for Visual Studio Code.

Configure VS Code

Mac OS

For macOS, you need to install OpenSSL which is a prerequiste for DotNet Core that mssql extention uses. Open your terminal and enter the following commands to install brew and OpenSSL.

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install openssl
mkdir -p /usr/local/lib
ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/

Linux (Ubuntu)

No special configuration needed.

Windows

No special configuration needed.

Get connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.

    connection information

  4. If you have forgotten the login information for your Azure SQL Database server, navigate to the SQL Database server page to view the server admin name and, if necessary, reset the password.

Set language mode to SQL

Set the language mode is set to SQL in Visual Studio Code to enable mssql commands and T-SQL IntelliSense.

  1. Open a new Visual Studio Code window.

  2. Click Plain Text in the lower right-hand corner of the status bar.

  3. In the Select language mode drop-down menu that opens, type SQL, and then press ENTER to set the language mode to SQL.

    SQL language mode

Connect to your database in the SQL Database logical server

Use Visual Studio Code to establish a connection to your Azure SQL Database server.

Important

Before continuing, make sure that you have your server, database, and login information ready. Once you begin entering the connection profile information, if you change your focus from Visual Studio Code, you have to restart creating the connection profile.

  1. In VS Code, press CTRL+SHIFT+P (or F1) to open the Command Palette.

  2. Type sqlcon and press ENTER.

  3. Press ENTER to select Create Connection Profile. This creates a connection profile for your SQL Server instance.

  4. Follow the prompts to specify the connection properties for the new connection profile. After specifying each value, press ENTER to continue.

    The following table describes the Connection Profile properties.

    Setting Description
    Server name Enter your fully qualified server name, such as mynewserver20170313.database.windows.net
    Database name Enter your database name, such as mySampleDatabase
    Authentication Select SQL Login
    User name Enter your server admin account
    Password (SQL Login) Enter the password for your server admin account
    Save Password? Select Yes or No
    [Optional] Enter a name for this profile Enter a connection profile name, such as mySampleDatabase.
  5. Press the ESC key to close the info message that informs you that the profile is created and connected.

  6. Verify your connection in the status bar.

    Connection status

Query data

Use the following code to query for the top 20 products by category using the SELECT Transact-SQL statement.

  1. In the Editor window, enter the following query in the empty query window:

    SELECT pc.Name as CategoryName, p.name as ProductName
    FROM [SalesLT].[ProductCategory] pc
    JOIN [SalesLT].[Product] p
    ON pc.productcategoryid = p.productcategoryid;
    
  2. Press CTRL+SHIFT+E to retrieve data from the Product and ProductCategory tables.

    Query

Insert data

Use the following code to insert a new product into the SalesLT.Product table using the INSERT Transact-SQL statement.

  1. In the Editor window, delete the previous query and enter the following query:

    INSERT INTO [SalesLT].[Product]
            ( [Name]
            , [ProductNumber]
            , [Color]
            , [ProductCategoryID]
            , [StandardCost]
            , [ListPrice]
            , [SellStartDate]
            )
      VALUES
            ('myNewProduct'
            ,123456789
            ,'NewColor'
            ,1
            ,100
            ,100
            ,GETDATE() );
    
  2. Press CTRL+SHIFT+E to insert a new row in the Product table.

Update data

Use the following code to update the new product that you previously added using the UPDATE Transact-SQL statement.

  1. In the Editor window, delete the previous query and enter the following query:

    UPDATE [SalesLT].[Product]
    SET [ListPrice] = 125
    WHERE Name = 'myNewProduct';
    
  2. Press CTRL+SHIFT+E to update the specified row in the Product table.

Delete data

Use the following code to delete the new product that you previously added using the DELETE Transact-SQL statement.

  1. In the Editor window, delete the previous query and enter the following query:

    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. Press CTRL+SHIFT+E to delete the specified row in the Product table.

Next steps