Shawn Melton
C:\Users\wshaw\AppData\Local\Temp\SNAGHTML519cfb8.PNG

Introduction of Visual Studio Code for DBAs

December 29, 2016 by

Introduction

Visual Studio Code (Code), have you heard of this product yet? This is an open-source, cross-platform and extremely light weight code editor from Microsoft. You may see some folks explain this editor as the little brother to Visual Studio Community Edition (VS Community), but it is more compared to editors like Atom, Sublime Text or even Notepad++. It is not something you can use to compile program code, so it is for the less complex coding projects. I utilize Code as my editor of choice now with PowerShell, and even T-SQL at times. In this article, I wanted to walk you through using Code and note some specific extensions I use for PowerShell and SQL Server.

Basic Layout

Getting around Code is easy, once you get familiar with it. Just like any other new program it may seem overwhelming but I wanted to walk you through this as it helped me to understand how to use it more efficiently. You can see the screenshot below for a visual reference.

  1. View Bar – The side bar will collapse as you need more real estate to work. This bar offers the ability to open and close the side bar as you need. If the side bar is already open for the Explorer, clicking on that icon will cause the side bar to close.
  2. Side Bar – This provides various functionality based on which one you have open. In top-down order:
    1. Explorer (CTRL+SHIFT+E) – Browse the folder of scripts/files that are you currently working on, referenced as your current workspace. You can create new files, folders, drag and drop files into the folders.
    2. Search (CTRL+SHIFT+F) – Search the current files you have open in the Editor Groups for specific string. Offers the functionality such as “Find and Replace” and to find all occurrences of the given text.
    3. Git (CTRL+SHIFT+G) – Integration with Git, if it is installed on the local machine. Allows you to initialize the current folder/workspace as a Git repository, if not already. Once initialized or if you open a repository folder it offers various functionality to perform against that repository (sync, publish, commit, etc.).
    4. Debug (CTR+SHIFT+D) – Debug bar utilized for debugging scripts based on support for the language/file extension if supported.
    5. Extensions (CTRL+SHIFT+X) – Manage the current extensions installed and search the marketplace for new ones to install.
  3. Editor Groups – Code supports multiple tabs being opened either in split view or just opening another file to the side. You can drag-and-drop tabs around as you need to and move them between each view.
    1. One keyboard shortcut that is nice to use when you are dealing with a large workspace is CTRL+P. When you use this keystroke, you can do an equivalent search of the folder for files by their names. As you type the name in it will bring up the files that match and you can select to open.
  4. Status Bar – The general active areas of the status bar are going to be the far left and then the far right.
    1. Left side – Most of the functionality added with extensions is found on this side. If you use Git integration you will see indicators such as what branch you have checked out. When you are working with PowerShell files, PSScriptAnalzyer is integrated in the extension and you will see any rules that show warnings or failures.
    2. Right side – Generally specific to Code functionality, apart from a few that I have worked with (e.g. mssql). You will also see the language that is detected by Code (based on the file extension mapping). Then the smiley face (that is found in most MS products now a days) is used to provide feedback via Twitter. This also contains shortcuts to open an issue with Microsoft via the vscode repository on GitHub.

Command Palette

This is the bread and butter (to me) of Code. If you learn how to use this fully, you may find yourself never having to touch the mouse or touchpad on your computer again. The keystroke to bring this up is CTRL+SHIFT+P as shown below:

One thing I will point out is you can see this starts you with the “>” symbol. If you have used the CTRL+P shortcut you saw that it does not include that symbol, however, if you want to just press CTRL+P and then type the “>” symbol you get the same result. The options you have in the palette are based on the extensions you have installed. As Code grows in functionality so will the built-in or native commands available to you. The extensions that offer commands will generally offer that information in the manual page (just click on an extension in Code to see this).

User Settings vs Workspace Settings

These two items are settings you will find yourself using at various times. The setting files in Code use a JSON format file. These files are created the first time you set anything, whether it be for user or workspace. There is no shortcut keystroke at the time of writing, but you will find both settings under File > Preferences.

