Build a .NET Blazor Server Scheduling CRUD App with Dapper and MySQL

hello in this video i'm going to create a blazer server application using net 6 it'll be a scheduling app with cred operations mysql database that will build from scratch and will connect to my sql using dapper here's what the finished product is going to look like there'll be an admin page and a home page in the admin page you can create available appointments so you can make yourself available or make someone available for an appointment and then you have these actions here as long as the appointment has not been reserved then you can either delete it or you can edit it so it says add new but then the form will change i'm going to edit here so then you edit the existing appointment then if you want to schedule one so we can see on the 20th there are two one has already been reserved i go down here and select the 20th i can see one of those has already been reserved and i can reserve the other one you can see the interface changes even though i'm only writing c sharp code i won't be writing any javascript but we will have an interactive interface put the information here add it and then you have your appointment here and if i go back to the admin i can see i don't have those actions so i can't delete it i cannot edit it because someone has already reserved it and if i go back to the home page if i delete cancel my appointment now go back this available appointment is now available again so you could delete it or you could edit it and again we'll be building this completely from scratch including the database before we get started though there are a few things i want to cover my development environment that i'm going to use is windows 10 using visual studio 2022 the community edition and mysql workbench for the ui and one more thing that i want to mention is since this is a scheduling application they can get very complicated there are many things to think about different time zones maybe you think your users will only be in one time zone and then later it turns out there'll be multiple time zones you have to think about daylight saving time and also universal time coordinated universal time you'd think the initials would be cut but they're utc because and this is a little trivia the french version is would be tuc and the compromise was to call it utc i'm assuming you have visual studio 2022 with net 6 installed and also mysql as i mentioned i'll be using mysql workbench you don't have to use my sql workbench though if you don't want to but you need to be able to connect to my sql and have it installed you may need to download apache as well depends on what your setup is i'm using windows 10 but you don't even have to use windows to complete this video you might use windows on a virtual machine or something like that whatever your environment is just make sure that your your setup and you can connect to my sql i'm opening a brand new project here create new project visual studio 2022 this is going to be a blazer server app if you haven't opened one recently just type in blazer make sure you choose blazer server app next i'm going to call this app laser laser server app and then for the solution i'm going to call it laser server with my sql dot net 6 authentication individual i'm choosing authentication here but in this video i'm only going to focus on the crud operations if there's a lot of interest i'll go do a part two and add in the authentication and some authorization have some different roles have a separate admin role things like that create and i'll run this project just to show you the app already works we're not going to use all the pages that we get with this project but i just want to show you as soon as this opens up this is in case you haven't opened one of these projects before some of the pages that come with it so you can see here in this project homepage counter page and the fetch data this is random weather data i'm not going to use these pages just wanted to show you close that out so first thing i'm going to do is open up this nav menu in the shared pages and i'm going to copy the first div because we'll reuse the home page and comment out these first three don't need those leave home as it is and for the admin we're going to with the wrench symbol this oi wrench these are coming from in here this iconic so we have access to these icons this is going to be admin and the link to the page is admin then in the pages layout let's add in some javascript bootstrap javascript that is get bootstrap.com get started and this is just because i want access to one component and bootstrap is a quick way to do it i'm gonna put that script in right under here and then one more thing back in the nav menu let's change let's see blazer server app let's change the title here we'll call it scheduling app save those all right we already have our index.razer but let's create one more let's create the admin.razer razer component if this is off the screen i'm going add and then razer component choose razor component i'm going to call it admin add alright we have a razor component if we look back at the other page index.razer you can see we need this at page so let's add that save and then let's build this again just to make sure i have the two pages that i want there's home and there's admin okay that works now we need some data we need a way to access data to populate these pages so the first thing that i'm going to do is build a class i'm going to create a new project add new project search for class library class library that targets.netstandard.net core next we'll call this data access dot net 6 create and we need to add in some dependencies manage nuget packages browse first we're going to search for dapper install it and then mysql mysql.data not mysqldata.entity framework core we do not want that one we just want mysql.data accept it all right we've got our dependencies there and now let's create a new class let's delete class one add class or you can go to add new item class add class we'll call this mysql data access i'm going to create a public this method here async task lists we're going to call this load data two two generic parameters there string the first string sql this is our sql statement delete from select star things like that and then the connection that's string we connect to the database control dot this using statement is going to our connection it'll open and close it as we need it idv connection an extra parenthesis there ctrl dot using system data control dot again okay and then a variable called rows equals weight connection query async this is where dapper comes in and we're going to return rows to list ctrl dot using dapper all right this this is how we're going to access some data this is our first this is how we load the data and then we need one more method and this is public by the way let's make this public we need a way to save data we'll take in a string [Music] sql just like above parameters and a connection string using return connection dot execute async sql parameters and with these two methods we'll be able to load all the data from the database the mysql database that we're about to create we'll be able to save data update delete etc and one more thing i'll do here create an interface this is optional but you might need it later depending on what you do with this project if you want to use multiple inheritance you want flexibility to be able to do unit testing things like that just like that we have an interface see it right there let's update our program.cs file go in here and say builder.services add singleton and we want to add in our interface that we just created i my sql data access which will implement my sql data access and we need using data access there we go now let's update appsettings.json we already have one connection string in here because i selected the authentication so in any framework core and sql server they're all set up for that so i'm going to leave this default connection alone i'll leave that right there and we're going to create another one and this will be called my sql connection and the server equals this is going to be localhost that's where i have it 27.0.0.1 or equals 3306 database equals blazer schedule i haven't created it yet but i'm about to user id with a space in there equals user id i have some credentials here that i'm using just for a demo video password equals password and that's it that's our connection string for connecting to my sql now we need some models we'll have two database tables so the models we'll create one will be for available appointment model and one will be for appointment model so let's create those i'm gonna go in here i'm gonna create a new folder add new folder models and here add class first one will be available appointment model prop tab twice but first we'll have an id and for the next one we'll have a boolean this is going to be for if the appointment has been reserved then we're going to have some date times drop date time start date date time start time the reason i'm creating a separate start date is because it's easier when we start creating the interface having just a date you'll see why i'm doing this you don't have to do it that way but i think it might make sense when you see we need a start date start time in time and then let's do uh some more date times i'll do a created app and an updated at so there's our first model and we're gonna build a database table that looks just like this second model let's add another class this is going to be called appointment model id available appointment id this is going to be a foreign key based on the id of the other table we'll have a string here full name string not empty and we'll have the reason for an appointment string that empty then we're going to have a start time oh this is the date time start time in time and just to speed it up a little bit we need a created at and an updated end also so i'll just copy those from the other model so now we have our two models and we need to create a database just like that for those and for that i'm going to use we'll use mysql workbench all right i have my sequel workbench opened up here and i'm going to create a new schema and it will be called blazer blazer schedule let's apply that apply okay let's see okay there it is we don't have any tables yet let's create a new sql tab here and let me double check something here this blazer schedule look in our project here just to double check appsettings.json.blazer schedule so that's what the name of the database so we need to create some tables in here and set as the default schema okay so blazer schedule and we need to create a table laser we really don't need the tight blazer schedule every time here but just to be sure blazer schedule dot available appointment so that's going to be the name of the table and inside where we need id which is an int not null and we want this to auto increment and that's going to be primary key then we would need [Music] just a boolean it's okay if that's null start date which we need as a date start time is a date time not know say start date say not no for that one too end time date time null and create it at daytime that can be no updated app daytime can be no right that it's just like the model typically i use lowercase letters when i'm creating my sql databases and but i want to match this.net project so id reserve star date start time and time okay there's one see if we're missing anything all right let's create this table okay so let's refresh table available appointment okay there's no data in there yet but we have our table all right let's create one more we can reuse a lot of this so i'm going to copy it this one is just going to be called appointment all right we still need an id but we also need available appointments id and and we'll say that can be null don't need reserved we need full name save our char 100 not null and then we need a reason for the appointment donate a start date start time end time created at updated at primary key is id but we also have a foreign key in this one a foreign key is available appointment id which references available appointment id and then we're going to say on delete restrict and the reason for that is if there is already this available appointment id if if it's been has been used then we don't want to be able to delete a record in the available appointment table meaning if somebody already scheduled an appointment we don't want someone to be able to delete that appointment or delete the availability for the appointment because it's already been scheduled it's already been reserved and that's what this field is for reserved so if it doesn't make sense yet it probably will as soon as we build out the ui all right let's take a quick look at this make sure we have everything id available appointment id full name reason start time in time created at updated at we've got our primary key of id and we've got our foreign key all right let's run this we've got another table let's refresh it so that's our appointment table okay no data yet so let's add in some data now so in the available appointments let's do that so let's insert some data so we'll say insert into blazer schedule dot available appointment reserved we don't need to add the id because that's that auto increments start date start time [Music] and time created it updated it values true or reserved we'll say this appointment has been reserved true and we'll say 2022 01 15. for the date and then for the start time we'll say 2022-01 dash at 1500 and this is going to be universal time universal standard time coordinated universal time i think is the official title and let's do so we've got reserved start date start time in time so let's copy this one but change the time we'll say team 30. then we can do now for created app now for updated at sure i can't remember if true needs to be capitalized here if it matters i'm not sure that it matters let's leave it like that and see what happens all right let's run this let's check available appointment okay we have a record now so that worked okay now let's put a record into the appointment table as well so we have a record inside there say insert into laser schedule an appointment available appointment id full name reason start time end time created at updated at values one my name we're going to assume that these appointments are for an auto shop to take your car january 15th 1500 and then put the end time in here 2022 january january 15 15 30. and then now now all right let's run this so we should have a record in the appointment table now okay we've got an appointment and an available appointment so let's test something now see if we can delete from our available appointment say delete from laser schedule dot available appointment where id equals one see if this works did not work cannot delete or update apparent row foreign key constraint failed okay that's what we wanted to happen and again if this doesn't make sense yet when we start building out the ui i think it will definitely make sense so now we have built out the database from scratch and a quick recap of what we've done so far where probably about halfway through and we need to start building out let's open up visual studio again so some of the things that we've done you know we adjusted the nav menu here we added the bootstrap in there so we can have access to one of the components that uses javascript even though we're not going to write any javascript and we added in a class library right here so we can using dapper so we can access mysql which we're about to test out make sure that works we created a new admin component this right here admin.razer and we updated the program cs file we updated our app settings.json with our connection string we created the models and we we have the models that look just like our database got those and we created our mysql database and now we're going to start building out the interface on our two main pages we have the home page index.razer and we have also our admin page so first thing i want to do something uh let's let's focus on the admin page first i want to i'm going to focus on that first so we look at our admin dot razor page we need to make sure we have access to the data library that we created for one thing so let's let's make sure i called it data access probably could have called it data library but anyway let's make sure we have access to that data access let's just say at using we need our models so our blazer server app models laser server app right there is our name and models then let's inject our interface mysql data access we'll call that underscore data we'll also inject eye configuration config take out this admin and i want to put everything inside of a div class container this is a bootstrap class and then we're going to have various sections so this first section this is going to be call this the available avail for available appointment section we'll put an h3 here gonna say current available appointments and then i'm only going to show this in the ui i'm going to create a table to display the appointments but i'm only going to show it if we have available appointments so i'm going to say at if available appointments equals null if it equals null then we're going to display this paragraph it just says loading else this is where we're going to put a table in first let me create this available appointments so inside the code section this is a list available appointments model it's called available appointments and the way we're going to get those appointments load the initial data here we're going to create this method here protected override async mask on initialize async we'll create our sql statement select star from available appointment and then we'll say available available appointments equals a weight data code data available appointment model dynamic and then we need our sql statement we just created an object which is blank in this case and then our config dot get connection string and we want to use our my sql connection string all right there's our uninitialized async so when this page initializes we should if there are any appointments which there's there's one because we created it it should be loaded up in here into this available appointments so now when we create a table and display it you should see that one appointment so let's create the table so we can see it and we can use bootstrap classes so let's create a table here table and then class equals table table striped i got my quotes there okay go ahead and i'm gonna add some styles in here in line i'll probably end up creating a separate css file but for here i'm just going to drop it in line and just to go with the the blazer purple theme i'm going to say background color one four one one e five e should be one four one e five e okay and then color white all right table row and some table headers so let's show the start time end time whether it's reserved or not and some actions stable head now let's go for the table body this is we're using that for each table row table data say at a dot start time at a dot in time at a dot reserved and then here i only want to show i want to add in some icons here to be able to delete and to edit but i only want to show these if it's not already reserved so i'm going to do table data but we're going to put a if so at reserved equals if it equals false then we want to okay let's put a span here let's say id equals delete appointment let's create a function in here on click equals arrow function delete appointment we're going to create this we're going to send the id and then we want to put in that icon so we'll say class equals oi y x and let's put some margin on the end of this one so the next one's not bunched up next to it too close margin end three and then we'll do one more i've i've got the wrap wrapping turned on that's why it looks a little funny and we're going to say this is going to be similar probably could that's not too much typing id equals edit appointment and then at one click equals edit appointment class equals oh i o i-pencil uh this is wrong this align content around that's not i don't know that should be o-i-o-i is what that should say why dash pencil span okay however i'm going to comment all this out right now because i don't have those methods yet and i want to build this and check it out see what it looks like see if we have any data so we've got the project opened up home page let's go to the admin okay that didn't work got an exception so let's see what happened i'll move this up see what we've got unable to connect to any of the my to specify my sql host okay so parameter host cannot be an empty string so let's see make sure this looks okay my sql connection string let's look at appsettings.json user id because user id password equals password port blazer schedule that's right let's look at this on initialized async all right i see the problem this should not be my sequel connection string this should just be my sequel connection because in appsettings.json that's what i called it my sequel connection so that's what i had wrong right there so now let's try this again now if i click on admin okay there it's working now so you can see we have our one appointment that's the one that we created it's reserved we have the actions commented out so we wouldn't be able to see them anyway but i commented them out because we don't have the methods written yet and it wouldn't build so there we go we're so far so good let's close this out our first method works so we can get the data but we don't just want to be able to look at it we want to be able to delete it we want to be able to edit it things like that so let's go back and that was our first thing we can do is we can start writing these the delete appointment and edit appointment methods before we create the edit and delete methods we need a way to insert an appointment so let's do that to create a new appointment i'm going to say insert insert appointments so say private async task insert appointment then available [Music] appointment model a equals new available appointment model okay start time equals new date time this is going to be a date time object and i think let's start building out the ui i want this method to make sense as i write it so let's build out more of the ui before that so we need a new section in this section we'll call this the create appointment section put some margin on top there and then we'll have two two different sections one will be for when you're creating the appointment one is for editing so at if create status so if create status equals true so those are going to be some booleans there so let's let's create those in the code section so the first one will be create status normally that one will be true and edit status that one will equal false and we'll change it to true whenever someone clicks the edit icon so if create status and we'll have an h3 add new available appointment then choose this edit form the model is going to be we'll create a model called new appointment and then on the valid submit we're going to call that at insert appointment all right and i'm going to add this in here i didn't i didn't use any of this validations in the model but in case you want to i'm going to add this in here [Music] the way you would use it is if you're in your model if you add in something for example here if you add in something and put it required in the form it will be required but i'm not going to do that here that's something you can play with you've never used this at new appointment need to create that so let's go back into the code section and we'll say private available appointment model new appointment equals new available appointment model so there we have okay our model is new appointment and on valid submit we want to insert appointment okay let's start building out this form so we're going to have a div here put some margin on the bottom and then i have a label date class equals form label date and then input type equals date class equals form control id equals start time actually id is date for this one and then at bind value so this is two-way binding and bind value equals at find value equals at appointment that date all right let's copy that down so there's one all right and okay so we have new appointment and we have ad appointment i'm creating another model for ad appointment because from my experience it's easier to do to have a separate class down here that i can work on with dates and then create a daytime object with that rather than because it's harder to display in the ui if you use this the form the edit form that we have with blazer it's easier for me to use regular inputs than the inputs that come with the form and when we get when we work on the home page on the main interface you'll see what i mean by that because it's easier to use for what i'm doing on that page than what i'm doing right here so i'm going to create another class here let's let's call it add appointment so private at appointment new ad appointment so we have a date time not working there start time and we'll have end time okay so now we have this new class ad appointment and an instance of it right here at appointment so that's what i was doing right here at appointment.date at appointment okay so now we need we just need one date and we need to need start time and end time so let's say start time time start time so even though i'm using start time or date time rather which has a date and a time i'm going to build it with the date they choose and the start time and the end time so that will it'll make more sense in just a few minutes if it doesn't already start time and then we need end time this is for end time types time id equals in time at the equals start time there so this should be start time should be in time okay and then we need to submit this form so let's create a div for that and this is going to be i want it to be a block button so class equals the grid tap two button type equals submit class equals button button primary margin top three and this is add and then we'll have an update one when they're editing all right there so our form should be done there so let's go back to this insert appointment now so now this method should make much more sense the way i write this now that we have the ui built out so start time equals new date time so this is i'm building a new date time object here so it's going to be add appointment dot date dot year comma at appointment dot date dot month date dot day add appointment start time dot hour deployment.starttime.minute and then add appointments a minute and then we're just going to say zero for the second there and then taking that entire daytime object to universal time okay there's one that was the start time and then for the end time since it's mostly the same i'm going to copy it we don't have to type out so much again and then we just need to say change it to end time then in time.hour in time.minute zero to universal time so start time end time and then start date equals add appointment that date and create it at daytime.utc now and then update it at see now again and then reserved initially equals false because we just created it all right and shift tab move that back all right then we need our sql statement insert into available appointments start time end time start date created at updated at reserved values at start time actually we can copy all these and just put an ad in front and we're going to say await data dot save data our sql statement a right here config dot get connection string make sure i called the right thing my sql connection and then we'll refresh the page by calling this uninitialized async again and then let's reset the form by available and add appointment close new ad appointment all right insert this the rest of the methods will not be that long if you're following along typing along you may think that seemed like a lot of typing a lot of work but it's really the rest of them won't be this bad and one of the good things about dapper is you can see exactly what you're doing think about exactly what you're doing so you lose out on some of the magic that happens behind the scenes with things like entity framework things like that any framework core but which is can be very useful in handy as well but this is good you can see exactly what you're doing and then you can sometimes it's easier to find errors and fix things when they go wrong all right let's save that and let's build a project let's see if we're missing anything so let's open up go to admin and we've got a form here so let's let's see let's say january 20th 2022 start at 8 a.m 8 a.m and then end at 8 30 a.m add it in there voila it works so we've got a brand new appointment in here reserved is false and that method works so that's good we can add but now that we can add appointments and you can see the form reset um we want to be able to delete and we want to be able to edit so let's work on those now so delete appointments let's create our sql string for the delete i'm gonna use a try catch and the reason is because we have that foreign key constraint so if it doesn't work there's different things we can do you know if we if we hide the the icon for delete this won't even come into play because they won't be able to delete but there's another option that we can use that i'm gonna i'm gonna show you so that's what i want to do with this try catch so we'll say await data.save data and then the sql new object id equals id and then config get connection string should be my sequel connection uh config dot get connection string okay then we have i'm going to create another boolean called delete error message and initially it will be false but that's gonna be let's create that there's also going to be if there's a in the try catch it'll catch the exception so we're gonna say error message or db delete exception that's going to be a string we'll call it db error message it's a blank string at first so let's go back to the delete and then so let's await this is refreshing the page if everything is good but if there is an error the exception we'll say delete error message equals true and [Music] tv error message equals exception to string and we'll put that in the ui so we'll put that i think right under the table probably be the best place for it so let's get a bootstrap alert for that it's gonna do boot up alert and we'll say danger let's she's the danger so we'll say at if delete error message we'd show that right there and then also in bootstrap let's look in their components let's use uh let's see which one will be good edge button group card collapse let's use a collapse yeah collapse we'll drop that in here okay but we don't want let's see here we don't need two let's take this one out so this change right here to say show error message say delete action failed and we can delete all this here and we're going to say at db error message now let's let's take away i'm going to uncomment this okay uncomment and then normally we would want this to say false but i'm going to change it to true temporarily because i want to test out edit appointment let's create a really quick so we don't get a builder once taking the id for this one too this is a task all right that should be good enough to build admin okay so we have right here says true so if we delete it says delete action failed and show error message show error right that didn't work so let's see did i put the javascript in there i thought i did so that should be working let's look back here copy that there so that should be anyway that should pop up we should open up i'm not sure why it's not opening up there let me look back make sure i didn't miss something in the ui so at tb error message i'm gonna take all this out and just drop it in a paragraph under here just to verify so let's see if that hot reload works let's try to delete it so there it is anyway i'm not going to waste any more time on that javascript my bootstrap button but there you'd have the error and this is one way to do it but maybe the best thing would really to be to just take away the button so you can't even delete so you won't see that error but that's up to you however you want to handle that i think what i'm going to do i'm going to change this back to false so this won't even come into play i'll leave it here but it won't even matter now because you won't even see the option so it doesn't matter now this one we should be able to delete this one because it's not reserved and there it goes so we're able to delete that one so let's go let's go to the edit now now edit is is going to be more involved we're going to have two methods for edit there'll be two separate functions for editing and the reason for that is the first when you go to edit we're just going to use that function to set some things they add appointment various things like that and then update appointment so when you edit the form will change and then the next one is for actually updating so the first thing we're going to do i'm going to say create status equals false normally it was true we'll say edit status equals true creator sql select star from available appointment where id equals and then this appointment dynamic sql new object id equals id config dot get connection string then another variable here appointment being edited equals id and we're going to use this several times so let me drop that in here all right disappointment we need to create that so that'll be another list i'll say list available model this is going to be the specific appointment that somebody wants to edit we need to track which one that is so add appointment start time equals appointment and it's a list but it has zero or what has one item at the zero place so this that start time dot to local time remember we're saving in the database as universal time so we need to change it to local time start time and we can copy this say end time and the date equals this appointment zero let's start date so with this right here this allows us to change from the create form to the edit form and to populate it with the correct data and then once we do that we want to be able to update the appointment so we need a function for that and this should be the last function for the admin and then we can i know this is ends up being a little bit longer than i thought it was going to be the video i think we've been going for almost an hour and 45 minutes but we still have the rest of the interface so this will be full credit operations right here but hopefully you won't stop here you want to keep on going and watch what i do with the home page because there'll be some things to learn there as well but this should be the last last function that we need here update appointment for the admin section so this is private async ask update appointment and then we're going to take our available appointment model a again equals new appointment model okay start time you know what let's it's a copy we should be able to copy most of this no reason to redo it i should all be except reserve will be true now shift tab let's double check everything here reserved is true we're updating it so created that we don't need that we've already created it start time end time we need the id though id equals appointment being edited that looks good so we need our string sql update available available appointment set start time equals at start time end time equals at end time start date equals at start date updated at equals that updated at preserved equals that reserved where this part is very important with the where are you going to be changing every single appointment where id equals at id then awaits data we're going to save data sql a big connection string mysql connection let's refresh the page with the uninitialized sync and then we can reset the form and then we'll change the ui so we'll go back to the beginning create status equals true and edit status equals false save that and build so let's see what we've got now oh okay we've got an error let's see hi my sql data is the type which is not okay let's see what we got here okay so what went wrong there disappointment string sql disappointment equals this should be sql not sure what happened there all right let's move this over get this in the screen so we'll go to the admin we've got okay let's add another one in here so let's say january 25th 26 2022 12 pm [Music] until [Music] 2 pm add that in okay so since it's not reserved that means we can delete it or we can edit it and let's click edit it's disappeared all right it's not exactly what we wanted to happen so let's see you click edit create status equals false edit status equals true so i just have the create form in here i need to put the edit form in here no wonder so let's put the edit form in here section and we need to put the variable that we created so that was edit status so let's give it let's give this an id we'll say today equals edit appointment class equals mt5 margin top five and then at if edit status and then we'll put an h3 you know what let's copy this no reason not to so add new it should say edit existing and we could use the same form if we wanted to we could we could instead of creating separate methods for editing and updating we could just put some logic in there but it can also get messy when you try to do too much with one form or one method really depends on the use case use separate ones here so at this is going to be at update appointment we're not inserting we're updating this time at update appointment edit status edit existing model is still a new appointment appointment.date start time and time all this should be the same all right save that i do want to do one more thing though all right let's stop to i want to create some custom css really fast let's do css add this is off the screen then i'm going to say add new item and then click web content style sheet there we go and i'll call this let me call it appointment.css rename this appointment.css let's make sure that we have it in layout so it should be in the css folder this is the one i wanted to copy this one right here paste it over that one css slash deployment.css save that go back to admin let's go back to the appointment css so let's put inside since we use those ids delete appointment cover and also edit appointment hover cursor pointer it's good i'll probably add some more styles in for the home page but this is good now i just wanted to so let's go back let's build this again check it out see if our pointer is working and the edit form so let's get this on the screen and go to admin now if we go to edit it says add new if we edit edit existing okay that's working and it's got the right well start time in time so okay you can see right off the bat here the times are not the same and the reason why we can fix this but the reason why is because the database the times are we've got a difference between local time and universal time is what we have here so let's refresh that so 5 pm to 7 pm is what it's showing in there but really it's not what we set up for so let me we did 12 to 2 pm so five hours difference i'm in the east time zone so that that's right so let's close this for now let's go back in to admin and let's see see what we have going on so start so the times that are showing that's we want to display to local time to local time that makes more sense so when it pulls it from the database you're looking at whatever time zone that you're really in and for a production app as i mentioned in the beginning you really have to think about how you determine people's time zones do you require someone to create an account and then set their time zone or do you use this local time different things like that are you accounting for daylight saving different lots of things to consider so date times it's not an easy problem to solve although many people have solved it it's not easy it's harder than you think going into it so let's make sure let me look at this form again and make sure that we're got everything let's build this again and look at it see what it looks like so if we look at the admin yeah that looks more like what we reserved so if i go to edit yeah that's right so i'm going to edit this to 3 p.m and i should have changed this button to say update all right let's see what happened there error in your sql syntax okay so first let me change this to edit or update rather since this is the update one let's go look at update appointment so let's see what i missed in the sequel uh update available appointment set start time equals that start time in time equals that end time start date equals at start date updated at observed equals that reserved alrighty was id that looks right let me look at that message again so we go to edit 3 p.m reserved equals one line one so reserved equals one start the updated it doesn't tell me exactly what it is okay i figured out what the problem was in my start date uh or let's see it was the start date equals at start date i had a space between this ad symbol and the start date and speaking of that just in case something is confusing about the sql statement to anybody we're saying start time this is the field in the database equals at start time which is coming from here so at end time means this end time just in case that was confusing to anybody so if i go back and show you that it's working now so i'm gonna uh well it's reserved now so can't edit it but let's do let's do uh as a matter of fact i need to change that because when you edit right here reserved equals false or don't even need to change it really because if we're editing based on how we have the ui up there we you don't you cannot edit if it's reserved so we could either say reserved equals false here or we could not even change it because that one if we look back when we created that table we said reserved can be null so we don't even need to change that at all so i'm going to take that out and then here we won't even use reserved right there save it and let's go back to blazer here make sure this is in view so let's create a new appointment say january 22nd 2022 at early say 5 a.m to 5 30 a.m at it and then let's edit it from so it'll go to 6 30 and now if i say update all right let's go we got another error see what this is where id equals four all right let's see what this is oh comma do not need a comma there it's got to be perfect remember computers oftentimes are they only do what you tell them to do and if you and they only understand certain things you put a comma where it's not supposed to be usually instead of being smart enough to know it's not going to work it thinks you really want a comma there and then that doesn't work so computers are only as smart as we program them to be so let's try that again let's open that back up let's reload let's edit this form 630 update didn't look like anything happened let's try 6 30 7 30 update okay so that's working all right so we have full credit operations here we can create read update and delete but we just have the admin so far so if we want to go a little bit further i i have something for the ui that i want to build out on the home page we're about two hours and seven minutes into this video so if you if you're still with me thank you and we'll continue we'll go on to the home page there's a lot we can do on the home page so let's do that right now i want to take about 20 seconds and mention this website here for ghost.academy for taurugos language academy this is a website you can use to learn spanish i highly recommend learning a foreign language even if it's not spanish but if you want to learn spanish then i recommend academy let's go let's change this index go ahead and stop that for now let's go let's open up our index.razer and that page is good at using our data access library i called it data access probably would have made more sense to call it data library at using [Music] blazer server app then we're going to inject that interface that configuration call that config all right and this this page will go a little bit faster there are there's less to do less functions to write to be a little bit more straightforward but i hope you learned something with the admin so let's also do we don't need any of this we do need the at code section well well let me put that in there so i don't forget focusing on the html first let's put everything in a container and then we'll have separate sections here this first section id equals appointment section and we'll have a section the next section we'll call it step one and we'll have another section we'll call it step two we'll have one more section called step three and we'll show the sections with those boolean values and that's all the sections that we're going to need one two and three and we're going to need to bring some data in here so we'll say list and appointment model this time called appointments but we're also going to need the available appointment model as well available because we want to show the appointments available so the user can select the one that they want so cannot be found let's look at ah models no wonder and then just like before we're gonna need to be able to track certain appointment missing a letter there it is i'm going to go ahead and create some of these variables that i know we're going to need we're going to need a new appointment when they're [Music] reserving an appointment and the user is going to select a date so i'll go a little bit faster for this section than i did on the admin i think everybody uh you're starting to get the idea if you didn't already know how laser server works so i'll go a little bit faster and we're gonna need an id and we also we're going to need to create some booleans that will help us change the ui so we'll say step two ready it's false and i'm going to create this toggle it's going to be used as kind of a workaround to reset a date input and let's get our initial data here let's pull it from save a little time so from appointment right and then let's see if we can borrow anything else from admin let's look at this table available appointments what are we calling it here appointments so let's see here i think we can borrow it we'll just change it we'll change what we need to drop it in there so it's just appointments appointments okay is it saying unclosed no matching intake let's take all this out for a minute section so section how about i just take everything in this section and then take out what i don't need so we'll call this my scheduled appointments if appointments this is appointments don't need that don't need this and we do want to leave delete we want to show name reason start time and time start time and time and then one more we're going to say we're going to call that cancel add our full name a dot reason start time end time delete appointment id but we also want to send available appointment id the deployment doesn't exist yet okay take all that out reserve we don't need let's create delete appointment and then see if it works then we'll await data save data sql new object id equals id id config get connection string my sql connection should be sql and it's actually something else we need to do here because when you when you delete an appointment then it no longer needs to be shown as reserved in the other table so let's say we're going to await a method called cancel reservation reservation or available appointment and we're sending the id with it so we'll create that one but let's say reset the date input and the reason even when you're deleting you would want to do that is because let's say you're you're in the middle of you're you're going through ui looking at the available appointments and then the list is is at the top so you delete one then you want everything to reset so that the one you deleted will also excuse me so the one you deleted will also be available so toggle equals toggle and this is going to be up in the date input it's going to be a key in the data input which i'm going to create soon and then step two ready goes false step three ready equals false and then refresh wait all right and now let's create that cancel [Music] reservation for available so we'll say string sql equals update available appointment set reserved equals false where id equals at id and the weight data save data sql new c equals cancel id config. get connection string mysql my sql connection um let me make sure so i've got a s little q and a little l it's a big q and a big l to refresh all right let's build that and see we can delete now loading okay nothing there we don't have any appointments there yet for some reason but there should be something there so let's see if appointments equals no so appointments no appointments should be appointments let's build it again connect all right let's see here appointments list appointment model oh copied and pasted and didn't change a couple things so there we go let's build it again all right we have an appointment there my scheduled appointment now let's see we can delete it well first let me look okay it's the 15th at 10 am 15th at 10 a.m and it's reserved right now so let's see if our logic was correct we deleted it go to admin okay it's not reserved so now we're working so now we don't have any appointments so let's create the interface so we can schedule an appointment because who wants to create appointments by writing the sql out every time and creating an appointment like this nobody wants to do that so let's create a nice interface we don't have to worry about making it too beautiful i think it looks good good enough for a demo you've got the purple matching so let's go in and let's create a method a function in here let's do actually let's build a ui a little bit so we know what we're looking at so we've got this section here appointment section that section is done i don't think there's anything else we need to do in there uh but let's step one here let's let's drop in something just so we have a little graphic in the ui i don't know what that popped up let's see here bootstrap bootstrap icons so icons.getbootstrap.com and then i'm going to look for one called tools with the idea being tools you're gonna work on your car and then i'm going to grab the spg now there's pros and cons whether you use svg or you actually install so if i go you know you could actually install bootstrap into your pot project and then it makes the code a lot cleaner instead of putting all this svg code it takes up a lot of space but i do like svg sometimes because they're they're easy to drop in and you can change like i'm gonna change i'm gonna change the size of it right here and also no matter how big it gets it's always gonna look good i'm gonna put that in there but you know it hurts the readability of your code is the only problem all right let's keep building this thing out so we're gonna have a under that say actually i'm gonna say have a paragraph class equals lead are you ready schedule a new appointment and then put an h3 here step one select the date put up another paragraph in here text muted if there are any appointments they will be played under step two right and then let's put an input not what i want key equals toggle this is what i'm using to reset the date i found that to be the easiest way and we're going to have a function down there called show available appointments and that's it for this section step one that's all we really need is show available appointments let's create that put it right under here so these will be all the appointments that were made available using the admin section and it's going to be on on change so when the input is changed it's going to be sending it's going to call this function and send some data so we'll say date value equals arcstat value then we'll have some sql here sql string equals select star from [Music] available appointment our start date equals date and then available appointments equals weight data code data available appointment model dynamic sql new object date equals date value and then config get connection string my sql connection and then again we're going to say available appointments equals available appointments that order by we want to order this because you can make appointments available out of order i can make an appointment available at 10 am and then the next record in the database i might make an appointment on the same date at 8am but i want to show in the ui to the users in order of course it's easier to look at something than when it's in order usually so i'm going to say order by and next start time and then i'm gonna send that to a list and then i'm gonna say step 2 ready equals true then i'm going to say if available appointments dot account equals zero no appointments equals true else no appointments equals false all right show available okay so now step two we said it was true in there so step two is gonna be and if step two step two ready we're gonna have an h3 this is step two select the time that you would like and i have another if available appointments equals no else now we'll show the available appointments also use a for each pay and available appointments i'm going to use a unordered list give it a class i think i'll put a little bit more css so we'll say class equals available appointments list and we'll say add if a dot start time dot to local time it's greater than daytime and a is not equal true so as long as the state as long as the [Music] start time of the available appointment is not in the past and it hasn't been reserved and then we're going to have a list item not what i want there not reserved we'll say at a dot start start time to local time and i'm going to format this so we don't see the seconds on there and also we're going to have a button an arrow function i have another function down there called select appointment send the start time [Music] and the id the class for this button is going to be uh class equals button and then reserve is what it will say and then we'll have an else elsif a dot start time to local time it's greater than daytime now if it is reserved i'll just copy this and then i'm going to drop a span in here some iconic icons in there so why and that circle with a line through it just got this band then i'm gonna have a non breaking space and say already reserved so select deployment let's create that one go right here private async task select appointment date time date time okay so selected selected date time equals date time appointment.start time equals daytime step 3 ready equals true selected available id equals available id say did i not create that selected available id selected avail oh yeah make sure i've got it spelled the right way there it is and our sql select star from available appointment alrighty equals that id all right disappointment wait dynamic sql new object id equals available id connection string and mysql connection are we getting really close let's see what we have here let's build this again make sure we don't have any errors see what the ui looks like all right so we've got our tools in here my scheduled appointments i don't have any right now so let's see 22nd and the 26th are available so let's click on the 22nd uh so i need a button there that doesn't look right let's look one that's already reserved so the 26th library reserve okay that looks right but let's look back up here reserve button class equals button say button see what it looks like we could change that color we could change it to uh purple color i'm just going to leave it right now so reserve okay when you click on reserve see what happens you click on reserve select appointment select appointment selected it we've made step three ready equals true but we haven't created step three ready yet so let's create it right now say add if step 3 ready and then we're going to have another h3 here we'll say step three this is the final step so we're almost at the finish line for everyone that's still hanging in there there's not too much longer to go enter your information and submit and then we need a form so we need a name let's do a edit form model edit form and then the model is going to be new appointment and on valid submit we we're going to do that save appointment and really we just need after this form we just need two more functions because we're going to need this save appointment and we're going to need one more that is going to mark the other table as reserved so save appointment there's no space there i'm going to put this in just for your reference data annotations validator and then validation summary all right i'm going to close the edit form so let's build this out quickly so we need a label for name class equals form label full name then input text class class equals form control margin bottom two id equals full name that bind value equals new appointment at full name all right let's copy that it's going to be reason reason then we're going to have an input date it's going to be hidden and the ad bind value equals new appointment start time i'm gonna copy this last input and this is going to be appointment time it's just going to be a regular input and it's going to be disabled so you can read it but you can't change it and the value is going to be at selected date time to local time and i'll create that just in a minute and we need a way to submit this also so let's get the add button all right and let's create our final two functions so we're going to need a save appointment that's what it was just to double check save appointment we'll put it right under select appointment so private async ask save appointment and then our appointment model will say new appointment model available appointment id equals selected available id full name equals new appointment dot full name reason equals new appointment a reason start time new appointment.start time to universal time and end time was new appointments in time to universal time then created at equals day nine daytime dot utc now and update it at equals utc now shift tab all right string or sql okay insert into appointment available appointments id full name reason start time in time created at updated it values and then we're going to say at in front of all of those at and then we're going to await data save data sql a config connection string config mysql connection and then we're going to refresh the data it's going to grab this and then we want to await another function that we're about to create it'll be the last one reserve available appointment we're going to say step two ready equals false step three ready since we're saving an appointment we're resetting the ui there that's what we're doing reset the date input you set a new appointment uh yeah i think i can call that one right there so reserve available appointment let's create that really fast that should be very similar to this one let's make a couple minor changes so that's supposed to be reserved reserve available appointment we don't need anything there because we're going to use cancel id that we've been keeping track of so cancel id i shouldn't let me make sure cancel id reserve available appointment let's see cancel id yeah we need that should not be cancel id that should be i know we've saved selected available id selected available id when you select selected available yeah when you select the appointment that you want to reserve we're setting that selected available id so update available appointment set reserved equals true or id all right save that let's build this let's see what we've got let's see one 110 down there at selected date time value equals oh it just should just be value just try that again all right let's see what we've got so 22nd there's one available 22nd hit reserve form opens up you can put your name put the reason 5 am is what's available edit and there it is okay let's pick a date that's not available so january 17th all right there's something there's one more thing i want to do we're just about finished here i want to tidy this up just a tad bit i'm going to go into the css file at appointment.css that we created i'm going to add a little bit in here just to make it look a little bit better so we had those various sections we had appointment section we had step one we had step two and step three say margin margin bottom [Music] 25 pixels and let's see what else say available appointments list hit the button in there so we're gonna say background background color three four a zero six four seven they're right three there's no four yeah and then we'll say uh appointments lists button hover colors white transform scale so it gets just barely bigger and we'll say available appointments cli list style none so when there's multiple appointments you don't see all those bullet points those disks then already reserved that we created color should be gray and one other thing i was thinking whenever there are no appointments let's see here available appointments will be shown here if you select a date and there are no appointments available on that day it would be nice to see a message that way you at least know that the search was made for appointments there just weren't any available so we could put that under the bottom of the section we could say for example at if because we created this no appointments that if no appointments equals true then we could drop in an alert so the class will be alert let's do alert info and then we'll say there are no appointments for the selected date all right now let's try this again to rebuild it it should look a little better and then if there are no errors we may be finished after this so we select date nothing's available okay we can't see anything there so let's select uh 22nd it's already reserved let's try 26th already reserved so what is available the 15th so i'll select the 15th reserve it and i'll say my name card will start add it in you can see it there they've all been reserved but if i delete it okay everything is working that was a long video i know hopefully you're still hanging around and i hope you learned a lot um if you've seen what blazer can do and that's just beginning there's so much more you can do i hope you enjoyed the video thanks for watching after i stopped the video i realized a few things first thing is when you look at the styles available appointments list button these should all be available appointment without the s let's move this over a little bit first looking at our styles we have an appointment on the 15th available we'll say now we have our purple button you could change the on hover let me see if i didn't change that one appointment cs delete button editor button hover so we could say background color let's make sure that's there okay there we go so it's not a big deal it's just something i wanted to do so there we go and if we see one that's already been reserved now it's has that more gray color now there are a lot of things you could do with this project you could reset the form after you cancel you could change the way the ui works you may maybe you would have built this completely different than i would have and that's fine if you have any suggestions leave them in the comments hopefully you learned something and i really hope you enjoyed this video thank you for watching

Channel: James Schneider

Views: 10,729

Keywords: .net, .net 6, blazor, blazor server, CRUD, CRUD app, scheduling app

Id: lc_UYcSibDg

Length: 198min 55sec (11935 seconds)

Published: Wed Jan 12 2022