Visual Studio Toolbox

SQL Server Extension for Visual Studio Code

SQL Server Extension for Visual Studio Code
5 (100%) 1 vote
(Video Transcript)
Hi, welcome to Visual Studio Toolbox. I’m your host, Robert Green, and joining me today is Eric Kang. Hey, Eric.

>> Hi, Robert.

>> How are you doing?

>> Very good, pretty good.

>> Eric’s here to continue our series on data. You guys may have noticed that we’re doing several episodes on data. I did an episode with Scott Klein a couple months ago to talk about what’s new in data for developers. They kind of focused on the Cloud stuff, a lot of what’s going on in Azure, touched a little bit on SQL. We did an episode a little bit back with Dmitri.

>> Mm-hm.

>> And Kevin Knain?

>> Mm-hm. Knan.

>> Knan. [LAUGH] See I wasn’t here to ask him how to pronounce his name. Kevin Knan on the SQL Server data tools which run inside Visual Studio.

>> Yes.

>> And you’re gonna show us how you can do SQL Server from inside Visual Studio Code.

>> Yes.

>> In a kinda cross-platform manner.

>> Exactly.

>> And then we have another episode that we’re gonna do. And I’m not sure if this one’s gonna be before that one, or that one’s before this one. So, don’t hold me to the order. But we’ll do another episode on, it’s gonna be SQL Server and connectors which is how you can talk to SQL Server from additional languages that you would be doing for inside Visual Studio or code like PHP, Node etc.

>> Yeah.

>> Right.

>> Jdbc all that matters.

>> Right.

>> I wanna actually show this one. So this is the one thing that you should remember as a developer. aka.ms/sqldev.

>> All right.

>> So this site actually has all the getting started.

>> Mm-hm.

>> And building your app with SQL Server, with Node.js, PHP, all the matter there.

>> Right.

>> And also it has the cross links for the useful docs, and then the tools site.

>> Yep.

>> So, this is the URL.

>> Cool-

>> That you can remember.

>> And there’s amazingly cool stuff going on in SQL Server, some people might be back from the world from a few years back. Where you just talk to SQL Server, it’s just sitting on some server somewhere, it was databases, you just put a connection to it, you had data in there. And you never really cared what version was running there. Is it running on Windows? Which version of Server? Who cares?

>> Right.

>> Cuz you’re just talking to SQL Server.

>> Right.

>> But those days are kind of long gone now [LAUGH]. There’s an awful lot more that’s been going on, and the types of apps we’re building are very, very different.

>> Yeah.

>> We’ve got SQL Server running on Linux. We’ve got the ability to talk to SQL Server from multiple languages on multiple platforms from multiple tools.

>> Right.

>> And so we’re trying to give everybody a really good grounding in that on this shell.

>> Yeah, exactly, yep, we are all on that developer focus.

>> Mm-hm.

>> So that’s one thing, so with that you can understand that while we have a SQL dev-

>> Dev, right.

>> Summit, we talk about those PHP, the JDBC, Node.js. Now you can understand that we focus on the developers.

>> Right.

>> And we want them to develop their apps with a SQL Server.

>> Cool.

>> Yes.

>> All right, so talk to us about Visual Studio Code.

>> Okay, so let’s switch to the slide that I prepared. Okay, so today’s session we are gonna talk about, the SQL Server vNext. The vNext runs on Linux. Mac OS with a Docker container, and Windows equally everywhere.

>> Okay. So when you say vNext, you mean it hasn’t shipped yet?

>> It hasn’t shipped yet. In November 16 with the Connect event, we released the CDP1 public briefing.

>> Okay, all right.

>> Yes, so that’s the stage.

>> Got it.

>> And mssql extension for Visual Studio Code is the main topic that we’re gonna talk about as a tooling perspective. The Visual Studio Code, as you know, it is light-weight, editor-based, developer tool, which is very popular in the developer community, right? And then, another, the usage is like a full-featured ID perspective, like at Visual Studio.

>> Right.

>> So, it’s kind of a choice for the developer.

>> Yep.

>> Yeah, it’s about the workflow.

>> Exactly.

>> Some people, and I’m in that group, like the fully featured workflow that happens all inside the IDE.

>> Exactly.