User

The user settings are configurations that you set and forget, and this applies to all of Code no matter what project or file you are working on. This includes things like font size or if you want to auto save files you are working on. There are various extensions that will utilize your user settings as well. Each time you open the user settings you will get a split view:

  • the left side includes ALL the settings possible and their default values
  • the right side is the current settings you have set for Code.

Going through the left side can be a learning point to find things you might want to turn off, or turn on. As you add extensions the left side will include any setting those extensions offer as well. To just note as well, this file is saved in your user profile under “%APPDATA%\Code\User\settings.json”.

Workspace

Workspace is like the user settings, with the exception it will only apply to the workspace or folder you currently have open. If you noticed when you opened your first folder it will add a folder called, “.vscode”.

There is nothing in this folder, until you go to File > Preferences > Workspace Settings. You will then see a “settings.json” file is created. This is where you can manipulate things around the current project you are working on. Think of these like environment settings, where you can adjust your environment based on what you are working on.

Extensions

While native features in Code are growing, they do not support things like code syntax highlighting or native intellisense for a language. This is where extensions come in to play and help expand what you can do with Code. I am going to walk through the main extensions I use for coding various projects as a Database Administrator.

PowerShell

The PowerShell extension is the only thing you need to develop with PowerShell. It supports using PowerShell version 3.0 or higher, on Windows 7 and higher. It will also support use of PowerShell version 6.0 on Linux and OS X. To run and debug your PowerShell code you will need to adjust your user settings, and just add one item: “powershell.developer.powerShellExePath”. You set this to the PowerShell.exe path on your machine and this executable will be what Code and the debugging services utilize.

Debugging

Each language is going to have a configuration to setup when you want to debug a script, that configuration is saved within a “launch.json” file and saved in the “.vscode” folder of your workspace.

mssql

This is an extension released by Microsoft and supports connecting to Microsoft SQL Server, Azure SQL and SQL Data Warehouse. It is an extension that works with connection profiles, and these profiles are kept within your user settings. If you want to execute a SQL script you just use CTRL+SHIFT+E, and will be prompted for which profile you want to use. An example of the profiles I have configured on my machine:

You can see I have profiles for 3 local instances and then one Azure SQL database. The prompts are straightforward to follow for creating a new connection profile. You will also find the template for creating a profile under the user settings, under “MSSQL configuration”. As an example, the profile for “MSSQLSERVER” shown above looks like this in my user settings:

If I wanted to change this to a SQL Login, and have it save the password for me I just add the “user” and “savePassword”:

Executing a query will give you a results tab in a separate editor group (there is a message pane in this as well, just not shown in the screenshot):

Once you are connected you will see the status bar change on the bottom right:

This shows you the server (or instance) you are connected to, database, and then the login. Anytime you execute a query it will use this connection. As well, any additional SQL file you open within the same workspace will use this connection. If you click on this area of the status bar, it will give you a prompt to change to a different database, if the login has permission. To disconnect you can simply use CTRL+SHIFT+D, or you go through the command palette (just start typing mssql).

Summary

As database administrator’s role grow and expand into other areas such as Azure or AWS services, you will find Code to be an invaluable tool. I have started using Code as replacement for Notepad++, which I previously used to get syntax highlighting for editing other languages like XML or JSON. Now I have one software tool to use for looking at multiple language files, and can debug them as well. I hope this article helps you get started with Code, and if you have any questions just let me know. You can also find help on Stack Overflow or the GitHub repository for Code. Each extension mentioned above also has a repository on GitHub that you can submit issues to for help and finding bugs.

Shawn Melton
Latest posts by Shawn Melton (see all)

Related posts:

  1. Add users to a Power BI workspace using PowerShell
  2. Getting started with Visual Studio Code (VS Code)
  3. Introducción de Visual Studio Code para DBAs
  4. DBATools PowerShell SQL Server Database Backups commands
  5. Top 50 PowerShell bloggers of 2018
Visual Studio

About Shawn Melton

Shawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada. After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server. He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS). View all posts by Shawn Melton

168 Views