Page 14 - MSDN Magazine, June 2017

Figure 5 Results of a Customer Data Query
Begin by typing sql in the editor window and IntelliSense will display the list of built-in snippets, as shown in Figure 2.
You can see that this isn’t limited to simply querying existing data— mssql will execute any (valid and permissible) TSQL. I like to list the databasesortablesandviewsfromaselecteddatabasetoverifythatI’ve connected to the correct database, and the ListTablesAndViews snippet is great for that. Normally, I’d have to ask Dr. GoogleBing to help me with TSQL like this, so I’m extra grateful for this snippet.
Pressing Ctrl or Command+Shift+E will execute the command. You can also select the text and right-click for the context menu, which includes an Execute option.
A results pane opens up with the response like the one in Figure 3.
In the upper right, notice there are two small icons. The first lets you save the results as a CSV text file. The second is for saving the results as JSON. Let’s do a query that gets Customer data, see what those results look like, then save them to JSON. With the list of tables in front of me, I can start typing my query and, as Figure 4 shows, IntelliSense kicks in with knowledge of the database schema.
I modified my select statement to get three columns from Sales- LT.Customer. Then, I was able to highlight the statement and use the Ctrl or Command+Shift+E keyboard shortcut again to execute just that query. As with SQL Server Management Studio, I can execute one or more statements and, in response, see one or more result sets.
The results are presented in a grid as shown in Figure 5. You can highlight one or more row and column combinations and then click the CSV or JSON icons, which will then prompt you for a file name to save to. And you can easily select all of the data from a context menu by right-clicking on the grid.
The filename prompt displays the current folder path. If that’s where you want to save the file, you don’t have to retype the path. Just type the name of the file and it will be saved in that folder. I didn’t realize that the first few times, so learn from my mistake.
I selected only the first row of the customer data my query pro- jected, and then used the Save to JSON icon, specifying a file name. Here’s the JSON that was
VS Code. Let’s create one to list stored procedures and functions. Start by going to Preferences and User Snippets. You’ll see that the mssql extension added a template for SQL snippets. Choose that and the blank template will open. As you add more snippets, you’ll continue to work in this file. Also, if you’ve created some snippets to share (or perhaps find a bug or have
another idea for mssql), this is an open source extension, and you can contribute by going to github.com/Microsoft/vscode-mssql to submit pull requests or issues.
After a long session with Dr. GoogleBing and testing various ideas, here’s the snippet I created to list all of the stored procedures and functions in the target database:
"List Stored Procedures": {
"prefix": "sqlListStoredProcedures", "body": [
Figure 6 Editor Window Context Menu
Keep in mind that you can easily add your own snippets to
10 msdn magazine
Data Points
output to my file:
[
{
"CustomerID": "1", "FirstName": "Orlando", "LastName": "Gee"
}
I was able to share this output by right-clicking on the results grid and selecting its “Copy with headers” option.
As the late-night television ads say, “But wait! There’s more!” The editor window has a context menu, as well (see Figure 6).
The most interesting items on it (in my humble opinion) are Go to Definition and Peek Definition. If you select a table name in the edit window—for example, Customer in the command shown in Figure 5—these commands will show you the CREATE script for the customer table.
The mssql extension is constantly evolving and I’m looking forward to future updates. The version I’ve shared here is still a preview, version 0.3.0. If you have it installed in Visual Studio Code, you’ll be notified of updates. You can watch and participate in its evolution on its GitHub site at aka.ms/mssql-marketplace. n
Julie lerman is a Microsoft Regional Director, Microsoft MVP, software team mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at juliel.me/PS-Videos.
Thanks to the following Microsoft technical experts for reviewing this article: Kevin Cunnane, Eric Kang and Sanjay Nagamangalam
]
"SELECT [Name],[Type_Desc] " ,
"FROM [sys].[all_objects] " ,
"WHERE ([Type] = 'P' OR [Type]='FN' OR [Type]='TF' OR [Type]='IF') ", "AND [Is_MS_Shipped] = 0"
],
"description": "List Stored Procedures"
Now when I type sql in the editor window, sqlListStoreProcedures is among the options. The results of executing that command against my target database are:
}
Name
uspPrintError
uspLogError ufnGetAllCategories ufnGetSalesOrderStatusText ufnGetCustomerInformation
Type_Desc
SQL_STORED_PROCEDURE SQL_STORED_PROCEDURE SQL_TABLE_VALUED_FUNCTION SQL_SCALAR_FUNCTION SQL_INLINE_TABLE_VALUED_FUNCTION