>> I love Wizards.

>> Right.

>> I love things that write my code for me.

>> Right.

>> I don’t wanna have to drop down to the command prompt. I don’t even want to have to go outside of a Visual Studio to be typing. That’s just me. There are others who like more of a less integrated work flow. They do the exact same amount of work, of course, talk to the exact same things. But do it more from a command-based mixing and matching various tools.

>> Right.

>> That’s what Visual Studio Code is for.

>> Right, so one thing that I read from a blog on the Internet is that excellent editor, plus a great run time tool to run your app.

>> Mm-hm.

>> Is what developer needs.

>> Mm-hm.

>> Right, so that makes sense? Visual Studio Code fits in that bucket very well, and when you build an application, and there is the data for application, right? And you have a choice.

>> Mm-hm.

>> For storing the data and processing the data, SQL Server is one great choice that you can store your application data and do the data processing.

>> Right.

>> And it’s part of your application basically. And when you build your application using VS Code or Visual Studio, then you need to be able to have a solution for your database port.

>> Right.

>> So mssql extension comes in that picture.

>> Okay, and this is one of the Visual Studio Code extensions?

>> Yes.

>> Available.

>> You know, for a while-

>> We released the preview version, public preview on November 16.

>> Okay.

>> So November 16 was the big launching day. SQL Server vNext that runs on everywhere, and we released mssql extension that runs on VS Code, that runs on the Mac, Linux, and Windows. And we also released a command line tools, that you can use it to run the routine task in script like a BASH script.

>> Mm-hm.

>> Or you can automate it for the task or you can also ssh in like a terminal into the machine that runs your server, and run command, right? So that’s basically kind of the whole portfolio and picture in tools perspective, how we can help developers to have the means to use our tools to develop the [INAUDIBLE]

>> See, I, I hear that, and I go, well, why wouldn’t I just use SQL Server Management Studio?

>> That, SQL Server Management Studio is also great. On same day, we released the SQL Server Management Studio, Version 17.0.

>> Mm-hm.

>> The version bumped from 16.0 to 17. That distinguishes or signifies that version starts supporting SQL Server vNext. So, key thing is that even though you are using Windows to manage or develop your application, it doesn’t matter. Independently, you can run SQL Server on Mac, Linux, or Windows, right?

>> Right.

>> So, SSMS has the capability to connect and run your task.

>> Mm-hm.

>> On any SQL Server platform and version, for that matter. Equally, SQL Server data tools that Kevin and Dimitri did a presentation in the previous session.

>> Mm-hm.

>> That has also version 17.0 released. And it supports SQL Server, vNext, everything.

>> Cool.

>> Yeah.

>> All right.

>> Okay.

>> Okay, so just quickly repeating the focus of our team is we really want to help our user to develop with SQL Server, Azure SQL Database

>> Mm-hm

>> And Azure SQL Data Warehouse anywhere in a simple and better productively. So whenever we design our product, in tools side the key thing is it has to be simple. And it has to be productive.

>> Mm-hm.

>> That’s a key principle that we always think in our team. So to do that, there is two things, two important things to deliver. One is a SQL Server should run on Mac, Linux, and Windows, right?

>> Yep.

>> So that was one, and the other one is that, as we just briefly talked about, the developer’s tool should run on those multi-res-

>> Right.

>> Independently from wherever your SQL Server is running.

>> Right.

>> So, the answer to the question on November 16th, we released the SQL Tools Portfolio with mssql extension across platforms supported for those command line interfaces and all that. So now we have many choices as a developer, right?

>> Cool.

>> So I just summarize it. The way that we look at it here is like this, you’re the developer, I want to develop my application and database on let’s say Linux and Mac. Go use mssql extension [INAUDIBLE] to your code. That’s your choice. It’s great. It’s light weight, but it provides the must have features like the connect to SQL Server, write a SQL code within and beautiful languages service and execute and view the result.

>> Cool, that’s it.

>> [CROSSTALK] So, let’s take a look at it then I’m gonna. So let’s switch to the demo right?

>> Yeah.

>> For this demo, I’m gonna use the sample database that we have published in the GitHub SQL service samples.

>> Mm-hm.

