Setup MS SQL in ASP.NET Core with Entity Framework

hello and welcome to this video where we're going to set up MSS Krill with NCC framework inside a.net project so it really doesn't matter if it's an asp.net core web application or if it's an API that you're creating it can even be a Blazer server app because as long as you use Entity framework you will be able to set up the MS SQL database just the way that we do it in this video so first of all I want to go and open Microsoft SQL Server management studio and this is simply just to create a database that we can start with so let's just connect to the database and inside our databases we want to right click and say new database and let's go and call this database for I could say test two I already just have a test so let's go and say Test 2 and say okay and when that's done we actually don't have to do anything else because we will use Entity framework to create the tables so I want to go and open Visual Studio and in here I want to say create a new project and as I told you before it doesn't matter if you choose ASP done it called weapon or a Blazer server app or a asp.net core web API I even think you can go with a console you just need to use Entity framework so I'll just go and take a Blazer serum app and say next and let's call this for Ms SQL app and say next and then here we can go with the.net6 version or the Thunder 7. I will just go with 2007. if you're going with net 6 or another version then you just have to install the correct Entity framework packages but I'll show you that when we come to the nougat packets section I'll just go with the.net7 and then we say create so now that we created the project we want to go and right click the project name say manage nougat packages and we want to go to browse and we want to type in Entity framework call so I do believe that we have three packages that we want to install first of all let's just go and install the base package for Entity framework call and in my case you can see I use the.net7 so I just want to use the new seventh version if you use.net6 go and choose the newest 6 version and again if you're using the.net 5 go and install the newest 5 version and so on so when we have choose the version we want to say install and say I accept and there's actually two more I want to install the first one is this Entity framework call Dot SQL Server as you can see this is the Microsoft SQL Server database provider for Entity framework code so it's a very important nougat package for Entity framework when we want to create a connection to a Ms SQL database and again we just want to go and choose the right package so in my case it's still the latest stable which is 7.0.4 I want to say install then I want to go say I accept and the last package I want to install is this Entity framework called the tools and what this allows us to do is that we can go and add a migration so when we create a model in our project it could be an employee that have a name and an ID and maybe a title we want to go and create that later on here then we can add the migrations by using this tools and we can also go and update the database so again go and install the right version for you and say install again I accept and then the next thing we want to do is to go to the app settings.json file because it's in here that we want to include our connection string and the way we do it is to add this comma after the allowed hosts so that we're able to insert a new attribute called connection strings and it's actually pretty important that we call it connection strings like this because we're going to use something called an interface that is called eye configuration to actually go and fetch this database connection and when we use that we can just say that we want to go and get the connection strings so in that case it would specifically go and look for this connection strings inside our app settings.json file then inside this connection strings we can add multiple connection strings if you have but in this case we just want to add a database connection and when you have the database connection you want to give it a value which is our connection string so we want to specify a server and in my case this is my server name the easiest way to copy and paste it into this server string here is to go and open your SQL Server management studio and if you already have it open then close it and open it again so you come to this connect to server page because in here you have the server name that you can just go and copy and go to the project again and then paste it in then the next thing we want to use is the database where we're going to say that our database was called test2 and then in this case because we are using Ms SQL locally we can just go and say that we have a trusted connection we want to set that to true and then we also trust the server certificate so also set that to true and that should actually be it for the connection string when you have to connect to a local Ms SQL database so what we can do now is to go into our data folder as you can C by default we have these weather forecasts if you have chosen a place of server airport for now just go and ignore them you will go and right click on the data folder say add and then add a new item and in this case we can just go and call it app DB context and then say add so this is going to be our DB context file that we can use everywhere in our application to use Entity framework to manipulate the MS SQL database and the first thing we want to do in here is to extend this class so we want to say DB context and if you get this red underline we just want to hover over it and we can say Alt Enter and then say using Microsoft dot Entity framework call so now our application know that this is a DB context class that we're going to create and that we want to use Entity framework and what the DB context is doing is just to give us all the functionality that Entity framework provides to us and the next thing we want to do inside the appdb context is to override our unconfiguring method so the reason we are overriding it is because that there is already a on configuring inside the DB context but it is intended that we're going to overwrite this on configuring so that we can connect to a database it only takes one parameter and it's this DB context options Builder and we can Target that by saying options Builder so let's go and copy this and paste it into the method because we want to use this and what we want to say is we want to say that and you can see it already say use SQL server and that's the one we're going to use to connect to our MS SQL server and I can see that there is actually an error here because we need to close the class off so just go and close it like this so you're sure that you have all the curly brackets in your file but we have to take a look at this use SQL Server because the only parameter that we're going to feed it is actually the connection string so what we want to do now is to dependency inject the eye configuring interface into our appdb context class so we have to create a Constructor so what we want to do is to say that we have a public and in this case it's a Methodist but we want to call it the same as the class so we could actually just go and copy and paste this app DB context and then make it a method then we want to go and include one parameter which is our eye configuration we could just go and call this for config and as with a lowercase c not that it matters but as you can see we also have lowercase on our other variables so when we dependency inject we can go and create a property and this property will be the eye configuration and we can also go and say config but then just say underscore because when we do it like this we can use this whole property inside the whole class actually we have to take it inside the class so just clip it out and put it inside the appdb context so when your dependency inject you want to have a property outside of the Constructor so that we can go and say that the underscore config should be equal to the config and by saying this we are actually able to use this underscore config inside our unconfiguring method because as you can see this config is only available inside this method and that's basically how your dependency injects so the next thing we can do is to take this underscore config put it inside our use SQL server and then say dot get connection string and this was actually the method I was talking about get connection string as you can see it's shorthand forget section and then say connection strings so it specifically look at connection strings inside our app settings but we can just write it like this and then we only want to specify the connection string that we want to connect to so we can go to the app settings.json file take the database connection and paste it in here so now the appdb context should be set up with a valid connection to our MS SQL database and the next thing we should be looking at now is to create a model and in this example I will just use an employee model so when you create a model inside our application we can actually go and model this model as we want we can just go and say that we have an employee and we have an ID a name and a title and then we can actually go and say that Entity framework should create this model as a table in our database so we will start by going to the data folder right click on it say add and then add a new item and this item we can go and call that employee and then just say add so the only thing we have to do in here is to actually go and say prop and then you can hit tab twice because then you will create a property then you can just go and say tab again because we want this to be an integer but we want to call this for ID we can then go and do the same again say prop and then the string hit tab again and we want to give the employee a name and finally we can just go and say Ctrl D to duplicate this line because we still want to make a string but in this case we just want to make a title to the employee so now we can go and save this file and then inside our app DB context we actually have to also say that this should be handled as a table and this is the syntax to say that you want to make a public property in here which should be the type of DB set and then we want to give it the entity that we want to set as a table which is the employee in this case and then we want to give it a name so this is going to be the table's name and that's why I call it employees with an S because you can have multiple employees in here you can also go and just not take the S with you it's really all up to what you prefer but when you have done this then we are actually able to go to our package manager console and test if everything is working and we test it by saying to Entity Framework work that we want to create this table inside our MS SQL database so we want to say add and then say migration and you have to remember this is inside our package manager console and if you don't have it in all these tabs down here then you can go to the search bar up here and then just type package manager console and then you can see it right here and click it but we say add migration and in this case we want to add employees table and then we can hit enter and if this succeeded then you will get this migration file and basically what the migrations file is doing is that you have two methods in here you have a op method and if you scroll down you also have eight down method so right now there have not been any changes to the database because when we say add migration it just created this migration file for us and it also when you do it the first time add this migrations folder where you can see the actual migration so this one is actually the file we have open right now and when you migrate for the first time you will also get a Entity framework migrations history table inside your database and basically what that is doing is that it will keep track of the migrations that you have added to the database and the one you have rolled back and in our case we have our up method here that will be run if we actually go and migrate this but if we later on go and roll it back it will just go and execute this down method and as you can see the opposite of creating the table employees will be to go and drop the table but we can go and check the migration file here we can see that it want to create a table called employee it then want to add some columns and it will go and add an ID a name and a title and of course that is all information it gets from our employee model that we created and it's actually so smart that it knows that the ID if you just write ID then it knows that this is the identity column and down here it will actually go and create a primary key e for the ID also and then because we said that name and title Should Be Strings it will just go and make them in Bacha and put it to Max so if everything looks okay you can go to the package manager console again and then we just want to say update database and again this succeeded it says applying migration and then the migration file that we just created and then it says that it's done so now we should have created the database inside our MS SQL Server so let's go and open SQL Server management studio and let's just connect to the server we then want to open the databases and we have our test2 database so go and expand that also expand the tables and now you can see we have an employee table and we also have the Entity framework migrations history so we can go and right click the employees table and save the sign and you can see we have our ID our name and our title I do want to go and expand this video a little further to show you how you inside Blazer and it's actually more specific to Blazer but but what I want to show in here is that we can go and then right click the employees say edit top 200 rows then I'll just add some data and then we're going to display it inside the Blazer server app so if you just want to spend two minutes on how to see how to display data in a Blazer server app then just hang on so now I added some data to the employees table we have John who is a manager James who is a driver Harry who is a programmer and they all have a specific ID so with blazer it's actually pretty simple to go and display this if we go back to visual studio and inside our application we can first of all go to the program.cs file and this is because we want to add a scope to the appdb context when we do this we can just go and dependency and check the appdb context into a Blazer component so first of all let's go and say Builder and then services and then we want to say dot add to go and then we want to give it our app DB context so the next thing we can do is go to our Pages go to the index.racer page so this is actually a laser component we can just go and delete the content that we have in here because what we are going to do as you can see this is the front page and we just call it index that's okay but what we can do is to go and add a code section and then what we almost always are going to do is going to say on initialized async because inside this method we're going to execute the code that will be run when the front page here is going to load for the first time so basically it just run this method every time we hit the front page and what we can do in here is that we can above the code section we can now go and say inject and then we actually just want to inject our app DB context and then we can give it a name and let's say that this is our DB context so as you can see it's not very heavy about this right now but if we just hover over and we say Alt Enter we can say that we want to go and use our project name and then the data folder because then it knows where the appdb context is so simply just go and use the folder so inside our code we can go and create a variable let's just go and create a list of employees and we could just go and call this for employees so this variable is just going to contain whatever we're going to fetch from our DB context so we can go and say employees should be equal to and then we want to await a call to the database via the DB context then say dot employees and because we created a list we can just go and say two lists and then we want to say async because we're doing this in a async way and as you can see it doesn't like this also so we could just go and say Alt Enter and say that we also want to use Entity framework call so that's actually it that's all the code behind that we have so now we can just go and display it on the website so I'll just go and say that we want to have a for each so that we can Loop through the employees and then of course what are we going to for each we're going to say that we have a variable called EMP for employee and we want to go and loop through the list called employees we can then say that we want a paragraph with the name so we just say EMP and then we say that and then the name of the employee just go and copy and paste this line and say that we also want to display the title and then we want to have some breaks every time we display a new employee of course you maybe want to make a table for this but this was just to keep it simple so if I don't forget anything right now we could just go and run this and of course we get this error that employees was no so let's just stop the application again and that is because we just want to make a new instance of this list employees so that is not null it's just an empty list in the beginning so we just want to say new and then say list and it should be a list of employees so let's try again and now you can see we get displayed the name and the title of every employee that we have in our database and if you want to learn more about Blazer then go ahead and check the other videos I have at the current moment I have some videos about how you display real-time data with blazer so when you actually go and update the database let's say that you changed John to John 2 then it will actually instantly go and also changes in the UI and that's actually created by a class called SQL table dependency and also using signalr and you also have to enable what is called a service broker inside the database I do also have some videos about how you create a blog also in Blazer and also how to create a chatbot with chat DBT but if you like this video please go and give it a thumbs up also go and subscribe to my channel if you want to learn more about Blazer and generally the asp.net core let's just have a nice day bye

Channel: ZetBit

Views: 4,670

Keywords: Setup MS SQL in ASP.NET Core, ASP.NET core, dot net core, asp.net core, .net 7, entity framework, connect to database asp.net core, connect to database blazor, blazor ms sql, mssql, setup database connection, database connection

Id: eep2l5jfs5c

Length: 18min 49sec (1129 seconds)

Published: Mon Apr 03 2023