Displaying Files From SQL - The Blazor File Upload Mini Course

this is the uploading file to Blazer mini course so far we've seen how to upload one or many files how associate files with form data and how to store our data in SQL now we want to display our data from SQL back onto the web page we'll work through say up web friendly links loading data and more in this video now this video is part of a mini course around uploading files I would encourage you to click on the playlist Link in the description and use it to go through the course in order there's also a link down there for the source code from this video now if you don't know me my name is Tim Corey and my goal is to make learning c-sharp easier I have hundreds of videos here on YouTube with more coming out each week I have a podcast answering questions developers have and I provide training courses designed to give you the real world training and experience needed to succeed in today's Marketplace you can find all the resources I have to offer at I am Tim corey.com okay so picking up where you left off we have the application running so if we were to run this we can load up we'll see that it actually works we can we could save information to a database we have a database let's close this out and look at our database we have our customer table which right now has just one record in it it has the Tim Corey record okay but we want to go a bit further and actually display the data back onto the web page in fact we use the same index page that's just fine we'll display it below our table or our input table so let's create a new store procedure and we're going to call this SP customer underscore um get all and no parameters we're just going to get all the records so we'll say begin and and select star from dbo Dot customer now I'm violating some rules here um we should probably you know do a quick actions refactoring here um which I believe doesn't load right now for some reason for me but you can expand this out to be all the The Columns and that's usually better than select star so usually when I expand this out if using ssdt that would that would work but um you want to expand this out because you want to have the columns in the right order so you want to have you know ID first name last name file name username in the order you expect them to that way if it doesn't happen all the time but if you're expecting the columns in a certain order you don't get confused or have the application crash if you adjust column order in the database also if you say select star then if we add 10 or 15 more columns we're sending back all that extra data that we're not expecting to need but in our case we're going to say select star it's the easiest it works and it will get us the information quickly so let's execute this we can close it out if we were to come back over here at store procedures and refresh we'll see now we have the get all so we now have the store procedure we need but we need to write some code in our SQL data access we only have the save data so let's create the load data method public async task of type list of type t so when we turn a list of T which we have to say load data of type t and this is going to be whatever model we want and let's let's do this on a new line because we're gonna need to have multiple in fact we're going to do this these right here that'll be the three things we pass in and let's make this nullable oops because you might have a null set of parameters in fact we will because we're not actually going to use any parameters we're going to um just say give me everything there's no parameters to pass in so now we do basically the same thing that this does we copy these first two lines we're going to get the connection string we're going to open the connection but then we're going to say VAR Rose equals a weight connection dot query async and the query sync by the way query async and execute async that's the only parts that Dapper is actually doing just so you know first day of type t so in here let's go a new line we're going to say actually the same stuff is down here so let's just copy and paste this it's the same it's the same basic command it's just that we're saying hey you can return some data some rows and this row is going to be an i innumerable of type T and it is nullable and then down here we're going to say return rows.to list if you want to return a list of type t so we're using generics here if you're not familiar I have a video on YouTube on generics and what they mean but essentially this makes it very very flexible for us to use whatever model you want to get our data in we have to tell it what structure to return our data in so again this is the model that you want to return our data into or I use that same customer model so that's all it took to clear load data but we're not quite done yet because we have this interface the cool thing is we can say control dot on our load data and say pull load data up to isql data access so I click that and now if we open up isql data access we see that it's load data is there as well so we've now moved that up into our interface which means that we can now come over to our index dot Razer page come down to our code let's put it right up here since you know working with it and we're going to say um actually we need to add a private variable here private list of customer model and we'll make this nullable called customers and not put a value in it right away so this is gonna be our list of customers from the database we're going to say private async task load customers this is my favorite part about Dapper and using it is how easy it is to set things up so I'm going to say customers which is our our list of customer model it's nullable equals a weight SQL dot load data of type customer model and then let's pass in our dbo.sp customer dot was it get all I believe so it was get all yep and then we're gonna say default for our connection string name and then null for any parameters that's it that's our entire command to load all the records in the customer model or customer table into the the list a customer model that's it all you gotta do so let's do this a couple times first of all let's do this on load so protected override on initialized async and this is getting a little bit funky so I want you to watch what happens when I type this okay I'm going to say oh wait in space and notice how it took my cursor now if you can see they're not the cursor is now up on line 53 and it's right before task which means if you start typing and you say await space and start typing right away you'll type in the wrong spot so that's a little funky it's something the editor does he wants to put this async marker in the method so if we were to back this out notice that there's no async here so when I say a weight it says oh you just async there so it puts async there but leaves the cursor where it was or where it is instead of bringing it back down to where it was so that's a little bit funky if you see it happen that's what's going on load customers so that's the the little customers so whenever we initialize the page it's going to load the customers and place them on the page well at least place them in this list we haven't actually put a page yet but the other place you want to do this let's copy this is going to be after we successfully save data let's go ahead and load the customers that way we can see the latest value right away but how we actually display this information on the page well it's come up here to after the errors um yeah let's do after the errors we can do it after year four but let's do it after we're gonna say at if customers is not null so as long as you have customers in a list so there's no customers don't display anything but if you do then we're going to say let's create H2 and we'll say existing customers and then we'll create a table and you may have heard some things about HTML and people saying hey don't ever use table and for the most part they're right because table has been abused for years because it was a great little layout thing and so people created tables to do layout and that's not what it's for a table is for data but what are we displaying here data so therefore it's the right call there's a table class equals table and table bordered this is again from bootstrap five it just makes our tables look nicer we're going to do T head um and the TR and then THS so again this is something I taught in the web development master course um how to deal with these properly with HTML5 and css3 and making sure that we're doing things the right way for a modern web it's important to use things like T head and create a row for your header values and so on okay so let's create four of these and we'll say first name and we'll say here last name and this last one is going to be the actual profile picture Okay so that's going to be the actual image that we we store whenever we upload a new user and we already have one of those in um in our file so that's associated with that SQL record and that's you know associated with Tim court so we will be able to see that when we start actually loading stuff on the page so let's go with t body and do a for each so the 4H is going to Loop over the customers so let's say VAR C in customers so we're going to Loop through all the customers we've already done a check by the way to make sure that customer is not null therefore you don't have to do a null check here because it can't be null if we're inside here because we've already done a null check so just to kind of clear that up so now you have for each person each customer we're going to have a TR inside here we'll have TDS and the first one is going to be at C dot ID I use CBS as short which makes it a little easier and a little shorter on our page and let's say the next one is going to be first name and we're doing quick and easy you know uh display of this this last one here is a bit different because it's nullable and it could be if it is not null we want to display it as an image so at if string dot is null or white space because it could be an empty string which also is not a file name c dot file name equals false so if it's not null or white space now again some people say well so you can just put a band character there and not have the equals false absolutely however this right here it's harder to see that this is not it jumps out at you is nor white space looking over here to find Notch is a little harder but this way you can very easily see false so um makes it a little easier to read quickly image SRC equals at and we need to actually pass let's just leave it blank for now we have passed in a value here that is the full path name because we have right now a relative path name that's at the width of this equal to just 400 for now we can you know this would be more more specific but that's um that's fine okay so right now the the image will be blank but it will be there as a placeholder let's start with this first make sure that just Tim Corey is showing up um and that you know it looks kind of right without the image we'll get the image next and so far this looks right we have you know ID of one Tim Corey profile picture let's add uh Noah Macy and let's choose Noah's profile picture and say create customer when we do hey there he pops up there we go and His Image is over in the folder as well so it's working Asia's not displaying profile pictures yet but how do we display profile pictures because the relative path isn't going to work quite yet because the fact that it's not really accessible by the web server you can't say hey go to the D drive that doesn't work like that with web servers you actually have to map it to a path the web server understands now I would highly encourage you and this is another thing we'll cover in the next lesson on best practices we'll talk about now I would highly encourage you to not put your uploaded files in the same location as the rest of your web files because it's a safety concern it's something to make sure that you separate those two things we'll talk more about that later but we are going to put them in the same location because we only have one web server we don't have access to the actual configuration of cash flow we're not going to get into that right now this is supposed to be the demo and so we're going to give them a web friendly location so let's go to uh www root and we'll right click on here and say add a new folder we'll call it uh images you see um we could probably even call it profile images um profile images okay that way it's very very clear what it's for now when it comes to accessing this when we say has saved on the hard drive if we right click and say um open folder in File Explorer this is the path okay it's the temp upload file solution upload files app wwd Root profile images okay we can copy this we can come over here to appsettings.json and we can paste this in and notice that puts a double slash in there which is good so that's the location now of where we want our root of our file storage to be but we've already uploaded stuff and it's already in the database we have to modify database right well no we don't what we can do is we can come over here to our previous location which is storage and just move when this gets copy but move stuff over to it so now T Corey and the two image file locations are there and we'll see that this still works but what we want to do though is we want to have a relative path so we run the web server um it's going to and that's actually run the web server now I want to show you that um you know the relative path is actually a profile images slash T query slash and as let's see SC as s r x f w x dot PNG there you go there's Noah's picture because that's the relative path is this right here but if we were to actually say just dot it would take it from localhost and say dot slash profile images and so on so we want to use that path for accessing it so let's say the web um storage root is going to be dot slash dot slash and that's going to be profile images okay because then we're going to start with our um actually I think it's sorry there we go um I know that way will work and put com at the end and what's going to do is that's going to allow us nope sorry let's go back to this way that's going to allow us to merge those paths together and say okay this dot profile images dot or slash T query slash and then the file name and that will allow us to use a relative path on the web but when we're saving it or actually save it to a real location a real file system location so kind of a mixed bag there um again just to be very very clear I would not do it this way for a production application because this stores in the same place as where Source control is which means that every time I save now this is going to be storing The Source control now I could exclude this folder from Source control using the git ignore file and I can do that and that would work and I could say hey the profile images folder ignore that folder create the folder but ignore all the files in there and then it will just use that folder from now on I could do that but I still wouldn't do it that way I'd probably do it a different way I'd probably store a different location that everyone has access to um a different file system location that we could set up an IIs or in Apache or whatever else and say this is the the uh the web path for it this is the physical path for it and this is where our image will be stored so in a real application it gets a little bit larger a little bit more complex but for our simple application this works even for a small application this would work as long as you just used get ignore to ignore this folder so but as you get larger you want to separate those out okay so we now have this set up now we need to go back and you know actually have a way to you know combine these look or get these locations so let's create a new method private string uh create full path and string relative path that's the path you're going to pass in and we need to do a path that can buy basically return uh path dot combine config dot get value type string red talk to our app settings.json and get the um the the values created which is web storage root so get the web storage root and we're going to combine that with and yes we're going to say you know what we know it's there so exclamation point at the end to uh because it's saying now hey is key no and we're saying no it's not no don't worry about we could do a full check on this we probably should um do a full check to make sure that our app settings are correct but for this specific case we're not going to worst case it gives us some broken links that we have to go say oh we don't have the right path in our app settings.json so we're going to pass in relative path and be done so it combines the relative path for passing in with the web storage route to get a full path um we can create the web path let's call it web path instead so create web path solar source is create web path and say C dot file name so pass in the file name which is the relative path use it to create a web path to this and give us our images let's make sure this works hey look at that we actually have profile pictures for Tim and Noah so if we were to say Dan White and we're going to say upload Dan's picture there we go let's just you know round up the team here Tom Tilley and choose Tom's picture whoops create customer there we go there we go we now have a database that has all of our pictures every time you come to this page we'll have all of our pictures here all of our data loaded from the SQL database and our www root profile Images slash T Corey has all the images in there upload just fine so that it all just works okay so we now have a fully working demo we can see how to upload a file or files we know how to take that and Associate the form data we know how to upload that to SQL we know how to get it out of SQL and display it on a web page how to use the relative paths or web pages versus the hard the full path for the file system for saving and we've even seen some best practices on how to work with this but we're not quite done yet next up we're going to do is cover some very specific best practices around all of this talk through kind of more of the um the the big picture stuff we've talked about a little bit we haven't fully covered yet though some things to look out for some pitfalls to avoid some dangers to avoid when dealing with uploading files when dealing with using uploaded files or uploaded files data and how to work with that properly so that's come up next in the best practice video Until then thanks for watching if you want the source code for this go down the link in the description if you want to see the previous videos it's a playlist Link in the description where you can see all the videos in order and thank you for watching thanks for being a part of this and as always I am Tim Corey [Music] [Applause] [Music]

Channel: IAmTimCorey

Views: 7,643

Keywords: .net, C#, Visual Studio, code, programming, tutorial, training, how to, tim corey, C# course, C# training, C# tutorial, .net core, vs2022, .net 6

Id: pKehZTYB62Q

Length: 24min 41sec (1481 seconds)

Published: Wed Jan 25 2023