>> This is the samples that GitHub repository you can go and clone just do copy and run Git clone and you can paste this URL They will pull down all the SQL Server samples to your Mac. And once you do that, and let’s go to VS code. The first step is that I already pulled down those projects, which is called Belgrade Product Catalog Database. The reason there is Belgrade is that we have a team in Serbia. That worked great. And the in the team actually made this application. What it does is that basically it is a ASP.NET Core application with using jQuery 2.0 to query the SQL Server.

>> Okay.

>> And it shows the new feature like Json data and function support. Nowadays all the data handling is Json. So SQL Server has a built-in feature to support Json data and the processing feature. So, it demonstrate that one and temporal table. Have you heard about the temporal table?

>> I’ve heard it-

>> Okay great.

>> The term, but refresh.

>> I see, temporal is more like, for example if you’re working in like a ERP solution, that means a history of employee history, right? That’s very important so that we can always look at the person was in this job position but, in next year he got promoted. So he became CEO of the company. Then there’s always history information for that one.

>> Right.

>> If you do online sales or web application then you have a product. And you should have at least a product and history. And you wanna see what has happened in your business, and all that kind of stuff. So historical data is very important.

>> Right.

>> And if you try to build that logic in your application, it can be pretty complex. But using SQL server temporal table is doing the versioning of your data automatically. By simply just to set the system versioning flag on your table. And link up to your main table and the system table. So, it’s a pretty cool feature, so the demo shows that one and the Security features like dynamic data masking and role-level security. Those are also the features that if you want to build in your application using your app logic, pretty complex. But the SQL Server supports that feature just out of box.

>> Right, cool.

>> So this demo actually shows you those one. So let’s quickly take a look at the app and then the jump into data set. So that, if we don’t do that then the developers will feel like, uh-oh, too much data.

>> [LAUGH]

>> So I will just entertain you a little bit at the beginning. So I already have downloaded it. And If you go to Integrated Terminal, VS Code has all nice features, like you can run command within the VS Code.

>> Mm-hm.

>> So it is a .NET application, as I said. So you can do .NET restore and build and run. Then it will run your application. So it’s that simple. I just did the .net run, because I already did the restore and build.

>> Okay.

>> And let’s go to our website, which is this one. So let me make sure it is running. Yeah, it’s running.

>> Okay.

>> So this is the kind of, it’s a simple application that shows all your products and the companies. And what it does is that look at it, if I click this time slider. It shows you the data in the past. This is temporal table’s usage. This is so nice. But it’s so simple to implement using SQL Server. You used to expand many many hours to implement this logic in this application. So this is our demo. And let’s go back to our VS Code project. Let me close it. To build and kinda work with the SQL server, you need two extensions in Visual Studio Code. One is the C# extension, and the other one is MSSQL extension. Right now, we have over 9,500 downloads, we have released in the November 16th. So thank you very much for everyone who [LAUGH] downloaded and started using.

>> It’s our-

>> We don’t need to be reminded. We’re here.

>> [LAUGH] Exactly. So that’s great, so our team is really excited that people are finding our tool useful for their day to day development work. So you all keep going.

>> You write a SQL script and then [INAUDIBLE]. That would actually be faster, and potentially more light weight, than using SQL Server Management Studio. If I just wanna run some simple queries and see results.

>> Yeah, that’s what I’m saying. We are now recommending one or the other. Now, it’s your choice. Now, you have full power to choose.

>> Well, if I just wanna look at a C# file on the disk, I will load it in Visual Studio Code. Because I just want to look at the code. It comes up much faster. Even if you don’t do your main development in your studio code it’s a really handy utility to have.

>> Yup.

>> For times when you wanna do things a bit faster.

>> Actually, it can be anything. It can actually be used as a notepad, right?

>> Yeah, there’s a markdown extension, so if you have to do markdown, I think it’s your markdown editor of choice.

>> Exactly, and then for anything like taking the text note, I use VS code.

>> Right.

>> And for literally anything, I use VS code nowadays, so it’s great. And even though we’re at the use your education team who’s producing the documentations. They use this screen. So that’s great. Its a simple, just to go to search box in visual studio and type in MSSQL. And you will see that there are two extensions. The VS code dash and the MSSQL. So this one is a Is one of our PM in our team. And who actually made the prototype MS SQL Extension. And we made the official version, MS SQL which is the second one.

