C# Web Application Activity 2b-2 Login Form and database users table

[Music] hi in this video we're going to create a addition to the last tutorial so in the last tutorial we created a login screen where we will we were able to log in bill gates as our only user and then what we're going to do in this app is we are going to have a multiple user list and then the next step is to create a database where we can look up the users and so we're going to extend the last tutorial so if you haven't seen the last tutorial go check it out and then come right back so let's go to the application that we're dealing with here and make some modifications so as you can recall from the previous version we have a single user that is coded into our controller that says process login will only accept bill gates as his username so i'm going to create another class and that class will contain a list of users that are able to log in and then we'll it'll tell us if one of those items was chosen successfully so we're going to add a new folder to our project so let's go to the right and choose the right click on the project name and choose add new folder and we'll call this folder services inside that folder we'll right-click again and we'll choose add a new item and this time we're going to create a class and call it security service so the job of security service is to authenticate a user so the first thing i need to do is create a list of what i'm going to call known users so in c sharp the data type is list and then in the brackets you tell it what kind of list you're making which is in our case a user model and so we'll call it known users and then make a new list which will be empty to begin with and you can see that i have to import the class so that it knows what a user model is next i want to build a constructor for my class and so the shortcut for this in c sharp in visual studio is ctor and you press tab a couple times and you get yourself a constructor so we're going to add an item to the known users list so we'll choose known users.add and then in parentheses we're going to put a new constructor so a new user model and then you specify the properties here inside of the curly brackets so for this guy it's going to be user id 0 of bill gates and his password is big bucks so now i'm going to create three more users so we'll do a copy paste for bill gates and then i'm going to change their username and password to something more appropriate so let's change marie curry let's do radioactive and watson crick with dna and alexander fleming with penicillin lastly let's all give them a unique id number so instead of zero for everybody we'll do one two and three now i'm going to create a single method here for finding out if this is a valid user login so i'm going to make the the function as boolean return type and the function name is is valid we'll accept a user object now i want to return a object or i want to return a value of true if this user is found in the list so how do you make a is found in the list kind of a function so one way to make this happen is with the c sharp keyword any so if i have a list called known users then i can use the properties of a list called any in parentheses we run a function that says i'm going to check for each that's what the function really is for each item in the list and we're going to use the variable x as a placeholder so for each item x in the list is there a match so does x dot username equal user.username so remember user is the parameter that was passed to it and then of course we have to match the password as well so x.password equals user.password if there is any match then we have a success found and so i can return that as a value so i don't have to assign it to a value called true or false it's automatically returned there that function any is a boolean return and so we should have a valid login checker now with is valid so now let's return into the login controller now i'm going to erase the part that says check for one username so i'll just delete that if statement now what i'm interested in in using is this security service so i'm going to make a new instance of that security service so we have to type the type name security service with a capital s and then create it as a new instance and you notice that it doesn't like it or it doesn't recognize it so i have to import or use the using statement now when i'm done here i have a new security service which should be able to validate a username and password so if i want to take advantage of that new service all i have to do is to put in the if statement to say security service dot is valid and it accepts a user object to tell me if it is found in the list so right now we have a list of is it five different users or four where we can check to see if any one of those is a valid user in the next step after this we are going to create a connection to a database which would be obviously the preferred method but right now let's test this out and see if any one of those four values work so i'm going to bring up the security service so i can see these names run the application and then try a few of them to see if they actually work so i got the application launched let's get to the login screen so type in login in the url so i'm going to try bill gates and use big bucks and it says i have a success so let's go back to the list let's try another one i see i spelled watston or something let's check the spelling on that one but if i put it in exactly as it has in the service i get a success let's try somebody else i'm going to try my own name and let's try some junk here and see what happens and it says doesn't work okay so we've got ourselves the hard-coded values pretty much here we've got a list of them which is slightly better but still the next step is to create a database and then integrate the search function for any users in the database so we're going to replace this security service with hard-coded values and they're going to change that out for a lookup in the database so this will be good introduction to you on how to use databases at least sql databases with this asp.net so i need to go find the database terminal here so go into your view menu and slide down until you come to sql server object explorer and so fortunately for you there is a sql server built in with visual studio so you don't have to install anything you could connect to other things such as postgres or mysql but since it's already built in we're going to use the microsoft sql server i'm going to open up the sql server tab and the first item that is a local database and open up the folder called databases and you should see it mostly empty here now i'm going to create a new database so i right click here and choose add new database and so you can name this thing anything you want i'm going to call mine the test database since really this is all we're doing is testing it out so let's click ok now there's a new item called test and we should be able to find that there are folders or at least supposed to be tables in our new database so nothing exists here yet so let's make a new one right click again and choose add new table so what you'll get next is a table design screen at the bottom of this item you can see that there is a script sql statements that is going to define what our table is and then up here on the top you should be able to eventually see a definer where you can put in the actual items here so for our users table we're going to have a username and let's see press tab and i'm going to change this to the maximum length of 40. so each username can be up to 40 characters long and then the next thing for our users table is a password so we'll keep this pretty simple we're just going to have user names and passwords as our items for our table now you think you're done but there's one more item the id is of course a key item it is a primary key and we also need to make sure that it increments properly and by default you'll just get a bunch of errors if you try to create new ones and it'll say invalid because you can't have duplicate id numbers well obviously we want them to be auto increment it'd be nice if there were a check mark somewhere that says auto increment but to find it you have to go down into the properties area and let's see if we can find it i think it's called identity specification there it is is identity is set to false i'm going to double click on that and it changes to true and it says here the increment is set to 1 and the identity seed is set to 1. so that means start at value one and increment by one each time and let's see if that that works now so what did it do for our code here it's it added the word identity and hopefully that works well enough for us so i'm going to close this or at least save it i'm going to now choose the update command so above the table definer we can choose update and it's going to tell us what we're about ready to do and choose update the database and down at the bottom i have all green check marks it looks like we're working so we have a new table up here in the tree branch on the rights or the left side so let's see if we can find the data in here so i'm going to right click on the table and choose view data and what do we get absolutely nothing we have nothing in the table yet so we want to create a new bunch of people in there so i'm going to right click on the item that says table test or the the database test and now i can write sql statements so for instance if i wanted to find everybody i could do select star from and then i type in dbo dot users oh it says table i forgot to rename this table it's just called table and let's let's go fix that now so right click on the table and let's choose view designer again and down here where it says create table dbo table let's rename that as users that's more like it and let's update it again and let's see if this will work choose update the database again a second time and now i have two items i have dbo table and then dbo users well obviously i meant to choose users so i'm going to delete the table so right click and choose delete and let's see if i can update that successfully and good it's gone all right so now i'm going back to the query tab here and let's see if this will work again so i'm going to type select star from dbo users it should be okay and let's click the green arrow for run and i'm getting a success at least there's nobody there so let's let's put some people in there i'm going to add a new line and let's put in some data so i'm going to make up a user that i'm going to put into the table so insert into dbo users is my command parentheses you have to put in the column names that you're going to insert so i'm going to choose username and password and i don't have to use the id because i set that as auto increment and then for the values i put in a name so i'm going to call the first guy max and his password is password exclamation mark and i'm going to retain the select statement at the bottom so i can see if there are any actual updates so let's click the green arrow and you can see at the bottom that max and password are correct let's put in another user so i'm going to replace max let's put in jenny and try it again and now we have two let's just try a few others let's try howard and we got three users so it appears that our users are being updated and the id numbers are being incremented and we can select them all so we've got users all right so now we have a functioning database with a users table now we can go back into our application our login application and we can access this data using sql select statements so we're gonna have to write some more code to make this happen one thing that we're going to need i know we're going to need is the property of our database so let's go to the test database and click on the icon down in the properties i'm going to find the area called connection string so double click in the connection string to select it right click it and copy it we're going to need that so the connection string is our way of connecting our c-sharp code to this database server even though they're both appearing in the same window the database server and the visual studio application are completely separate these could be on different computers even so that connection string is the bridge between them so let's open up our code again and let's go into the services folder and find security services so this here is going to be modified to talk to the database now i'm not going to put all this security service code together in one big file i'm going to separate them so in services i'm going to make another file so let's go to add a new file or new item and i'm going to call this thing a user's dao so users dao and i'm going to use dio's capital letters so a dao is a common database uh design pattern when you work with a bunch of data so again they call it user's dao you could call it security data dao as well the name is more reflective with users i believe and the first thing i'm going to do is keep this i've got this string that's been copied to the clipboard and i want to remember that so i'm going to call this thing connection string and then i'm going to paste it so i get the connection string and you can see if you wanted to parse through all those details of what the server is and all the rest of the items but we need to make it into a string so i'm going to put quotations at the beginning and the end and then a semicolon we still have probably some issues i think you need to have an at symbol because if there's any slash marks in there then this will treat it as a as a literal and so now we have a connection string so i'm going to create a method that's going to do our searching for us and i'm going to give it a very descriptive title i'll call it find user by name and password and then the parameter or the the item that's passed into it is a user object so you can see i have a bunch of angry underlines we need to fix so first of all let's attack the user model and it says here you can probably use the first item called using the models folder so that adds an item up on line one and then the underline goes away now why doesn't it like this is because we haven't returned any values in our function yet and so that will stay red until we actually return a true value so that is what we're what we're going for that's going to take a bunch of code to do that we're going to connect to the database and then do a query on it so the first job is to create a string that is going to be our selector so i'm going to create a string called sql statement now the sql statement is select everything from the table called users table and i want to match the user's name equal to something and the password equal to something else now i'm going to use prepared statements here and so we'll define what these placeholders are in a later line for right now we'll just say at username and at password so that will have to be defined in about three or four lines down the next item i need to do is actually make a connection to the database the statement i'm looking for is called sql connection and it is an object that is defined in another library so i will pass the connection string along to it but you see that there are still some underlying errors so let's see if we can import the correct items so let's hover over sql connection show potential fixes and this time instead of a using statement i don't have that option i have a bunch of other false suggestions down at the bottom though there is a true suggestion that says you have a package a package is something that is not installed in your application yet but can be and if you install it you will be able to add the sql client so the sql client is the piece of software that communicates with the database so let's go ahead and select install the package and find and select the latest version and let's see what happens so all of the code generation is finished and what i see at the top is line four we have a new sql client and the error message went away so what i want to do now is use a statement called using and that allows us to generate a block of code that will use this connection and automatically terminate the connection after the block is finished so this keeps the database connection closed in case we need to use it with other applications now to issue a command to the database we have something called a sql command so this is also a predefined object so the command is going to be a constructor with two parameters it's going to say tell me the sql statement that you want to send and then the connection that's been connected to so we have two predefined items one called connection string and the other one called sql statement so that should issue a command now we're going to go back to the parameters that were created in line 18. so i told you that this is a prepared statement so the parameter needs to be defined here with the statement we put in the string at username to let us know which parameter we're changing and then we have to go tell it what kind of a variable this is this is a varchar as you remember from the database setup so to find that we go to systemdata.sqldatadb type and fortunately those are all things that are type ahead help for you the length of the item is 40 characters and then we we set the value so the value is going to be defined as what came in in the user object user dot username so pause the video here and take a look at where all these things come from and then we're going to continue on with the second parameter so the second parameter is like the first one so we're going to copy and paste our first parameter and just modify it so instead of username of course the second one was password and it was also 40 characters and then the parameter is changed to password so now we have two different parameters that are now defined in our prepared statement so now we're going to have a tribe catch block so there's a possibility that this will work and of course a possibility that it will not work so try catch is a good way to do this so when we do a try catch we're going to prevent errors from crashing our application let's go to the try section the first thing we're going to add is called connection.open so we're going to initiate a connection to the database we're going to then define a new object called sql reader sql data reader and it is going to issue it is going to come from the command dot execute reader so it is a obviously this is a predefined set of statements i would never invent these on my own if i didn't look them up in the documentation now if the reader has rows then we know that we got something back from the database and so we have a success now we should be able to define a boolean variable as a success or failure so i'm going to go to the very top of our function here and define a boolean and let's go with success and make it initially set to false so that way we assume that there's no connections if there are rows then we're going to set this to true down below so now the last thing is to handle any exceptions so in the catch part of our try catch statement let's put in a parameter and the expected item that is put there is an exception so we can name the exception anything you like i'll call it e and if it does have a problem then let's put a console.log message or console.writeline message so cw tab tab will give you the console rate and then finally our result we need to produce a return statement so it's going to be returned as success so success is a by default set to failure or false and if there are any rows found success is switched to true so when we return success we are returning a true or a false value so one might ask where in the world am i going to remember all of this stuff about connections and sql execute and try catch and all of that well fortunately you don't have to remember it you can just look it up in the docs and so the documentations here are on the sql command property and let's look down at our example so you should see something that looks an awful lot like what i just typed here so you can see the pattern here is i have a sql command followed by a using statement and then a connection we have some parameters here that are added and then finally we have a try catch at the bottom so all i did to create this part of the tutorial was look in the documentation and adjust it for this particular user object that we were working with here so that's what you should do as well and we're going to go on and do some more things instead of just reading from the database we're going to write to it and delete to it and so we'll be using the documentation to find those examples and so our code will work so now let's take our dio and integrate it into our security service so i'm returning here to visual studio and i'm opening up security service now in our previous uh iteration of this program we had a bunch of hard-coded values and of course we don't want to do that anymore we want to use our database service so i'm going to just knock out all of those items there and use a new security service so to make this happen we're going to define a user dao object and so we'll just initiate a new one and then for the constructor of course we don't need any of those hard-coded data and then finally in the is valid section we're going to delete the current process and replace it with finding a user in the database so to find that i can just say users dao dot and i created this method called find by name and password and so we're going to pass in the user object so you can see it's just a lookup to see if there is an item in the database just occurs to me now that the known users is now obsolete so line 12 can also go away so this is much simpler we don't need the constructor we really don't need much of anything we're just turning around literally handing the user object to another class so now let's test this out so first of all i'm going to go back to my sql query to double check on who my people are so i have max jenny and howard and they all have password exclamation mark so one of those will work let's try max all right so let's go and try the login screen here so i'm going to use the first item here called max and password is i believe with an exclamation mark after it so let's check that out here password is good and we choose login now hopefully i get a login so i didn't get a login it says you have failed me and the information is incorrect so what went wrong here so something is in my output down here it says uh there is an exception thrown so i wonder what exception was thrown i was expecting to see a message being printed and it didn't so what we're going to do is demonstrate a little bit about why you would use a debugger so somewhere in this code here i think in my my user dao didn't work so i'm going to park a a what do you call it a a stopping point here a break point and then i'm going to run the code again and let's check to see where the exception occurs and why it didn't work so your program may have worked just fine but i'm going to show you how you would trace down a problem if you actually did make a problem so let's see i'm going to type in max and his password again and when i check the login button you will see that the execution of the program will stop so let's see here log in come on let's go and there we go so let's trace through what's going on here so if i look through each of these variables i'm going to see that they either are null or they are assigned something so right now the connection is set to null as you can see because the execution is about ready to go on this row so i'm going to skip over here and then go back to see if there was something here so connection has a whole bunch of data in it now i might not understand what all that data is but at least i know that connection has been assigned so i assume that it worked let's jump to the next one so now we have a new command and you can see command is also set to null if i switch to skip over it and go back now command has a new command in it so the command can now receive parameters so let's skip over those and i can see the yellow line tells me that i'm still working and now i come down to we're about ready to try the connection open and that seems to work the next one says execute so the reader doesn't have any data in it yet but it will in one more click so i click there and instead of getting data back i jump down to the catch statement so this line here where it says execute reader failed and it jumped out of the try and went into the catch region one more click and i'll find out why so now if i hover over e exception we should be able to find out what it was so let's see here open up e exception and uh let's see if there's anything of sense that may give us a hint of what we did wrong so it says here online message must declare the scale of scalar variable at password so it doesn't see that this line up here where it says password equals password didn't work it says you never told me what that was well i certainly did tell you i told you on line 30 that this password is going to be equal to user password except if you look here i didn't tell you the correct password so a simple missing letter s here make it a double s for password and things should work better now so let's uh stop the program and continue or restart it and let's see i'm going to take out this this break point and let's see if it works better all right so we're back and i'm going to type in login and let's see if we get anything better this time so let's go to max and password so when i log in this time i get login successful so that shows you the pattern here of using a database to look up users and also you got some bonus features to find out how to trace down an error now in the rare case that you make a mistake you'll probably need to use the debugger as well so we've got ourselves a login successful with database now we've uh we've gone from hard-coded values to a lookup of several in a list and then finally we're here in a database so i guess what is missing is the challenge part is to create a registration screen so we have a database of users how do you get a entry form to put data into the database so stick around for some more tutorials on asp.net and we're going to continue on your journey of becoming a full stack web developer in c-sharp

Channel: Programming w/ Professor Sluiter

Views: 30,455

Keywords: asp.net tutorial for beginners, c# web application, C# tutorial, C# asp.net, C# asp.net core

Id: 8Cu7Gy-Vm2I

Length: 29min 8sec (1748 seconds)

Published: Sat Dec 19 2020