Well hello there folks. Today I wanted to post about something that has been rather helpful to me over the past couple of weeks with regards to working in Microsoft SQL and Visual Studio. If you didn’t know Microsoft released a really robust code editing software titled VS Code which can be found by clicking this link. During day to day task I routinely use VS Code because it’s light weight and it’s really functional. Recently I’ve been using it to run scripts against databases without needing to open up SSMS and connect to the server.
So I wanted to share with you some basics of getting the ability to connect to your SQL instance inside of code, then some examples of running SQL statements against the database. For this instance I will be using the TWO database (Fabrikam) for Microsoft Dynamics GP.
When you have downloaded and installed VS Code open the application up to a blank workspace. Here I’ll have you hit CTRL-SHIFT-X which will open up a small window on the left showing different extensions you can download and install.
In the search bar type in mssql and you should return a single result as the image below shows. Proceed to click on the green install button.
Once the install has completed you will need to reload the editor. Simply click on the reload button and VS Code will do this automatically.
The window will reload and be blank.
So now that we have the SQL extension installed, lets run some scripts! To stat this process you can hit CTRL-SHIFT-P to bring up the command line for VS Code. Here we will simply type out SQL and you will then be presented with the options for SQL.
First thing to do is get connected to our SQL instance and database. The application when you choose to create connection will have you change the language type to SQL and then run you thru a small set of steps to get connected.
1) We enter the server name
2) Next we will enter the name of the database to interact with. I typed in TWO
3) You will then be prompted for the type of security credentials to use. This is all personal preference so I choose to use SQL.
4) Enter the user name to connect with.
5) Enter the password.
6) You will be prompted to save the password. I choose not to. I never save credentials simply for the fact if my device is stolen I’ve got that thin layer of security to buffer me.
7) Last you will be asked to enter a save name for the connection profile. I typed in my laptop name and continued on.
A few seconds later you should see a message stating you are connected similar to that below.
The next thing I do is close the output tab at the bottom of the screen.
So now the good stuff. Start typing out a simply query and as you begin to type you start to see the Inteli Sense start to produce information about the database for you. In my example a SELECT * FROM RM00 presented me the RM tables.
Once you have your table selected simply highlight the query, right click and choose to execute it. The results of the query will be shown on the right hand side of your screen along with the execution time and amount of rows being returned from the query. Check out the screen shots below for some additional demo code.