>> Yup.

>> So go and install this one instead of VS Code MS SQL. We are going to Unpublished VS code dash MS equal sometimes. We just elected to have like a transition.

>> Okay. Alright.

>> So those are two extensions that you need. So let’s get to the real business. So if you go to the folder, Belgrade Product Catalog Demo. There is a set of scripts that you can help. So if you’re an application developer you focus on the building mission then the core at ASP.net to coding and writing code for jQuery. But to test new applications. You need to prepare your SQL Server extension database, all that kind of stuff. So these are kind of the SQL scripts that set up your application database so that you can test it. One thing important is that, as you see I’m running everything on my Mac. One of the key scenario that I want to point out before we continue is that there is a need for developers to do things all locally. For example, if you are using Mac, then you should be able to develop your application, develop your database, and be able to test it.

>> Right.

>> After that is all done, you can choose your production environment. It can be Azure SQL database or any cloud or Windows Linnux. But you should be able to work your day to day job in Starbucks as well.

>> Right.

>> So, the key thing is that on Mac, with the SQL Server, you can do that now. I’m running SQL Server vnext on Docker container on Mac. So it’s ironic.

>> Clever.

>> Right. It’s very, very easy. Let me go to docs.microsoft.com and there is a SQL/Linux. Or you can just remember aka .msSQLLinux, if you’re typing. It will bring you to this page. And there is one nice icon that says Install. All right, just to click it and there are a bunch of Getting Started guide that we prepared for the new MS 16 release. So, one of effort was this, not only for the installation, it also has a what you can do for a development perspective. What you can do for management, what you can do for migration. If you already have, let’s say a database running on a SQL Server on Windows. Then you need to know how I can move the database from SQL server on Windows to SQL server on Linux.

>> Right.

>> So this is very, all those documents are done through a step by step guide perspective. So it’s really easy to follow.

>> Okay.

>> Same for the uninstallation of SQL server. Run the Docker, that is the main topic that I wanna do. To install SQL Server container, the image and to start the container, docker container for SQL Server Linux, it’s just running these two

>> Okay.

>> Commands, so copy, paste, and run it in the terminal and it will set you up. Only one thing that you need to remember is that, go to Preference on the Docker, of the Preference Page. And make sure the memory is at least a full gigabits

>> Okay.

>> Because a SQL Server Container. Docker container, requires minimum four gigabytes of memory. So this is something that you remember. I got this issue at the beginning and then, Docker starts with two gigabytes memory at the beginning.

>> So Docker’s got two, so this four gig is. That’s not two for Docker plus four for SQL, you’re not using-

>> It’s the total.

>> That’s total, okay.

>> The Docker uses 4GB of memory for the container.

>> If you’re on your, say, Surface Pro 3 with eight giga RAM, you got four gigs left for whatever you’re doing.

>> Yep. That’s pretty optimized, and the solutions, so I’m running this one. I’m also running parallels, the VM stuff and I never see an issue of performance on my Mac. So, that’s the initial setup. It’s just a one time setup and now your Mac is fully ready for your app and database development locally, without switching back and forth between [INAUDIBLE]

>> That’s nice. So you don’t If you just wanna test locally against SQL server, you don’t have to install SQL Server developer edition, you don’t have to install SQL express, necessarily. Have that run and you can just do this and have full-blown SQL Server running only when you need it, and turn it off when you’re done.

>> Right, so Docker Container is also available for Windows as well, so you can do the same scenario on the Windows as well.

>> Cool.

>> That’s pretty cool technology. I’m in love as well. So that’s the initial set-up, then we’re fully ready, so now we can really go into the MS SQL Extension capabilities. So as you saw, I already did the demo preparation for you to show the how app is working. So let’s clean up and start from scratch, so that you can see the whole end to end developer activity, using vscode. So, what I’m doing is that on the editor, I opened up the SQL I’m typing in SQL, these are snippets. So, for most the commonly used actions like DROP, DATABASE CREATE, DATABASE, you don’t have to remember all of those syntax and stuff. You can just use sqldrop Database here. And it produces the snippet templates for you. And you can type in ProductCatalog, this is the name of the database. And it just completes your template for dropping the database. This one will fail, because we’ve been later using the database. And if there is a connection happening, then the dropping database fails, it has to be a single user mode, or there’s no connection like that.

