How to connect Visual Studio Code to SQL Server Database and Run SQL CRUD queries latest

hello everyone today I'm going to show you how to  create a user in uh SQL Server Express and then   using that user ID you can connect to SQL Server  from Visual Studio code okay so you can see Visual   Studio code is already open uh here but before we  progress with this the prerequisition test is that   you need to have a user ID uh AOA user in SQL X7  Express okay so uh for example here uh like I have   this SQL Server Express database okay so this is  a for people who don't know so SQL Server Express   is uh is basically a development version uh you  can use initial locally uh the link is there in   the description below so you can download SQL  Server Express and then once you install it uh   you can use it for your development purpose okay  it's free by the way uh so in order to connect so   let me first disconnect these instances here uh  so for example I already have this SQL space here   and I have a user called as sa user which uses  SQL Server authentication and I can log in using   this user I also have Windows authentication so I  using that also I can log in you can see here that   able to login let's close this window uh yeah  so you can login using Windows authentication as   well as SQL Server authentication now this I have  configured when I installed SQL Express so you can   first install SQL Express and after that you need  to create a user so follow the steps to create a   user first go to SQL 2019 configuration okay so  click on yes it opens up and here uh find out   this network configuration okay and protocols for  SQL Express so here by default it will be disabled   so you need to enable it double click this this  window comes up and uh in this drop down you can   select yes and so this will be enabled second  go to IP address scroll down here you can see   this TCP Dynamic ports so whatever value is here  just if it's zero also just just make it blank and   then TCP Port give anything like I have given  1390 you can give any number okay uh like one   five zero zero one six zero any number just keep  that number and then uh uh then that's it from   your configuration point of view so click on OK  and once you do that you have to restart this   okay so right click this and restart so that's the  process that's the initial configuration once that   is done go here and here uh if you have already  enabled Windows authentication login here and   then double click uh or right click properties not  double click but just right click properties uh   and then here in security select SQL server and  windows authentication mode so ensure that this   is selected college and then once you have that  I think you don't need to do anything else let   me just check quickly uh yes so everything else is  fine you just need to select this one now click on   okay and the third step is uh you need to create  a database I I hope that you already have database   if not you can create new database by clicking on  this so you create the database and then we have   to create a database user okay so go to security  here and you can see users so right click new user   and then you provide some username login name  here so what I will do is I'll create a user   who can access multiple databases so in that case  so so that I can use it for other databases also   okay you go to this security here so instead of  install database so you can see there is security   here right so go here login right click new login  and here select SQL Server authentication and   let's let me select essay test okay give some  password uh for demo purpose I'm just giving   any password you whatever you are comfortable  with uh so just give that password and uh better   deselect this one enforce password expression  and default database I am selecting student   okay and in the server rules uh I am making this  person assist admin right this user says admin uh   so yeah so once I do that click on OK so the  user has been created let's go to student   database here the user should also appear let's  go here and let's refresh let's refresh this okay it is not appearing server role  user mapping is student right so security   that is enable permission Grant uh okay we can  give these things but let's not give this these   are these are high level permissions uh but uh  what we can do is uh I think this should be okay   so click on OK and again go here refresh okay  so now essay test is coming up right so asset   test is our new user uh let's see if so here  let's make this person uh DB owner okay so   that by default then it will it will get all the  permissions alter Grant create everything it will   get okay or you can make like if you don't want  DB owner you can just read a writer you can do   that also okay so for a simplification purpose  DB owner but from security point of view it is   not that good to make someone DB owner only  uh for the student purpose I am doing it uh   and once you have done that let's see uh  here also DB owner and so username login   name everything created by default so let's  click on OK so essay test has been created   okay now let's try to connect and see if we are  able to connect using a set test so essay test yes so I'm able to connect now so uh if you're  unable to see this let me disconnect everything   and show you once again I think it was too quick  so file correct Explorer as a test okay it is   selected as a test so you have to select SQL  Server authentication here and asset test and   then type your password and now this is coming  up right so now you are logged in as essay test   so you are able to see these tables here these  are the some dummy tables I have created just   for this tutorial Perpetual purpose you can  create these tables uh it is very simple so   you can create as many tables as you want  so like new query create create tables okay   there if you if you're new to this you can go  to w3schools uh I'll recommend that that is very   good for beginners you can go to W3 schools it has  all the all these basic uh commands you can follow   that and you can create list tables or delete  tables alter tables all these cameras are there   okay uh so so now we have these three  tables which has some some data in it so   let's close this and then go to visual studio code  Visual Studio code uh now this is the interface of   Visual Studio code you can download download it  for free it is also available uh for free for   development purpose the thing is that whatever you  want to do you have to go you have to install a   extension for it okay so go to this extensions  here and search for SQL server so click on   this SQL Server you can see SQL Server coming up  install it will take couple of minutes to install   okay now SQL Server is installed uh so close  this and then you can see these icons here so go   to SQL server and here click on ADD connection  okay your add connection you have to type the   name so this name should be uh this name here  okay so let me copy this and copy and go back   to visual studio code and then paste it here and  press enter yeah database name we already know   so student press enter you have SQL login here so  select that and here it will ask for username our   username is asset test so again enter and then  the password and again enter right it will ask   you to save the password I don't want to save the  password no profile name I will give uh student DB   and then hit enter so now it's testing you can see  here and profile created and connected so profile   is this name that comes up here okay so let's  make another you can have multiple connections   so you can identify those connections using your  profile name so uh now student TV has been created   go to tables you can see all these tables here  right so now let's run a query so select top 1000   uh if I do that then it will take a while uh it  will be little bit slower compared to SQL Server   because SQL Server directly the database is there  so to be very quick whereas Visual Studio go to   go to SQL server and then retrieves the data  so it takes a while but you can see uh these   things has been retrieved uh so for example I can  also go to let's say this table here these are   all Demi data I have just created uh so you can  create any any type of table or insert any type   of data whatever you want or whatever is needed  for your project uh so now you can see so I was   able to retrieve data from both these tables uh so  let's run a create command so close this windows   uh let's use this window and let's  create a table as well yeah so create   a table uh table name is uh let's say a student City yeah uh then bracket and give ID int  identity so which means automatically it will be   incremented no need for me to insert it in certain  data here and then ID and then City okay bear care   and 50. so that's it now let's uh execute this  so you can see this button here execute query   so it will then come up with some message  on this pane here that the table has been   created okay now you can see uh command completed  successfully so the table has been created uh it   is not appearing here because we have to refresh  it so refresh it and then print city is coming   up right now you can again run insert query  and insert the data in this uh in this table okay so now let's insert uh something so the  table uh doesn't have any data uh so to insert   something in this table we have to type a insert  command so insert into uh table name is uh   so table name is this okay DBU  Dot student uh city is not coming the T values bracket and okay so you can see that uh   so you can see that one row has been created  let's insert some other records uh jelly uh New York okay so three records  should be uh in this table now   uh right click select top 1000 and  so this should come up here now okay so now you can see the city names  are coming up here so that's it so we   have I run the select command we have run  the uh create uh create table command and   uh let's run a delete command as well  let's see if we can delete delete uh delete from right so now I have deleted all the records okay  three rows affected so if I close this let's close   this window uh windows uh and again uh run a  select command on this so now nothing should   come up or only the the column names should appear  there should not be any record in this table okay   so you can see here so on the identity nothing is  coming up because we have deleted so create table   delete table select I have shown you all these  three examples so now you can follow this and   enjoy your Visual Studio code and SQL Server okay  thanks for watching I hope you like this video   please hit the like button and subscribe to my  channel and hit the Bell icon thank you very much

Channel: tutorpraveen

Views: 5,635

Keywords: connect visual studio code to sql server, how to connect sql server with visual studio code, how to connect sql server with visual studio, how to connect visual studio code to sql server, how to connect sql server with visual studio 2022, how to connect vscode to sql server, how to run sql in vs code, sql in visual studio code, create sql user, SQL user, Database user, SQL server express, SQL express, sql express configuration, VScode 2022, vscode sql server connection

Id: K05Gpeat9kA

Length: 14min 36sec (876 seconds)

Published: Fri Nov 04 2022