Page 13 - MSDN Magazine, June 2017

Figure 3 Results of a Schema Query
manage it on the Azure portal and connect to it from any type of app. So, imagine your app is PHP and you’re using the PHP extension for VS Code and you’re talking to a SQL Server database on Azure.
I took advantage of the free Azure account and credits that come with a Visual Studio subscription to create an Azure SQL database based on the AdventureWorks sample. The documentation to do this is at bit.ly/2o2IDTy.
Back in VS Code, you can install the mssql extension by click- ing on the extension icon in the VS Code Activity Bar, filtering on mssql and then clicking its Install button. You’ll be prompted to reload VS Code when it’s ready, but keep in mind that it will delay installing part of the extension until you use one of its commands. Note that for macOS, you’ll need to install OpenSSL. Check the mssql documentation link (provided earlier) for details.
In addition to the query execution engine that’s installed, mssql places a slew of commands in the VS Code command palette. It makes sense to start by connecting to your database, although other functions will prompt you to connect if you haven’t already.
Open the palette with F1 (or Ctrl or Command+Shift+P if you have one of those funny keyboards without function keys) and type MS SQL to filter on all of the mssql commands. If you don’t have any other extensions that provide commands with the SQL keyword, just SQL will do the trick. I love that you can even get to help files with the Get- ting Started Guide command. You can also store different connections and then connect easily with the Manage Connection Profiles feature.
will not only give you help writing TSQL, it also knows how to execute queries and perform other tasks defined by the extension.
With a SQL file open in the editor, selecting MS SQL: Connect from the Command Palette will display a list of existing connection profiles you’ve already created and let you create a new one. Select Create Connection Profile and you’ll then be prompted for each key element of the connec- tion string. For example, the first prompt asks for the Server name, as shown in Figure 1. My SQL Azure database is on a server named thedatafarmsqlerver.database.win- dows.net, so that’s what I’ll enter.
Next, you’ll be prompted to enter the database name, your login and password, then an optional profile name. By the way, Manage Connection Profiles can also lead you to this point as it has a Create menu option.
Once you’ve filled out the connection information and are successfully connected, the profile will get saved in the VS Code settings file. With version 0.3, if the connection fails, the profile won’t get stored; but that experience is on deck to change. You can look at the stored profile by going to Preferences and Settings from the VS Code menu or via the Ctrl or Command+, (comma) key- stroke combo. Here’s an example of a connection profile:
"mssql.connections": [ {
"authenticationType": "SqlLogin",
"server": "thedatafarmsqlserver.database.windows.net", "database": "AdventureWorksSample",
"user": "me",
"password": "mypassword",
"savePassword": true,
"profileName": "AzureAWSample"}
}
With profiles stored in the settings, you have the option to select AzureAWSample or other stored profiles when you want to connect. Once connected, you can start writing and executing TSQL. The easiest way to do that is to have a file with the SQL extension open in the editor. As I mentioned, this forces the SQL editor features to kick in, and one of its wonderful features is the built-in TSQL snippets.
The easiest way to interact with mssql is by having a file open for editing and ensuring that VS Code knows you’re editing SQL. You can do this whether you have a folder or project open thanks to the New query com- mand. That will create a SQLQuery.sql file and the sql extension will cause VS Code to switch to the mssql editor. The current editor is noted in the lower right-hand corner of VS Code, and this changes based on file exten- sions to give you proper IntelliSense and other relevant features provided by the extension. You can click on what’s displayed to change it if needed. The mssql editor
msdnmagazine.com
Figure 4 IntelliSense Reads the Schema and Helps Build TSQL
June 2017 9
]