So I added the command here. [INAUDIBLE] The main reason that I put it as a command is that, it’s dangerous if you run it and drop accidentally, your database then is kind of bad thing happen. So to prevent it, I just intentionally put this alter database statement that changes your database [INAUDIBLE] as a command. So think one more time before you run it. So now, I’m writing it. And Visual Studio code for, the audience can be like the real Visual Studio code, the developers. But, there could be some people who’s coming from the SQL side. First time using VS code, because of MS SQL. So, in VS code, always the starting point is the contrast of the p to open up the command or prefer to use F1 key. So if I click F1 key and type in SQL, then it shows all the commands that the provides. And going in a little more detail if you take a look at it, we wanted to increase the productivity in a way that which is natural to the developers. So, for a VS coder you just what is natural is using keyboard and using shortcuts.

So that was the design, so F1 in SQL and if you take a look correctly just the typing two more words. It always selects exact command that you need. So we put the effort to design it at this level. To increase the productivity of the developer, every single inch. So, that’s basically what we are doing. So, let’s go and then MS SQL Connect. Because there is no connection, or connection that finishing before whatsoever, it starts with empty connection profile at least. And it has a Create Connection Profile. So by clicking it, it run you through like a command or like flow, so it asks you a question what is server name? It’s local host. And database name? It’s optional. I can just decode and it will select a default. And username, I’m using just SA for now. Type in the password. Now we ask you to save the password, now. For Mac, we save the password in the key chain. So we don’t leave it in the clear text in any of your file, if you choose to go that route and it’s secured.

For Windows, we use a secret store in Windows version. So that is kind of what we do. So if you yes and the profile name, let’s just make it as a Docker, that will create it. So, I will show you one more thing. SQL Connect, if you go to SQL Manage Connection profile, there is multiple sub tasks that can give you more the actions to manage your connection profile. One is the edit. As you saw in the visual, we only asked you four questions. Server name, database name, user name, and password.

>> Right. But in the SQL Server Connection, connection string can have many more stuff, like what is the connection timeout.

>> What is the security like encrypt on, or not, or to trust the server certificate, or not. For those kind of stuff, you can just directly go to the Edit menu in the Connection Profile Management, and we added all those suggestion list. For all the connection property strings. Even for the seasoned SQL developer, they may not remember all this connection profile, and they know exactly what it does. So to help them, we added those ones. So, if you go to encrypt, then it puts the default value, suggested value for the encrypted property, and those are kind of features that we do. And this one is saved in the user settings, it’s only visible to you. And if you wanna, let’s say if you have 50 connection strings, but then you have to work on Mac, sometimes you have connect from your other windows desktop PC or Linux. What you can do is that, just copy and paste it there, you don’t have to go the create connection profile 50 times whenever you switch your machine, right so.

>> Okay.

>> That’s kind of a detail that we did design and implement. For this one. So I’m just gonna save it and continue. Now SQL EX, execute query. It executes query and delete the database. So let’s double check it. SQL use. SQL use, it is a use database command, that first showed you what are the database in your server and if you want you can switch the connection. So good. So, we have cleaned up our demo setup, I’m just closing it. The first step is always, you have to create a database. And then in the database, we have to create tables, the procedures that your application needs. So I opened up setup.sql, and doing the same thing. So because we have created our connection profile, now you can see that Docker shows up there. And since then, then you can just click that connection profile that will just make a connection to your servers. And I’ll execute it. By the way, instead of using shortcut, I’m using this F1, at the command line, the command palette. Because it’s a demo.

I have a muscle memory, I just quickly press the shortcuts, but if I do that then nobody can see what’s happening there.

>> [LAUGH]

>> Magically, suddenly it says executing and all that kind of stuff so that’s one thing that I want to say and by clicking it. It runs it. One thing that you can notice is that, this is the message pane and that there is a timestamp and total execution time. Whenever we execute the statement to SQL Server, what is important is the elapsed time and then the time it was started, so that we can always measure the performance of the query. And to help, if there’s any issue then by clicking the line number you see that in the editor, we highlight the corresponding batch block. So you can always navigate. Between the correlated results and additure in this way. And you don’t get lost. So now. Let me show you SQL use database one more time. And now you see that product, [INAUDIBLE] database is created, right. So let’s make a connection. So its connection is also successful. And the second one that I’m gonna do is that the setting up the temporal table and stuff in the newly created database, all right.

