 Hey, if you're like me You know that you can tell a lot about a person by the type of music they listen to Don't tell me you haven't wondered whether or not you can actually be friends with this person once you find out what their favorite band is In that vein I created this app called judge my taste a place where people can put in their favorite band and The internet can judge them Basically, it's just to demonstrate an ASP net core app that saves data to a SQL server database so I'm doing this all with visual studio code and SQL server 2017 doing it all in a Mac So there's a little bit of squirreliness with setting up a SQL server 2017, which we'll take a look at here But other than that enjoy So the first thing we need to do is scaffold our ASP net core 2.2 application So I'm just going to make a directory here called judge my taste Then I'm going to go ahead and change directories into that and I'm just going to scaffold the application by doing a dot net new Fucking type MBC and it will scaffold the application and restore all the new get packages that go with it Now to run it. I just need to type code and dot and when I hit enter This will open up visual studio code and the judge my taste app Okay, so the first time you open your judge my taste app You might see this little thing down here in the corner that says some required assets are missing Just go ahead and click yes And what that's going to do is it's going to create this via dot vs code folder for you And that dot vs code folder is going to have a launch dot json and a tasks dot json That will launch your application and launch a window And all that good stuff It's good for Easily debugging if you've never used visual studio before now you can if you never use visual studio code before You can now use vs code and just hit your f5 like you normally would and visual studio proper And it'll actually use those that launch in tasks to launch the application and then launch a browser window So when we run this we'll see we've got my this is the basic Scaffolded app for ASP net MVC 2.2. It's just got a privacy page and a home page And a little link to building web apps with ASP.net core So next thing we're going to do is we're going to go ahead and scaffold the SQL server database Okay, now so in the original article that I'm following along here The blog posts that I wrote I used Ubuntu Linux and in The screencast here. I'm using Mac OS and there's one major reason for that And that is getting SQL server setup on Ubuntu or on Windows is pretty straightforward You just download it and you install it On Mac there isn't an actual installable version. So you need to end up using a Docker container So that's the reason I wanted to do the screencast this way So I can show you the the Docker container and how to get that started. So If you don't already have Docker desktop installed, I'll go ahead and install that it's just docker.com and There's products Docker for desktop or I think it's called actually Docker desktop and just install it there It's pretty straightforward. Once you've got that installed. You should be able to do something like this Docker images now You won't see any But I've got a few of them already running here. So I'm already downloaded In this case the one you're going to want to download it is this Microsoft slash Microsoft or MS SQL server Linux 2017 latest and if you don't have that which probably won't if you just Install Docker desktop just do a Docker pull Microsoft slash MS SQL Server Linux with a colon and 2017 dash latest Okay, and that will pull it down for you Now since I already have it installed here Once you have it pulled down, there's like nine layers to it go ahead and pull it down. It's a fairly sizable as you can see there It's 1.35 gigabytes So it's fairly sizable and depending on your internet connection will take a few minutes So to run an instance of this locally you would just run a Docker run Now you can create a Docker file for this and start it all up together if you wanted to But just for demonstration purposes here, let's just run it and we'll run it detach so that we can see So that we can set a name and connect to it like we might want to now the things that I'm going to pass in here You'll see there's an a dash e here. That's for an environment variable And I want to set it to accept Eula to Y for SQL Server I also need one more environment variable called MS SQL underscore SA underscore password And then set it to something hopefully a little bit more secure than this And then I'm going to marry port 1433 to port 1433 On local host this will mean that I'll be able to go to Local host port 1433 and connect it to my SQL server Which will need for Being able to create our databases and do some basic administration tasks that we're going to need I'm going to name it SQL server one. You can name it whatever you want to I'm going to run it in detached mode And here's the image that I want to build this container from which is the Microsoft MS SQL server Linux 2017 latest That we just pulled So when I run this It's going to spit out a big long hash now That's the the hash for the container that it just started up for me. And if I do a docker ps I'll see this This container that's based on the Microsoft MS SQL server Linux 2000 latest 2017 latest Image And the command was actually in the docker file that started up just starting up SQL server It was created 10 seconds ago. It's been up for nine seconds And I've got local host port 1433 forwarded from port 1433 TCP inside the container and the name of it is SQL server one Now this will allow us to connect to it and do some administration like Creating our database for our application and creating a user for our application to use to connect to the database all right, so I've opened azure data studio here And I'm going to want to make a connection to my database It is Microsoft SQL server The server is actually local host And we're going to use a SQL login, which is sa and the password that we created earlier That's super secure password that I used The database we want to connect to is default So all of this other stuff you can leave just like it is And we'll just go ahead and connect to it Now we'll see we've got our master model msdb tempdb all the basic stuff that it creates What we're going to want is we want to want to collect click on master And then we're going to create a new query here and the new query we want is to create a database Some of the cut and paste some predefined code here for creating this database so What we're going to do is we're going to use master Then we're going to check to see if the judge of my taste database exists If it does Don't do anything if it doesn't go ahead and create a database called judge my taste Now I can just go ahead and run this guy here and it'll see that it completed successfully just took A couple thousands of a second Don't need to save that the next thing we're going to want to do is we're going to want to create a Table in that database so If I come over here to my server And the database is judge my taste will be here We could come in here and search for it But it may not refresh Like it's supposed to so that's why I come over here because I can at any time I can right click and say refresh it And then I can see my database so I'm going to go in here and right click. I want to do a new query And it's another ddl query that's going to create our favorite bands table inside of that database So we're just going to Drop the table if it does if it already exists And then recreate the table with an id. That's an identity Seated to one The name of the band The who it was entered by and when it the date that it was entered on So I can go ahead and run this And it takes just a couple of milliseconds Then the last thing we're going to want to do Is we're going to want to create a user For the application to use Instead of connecting via sa obviously So again, we'll go ahead and right click on judge my taste. We'll set up a new query And we're going to use master. We're going to create a login Called a web app with a password of this P at sign ss w zero rd with an exclamation point on it again Probably a little bit more secure something for your production apps The default database is going to be judge my taste for this web app login Then we also want to enable the login And each one of these goes in between here if you're not familiar with the sequel It's just going to go ahead and do that So alter login web app enable go Then it's going to say use judge my taste which will switch us over to the judge my taste database And then I'm going to create a user called a web app for the login web app that we just created And then I'm going to add the role Of db owner I'm going to add web app that user that we just created here So we created a user that links to the web app login Then I'm going to add that web app user that I just created using the sp add role member to the db owner Of that database since we're using judge my taste If that makes sense So go ahead and run this And it said couldn't couldn't find stored procedure db owner. That's because I had it highlighted Because I'm a moron So now we executed all those lines successfully in just a few seconds Now we'll be able to connect our application to our database Okay, now we're cooking So the next thing we need to do is go ahead and get our application connected to the database that we just created And allow our application to save data into that database. So A couple of steps here that we need to do first of all, I'm just going to order Open up a terminal window And I'm going to go ahead and add a dot net package To this now you can use your new get package manager like you normally would But with visual studio code, especially on mac and linux Found it's a whole lot easier to just use the dot net command line So I've added a package here called microsoft dot entity framework core dot sql server I'm locking the version to 2.2.4 because I know it works So we'll go ahead and add that package Then the next thing we're going to do is we're going to jump over here to our app settings dot json And we're going to add the connection string To our app settings dot json for connecting to our database So in this case the server is a local host To remember we married local host port 1433 Which is where uh sql server runs We married that to Our local host port 1433 The database is just my taste. That's the one we just created The user id is the web app the user id that you just created for the judge my taste Database and the password is the one you just created for that user Now the next thing we're going to want to do is Um You need to be able to have a model that matches that table that we just created So let's go ahead and create a new file here and we'll call it favorite bands Not in all cabs And this just has a couple of using statements and a property for each one of those Um items in the table So the id the name the entered by the entered on now I've got data annotations already up here and it says it's unnecessary and it is unnecessary for right now But it's going to help us out a little bit later So The next thing we need to do is actually create a database context so that we can create so we can connect it to the database So I like to in my application add a A folder for that So I'm just going to add a new folder called data at the root And I'm going to add my context in there. So the new file is kind of going to be called just judge my taste context Dot c s And the content of this file Is just a basic database context with a db set So we're bringing in entity framework core and the models that we just created And we're going to derive from db context pass in whatever db context options are passed to it when we create it And we're going to set up a db db set called favorite bands which matches that table name And the favorite band which comes from the model that we just created Okay, so now The last thing we need to do To connect our database Is go into our startup And in configure services We want to add one long line Right before the mvc add mvc line We're just going to create this line that says add a db context called judge my taste context We may need an import statement using statement for that And you we're going to use sql server, which we will need an import statement for that as well Um, if you don't know i'm just using the command dot To get those uh menu items to come up windows dot if you're on a windows machine Um, and then i'm going to pass some configurations to it Which this is the options that are being passed to it use sql server and then get connection string from just my taste database Inside the app settings that we just set up here. So And then the very last thing to get these two connected Is we're going to want to Generate a controller Now with regular visual studio, it's fairly easy. You can say add controller and Uh, you get a bunch of dialogue boxes But if you're on uh linux or mac and you're just using visual studio code You can get some of that that same goodness One of the first things you're going to want to do is you're going to want to install this global tool Called dot net asp net generator And here's the command to install it It's just dot net and it's a tool and you want to install it globally And it's called dot net asp net code generator. Now. I already have it installed. So I don't need to run that again Once you have it installed globally, you never need to run that again But I also do need a package For generating a controller So the net the other package that i'm going to need is The visual studio designer. So This package is the visual studio web cogeneration design Okay, so let's go ahead and add that package to our local project And then we're going to run this one long command That generates our controller and all of our views for us So Let's go ahead and paste this in and let's take a look at what it's doing. So Um It's calling the dot net runtime or the dot net cli It's saying I want to run the asp net code generator. I want to create a controller I want to name it favorite bands controller I want to use async methods in the controller. That's just best practice The model that I want you to use is the just my taste dot models dot favorite band That's the one we just created up here in models So this is a fully qualified with the namespace and everything The data context is the just just my taste dot data dot just just my taste Judge my taste context um The namespace that I want the controller to go into is controllers And the output directory that I want for it is controllers This also says use the default layout For the views and and for the for the controller So when I run this guy It should say it's going to building the project locally Then it's also going to start scaffolding out the generator or the controller and all the views So you'll see that it added a view in views called create edit details delete index And the controller that has all the actions for those So let's take a look at that controller just real quick Um, here's our basic controller. It's going to pass in the just judge my taste context Um, it's going to set it. This is all generated for me by that generator It's creating async tasks It's going to pass out the views But I've got um as well as passing out a model for those views Using a weight um, so it's using all the best practices and it's Pretty slick. Um, I really like that that part of it The other thing we're going to want to do is we're going to add, um on our main views on our layout We're going to add a menu item so that we can actually get to the favorite bands page that we Or at least the set of pivot favorite band pages that we just created So let's go ahead and add another nav item here Clean this up a little bit Okay, so we just added a nav link To favorite bands index called favorite bands That's not a big deal So now we should be able to run this thing And when it pulls it up in the window, we should be able to see our favorite bands Listing page Um in the browser So now we've got the app running. We can see this favorite bands link and we link over to it. We can see here's our Name entered by entered on we can create a new one And in this case the name of the band is going to be foo fighters entered by me and entered on this specific date And this specific time Now we've got one here Now as you can see first of all entered by People can put whatever they want in there, right? That's not ideal for us and entering the entered on date time We don't really want to do that and right now Anybody can go and edit anybody else's Favorite band And really the whole point of this is so you can judge people harshly by the By the crappy bands that they listen to So let's not do that. Let's um, let's clean this up a little bit We'll add some authentication to it and we'll get some things automatically entered for us To make it super easy for people to enter their crappy bands and make it easier for us to judge them All right So the first thing we're going to do is because it's super easy to do And we don't need to have any other setup to do We're going to get the entered on date automatically entered for us So the first thing we're going to want to do is find the create method We're going to remove entered on That's being bound to the favorite band object when it's coming in So we're going to move that from the model binder and we're just going to add it right above here Where it says if model state is valid we'll just take favorite band Dot entered on and we'll make it equal to date time Dot now The other thing we need to do is go into that view So if we go into the favorite bands create view We don't need that The entered on section anymore, okay So Now we should be able to run this thing and not have to enter the entered on date For us we can actually Have that entered On our behalf based on when we actually entered it So that gets rid of that cumbersome step The next thing we're going to do is we're going to add some authentication And actually get the entered by entered for us from the authentication framework All right, so now we want to add authentication to our application so that we can get The users who are actually entering their favorite bands Without them being able to enter other people's stuff So we're going to use octa obviously because I work at octa and this is the octa channel. So this is What we're going to use If you don't already have an account you can just go right here to developer dot octa.com Click on the sign up button. It's just that simple email first name last name company name. That's it Then you got a free account Once you get logged in you should see something that looks like this What we're going to do is we're going to create an application And the first application we're going to create is the just my taste application Now this is a just a regular web app and it'll say right there dot net java, etc They also have we also have single page apps iOS Android native apps And machine to machine. So apis talking to other apis We're just going to go ahead and choose the dot net web app here And we're going to call it Judge my taste And we know it's running on port 5001 It's also running on HTTPS So we'll need to change these two Just the first parts of these To go to localhost 5001 Authentication code Is what you want to use So that's the auth code flow for web applications. Just go ahead and click done But one of the other things you're really going to want to do is right after you click done It'll be taken to the you'll be taken to the general settings screen Go ahead and click on edit again and we're going to add a logout uri Just click on add uri here And you want it to be the same thing HTTPS localhost colon 5001 And the end point is going to be called sign out callback Because we're going to use the octa SDK for net and this is the route that it handles for us When the application redirects back when octa redirects it back to our application after sign out So just go ahead and hit save and down here at the bottom You'll see a client id client secret and we're going to go ahead and plug those in to our We're going to go ahead and plug those into our app settings.json file Okay, so before We go any further Let's go ahead and actually install a package here And the package is the ASP net core dot net SDK package So it's just called octa dot ASP net core. We're going to lock the version to 1.15 And let's go ahead and install that real quick And then right below that Database connection that we just set up earlier Let's go ahead and set up The octa configuration It's pretty simple We want our client id which we can pull from our client id app settings And our client secret which we can also pull from our client app settings And the octa domain which you'll get from your dashboard Right at the top of the screen And it'll be something like Dev dash da da da Bunch of numbers Dot octa dot com and we'll leave this at local host 5001 of course So now we're all set up To use octa for authentication But we need to let our application know how to use that So we'll go into our startup dot cs file and we're going to add a couple of usings So the using statements we're going to need are this ASP net core That we just brought in and the microsoft ASP net core authentication cookies So that it can use cookies for the authentication once it gets The authentication back from octa So at the very beginning of the configure services method Let's go and add a little section here Before the cookie policy So we're going to set some octa mvc options like we're going to pull the octa setting from the configuration We're going to set the scope to open id profile and email That means we're going to be using open id connect We're going to want the user's profile and the user's email as Um in the id token As some of their claims and we're also going to get those claims from the user info endpoint The next thing we're going to do is we're going to set up some options For the authentication one, we're going to use The regular default authentication scheme scheme for cookies And we're going to use the octa default from the octa authentication schemes We're going to add cookies and we're going to add the octa mvc and pass those options that we created up here We're going to pass those into the octa mvc Now the very last thing we need to do Now that we've configured it we've configured that service We need to go down here into the configuration for the app And say to use that So use authentication Means it will actually be using this authentication that we just added configuration for Now we're going to go into our controllers folder And we're going to add a new controller Called account controller And the code for our account controller is pretty simple But let's walk through it piece by piece So we brought in our cookie authentication again Our asp net core mvc and we brought in the octa asp net core So we've got our account controller And it's based off controller and it has a login function That all it really does is it runs a challenge and it pulls the octa defaults And sends it to the challenge method the challenge just says challenge. However, this Method says to challenge them and for the log out This is where it's going to end up back at is the account controller log out And it's going to take us it's going to Remove those default cookies and go back to octa to log them out So these are the two methods that we're going to use to log in and log out Now in our view is shared We're going to create another View here just a partial and we'll call it our login partial That's cshtml All right, so our login partial Is just going to be a chunk that we can Spit in any place we want it for the login So Really, I'm just going to add some extra nav bar stuff here And we've had our login and log out They're going to use those actions that we just created on the account controller Again, our account controller login action Just runs the challenge Which is going to actually redirect us to octa to log in and log out Now in our shared layout, that's the last thing we really need to do Is we need to make sure That we have the The login partial there So right below the favorite bands Actually, there's a couple of things we need to do to this So in here One of the things we're going to want to do here is this nav bar collapse right here We're going to want to change that out just a little bit So that we can push the login menu to the right hand side And keep everything Else on the left hand side So let's go ahead and replace this whole div section And then we'll talk about what we changed Okay So what did I change in here? I made sure that I didn't put to the left, but I justified the content between Which is going to push the other one out to the right And if I look at my login partial It should be ml auto which is move left auto Make sure we save all these files And everything should be in there that we need now So don't just sit there. Let's fire this thing up and see what it looks like All right with the application running you should now see a login button on the right hand side here So when you hit login menu it redirects you to octa to log in Once you've entered your password Um comes back and says hello lee britt now I can uh Enter stuff this one was entered by me But now I just need to update that form So the form actually gets the user that's logged in and gives the entered by to the user Now back in our application Let's go ahead and tell our controller to get the enter by entered by value From the local user's name So all we have to do is go back to our create method Remove the entered by from the binding just like we did with the date time now And just do favorite band dot entered by Equals user dot identity Dot name That's going to get the user's name In the entered by value The other thing we're going to want to do is we're going to go back to the uh Create view We're going to remove that entered by section And the only thing they need to put in now Is the name of the band that they that they love Right So there's only a few more little cleanup things that we need to do Because right now people can still edit other people's favorite bands So we need to put in a little bit more security To make sure that nobody can edit your band, but you Okay, so the last bit of cleanup we're going to do is going to first of all make sure that people are logged in Then we're going to also make sure that they can't edit a favorite band of somebody else So one of the first things we're going to do Since we're getting the entered by name on the create. Let's go ahead and add our authorize Attribute And you may need to use a statement Now we can guarantee that that's going to work Then in here, we'll do the same thing Authorize attribute on there And we're going to pull the entered by an entered on out And the first thing I'm going to do is I'm going to get the existing band the one that's in the database already So I'm going to go ahead and get by id I want to find the one that's saved in the database Currently get it by its id Then I'm going to check to see if the existing bands Entered by Is not equal to the user dot identity Dot name In other words, the person who originally entered it is not the one that's logged in right now Then we're just going to return it unauthorized You're not authorized to change this person's Otherwise what we're going to do is do We're going to set the existing band's name equal to the one that just came in favorite band dot name And then we're going to save the existing band. We're going to update the existing band And then just save those changes back to the database All right, and we'll do the same thing down below with delete We want to make sure that Before they can delete one that they are logged in and Before we delete them. We want to make sure That the band that we have is the one that we're trying to The one they're trying to delete is actually one that they own Okay, so We've got uh context favorite band They're going to find that person that band Then If that band does not belong to the currently logged in user if favorite band dot entered by Is not equal to the currently logged in user Then do the same thing return unauthorized You can't delete that person's favorite band No matter how much you might want to When you find out somebody loves nickel back and you just really want to delete it You just shouldn't be able to Okay, so a couple more things we're going to need to do here. Okay A couple more things we need to do here We need to go into the index and you'll see that on the index it's got these edit and delete here We're going to want to surround those in Um in these if statements, so we moved details up Above it and we put a span around those bars So if the user is authenticated And the item that they were getting ready to display was entered by the currently logged in user Then show them the edit and delete button. This way it won't even show them the edit or delete button Unless they're actually logged in And they own that favorite band Then they can do that and same thing with the details the details just has an edit and a delete in there um Just go ahead and wrap while it only has an edit I added the delete because it would be nice to be able to delete it from there But if the again if the users logged in and they own this record Then they can edit or delete it Otherwise they can't And the last thing that I did do that I should have done earlier Is I went through and I removed the entered by and the entered on from this form when it scaffolded it out it put those in there and um, we don't need those anymore And honestly you wouldn't need them in the entered by and entered on anyway for the edit Because those things aren't going to change those are entered by values. So we don't want to change those So now we should be able to run this thing And only be able to edit our records and only be able to Delete our records All right with our application fired up for the last time We are logged in because I'm still logged into octa. So When I log out it will actually log me out of octa. So when I go to log in again It'll reprompt me for my username and password Now when I come over here to favorite bands There is favorite bands in here, but I don't see the edit or delete here because It's the favorite of me. It was entered by somebody named me, which that's not who's logged in currently So if I go to create a new one, here's my band artist name um, and we'll say Who's my favorite band this week? Five finger death punch We'll go ahead and create create now five finger death punch is created is a favorite of lee brant It's created then and the details Edit and delete are there Hey, thanks for joining me with this, uh, pretty long screencast Um, I hope you enjoyed it if you did make sure you click the subscribe button down below And hit the little bell so you get notified when new content comes out for market developers. Happy cody