Connecting C# To MySQL Using Blazor (Full CRUD Actions)

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
how do I talk to my sequel from c-sharp that's a pretty common question my sequel databases are very popular especially on the web so when you're building a web application especially you might be considering using my sequel fortunately there's a very easy way to connect to my sequel databases using c-sharp in this video I'm gonna show you how to do just that now in case you haven't watched my village before my name is Tim quarry and it's my goal to make learning c-sharp easier my goal isn't to entertain you my goal is to turn you into a world-class developer I do that by making sure that everything I teach focuses on preparing you for the real world if you want to get ready for real-world development we're just starting out or have already been doing it for a while hit the subscribe button to make sure you don't miss a thing ok let's go to visual studio and we're create a new project now since my sequel typically deals with web applications it doesn't have to you can do desktop applications for sure but since it's typically a web application thing let's build a web application now if I'm going to use a web application I'm going to default to blazer server so choose a blazer app here and the reason why is because if I don't know going in what the application is gonna be like or if it's a demo application or if it's a pretty broad range of of reasons I typically use blazer server it's the best kind of overall or well rounded thing to use in my opinion so I'm gonna use a blazer server project so it's called this just let's call it blazer server for the project name and the solution name we'll be connecting to my sequel alright so Blazers server application will configure for HTTP but not for docker support and hit create so now we have an application that's this gods the basics of blazer so if we run this let's just do a quick run here and see what it looks like to make sure you understand what the starting point is all right let's straight out of the screen there you go so there's our blazer server application so we have to counter we can click that we can fetch data if we go back and fetch data again notice this number it's 43 right now and now it's 42 and now it's 22 so it changes every time and go to that page so that's that's just a basics of a blazer server and what looks like right out of box now we have this let's start talking about how to add my sequel now what I'm gonna do in this video is I'm going to show you how to sit up in a class library and one of the reasons why I'm going to do that is because you can take this same class library and use it in a in a different video or a different a different application how about that if you're doing videos you can do news videos too but in a asp.net core MVC application or asp.net core RAZR pages or API or WPF or wind forms or even console so you can use the same library the same code to do data access in any of those it's not a blazer server specific bit of code so if you're thinking about asking the question but Tim how you do this in and name a different user interface I'm gonna tell you this answer the same way okay so we're going to do it intentionally separates from the user interface you should definitely do this when you're building applications create separate from your user interface so let's right-click on a solution we're gonna add a new project then we're gonna choose a class library okay so we can just type in here class library and we get a few choices we get dotnet standard we get universal windows yet razor class library dotnet framework and dotnet core now we're doing a dotnet core project we could also do dotnet standard the difference is we use this project in other let other platforms as well so notice Android iOS Linux Mac Windows so it's pretty much a universal platform at dotnet standard I'm gonna choose dotnet standard because if I choose a dotnet standard 2.0 project not 2.1 which is the latest version that's out right now but - 2.0 if I choose 200 then this library will work also on dotnet framework as well as xamarin so you can use the same the same library across pretty much any project type the one exception being blazer web assembly because player webassembly cannot talk directly to a database so dot a class library dotnet standard that's why I'm choosing this one hit next and we'll say this is our data libraries fine and there's a crater application forest notice we go to data library here it says dotnet standard - oh that's what you want we don't want 2.1 here let's delete class 1 I'd like to delete those classes instead of trying to rename it and potentially have problem so let's start by adding references so right click on dependencies and say mesh NuGet packages and we're going to install first of all dapper this is my go-to library for accessing dia it's really lightweight and it accesses a number of different databases so notice sequel server my sequel sequel Lite sequel seee Firebird etc there's a lot in that etc so this one library is same codebase can be used over and over and over again to access different data types or database types how's install that and we're also gonna install one more thing and that is my sequel dot data and what this does is it allows us to access and talk to a my sequel database ok so notice it's dotnet framework dotnet stand if it's 2.0 dotnet standard that's 2.1 so this will install a number of different platforms so we want the dotnet standard version which is going to install for us so my sequel dot data hit install and that will say we accept and now we are gonna have access to our my sequel database now let's write all the code we need to talk to my sequel so let's right click on data library we're going to say add new class and the class name let's call this data access if we're gonna have data access for more than one type of database that would specify my sequel T to access but in this case I think we can get away with saying just data access make this class public and inside here I am going to add two methods all right and talk through these as we go but I understand they don't totally click the first time that's okay we're gonna come back and review them again at the end after we've used them so you can't see what's going on but I'll talk you through as we go now if you've watched my other dapper videos a lot of this ago look very very similar because it really is the same or almost the same it's not the same it's almost the same so let's look at this using I DB connection so using an ID B connection which that mess likely raises up a little bit that's okay oh you know why because I'm not inside a class or inside a method we got talking didn't actually finish my train of thought let's start with the method name public static list of T okay that's what's gonna return load data and I like to pass in two generics all right string sequel and then you parameters and then finally string connection string okay that's the information that's our signature for our method so that's the load data method it's going to go a table and get information out of it all right so it's a generic method so it's got T and u which I just named those based upon whatever I felt like now typically your first generic letter is T that's the standard and so the next letter in the alphabet is u therefore I chose you so we got here is we're passing in we'll get back to what these these aren't just a minute first all we have passed in the sequel statement this is the the statement that says select star from this table or insert into well it's a inserting this is a load but it would say select from table okay that's the sequel statement then we have and no it's not a my sequel statement even though this is a my sequel database the actual syntax is called sequel okay so it's sequel syntax therefore is called sequel that's fine you parameters so it's our first generic in use what is that you stand for it stands for any type we're saying any type we're gonna send as parameters that means I can send a dictionary I can send anonymous class I can send a whole bunch of other things through this and it would pop through as parameters for most cases that makes things really easy so we're gonna leave it like this and then finally we have our connection string this is how we connect to our database now inside here let's put our unit statement so I'm going to paste that back in so I DB connection let's call this connection and we'll say equals new my sequel connection okay now notice it actually added by using same up here so it actually added this one too I did the IDB connection this is this smart upgrades to our editor here I didn't have as a using statement but it says oh you know what I know you need that one so therefore let's just add it instead of asking you or making even doing a control dot to add it so new my single connection and we're a pass in the connection string so what this does is it opens a connection to my my sequel database using the connection string that you passed in it does it a using statement so that when we're done it's gonna close this connection properly no matter what even you crash the application it's gonna try to close it properly now there's a weird edge case you probably hit where it wouldn't close connection connection properly but as much as you can control it's going to close that connection properly now inside here we're gonna say list of T rows equals connection dots query notice add the using dapper at the top it's pretty awesome stuff here and of type T so there's our other generic so we're saying query of type T meaning hey I want you to run this queries we run this so let's just say select star from in our case between the people table so select star from people well each row it comes back I want you to put it into this particular model type and that model type is going to be the T so in our case we'll create a model that has first name and last name we'll call it the person model well we're going to pass the person model in as this t and says okay i'm gonna map the data coming out into for each row it'll be a of type T which is person model in our case well the it'll be a set of those one per row all right so that's what's gonna happen and we're gonna say there is the sequel to call and then we also have to pass in our parameters and finally we're going to say to list and add the system using system dot link at the top as well so when this is done is it's made a call to our connection that's our connection to my sequel and it said hey here's a sequel to run and by the way there's price and parameters to insert into that statement therefore there's the parameters as well this protects us against the the sequel injection because we're not actually adding the two together as a string we're allowing my sequel over all along this program to do it in a more safe manner you should still protect the data coming in as your parameters but this is one more layer of protection so we take our sequel statement leap we stick in our parameters we call that and then the records are get returned each row goes into that type we get a set of those type it's actually an ienumerable of that type and then we're going to convert to list but I like list because unlike we like maybe an entity framework we've we've already pre-loaded data it's not like the data isn't already here so therefore I want to be in a list so I have more options of how to work with that data let's try to do that right away and then down below I'm gonna say return rows now I do this have us in two lines so I have a line here and a line here I could eliminate this right here and it's put a return statement with where that code is highlighted and that could eliminate this other line down here well I could but what's gonna happen is it's gonna be a little harder to debug therefore I'm gonna put in two lines and that can put a breakpoint right here see what my rows are before I return them and just make sure everything is good that's just a nice way of doing debugging that helps you now when you first try it out but also helps you down the road so I prefer to do two lines here instead of one liner yes you could break it into two lines later on but in doing so you're changing code while trying to debug it if you change code while you're trying to debug it you may change what's happening and it might affect the debugging process you might actually run up the wrong tree trying to find something because you actually introduced a bug when you changed out the the one-liner for two lines so I would just say leave it like this okay so that's our load data now you're probably thinking how do we save you well that's the next method in fact that method is pretty close so I'm gonna copy this entire statement and paste it down below where I call this save data and instead of you know it's an issue ie yeah let's say I'm a static let's leave them static I bet back and forth to bay in this you can't instantiate this if you want and well it's a debate I'm gonna instantiate it and here's why I'm gonna put it through the pansy injection which means I can also then in my unit testing replace this piece of it with a a mocked version of it so that I don't actually write to my database when I'm doing unit tests which is important so I'm going to take it off static so save data is gonna be a void instead of olicity and it's gonna be just where it's got past tea and not tea and you we're not gonna have a return statements we're not gonna have this with tea parameters not you parameters and then down here instead of query with a type tea we're going to have execute and of course you don't set it to list because there's nothing being returned and we're also not going to be doing that so now it's just connection dot execute so this is really as a stripped down version of this that takes out all the returned stuff and just changes query to execute instead so now we are sending in our sequel string before we're saying our parameters like you for but we're call it tea instead of you because it's the first generic value if you want to be consistent you could switch these around and make this you and then make this tea that way it's the same between the two methods if you need to do that I haven't bothered the connection string lucky for we're still connecting to the my sequel database this time we're just saying execute and there's our sequel and our parameters that's all we need to do okay so that's our save data method and that's all there is to pray about 95% of your data access with my sequel so let's actually use this to see how it works let's go back to our blazer project we can let's leave it open for now we collect our blazer project here and first thing you do is add a dependency so add our dependency to a project reference and that's our data library note that you don't add a dependency the other direction you do not a dependency in dáil library to your Blazers server project that would create a circular dependency and you can't do that because one of the projects has to get compiled first before can be used in the other project when it gets compiled so if they both depend each other then neither one can compile because they're all they're always waiting for the other one it can be compiled first so that's why we can't have a circular dependency so now in our pages let's go to the let's create a new page or create a new razor component and a razor component is a blazer page I know it sounds confusing that's a razor component and it's a blazer page because a razor page is something different yes that's confusing but a razor component is a either a piece of a page or a whole page Japan how you configure it and that's a blazer server or blazer web assembly thing so we're going to create a Razer component we recalled this our just people it sounds good to me now you're first starting out creating Razer components it can be somewhat intimidating to say what do we add now to this page but don't worry you can go to the fetch data page and kind of see how this flow goes for example we have these three things up top we have the app page reference which says here's the basically URL for this page that's the path to get to it so it's slash fetch data I mean a using statement how to do that and you have an inject statement this is a dependency injection so we use all three of these eventually but first this inject well you have to have some independence injection in order to inject it so let's do that let's go to our startup CS and about a third of the way down here we have configure services well we can say services dot add and here we have our choice of singleton or transient or scoped in this case I'm going to add a singleton for our data access notice that I don't have using yet but when I filled us in is going to create are using forming now I could create an interface for this but I don't think it's necessary for this demo project because you can still replace this with a different one if you really want to you know what nope I'm going to do the whole way that way I prefer to have an interface because then it's easier for your unit testing to replace it and because that way you're set up for the future so let's just do that real quick because it's important do things right I prefer to do that so you can see the real-world interactions not just hey this is the demo don't do this way in the real world I try to avoid that as much as possible so on our public class day to access I've got my cursor there okay control dot now say extract interface and create that interface notice that it say hey which members do you want to add to this interface but a fault it's all of them and the new file name is going to be I and then the name of your class which is perfect so now we have an interface that matches our our class and some people ask the question well why would you do that that's simply a waste well but it's not and the reason why is because now in our dependency injection we can come over here and say I did it access and the implementation is data access and what that allows us to do is we'll ask for ID to access well it doesn't have to be the data access class we give it that's just the one we put into this current dependency injection system but for unit testing we can put different class in and down the road if we say you know what that data access class is kind of messed up but we can't really touch it in all the areas well we can replace it or create a new version of it that is I that called better data access that also implements I did access and then we could inject that in this one spot and change our entire application so there's some real benefits here to doing it this way and again I'm trying to do it the way that you're actually going to see it in the real world so now we have our our singleton edie which means that where I create the instance once for our entire web server so multiple people will use the same instance which is fine you are not storing any data so instead of creating and destroying it create and destroy it over and over again we're going to create at once and not destroy it until we're done with our web server to our website reboots which that does use memory constantly for this it's not something I do for all of my classes but it is such a fundamental class the data access that I think we're going to enough to justify the added overhead of always having it in memory versus creating it and destroying it multiple times so it's not just a given they're gonna make it a singleton but that's I'm going to choose to do for this one so now if we go back to fetch data let's just copy all three of these lines so we have a model for our people class all right come down here I'll paste them and we're gonna call it fetch data we're gonna call it people and we don't need a using for Blazers server data but we do need one for data library dot actually nothing just data library now I could use a baz using two our imports dot razor but since we only use it on this page I'm not gonna do that and finally whereas say inject I'm gonna say I data access and we'll call this data all right so now we can say and actually I must say underscore data that way we know that is a kind of a don't mess with me field that's kind of I think if I see the underscore first it's either a private backing field or it is a value that's been injected with our Tappan Zee injection system so now we have access to our data but having this call again fetch data will help us out we come down here you'll see that we do the protected override async task on initialize a sink well this is where the call is made to the service and it's away table so we want to do something similar here because we want to talk to our database well one thing you do we should make this asynchronous right now our our data access isn't asynchronous now on the web even though you're making synchronous calls it's really asynchronous because of how the web works but we still met one of your calls a cigarettes whenever possible so let's transition this over to an asynchronous database connection let's start the save data first this is a pretty easy one if we do a ctrl J here it's gonna give us a list and one of the options is execute a sync there we go now notice that that's going to return a task so we want to return that and make this a task okay so we're not whoops ctrl J here a control dot here to add the using statement so now we're not awaiting this we could we can say oh wait and then we can go back to avoid but that's not we want to do we want to return the actual task because there's no reason to await it here we're just saying hey go ahead and execute this the caller can wait on it we don't need to wait on so that takes care of save data now notice that I did accesses now yelling at us because our signature has changed but we can do control dot and say that we want to pull that up into I data access alright so now it's in I did access the only problem is I think I might have changed that command it pulled it up but it added it instead of updating it there we go so not ideal what we did that but it did work okay let's look at load data well first of all we know we want to return a do I have an asynchronous here so you want to say query async so control J and say query async but then some of y'all's for the to list and the reason why is query async doesn't return a I enumerate anymore it returns a task of type ienumerable therefore we can't do two lists so let's take that off and let's say that we want to await this like so which means you have to say that this is a a a sink and this is me a task of type list of T we return there we go so we're awaiting this but saying hey you can't implicitly convert from ienumerable to a list of T yeah I change this well we can do a shortcut and save R which would change it to ienumerable of type T of course then we can't return that we've got to say dot to list so now we've kind of solved this but let's look at how we solved it and why we did it this way down here we didn't do any weight but up here we're doing a weight why well because up here we really wanted to return a list not an ienumerable well to do that we have to wait or wait for this to be done before we then convert the results to a list therefore we have to do to wait so we not only return a task of type list of T but we also mark the method as a sink notice that this method down here is not marked as async and the reason why there's no oh wait in here there's no reason to mark it as a sink so let's go back to our method here we're gonna do a control dot to change the signature nope let's try it again I was trying to find if we can see a different way to to pull that up so let's pull it up and we're gonna pull it up - I did access it is gonna create a duplicate but fortunately if you hit f12 on your idea to access you can go right to it and you can eliminate the one that's incorrect so if you're renaming it or re changing that signature line it makes it a little bit more messy but it's not that hard to redo so now we have everything asynchronous in our data access class now we can follow the same pattern that they have here so let's just copy this I copy it and comment it out so down here in our code now paste it I'm gonna comment this out this allows me to see what it is but at the same time not use it I'm gonna say protected override and I say on initialized async which messes with the formatting this is a known blazer issue so let's just reform that manually for a minute so on initialize async I got marked this as or uninitialized async but we have the market here as async you want one like an async call we don't need this return base so now we can do is we can say a weight and then they call to our underscore data dot load data okay that's what I'll do in just a minute but first we have to have a model to put the data into and we have to have something to call and then we have to create a private variable up here somewhere to actually put it into that's a lot of stuff to do kind of I'm gonna open up my my sequel database now I haven't shown you how to install my sequel it's kind of a outside of scope there's video especially so it's gonna be a bit long anyway but this is my sequel installed on my machine as it's my sequel workbench I've got a database here called YT demo with one table and it called people and if we do a select star from people this is it's really small so that's the zoom in here this is the results you get so there's the six records so we have Tim Cory Jane Smith Sue Storm Bob McClure Mary Jones and race don't those are the six records we have in our database right now note that the three columns are ID first name and last name so over here let's go to a let's break click on our Lazer's server and say add new folder and call it models and then in here run creates a new model and i called as we could we put this in our data access library but I think it's okay in the user interface as well so let's call this the person model and where I create three properties prop tab twice int ID enter twice prop tab twice string tab first name hit enter enter again it's really quick way to create these things all right so there's three auto properties in our poco object our plain old CLR object so it's a person model so now we can do is up here we can say that we want a a list of person model and it says hey that's actually in this path right here let's add a using statement for that so add a using for Blazers server dot models and now you can get rid of this portion it makes it a little less verbose I'll call this people and what I'm gonna do is I'm actually gonna initialize it as actually not gonna initialize I'm Eliot null so the reason why is we're gonna do a check on this to see if it's any people in the list yet so if you notice in the Razr pages up here to be getting if forecast equals null then say hey we're loading and the reason for that is because we load this page and then we load the data so the page will actually show first and then the data will get populated depending on how long it takes to populate the data will determine how long we see that loading screen for the I initialize that async the reason why you execute it that way is because we want to make sure that we are injecting our stuff first and that everything is here so we we say okay let everybody come in let you know let's load this piece load that piece bring all of our depends the injections stuff in let's make sure the page is rendered and then once that's all in okay now let's use that stuff to then load our data and put it on the page so that's why we have it on initialize a sink instead of a constructor to do this call so now I can uncomment this we can say people please spell it right P cool people equals a weight data download data well I need a sequel statement and a parameters and a connection string okay let's get those so I store that sequence here string SQL equals let's start really simple select star from people all right that's the name of table so that's our entire sequel statement so we can say first of all there's our sequel statement now for our parameters we don't have a so let's return a new anonymous object and then finally we need a connection string let's just put an empty connection string for now we don't have that yet oh and that's the only is because we didn't actually specify what T and you were sorry say the T is our model so person model and then U is dynamic there we go so now we have our call we still need a connection string though so let's go to inject up here right inject one more thing and that's going to be our I configuration and we'll call it config that's the add this whole using statement here let's let's cut this out and add it as a using like so so now you've injected our I configuration which can talk to our settings JSON file so now down here we can say honor score config dot get connection string and let's call this just our default connection all right so it's gonna get our connection string the default one and it's going to load it in here but we do need to add a connection string called default in order to call it so let's go over to our app settings dot JSON again after a loud host or s a comma or sa connection strings plural inside quotes and then colon and then a curly brace open and closed and default that's the name of our connection string and then here is the actual connection string now you can go to connection strings dots comma believe and find out a my sequel connection string that works for you but I'm gonna type one out that I know works server equals in this case has install locally so that is my local host port server number that's everyone's local host IP address and then we have the port number my port number is 3306 database we're gonna say that the database is called YT demo this is the the database I created and then we have a username so user ID the space in it equals YT user so I'm giving you my username right now you can see that and then here it gets even scarier password equals this is a super secret one if you know that past we can not only crack into this application but also you can crack in a timko retail manager if you're on my machine so definitely I say it's doubly not safe before using for both both locations but yes this is a username and password in your app settings JSON file now if you watched my video the dev questions video on securing a connection string that applies here so you haven't watched that Oh check that out too to see why this isn't a problem because this is gonna get installed in a webserver it's okay to have it in clear text now beyond that we're probably also going to have this be our developer connection string and then when we deploy to production the deployment process will replace this connection string with the connection string for production so this should be a password that anyone can know and it's okay and I'm surely on camera I'm not blurring it out because you can know that my developer system the password for my developer demo database is PWD 1 2 3 4 5 . okay so just know that's ok adieu I don't get don't get freaked out there don't ignore it don't just pretend like the world is good evenly that password in clear text there's things I think through again watch that that dev questions video on securing a connection string ok so there's a connection string it's called default so now when we ask for default it's going to give us that connection string in its place so now we have this people list in theory it's gonna get populated from our database so let's show that off it's up here in the HTML section I'm gonna say if people equals no okay rest are off the if statement just we did over here because if it's no we haven't yet loaded our data so I'm gonna grab the same loading screen or loading stuff just so that it's consistent and we'll say else so if it's not null there's something in it if it's something unit let's go ahead and display that now normally I create a table here and make it you know striped and a dark header and all the fun stuff but for this video I'm just gonna do a simple for each and we're going to loop through every P in people and we're going to do something really simple we're gonna say a paragraph tag and inside there sa at P dot first name space at P dot last name that's it so a paragraph for each person's name is gonna list them on our screen really not pretty but it at least shows us that it's working okay so let's run this application as it is right now loading data from the my sequel database okay we can come over here and nothing seems to have changed but if we go to our path which is slash people so slash people there's our people okay that's lower the people from a database there's a bigger view for you so that's lower the people from our database from my sequel is fast enough that didn't even really show that loading screen but that's how you call my sequel from your c-sharp code whether it's blaze or server or it's asp.net core MVC or asp.net MVC the.net framework version or DEP after wind forms or whatever else you really want except for the blazer webassembly version so that wasn't that hard once we got set up right once we configure data access which this code doesn't really change so they can figure that then it really comes down to just making these one-liner calls you'll probably ask the question will Tim what about actually inserting data or updating data or deleting data how we do those things well I'm glad you asked because we're gonna do all of those things so let's start off by putting some buttons up here and my coordinate my counter page this is why these pages are here okay so you may wonder well why is there these sample pages in my my brand-new project I want clean well yes but you're also probably learning blazer server and this is a great way to have quick samples when you're done that just delete these two pages and then delete the data floor too but that's it delete those things and it's all gone so in this instance I have a button here we click on the counter page that does something well copy that button so I have my example and then up here I'm gonna paste that button in and I'm gonna say it's gonna click me I'm gonna say insert and we'll call this on click insert data and then let's come down here to our code and we're going to say private async private async task insert data like so so it's gonna be an async rest method that's going to do an insert let me click that button well what we're going to do well let's create a string sequel equals and let's say insert into and spell insert into people first name last name values at first name at last name okay so that's a very simple insert statement for a my sequel database where an insert a record one record into our obstacle where I insert one record into our people table or insert just two columns the first and last name the ID column is auto incrementing so we'll leave it alone and the value n search our parameters that are first name parameter and last name parameter so that's our sequel statement now let's create our call so we're going to say oh wait you want to wait until it's done but data dot save data remember data is our connection isequal save data and we're gonna start off with our sequel statement and then rest say new and then our dynamic parameter is going to be say first-name equals hard-code this to Billy and last name equals Bob okay so that's our our parameters and then finally let's let's bring in our default connection string so comma and our default connection string so now we have our call yes we're gonna hard close data I'm not gonna create an insert form on this page I just want to show you how to insert would work if you create an insert form you just take the values in that form and put them in these spots so let's do an insert into our database let's first go to our database so let's bring it over here I'm gonna run this statement again just you can see there are six records okay and let's zoom in here and notice we see record six is Ray Stone it's the last one there is no Billy Bob in this list so let's run this again and we're going to once it runs we're gonna click the button that just says insert so I'll go to slash people as an insert button I did it it seems like nothing happened but watch they go off this page and then go back to people now Billy Bob's in the list now why didn't it refresh because we didn't actually refresh the data we could have let's go back over here execute this again notice that number seven right down here is Billy Bob so we have done insert now in that one line of code now if I'd wanted to refresh the data I could have done one or two things I could have either just put a new entry in people for Billy Bob or what I could have done is I could have called this method again and that would have reload the page by putting the people up to the people list with new data in fact I'll probably do that for the next one so the next one I'm going to create a private async task that's going to be update data again we're not taking information in where it's got a hard code this thing string sequel equals update people set first-name equals first-name equals Timothy where last name equals whoops single quotes Corey there we go so there's our update statement we're going to update the people table whereas set the first name column to be Timothy where the last name column equals Corey make sure you have the where clause on any update statement or or delete statements because otherwise you affect all the rows in table not just one pretty common mistake especially for newer database developers although we all do it so now I'm going to copy this entire statement like so and actually I could a parameter here just to show that we have a parameter would work let's do that so at last name and at first name the first name is gonna be Timothy that's the new name to put in place and the last name is going to be Cory that's the name to match on but it's it's still the same save data and we're still using the same connection string so the only thing we'll do it at the end here let's say go the next line and down here we're going to say that you want to call the on initialized async like so and we'll await that all right so we're gonna make this call again even though it's called initialize async we can call it again from another method to kind of refresh that data so right now the data says Tim let's zoom in here it says Tim Cory not Timothy Cory let's create button for this let's copy this button let's paste it we're going to say that this is update data and we'll say update and you know what we're going to change this to warning just have a different color because why not all right so we're launching this once it launches we'll go to the slash people page and now we have an insert and an update let's hit update watch Tim here it now says Timothy Cory because it's updated and it called the Refresh to the database if we execute this again we'll see that yes in fact our database does say Timothy we have to an update of course you've already seen that because you saw the results load in by one make sure you know there's nothing up my sleeve okay let's do one more sort of copy the button here RSA delete data and we'll say delete delete there we go and this is going to be danger again just to change the color of the button so let's copy the update statement the whole thing and we'll say delete data there we go and we're gonna delete delete from people where last name equals last name so now we're only passing one parameter in so let's take off the first name here and instead we're gonna change this yeah let's just delete me that's fine no you know what I'm gonna delete Bob so last name is Bob so Billy Bob is gonna get deleted all right so we're also going to still await that and on it initialize async the only difference is our sequel command and which parameters were sending in so let's begin and see if the delete command works I may have a setting in my sequel database that doesn't allow you to delete this way we're gonna find out delete it's one way cool so it did delete I don't have that setting which is great for demo purposes notice that Billy Bob is in fact gone the last record here is ray stone now the cool thing is we can now say actually it's fixed insert so we actually have that right there the the update like so so that now we do an insert will see it appear in the list now you can see all of these happen right away so slash people we can say insert and now Billy Bob is there we can say delete and play Bob goes away we can say update and nothing really happens our peers - because Timothy is still Timothy okay so that's how you do all for the crud actions with my sequel we have our create which is our insert we have our update I'm sorry I read which is our our load data we have our update which is our update we have our delete which of course is our delete so it's a for crud actions with these examples you can do a lot of stuff with my sequel and c-sharp the two work together really well okay so hopefully that was instructive and helpful to get you started using my sequel in c-sharp easily if you have any questions you want to see some more advanced stuff in this or just wanna see leading eltz make a different database let me know in the comments down below I'd be happy to put it on the suggestion list and hopefully create a video for that topic okay thanks for watching as always I am Tim quarry [Music] you
Info
Channel: IAmTimCorey
Views: 84,385
Rating: undefined out of 5
Keywords: connecting c# to mysql database, connecting c# to mysql, asp.net core 3.1, blazor, blazor c#, mysql in blazor, iamtimcorey, tim corey, c#, visual studio, dapper, async, dapper async, async dapper c#, async data access, async await, connecting to mysql database, connecting to mysql
Id: _JxC6EUxbDo
Channel Id: undefined
Length: 56min 6sec (3366 seconds)
Published: Mon Jul 06 2020
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.