So if I make a connection again, now you can see that we only created, yeah.

>> So you have to keep connecting, it doesn’t maintain the connection?

>> It is editor based. So one thing that we are doing is that connection is editor session based. We actually thought about which one is more preferable, maintaining a global session for the entire VS code. So whenever there is a new editor session opens up, we just inherit the same connection.

>> Mm-hm.

>> Versus keep that scope to editor based. Only one thing in SQL Server is that you can open up many editor sessions with a different context.

>> Mm-hm.

>> Let’s say one editor you have a drop database step that we just executed or delete from table statement. And you can think you’re connected to your local task database. But accidentally it can happen that you’re actually connected to your production and you run it, right? So those are kind of danger.

>> Okay.

>> So that’s the way that we thought and as an initial start we made it as an editor session and the connection session is kind of the same level. But we have like a plan backlog item, if the user wants to go with a global session, we want to have customizable option that you can set it. Okay, so instead of doing that, please know I inherit the connection from the global session. So that is one of the backlog item. So those are the suggestions and feedback, we want to gain.

>> Mm-hm.

>> And every single extension is fully open source. So while you come to the GitHub MS SQL extension product page, now you can see all the issues, yep. You can see all of our plans and suggest and [INAUDIBLE] you want, all that you can do.

>> Okay.

>> That’s basically what [INAUDIBLE] users. One thing that I want to [INAUDIBLE], we only created docker connection profile, but if you take a look at it now, [INAUDIBLE], right. What’s happening here is that whenever you make the connection to a database, we keep most recently used connection history and put it automatically in your list.

>> That’s cool.

>> So you don’t have to create a new connection profile from product catalog, right?

>> Right.

>> Originally it was just default, we didn’t specify database but second one we automatically added as the connection to product catalog database. So that is the key feature that we added to make things much easier. So it’s more like it is a one click into one click into kind of operation in that perspective. So we made a connection here. So product catalog and I’m running. We call the X that runs it. Okay, so basically this one created the SQL Server database and put all the data in the system history. Sorry, history information in our database, so that my application can use it again. So that’s basically what developers would do. Open query, write query and execute it and that’s one. So going into, I’m sorry, I’m putting it here. So lets simulate what people do as a developer. So these are like test script I wanna verify the SQL statements and stored procedure by application calling against my product catalog database is actually a good in shape. So I have a [INAUDIBLE] script, when you write the [INAUDIBLE] script [INAUDIBLE] likewise [INAUDIBLE].

Not connected. Okay. There is a few seconds of delay to pull out the schema information. Now it is working right. So, whenever you connect the first time, there’s a little bit of time to pull out this scheme information.

>> Right, right.

>> To populate these suggestions listed in IntelliSense. So now you can complete your query using IntelliSense.

>> Mm-hm.

>> And then you can run your query. So now, when you saw the create database and the setup temporal SQL, you only saw messages, right, because it was all DDL execution. There was no results set. Now when you actually run your business logic using SQL statement, query statement, then there are data that comes out, right?

>> Right.

>> And I executed multiple select statements, so there are multiple ones.

>> Yep.

>> And one thing that is fun is that it’s more than eight records and you wanna work on the second, the results set. Then you can click this one and it maximizes it.

>> Okay.

>> Right, and you can work on it and reduce it. If you wanna look at the message, you can even collapse and open or collapse the message that you don’t need.

>> And you can save those results?

>> Yes, exactly. So let’s go to company here, I’ll just maximize it, I save all. Now you see two commands, Save as CSV, save as JSON.

>> Okay.

>> And this one icon does the same thing. So Save as JSON icon, you click it. I name a company, data.json. Yes.

>> Very nice.

>> And now it produces JSON file or CS file that you need. Okay, so this one completes the basic demo of what you can do as a Visual Studio code for just the core features like writing, executing and view the results and this versus save result. I’ll switch to, let’s see, Windows 10. It was on hold. I’m going to show you something more interesting, what’s coming.

