Skip to content

Latest commit

 

History

History
194 lines (136 loc) · 8.86 KB

sql-database-connect-query-vscode.md

File metadata and controls

194 lines (136 loc) · 8.86 KB
title description metacanonical keywords services documentationcenter author manager editor ms.assetid ms.service ms.custom ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
VS Code: Connect and query data in Azure SQL Database | Microsoft Docs
Learn how to connect to SQL Database on Azure by using Visual Studio Code. Then, run Transact-SQL (T-SQL) statements to query and edit data.
connect to sql database
sql-database
CarlRabeler
jhubbard
676bd799-a571-4bb8-848b-fb1720007866
sql-database
quick start manage
data-management
na
na
hero-article
05/24/2017
carlrab

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.

Prerequisites

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.

    Setting       Suggested value Description 
    **Server name The fully qualified server name The name should be something like this: mynewserver20170313.database.windows.net.
    Database name mySampleDatabase The name of the database to which to connect.
    Authentication SQL Login SQL Authentication is the only authentication type that we have configured in this tutorial.
    User name The server admin account This is the account that you specified when you created the server.
    Password (SQL Login) The password for your server admin account This is the password that you specified when you created the server.
    Save Password? Yes or No Select Yes if you do not want to enter the password each time.
    Enter a name for this profile A profile name, such as mySampleDatabase A saved profile name speeds your connection on subsequent logins.

  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';
  1. 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