ASP.NET MVC Data Access in C# - The complete data path from database to display and back

tutorials are excellent at teaching about a piece of the overall developers 'el but sometimes you want to a step back and look at the bigger picture that way you can see where those puzzle pieces you've been collecting fit into place this video is you know a bit bigger picture overview we're gonna build a small demo of an MVC application and its accompanying database we'll set up a form to collect data capture that data and put it into our database well then get our data back out of our database and display it on another form the goal is to show the data access process from beginning to end in asp.net MVC this won't be a full application and it won't complete the full process since we won't be putting this in a source control we will be deploying this application and you'll be set up authentication now if you want to see a full application being developed you might be interested in where might start to finish courses on I am Tim quarry com if the one you want isn't there chances are it's probably the works either way also consider using the link in the description to sign from a mailing list there you'll hear about new courses changed my content and exclusive discounts on courses okay let's get started in Visual Studio where I start right away with a file new project it plays this in the demos folder and we'll say asp.net web application full dotnet framework not the core so on our c-sharp web it's the full framework version we're gonna cover core in a future video but for now rest stick with what's generally out in the marketplace right now for existing applications for new applications but you'll probably know core but for learning how asp.net works and for certain circumstances MVC is still the way to go but the most part for brand new applications probably core is a better bet but an awful lot still out here with MVC so we recover that first let's call this MVC app and we'll call this MVC data from start from database to form it's a little long but it's descriptive it's okay and we're gonna be asked what type of template you want to use when I use the MVC template we're not gonna use web forms or API or docker or unit tests and we're not going to authentication so I'll leave it right as it is at MVC I'll hit OK and that'll scaffold up the program the project it will also bring doubt you see in the lower left hand corner here it brings down a bunch of new get packages to get us started on all the tools we'll need for this application now again this is going to be focused primarily on moving data through an application so let's go ahead and add a new project and we'll say under desktop there's class library dotnet framework version the full net framework version where I call this our data library so it's me where are all of our data access lives we'll go ahead and delete class one and then we're add one more project and this is going to actually be in the other languages sequel server we're going to add that sequel server data project now if you're not familiar I do have a video that just released a couple videos ago on this topic actually the thing last week so that will hopefully help you with this what that's gonna do is allow me to create a sequel server that then you can have so when you take the source code from me you can build it and publish it locally and then point to it and use it so where I call this our demo DB let's call it MVC demo DB now it's a little more clear so there we go so now with that being able to have a three products we need for our full scope of our application now we're not going to go into depth on modifying the NBC so we're going to start with something simple so in the home controller we're going to actually let start the models first let start at data first so in the models let's add a class and we'll call this the let's be simple and call it the employee model it's the idea here is where I create this little form that employees can fill out with their information and let's go ahead and list with that information will be so start with employee ID so first name last name and then email address all right also have since this is a view this is a model for our UI our MVC product the entire thing is our user interface so even though there's models in here and controllers a still UI code just to be clear on that so this is a UI model so I'm gonna have another string called confirm email and then let's create a couple more password confirm password that should be good so now we have those fields where actually a form where employee can fill out this information so they're signing in to our system for the first time they we they give us the employee ID first name last name their email address their password and it sets up an account for them so now we need to do in a home controller let's create a an action called sign up and it's with the same thing as all these other ones such as copy and paste this and we'll call it signup and this is the employee signup so that's the the view portion with the the get request so let's create a view for it notice we don't have any yet we have about contact and index that's it so if we right-click and say add view now we could fill it in as a model I'm not going to yet I want to show you well you know what let's do it so I put the model in first where I do it create so it's going to create template and the model is going an employee model so I'm going to show you a couple different ways of going about this how you create this form this is probably favorite just B that gives you all the the fancy boilerplate stuff that you might want and then you can just decide not to keep sumit versus other methods are less less value to you I don't give you as much but you can add to it and I'd rather take things away because the fact that it's easier to remember with a delete then it is to remember the type so this way if we use this create templates gonna create a form for creating a record and we're going to have it use this model as the the method for creating that form it's gonna scaffold it all out for us let's hit add you'll know it's down here it's gonna pop up with a new entry there you go signup and notice all the code that it created for us that's a lot of code and what this code is and let's just get off from it is it starts off with saying okay here's the model we're bringing in that's the employee model and we have the the view bag title of signup let's make it employees app that's a little clear and let's make it employees up here and now it creates the begin form this is razor syntax that's the app symbol and so the razor syntax allows us to bring see show into our HTML form which is really convenient so it starts off a form so it's gonna create the form tag and set up some initial stuff for the form and when the first thing that does it brings in an anti forgery token and so what that does it puts a hidden field on your form and then you can check for that value in the control it comes back and it's really you know if you don't really notice almost but what allows you to do is make sure that people aren't trying to post or pretend like to post in your form but doing it directly instead of through the actual web page and try to bypass stuff so that anti Forja token is a really easy thing to add to increase your security of your form all right then it creates these sections so we have employee model and this is so that's that the header if the overall form and then a validation summary and then right here is our first form group a form group represents one entry point or one one piece of data we're entering into a form so that's case we got the employee ID then we have first-name we have lastname you have a dress confirm email I'm sorry yet email address confirm email password and confirm password so all of this setup and they've have all these complicated label four and then it's got the the model and it's got the arrow or the model dot password the editor for and validation message what this is doing and this is why I like it the fact that the the template creates this for us instead of us typing us out with this it's hard number all this stuff but what does is it allows us to do a lot of configuring and tweaking of our form without having to do any work in the HTML itself so we'll see that in just a minute but for right now we're going to do is where I get ready to launch this just so you can see this form just it is boilerplate I only changed the employees app up here and the view bag title so it's pretty pretty boilerplate but first let's go ahead and go to layout and we're going to add one item let's add it right after home and we'll call it the signup and it's these sign-up method in the home controller so that's controller that's the method or the action and then here is the the link name or what's gusting a link itself so with that being said that should it be all we need to get started on seeing our application looks like let's bring this down here once it starts up now we can see that the website load up now we have a sign-up button right here we click it and there's our form I mean we don't have to do anything except say use a template and I modify of course this right here this was an employee model that's not really cool we'll change that and then it's got employee ID which sure we want to see that but it's kind of running together there and first name kind of runs together and last name if if I don't feel anything out I hit create employee IDs is required okay so I'll put one there and it seems to do something it wipes the form out and that's kind of weird and if I hit refresh here it's gonna say - I'll resubmit the form which means that the form was submitted with just that one there and that's not great either so you know it's not checking for it to make sure that my password matches my confirmed password a whole bunch of you know not great stuff Billie's it's a start right well there are things we can do to make this better really quick so let's go over to our model now this employee ID already said that we is required but we also want to add that has to be a certain range because employee IDs may there's six digits long so let's let's well yeah stop it's made us start with a range hit control dot add the using system dot component model got the annotations here is a range and say you know what this has to be a minimum he can be into or commit double but let's say a minimum of there's six digits yeah at six it is and we'll do the maximum has to be you know nine nine nine nine nine nine because that's our employee ID range is somewhere in a six digit range and if we want to we can add an error message here so let's do that so we can say error message we don't have to add us notice the the square brackets here it's optional arrests say that error message is you need to enter a valid employee ID it's something simple and one thing that you before because it says that I'll get one needs view have can't be of int has to be a system type and argument to cannot convert from into string which means I because if you go here it looks like you can because actually here minimum maximum and you know we're on an error message but the the problem is that we have to specify white now we're actually on this one which says type then minimum maximum so if we just say error message equals and that allows us to do it in that order so minimum maximum and the error message and we also said that the employee ID was kind of clunky just putting that right on the screen as the label so let's say instead display and we can say name equals employee ID like that but the space in there I also capitalize it the D so it's more in line what you'd see probably in the in the real world so these are data annotations and they allow us to modify our model so let's go ahead and do this few more in fact I was going to copy and paste just to save some time here and say this is the first name and I was going to say this one is required and if if they don't provide it I'm going to say you need to give us I've hurt your first name so let's grab that do the same thing for last name and then email address we'll do the same thing now this one's a little bit interesting we're not gonna require it even though you'd think we have to have a confirm but instead we're going to say compare and then give it which field we're gonna compare it to in this case email address and so it's going to compare it to the email address and then we're also going to say the the error message equals the email and confirm email must match we are going to give it a splay name so let's let's do that too and then password we're gonna say the data type data type here's data type dot password I will do the same for email address in just a minute we're also going to add to this a another attribute called string length we're gonna say the the maximum length for the string is going to be and this is silly this actually I haven't a problem at Bank they said the maximum length of my password had to be having no 120 characters that's kinda ridiculous I would set this at a hundred so password can be at least a hundred or at most 100 characters long that seems probably good enough but where I say that minimum length equals and let's say something more reasonable let's say 10 characters I know it seemed like a lot but I think it's a pretty decent password and then where I say that the error message equals you need to provide a long enough password okay I'll make this required and say that the error message equals you must have a password and we're also going to say the note annotation we got here this is okay I mean it is what it is because we're defining here so you can use it over and over in multiple places or just cuz it's password you probably have to since they're both called that my may be a little more clear here we're going to say that the data type is still password for a confirm we're not going to leave the requirements for length or required and paint it kind of stuff we're going to do the compare to so that's neat password and the error message equals your password and confirm password do not match I have an error message here for that for email address let's go ahead and add that one more thing on there that I almost got past which is the data type and this is a email address notice all the different types you have here so we have credit card currency custom date date time all the rest some great stuff in here URL will do is it'll do basically validation based upon these different rules so email address it's actually gonna validate to make sure it's a valid email address so let's do like that for now let's see how our form looks now with just that change and see if we can't get you know a little better view on what's going on here so all right got loaded let's go sign up and now notice employee idea the space confirm email as a space we've forgot confirm password so we got to fix that if we type out a password here notice it just has a dots if I click off it it's not long enough if I make it long enough my password in confirm pass or do not match so it's tracking these kind of things which is great and it's doing it on the client side this is actually jQuery validation that comes come bundled already with MVC if you're not a big fan of jQuery which he should be the reason it's got a bad name is because we've overused it and there's some better tools out there now they're a little more streamlined and specific for we want but for this case it's great for us it does the validation it's built-in and it's one last thing to worry about when you're starting up with MVC as you move forward maybe while look at replacing that library or something a little more full-featured or a little more or lien but for the most part this is perfect for getting started okay we'll put one in there for our employee ID it's not valid employee ID let's now it's a valid employee ID now it's not again there you go skip over let's just put a letter in here I'll skip back and hit take away and says hey it's a first name because I just kind of skipped over it's saying well you may you're come back to it as a test here it's not a valid email address test at still not a valid email address test calm now it's a valid email address and if I say test the team ale and confirm email must match so so now you got something going on here Cliff Lee hits create it's not doing anything yet because it's saying you're not done yet now if I start giving passwords and everything out hope there we go and now I hit create the form clears away it but nothing really happens so let's work on that next now we have this form kind cleaned up want a fixed confirmed password and I'm also gonna show you one other thing you can do so let's start with confirm password let's grab that display whoops there we go and now we should say confirm password I come over to my signup dot CSS HTML file we have this whole form here well let's do this let's minimize this right here that's basically the whole form I'm gonna highlight it I'm going to hit my comment out so comments the whole section out so the form really should be nothing it's got the anti forgery token the begin form and that's it until the end form but I can do here I'm gonna say at HTML dot essay editor for model that's it okay now let's rise again I want to show you this little built in it's it's not something use all the time but it's something that that can be useful in the cases where you wanna just create a quick little form there's before it looks pretty similar we've got some differences but it's it's a form in fact if I start doing it it's got the validation of ice if I hit submit and it's actually the wrong format the editor for I think any create for but but anyways this form you know it works in a similar manner it's really good for a quick starch on a form if you're gonna you know get something out quick boom that one line to it but for anything we need to edit any of stuff here I think you'd rather go the scaffold route which does all the work for you that you can then modify so let's hit stop I'm gonna it's gonna undo those changes here there we go it's now our form is back the way it was and now we can tweak things like let's move us div around or in this case that's just horrible sign up for our online account it sounds great just something different than the model so so there's our form if we look into each one of these things we kind of see how those different things worked so we have let's look down here it's a little more distinct we have a form group and this form group is for first name and so it has label for the first name and the reason why I says label for is modeled give me the first name property of the model is because it goes and looks for though that metadata the the display for or display name and this is okay if you got a display names show that but if you don't finish grab the name of the property itself and so at first you grab the name of the property we changed the display name and now it grabs that instead so that's how that label works and the editor for same kind of thing it applies some attributes to our editor where you know we can we can say for example the password where it says oh you wanted editor for which would normally be a text box but I see that your media says that you're a password therefore I'll create the password text box so you can't read the characters being typed into it and so all that stuff gets done the editor for and then if you don't put the right information in then the validation message it goes right below it that's right there so you could make some modifications on this form where you move these things around maybe you want to see all the validation messages at the top instead of underneath the the box itself which I wouldn't recommend me that it's nicer to see them right where they're at but say you did well you can just take these lines and move them the top so this allows you a lot more functionality and configuration ability it was just as fast to create as that eight or four model alright so we have data going into our form and again we're gonna move kind of quickly through these different areas and if you want more detail on something I've got prey another video that covers it in more detail but I want to surely we move as data through the process so you've got data in the front end we've got this employee model and now we need to actually capture it so let's do this copy I'm gonna paste it again and the first thing's gonna do is yell at me because I have a duplicate method name I want to keep it that name so what I do instead is add an attribute at the top that says HTTP POST so when you ask for a get when you say I might get the information to get the form it's going to load this method but when you send data from the form it's going to send it to this method and where I say that the data came back is the employee model where I say add that you just a minute we'll call it model now the other we're going to do is we're going to validate that anti forgery token remember I said on the form itself we have this anti forgery token the other half of this so you put this on the format cell just as it is and creates a hidden field and the other half of that is just to say validate it that's it so we've now validated the anti for your token next we're going to say if model state this as well as built-in things to MBC is valid so if it's valid he's got check this model and set say is it a valid model meaning does it comply with all these rules the compared to and the the lengths and all the rest you may say well yeah but we're doing that check out rating a friend while you begin and the reason why you do it again is because there may be a way to spoof the friends interaction or maybe you turn off JavaScript that's real all that is is JavaScript so you could say you know what trick JavaScript into believing it's valid data and then submit and so you could if there's a workaround there that you could do everyone trust just one layer security and you never want to trust front-end a client-side scripting as your only means of validating data so we have validate a second time and just say mile State DOT is valid if it is we're going to run this code if it's not we're gonna return the you what that does that sends us right back to the same page we were on but if it's valid let's do a return redirect to action and we'll just say we go back to the home page so go back to the index action what's gonna do is gonna post the data and if it comes back is valid and at posts then it's going to redirect us back to the home page we'll see that in action in just a minute let's go ahead put a breakpoint here get this started we'll fill out the form and make sure that fills out properly and then we'll see how it hits the breakpoint so sign up and let's fill out valid data time and we'll give it a really strong password 1 2 3 4 5 6 7 8 9 0 and hit create so this is a valid form yeah 6 character or 6 numbers for employee ID first name last name but filled in email addresses match and they're valid email addresses and the password has 10 characters and it matches a confirmed password so hit create and it goes right here and hits our breakpoint and if we look at the model the model has all of our data in it it's a little small but has all of our data in it so it also says the model state is valid so if we hit just hit continue right here it goes back to our home page if we were trying to hit back it's got some data but the past was been wiped out because of security reasons you know if we hit sign up of course it's all gone so now our our form is posting data to our MVC controller now we need to take that data and put it into the database so let's do that now let's close this out all right get to the breakpoint off for now so now we have our data coming in to our home controller we need to send it on to the next phase the next phase would be where our business logic and data access comes in so let's set something up for that now you for we start with our data library let's start with database itself because you know we're going to need a store it somewhere so let's create that that table so start with our folder for dbo and then inside here I'll add a folder for table up tables and then inside here going to add a table and we'll call this table the employee table make it an identity and then we're going to have our we could actually if you wanted to just put this right next to our window but we have employee ID first name last name email and password so employee ID and that's different from our ID our ID req identifies one row in the database our employee ID is something that we give employees to identify them we don't want the to mix the two because then that that causes all kinds of problems so keep them separate I will make this into a numeric field so actually type int and then we'll say no we're not allowed nulls say first name we'll say that's an n varchar' 50 oops not no last name and varchar' 50 then we have our email address and varchar' 50 I'm put about 50 really does aren't realistic email I just keep very much longer than 50 so technically I think it can be oh my goodness I think me over a thousand characters now I can imagine that business card but in theory it could happen maybe I was sue me for some kind of automated system but the most part you want your email address probably be at least a hundred characters long that's pretty much the the upper limit of people's email addresses but you want to get those edge cases and there as much as possible so I wouldn't say 50 but for this demo database we'll stay at 50 and the other thing here is I'm gonna put this I'm gonna put it as you know what I'm gonna do no I'm not gonna do it it just pains me too much I'm gonna pretend like we're not even storing the password okay so I could put it here and just to show that works but it it concerns me that people might emulate me or put passwords in clear-text in the database and that's not a good thing now okay so there's very very very very very few reasons why you would put a password in clear-text in the database the only reason I can think of is it's not really password it's more just a separator you know some way of separating yourself from somebody else that's you know for internal systems where everybody knows everybody maybe but tonight you know I've worked with clients that do this and it's okay for a certain circumstances but really if I'm gonna have to give a general rule don't put passwords in the database period so I'm not gonna put a password there where I'll leave it as just this we're not going to collect that password so we've now saved the employee table and we're done there that's all the tables around create we're gonna create you know lots of tables or views we're not going to create a store procedure we're just gonna have the employee table so let's go ahead and publish this just we have that create so I'm going to browse local my local DB or a called this MVC Dan dB I'm going to save this profile and let's create it in the create a new folder so a new folder is going to be published profiles like so there we go and now we're going to say publish and that should go out to our our local DB sequel server should create a sequel server for us it did and we should have one table and they're called employee we do and it's got our columns in there employee ID first name last name an email address so we're all set now with our our table let's move on to the next piece which is actually wiring up and connecting to that table so we can take our data from our MVC and put it right into sequel the first I'm gonna do is add that connection string just so I have it so web.config and we need to add a section here since there isn't one you know a section for connection strings make sure there isn't a section first before you start adding one add the name we're gonna call this MVC demo DB because it matches the database that's typically my pattern is to match the database name with your connection name connections train equals really the blank from it gonna go over meiotic Explorer go to my local DB my NBC demo DB right click on it and we're gonna look for properties one of the properties is connection string so I don't click there I hit copy I paste it in right there and we're good to go so now that's a connection to my demo DB pretty simple stuff especially since I'm not actually doing anything it's copy and pasting so now I have that in my web config of my front-end project that's where the web config lives is in your friend user interface whether it's des PF winform console app MVC doesn't matter that's where your config file lives if it's a desktop app it's app duck config if it's a web app it's web config all right now we can go our data library and we're going to add a couple of classes so let's add first a folder i'll call it models and in here i create a model for our data we call it employee model and you might be freaking out right now it's a hand employee model it's right here why wouldn't I use this for data access and the reason hopefully become clear over time this is a front-end user interface model that's entirely different than our business logic / data access model so the difference all this stuff right here is MVC specific all the splay name for the range all the rest we wouldn't want to decorate this if we were putting into a console application we wouldn't need these things now we could we can shoehorn some of this stuff in here but really this is for the display side of things on the other hand the database doesn't care doesn't care okay it just doesn't care about these modifiers it only cares what the property itself so there are two different things for two different purposes so it is okay that's not repeating ourselves to create a different model we should here's the other reason why we have email address and confirm email address why do we have two properties for one thing well because this will the user is filling in for both of these well my employee model over here is not going to have to email fields okay so these are definitely dead things the other thing is we can also have an ID here for the table notice this table has ID separate from employee ID that's the row ID so I'm gonna store that actually in this in this model that was wrong the other thing is I'm not growing the password so we're had to map these two together because where I have data in this model we ready to get in this model and that's okay and there are some tools out there we're not gonna use a tool today where I'd do it manually just to see how it works but you can create tools or you can use tools off a shelf like Auto mapper is it's a pretty decent tool for this kind of stuff where it takes one model and maps it to another model because it's a very common thing to do in applications you really should be mapping data from one model to another that indicates you're using your models correctly if you're passing the same model around for everything you might be using the model as incorrectly or at least you're not using them as efficiently as you can so there's there's option I mean I have used models all the way up through because they're they work now I had to make any modifications so it is possible it does keep things simpler but when you come to a case like this you want a map so we're going to do that in just a minute next we're going to create a folder your folder right here we'll call this let's call it logic so in fact it's called business logic just to be more clear and they'll create another folder and we're a call a dear access now this is a simplified view it's not going to be this the full-featured three layer and layered application but it does kind of simulate some of those layers because that's what the day is gonna go through so let's create a data access layer first create this we'll call it sequel data access make it public and this is where we're going to actually do our data access to the database so let's add in my favorite tool which is dapper if you didn't know already so let's look for dapper we'll install that and once its installed we could ignore that error once its installed now we can do is we can actually I using say that for dapper and then we'll also add user statement for system configuration and the using statement for system data data first and then using system data died sequel client so those are the four run be using primarily so I'm just gonna add them up front instead of doing a control dot every time just makes my life a little easier so the first thing we're going to do is let's wrap let's see our connection string let's kind of wrap it in a little function so let's make this there's no reason not to make a static all right when I got stored data here so I make a public static string get connection string now we could pass in a connection string value so let's do that string connection name equals we're I'm going to default it to our MVC demo DB just because that's that's more like becoming using so therefore I don't have to actually specify it for the common scenarios this is really simple we're going to return the configuration manager and it can hit control dot here and add the using system that configuration that's a DLL so I do that as a DLL it pop it open there it's right there so now to add that that I can say dot connection strings square brackets and then connection name that's passing in the variable and then dot connection string what that'll do is got go out and get the connection string if you don't give a connection name it's gonna get the MVC demo DB which we just set up so that's perfect so that just kind of shortened this whole call down to a little more reasonable size and now let's create a public static load data oops T load data of type T and we'll do a string sequel and let's leave like that for now so marine is very very simple I'm not gonna even allow to pass in parameters render load I'm just gonna say you know what you're getting all of it Noah's not not you know totally ideal because he has you know a lot of parameters but for our test scenario that's all the stuff I need to do and so Y created elaborate did access so somewhere I really did need to do one job so it's gonna do that one job and do it well so I'm going to add my using statement here where I say i DB connection I'll say CNN equals new sequel connection and this is going to grab the get connection string alright so I get the connection string from this method right here and I put it into here and then open that sequel connection so then in here I can say return and so I'm really simplifying this but return CNN query of type T pass in sequel the one thing I gotta do here is it's actually turning not T but list of T and still yell at me because I'm not returning a list here as an eye in Noom can I say to list okay so what I'm doing here it seems really simple and it really is but the same time can be confusing with T's so let's talk us through we're gonna say load data and here's the model I want you to load it into that's the T and so it's gonna return back a list of that model and so I connect to sequel and I say okay give me this sequel there's a sequel the strain to call whether it be a store procedure probably not or does a select star from or whatever and it's going to execute that sequel and it's going to load that query into type T now this query returns an ienumerable well I don't want ienumerable I want a list so I say dot to list return that whole thing and now that's all we need to load data if we don't need parameters so that's all well and good but we're not really interested in loading deal yet we want to save data that's we have data already so let's look at that public static void not gonna return the always gonna say yes it worked actually know what let's return and that's a number of rows affected just so we have something to return I say save data and we'll say of type T I'll say a string sequel and then T data okay so it's gonna save one model so that's a day that's gonna save using this sequel statement right there so right copy that's using up here but instead of this right here we're gonna say return CNN dot X acute here's my sequel and then here's my parameters oops not T it's data there we go so data is my parameters now we're not putting that into a special dynamic parameter list we're not Kriya anonymous object we're saying that the the object you pass in that will have the parameters matched up to hopefully the the parameters in the sequel itself it should and so we're gonna match those two up so if it has an at first name in the sequel we're a look in the object called data for a first name property and take that value and replace it so I'll use parameter is equal four so this is all the save method does we're returning this because that returns the number of records affected hopefully one every time so as long as it says one we're good to go next let's create our business logic class a new class let's call this employee processor and save public let's create a method here called public you know we can even make this stack as well we don't have to make these classes and stands you will instantiate able because we're not gonna store data here this is a processor so public static let's return a int for number of Records affected and will say creates employee and what we need to pass in to create the employee when I got past a model in where I pass in employee ID whoops it's an int a string first name string last name that's getting kind of long so let's go ahead and hit enter after string REE for string and then say after last name we'll say string email address and my missing anything let's look at my model ID employee ID first name last name email address nope we're good so we're passing those four values in guess around you're now now let's say the employee model control dot add using for our data library dot models don't don't add a reference to NBC app that's the wrong direction you don't want to do that internally wanna say data library dot models because this is the one I refer to most cause data equals new employee model let's do the curly braces not be open closed parens and say the employee ID equals employee ID the first name equals first-name lastname equals last name and email address equals email address and guess what we've now mapped our model from one to the other so now we can say return sequel data access dot control dot first add that using statement dot save data we can just pass in our sequel statement which I don't have yet let's call it sequel and our data now I don't have to specify the the t here a type here on set have a say you know of type employee model I could now have to the reason why is because I'm passing in this and that's of type T so I can say okay well if that's a type T that I know the type T type is and can I say type one more time so that makes it a little shorter type okay we have strings sequel equals and what's our sequel statement gonna look like well ince let's correct make sure you put the @ symbol in front here that we can do multiple lines without having to do that plus and their line all the rest so you'll see the minute inserts into DB oh oops let's fill it right Tim DB o dot employee employee ID first name last name email address I want to keep us on one line I think I will to enter here there I'm cheating alright so now I can say values and say at employee ID at first name at last name and ads email address and where those come from well these are this is parameterised sequel and so the at indicates this is a variable and it's gonna say okay employee ID does the PERT be the data you passed me along with the sequel statement which is is this right here does it have a property called employee ID why yes it does well does it have a value yep so put that value right here the really cool thing is first name last name and email address are all strings but notice there's no single quotes and all the rest it's smart enough no oh that's a string therefore wrap that in single quotes so you don't have to put those single quote you don't you shouldn't put those sale quotes there because it would call actually caused a problem so there you go so now you've got your parameter is equal and you've got your data so now it returns that value so we our business logic is done for creating the employee let's create our the next piece which is public static list of employee model and yes for passing employee model up the chain we don't have to we could map it as something else we can return dynamic if you wanted to but we return employee model from this from this library so it's being this employee model not the one down here in our MVC app which is actually hidden right now so employee model or si load employees it's all going to here is we're gonna say string sequel equals select employee ID actually read the ID as well employee ID first name last name email address oops didn't add my @ symbol here let's do that so now I can just hit enter here put them up lines from dbo dot employees so really simple sequel statement is just selecting everything which I can now say return whoops no semicolon where those when you start typing it's over here or somewhere wonky the reason why is because you forgot something the line above so I forgot the cycle at the end so it's Auto it's continuing my thought from the previous line return sequel data access dot load data and the daily while load is employee model you have to put that in the paren earn the angle brackets because passing tea in somewhere there's a sequel it's not parameterised view there's no parameters to pass in so now it's going to load all of our employees back so now there are data access and business logic layers are done we can come back into our MVC app come to our home controller and since this model state is valid now we can do is we can add a reference to our let's go to browse our data library here okay we don't need to add an add a reference to our secret project to see now so now here we can say is let's add a user thing at first just make it little easy easier so I'll say using data library and we can say we want to call the the business logic so employee processor oops using dot business logic that's why all right employee processor dot create employee you pass in all the data we need now this makes a little bit longer of a call I'm going to show you something we can do here I'm actually got cut that out go up a top here hit dot and add in and it doesn't it says no you can't do that unless I do this using static and this as well as new things I believe in c-sharp seven or seven one one of those what allows us to do is add a static up here all the way up through the class name so it down here we can just say create employee as if it was a method in here now obviously if you got to step on toes that would be a problem meaning you have a creat employee method already in here so but we don't so we're good so model dot employee ID model first name model dot last name and model dot email address not to confirm don't you ever bet and I got extra close paren at the end and let's make this little neater here so there's our create employee we could catch this so int wreck Rex records created equals and there are call and now we're not going to do any with that so it's gonna put a breakpoint here so we can actually just read it and make sure that it says one so hopefully with all that being done our data access for creating a new employee should work let's find out if it actually does work so we'll load this up takes it with the load but as long as you know you're doing hopefully can do a little more work before you just you know keep loading up I don't quite a bit work right now which is hopefully I'm all work that's the the one problem with doing a lot of work remember that we're not saving the password it creates the breakpoint gets hit records created says one our model has all of our data in it so if I hit go it's a go right back to the home page I close this out and stop the project come over to sequel server object Explorer go to my local DB and let's say view data and there is employee ID number one I'm sorry ID number one employee ID number one two three four five six Tim quarry and my email address and that was valid they in the front end to make sure that valid email address make sure Matt read typed the first time and made sure the employee ID was a valid employee ID or at least in the valid range so all these things are already been validated a couple of times which is great so that right there allows us to see all of our data our DEA's gone from the front end all the way through our application our business logic which wasn't much was just change it a little bit and then put it into our database through our data access code so that's the whole process from start to finish from from top to bottom now let's go the other direction from bottom top take our data from our database through our data access into our business logic into our controller you are in the front-end and then finally to our screen so when you create a view for this so let's create a view rebuffs sign up will say public action results view employees and we'll say that the view Bay dot message equals employees list and we're going to say we'll start with a return view but before we actually complete this I a make a call to my my business logic so the employee processor so let's let's say instead instead of create data and create employee it is called load employee so let's do that so let's say our list is just say var for now it's the easiest data equals load employees and we don't need to pass any age loads all the employees now this data if we mouse over it it says well it's a list of data library dot models that employee model that could be okay so we could just take those data miles since they're just displaying we could take those and just display them or we could transform them and so the transformation process this is where automap is kind of nice because it's an automatic process and it's a little more concise look at just two a for each here so we can do a for each and then convert it over to our our existing employee model over here so it's up to us how we do it I think I'm gonna do that I'm gonna do a for each just kind of show off what we do here so list of employee model notice we mouse over this this is the MVC app models that employee model so this would be the employees equals new list of employee model this is what really helped me because I didn't have to set up and say data library models dot employee model I guess a bar and then I can do is say for each item or row let's the row in data that's my existing employees from the database in the the model we don't lie display I'm going to say employees dot add new employee model the curly brace and say employee ID equals row employee ID then first-name equals Rodat first-name and lastname equals row dot last name and email address equals road email address now we also have confirm email address equals Road email address I look the same because if our display display both which I'm not going to but if I were going to they want to have both filled in let's just say I'm gonna allow them then to edit employee I wouldn't want them to have to retype that employee that email address if it's already valid so now fill in both here the password I don't know which is okay I wouldn't want to pass the password back through anyway so now what they'll do is it's gonna add a new employee for every data entry and that can pass this into my view so now I have a list of employees I can right click on View employee say add view I'm gonna say it's a list and it's of type employee model but is the MVC app top models not data library down models I hit add it's gonna scaffold the whole thing out notice it has employee ID first name last name email confirm email password and confirm password don't want those so let's just delete those out and then same down here be step that was a header rows and now I can delete the data rows and it does have the the Edit details and delete which I'm not going to create so let's go ahead and leave those off so now here is all of the here's my new record I'm going to say you know what for the create new sure let's leave that on here but instead of saying create its signup that's my action is signup so I'll save you employees that always good let's run this and see what shows us so hopefully here we're gonna have one employee and we don't think those whoops nope we don't so we say in vowed object named DB o dot employees let's find out why so our sequel statement says grab it from DB o dot employees but it's saying invalid object named DB o dot employees so I'm assumed that means is that let's see there's an inter exception there probably won't be there is not so assume that means either guy a typo here let's look at its employee table not employees so I got the singular versus plural mixed up there my bad so let's go over to my my business logic where we have the employee processor and right here I need elite that s now if I start out from here it's gonna do it's gonna start at the homepage which I haven't yes as I've been thinking I haven't put a link to go to the view employees but if I start it from the view employees es HTML it will start right on that page so just so you know but I will put that link in the layout dot CSS HTML so we have sign up and let's get rid of contact and about let's paste in the this link right here will save you employees and we're gonna say that this action method and I've already forgotten what it is it is view employees so there you go I just always verify instead of just trying to type yourself otherwise you put employees versus employee something like that so I start out as yes HTML so it's gonna start the home page not a big deal but if you want to go right to the page you're working on it's nice to already be on that CS HTML ok this app we have View employees let's view employees and there we go we have Tim Cory and we have create new which takes us over here let's create a new one and we'll say that this is Mary Jane and the password she's a little bit more secure than I am she starts at zero and goes up create now I could do is instead of going to the home page I could go right to the view employees which now has Mary Jane so I can make that modification I wanted to but really now we have a complete application with data going both directions we have data coming from a database going all the way up through the stack and into our user interface our MVC application and a view and we have data going in other direction where we're taking it from the user interface all the way down the stack into our database so this has been an example of kind of a a little higher level overview than normal where I've showed multiple pieces kind of faster than normal as well because really want your see is how the pieces you've been building and how you've you've learned little pieces over time hopefully you've learned the different pieces I've shown but how those kind of fit together into a larger picture and this larger picture is how data access works in MVC one of the questions I get a lot is well how do you do to data access with X whether it be NBC or net core or the UPF or WinForms or consult whatever user interface you're talking about and it really sometimes confuses me because I say the answer is the same no matter what and that's because this data library they can now attach to wind form application or it can attach to a console application or any other user interface you want to put on it really because the logic and the data access stays the same so the MVC app is just user interface it's just for displaying information and getting and sending information on to be worked on now there was some tricky bets and I understand that so there wasn't tricky bets about how we translate those models from one model to another like actually it's you know a couple of examples of different ways you can do it but but really what comes down to as a user interface should just be a displaying information and getting information not really about making decisions on the information or saving that information you shouldn't have data access right in your MVC application it should definitely be in a class library somewhere I hope it's been helpful I hope that this kind of higher level view has kind of shine a light on how this process works from start to finish now again this is a one of the processes in creating a full MVC application or a full application in general but it is that larger process of how the flow works that's the most important part of an application is the data flow I hope you found it valuable if you have any questions or comments please leave them down below in the comments and I will try and get back to you as soon as possible I appreciate it if you would give me a thumbs up that that's always helpful and also you know subscribe if you're not subscribed otherwise have a great day and as always I am Tim quarry [Music] you [Music]

Channel: IAmTimCorey

Views: 226,299

Rating: 4.9557304 out of 5

Keywords: .net, C#, Visual Studio, code, programming, tutorial, course, training, how to, tim corey, C# course, C# training, C# tutorial, C# app start to finish, data access, asp.net mvc framework (software), web development, asp.net 5, mvc 6, c sharp, .net tutorial for beginners, sql data tools, dapper, c# dapper tutorial, c# dapper sample, c# dapper sql, c# dapper mvc, asp.net mvc tutorial, asp.net mvc project

Id: bIiEv__QNxw

Length: 75min 55sec (4555 seconds)

Published: Mon Nov 12 2018