>> Okay.

>> Very soon. And so probably when we publish this session maybe the new upcoming feature will have been already released, I don’t know.

>> Okay, so this is what’s new in the extension?

>> Yes, extension in the VS code.

>> Got it.

>> And we are going to release in a week or two.

>> Okay.

>> So it will be available. lf I switch it into Windows, now you can believe that it runs on both iOS.

>> [LAUGH]

>> All right, and let’s do. I made the demo script, so that I can remember. So what I’m going to do here is that I’ll get a query and the result from a query from [INAUDIBLE] company. And I want add simulate my apps the logic that insert one more component data in the database. So that I can actually see what is exact code that I need to write. And if it works correctly, that I can simulate it with VS code with MS SQL extension. So I’m executing query. So we have a theory records here, right, and I will Save All as JSON. So company, I’m just doing it again on Windows.

>> Yeah.

>> Okay, now it is a theory records. Okay, close it. Oops, sorry, definition. And what I’m gonna do is add this more. So I made it as a name, VS toolbox address [INAUDIBLE], so I go here and this is my new JSON data, test data code that I can just check in into my project. So, that’s good. And then just to clean up the table, so that we can see the difference very easily. Okay, so there is no data, now I will show you one interesting thing.

>> You’re gonna add that as JSON instead of doing in a traditional [INAUDIBLE].

>> Exactly, and I don’t remember.

>> So you’re telling me I don’t have to re-type all the field names and-

>> No.

>> I can just take this JSON piece and slap it into the database.

>> Exactly.

>> I don’t believe it.

>> I’ll show you. But one thing is that I don’t even remember exact syntax. [LAUGH] So what I did was that You see channel nine insert from open JSON. It’s a user defined snippet. I copy the code from a sample documentation and pasted it here and created my own snippet. What you can do is just to go. To your Preference, and there’s your User Snippets. It’s a VS Code feature. VS Code has so many awesome features. One of them is User Snippets. And you can just select the SQL type, then this page gets opened. What you need to do is that Just put description, prefix as to what will show up in your suggestion list.

>> Yeah, okay.

>> And your body is basically your syntax with just a quotation mark, that’s it. Quotes, I mean, right? And then, it will automatically show up in your editor as a snippet that is within. Now It’s not done. So one thing we need to do is declare the name. It’s base on my. There is an adjacent data which is a variable in the template. So I wanna name it as a company, the variable name. Right I did that. Then there is, changes all Variable template to add company.

>> uh-huh

>> And this is where I want to add the json data, in json format. But before that, insert into company and then I have to put column list. This is another variable in my template I will show you quickly. Here there;s a column list. It’s a variable.

>> Ok. And I don’t know what you can do. What are the list columns that you have now. Beautiful, isn’t it?

>> [LAUGH]

>> Wow. SSMS, SSVP, they don’t even have this feature, right? No, it’s not all.

>> I gotta tell you that’s one of the coolest things I’ve seen all year in this show.

>> [LAUGH].

>> I’m telling you that is top ten coolest things we’ve shown on this show.

>> Great.

>> Well done.

>> Thank you. So now Company, autocompletion. Name, I don’t even have to type all the stuff.

>> Now, if you invented something that would copy them in automatically without me having to type.

>> [LAUGH]

>> I’d put it number one on the list.

>> All right.

>> You’re just gonna have to settle for top ten.

>> That’s good. Suggest it.

>> [LAUGH]

>> Great, isn’t it?

>> It’s so easy.

>> That’s awesome, mm-hm.

>> I didn’t even know

>> The syntax.

>> Right.

>> But I could finish it.

>> Yep.

>> Right?

>> You don’t have to go into the SQL explorer, call it up, expand, expand, expand.

>> Exactly.

>> Look.

>> Yep.

>> Right in line.

>> Yep.

>> Fantastic.

>> And the last one is width, this is specific to the json format.

>> Mm-hm.

>> You need to provide a list with type

>> It’s here.

>> I just, oop sorry. Copy and paste it. Copy. Paste. Right? And then

>> I can just remove this one. So this one remove this one In last one we can just click all at a time. Change all the class.

>> There’s a Visual Studio code feature.

>> Do you have to take that null out, or do you leave that?

>> Yo thank you. You just debugged my code, right? And if there’s an issue, there’s squiggly showing up. It tells you right away you have an issue. So that’s how you can actually get a notice. Wait a second there. There you have a code issue. So we just did code review. And let’s go to company Json, what I do, control. Just Select All. Copy. Go here. And Ctrl+V to copy it. And then let me write. And then I will select to show you. Actually it was executed. All right [APPLAUSE].

>> That’s so nice.

>> This is very nice. Our engineering team has done this brilliant job and I really want to thank them and that’s great. And just for fun, one more thing. I have a application, one of the recommendation from SQL is that when you build your application. Instead of writing query in your indirectly, create a for layered protection and then management. And use it. So if you want to debug and know what’s inside of. Built this application so I don’t even know what’s inside.

>> Yeah.

>> Right, and it happens every day. Now go to.

>> Nice.

>> Right, right in the editor directly you can do, take that, finish it. Or go to print it out in your other editor and you can debug it here.

>> Fantastic.

>> This is the new feature coming up in a couple weeks.

>> Great.

>> So that’s great. I will do one more demo to-

>> Okay.

>> Before we wrap up this session. One is called a progress in rendering, I will explain it this way. Performance matters. It has to be fast, right? Went into society when we talk about the performance it’s about response time and throughput time for cory execution. So throughput time can be two hours if you have really simple cory. With the large data it can take two hours. Response time is whenever there is a. The first role of first set of regent is ready to rendered. Then it should be a readable to users. That’s response time, right? A SQL server engine has the same thing. So I would show you the difference between What we have, and what we are going to release. So, I’m going back to the Mac one, which is running the released version, as of today, in the VS Code Marketplace. So, I’m going to execute it. SQL. So it’s executing. What I did was that the wait for delay in each query statement so that they kind of simulate the long running query, right? And the throughput time is over 12 seconds.

So you have to wait the entire time before you get that.

>> Right.

>> Now Take a look at the upcoming one, so I’m executing it. See, as soon as the original is coming out we are rendering it. So Ben in our team has been working on this feature and he’s in the stage two. [LAUGH] So stage three is coming out. That means, right now it is per result set. When result set is ready then we print out. In his stage three, he will do role by role.

>> Role by role,

>> Exactly, so that’s as. Another fantastic issue that we are releasing. And-

>> Okay. So if you’re doing row-by-row. This is, how do you draw that on the screen? Do you have to continuously re-draw the screen? Do you get these couple rows, then this row, then that row. Is that the way it’s gonna work?

>> We need a band. [LAUGH]

>> [LAUGH]

>> That’s where it gets you. As a PM, it’s magic.

>> Okay, I’ll be interested to see, something to look forward to.

>> [LAUGH] Sure, so that’s my demo.

>> Cool, fantastic.

>> Give me one more second. MSSQL extension is open source.

>> Yep.

>> It’s open source, we don’t even use any of the internal system to we all use the github repository project, vscode-mssql.

>> Okay.

>> And then if you go there you can see full transparency of the

>> Even though with internal discussion, it’s not internal. Everybody, every developer over came that.

>> Yeah.

>> You can join us. And then you can see all that one and we also have daily built. And we also have project plans. Even though watching this session, you can actually see what’s coming in two weeks. So basically that’s Extension one. And that everything I explained today is all here.

>> We put up the links for all those in the show notes.

>> Yes, please.

>> And don’t forget aka.ms/SQLDev. If you’re a SQL developer, this is the page that you go.

>> Very cool, thanks so much.

>> [LAUGH] Okay, thank you.

>> Very, very cool stuff.

>> Hope you enjoy that, and we will see you next time on Visual Studio Toolbox.

 

Read the video

In this episode, Robert is joined by Eric Kang, who shows the SQL Server extension for Visual Studio Code. With this extension, you can connect to SQL Server, Azure SQL Database and SQL Data Warehouses, create and manage connection profiles and most recently used connections, write T-SQL scripts with IntelliSense and Go to Definition, execute your scripts and view results in a simple to use grid, and save the result to json or csv file format and view in the editor.

Resources:

 

 


Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...
1Code.Blog - Your #1 Code Blog