How to Connect to MySQL Database in C# - Full Tutorial

hi guys and welcome to my sql database connection developed in c sharp let me show you guys how this works right here we have some data of captain alan brown and we can select all the data that of tony montana let's select captain jerry a baby and then we can even select whoever very holy way okay and one other thing that we can do is we can delete whatever data we don't want let's get rid of remy brown ibrah which we can always get rid of it all in valley okay select all the data in there and reset and right here we can enter new candidate details and let's assume the name of this guy is going to be kim kelsey and details of kim kc address is number 45 big time and postcode there we go telephone number of king casey all they need to do is just click on add there we go if you scroll right down we should be able to see kim casey's details right there that's kim casey's details one other thing that we can do is we can print and there we go all you just need to do is to heat and print and select the printer that you want okay i'm gonna get rid of that because i don't have a printer then we can update if we want let's say we want to update let's look for the details of someone okay let's update details of paul parker we'll just come in here let's assume chain paul parker to appear back there all they need to do is click on update there we go it's now peter parker okay and that is how the system works we can't even exit no i do not want to exit so what i'm going to do now is i'm going to take you guys straight into c-sharp development environment i will put one of these together so let's do that now guys and let's start a new project i'm going to click on new make sure c sharp is selected from the template and click on next i'm going to give the project a name i'll call it c sharp underscore my sql connector there we go and let's click on create now that my development environment is ready i'm going to go straight to the project and let's select the form let's change the size of this form to approximately 1014 by 788 okay that's cool now the next thing i want to do is i'm going to come straight to the toolbox here and let's grab a panel yeah there we go there something like that and i'm going to change the back color of that very panel let's come right here change the back color to cadet look blue that blue there we go and the size of this panel i'm going to make it approximately 969 almost there 969 by 97 okay okay and let's grab another panel i'm going to grab this one just make this be the inner one and i wouldn't change that color of that too let's change the back color of that very one to control come right here let's change that control then that's the default color and then change the size i'm going to make this size here 82 by 948 okay that would do and just drag it right in there yeah there we go pull it up a little bit okay so that is fine now let's add one here so i can even copy this now let's come right down here there drag this right in here and reduce it that much okay so i'm going to copy this again ctrl d and let's just reduce that i keep saying ctrl d this is not uh it's not unity why do i keep saying control d all right i'm going to copy this one again let's just reduce this bring it right up and reduce this down bring this up here okay let's add other components to this i'm going to come in here let's grab labels let's see right here one label here and i'm i'm going to change the content in there to my sql connection c sharp there okay let's come right here and change the font change of form to about let's say look at that blue right and what about the font size i'm going to make the font size about maybe 48 yeah let's enter 48 there 48 bold click on okay there we go that's not too bad yeah click it up okay that's fine let's add more labels and text box let's see this text box right here okay create the size of these that much and this is going to be sore name no no i'm not sure name let's change that to see then id all right still an idiot and i'm just going to copy that through so first thing first let's change it to that much bring this down a little bit and repeat the same thing for this yeah let's just copy those hold on to your control click and drag more here there we go i think i have about six that's fine okay back in there let's grab yeah data review just grab that that much and i need some buttons here let's come right here let's grab about six buttons there one and two three six and right here let's add a text box here i'll use that as my search search text box so grab this and just drive there so i'm just going to speed up the the design of the interface or the beautification let's just look a be presentable i'm gonna take you from there so i'm just gonna speed that up this one is gonna be first name then i'll get back to you guys as soon as that is done so the rest is just just about making it look beautiful [Music] okay okay that's my bed so let's add let's add two more objects here i'm going to come down here i need a print document there we go okay let's also add a printout yeah selected print preview dialog and come straight to where we have the properties see where the document drop that down and let's select print document one okay print document one taken care of now let's do this go straight into into the tools here and let's select nuget packet manager and right inside you get packing manager i'm going to select money new get packet for solution so click on that and go straight to browse india we want to enter mysql now i'm going to select the name of my project where we have installed let's get that installed there we go now the system is installing installing the mysql database components there we go now the preview changes appears click on ok and then license agreements click on i upgrade now the system is installing all the required package okay i finished installing all of the required package so i'm going to come in here close that there and just connect this a little bit all right let's start to work with the coding so first thing first let's double click on exit right here and i'm going to first of all declare the following i'll grab the following dialog results now call that i exit then enter semicolon though and now let's say i exit is going to be equals to my message box this can be equal to message box and the message box we have the following equals message box message box dot show and this message box will have four arguments so it's gonna be confirmed if you wanna exit okay that's my very first argument confirmed if you want to exit enter a command here and just enter the second argument which is going to be my mysql connector there that's argument number two taking care now let's take care of argument number three i'm gonna put a comma there and let's press enter argument number three is going to be message box button dot yes or no and argument number four is going to be message box icon there we go and what type of icon do i want i'm going to go for the question mark there we go and right here enter a semicolon now let's use an if statement to determine the selection mean so i'm going to say if i exit equals equals i have to use equals equals because i am comparing the selection made okay comparing whatever we have in here to what's in here so we want to use equals equals dot yes if it is yes okay now i want as follows so let's say application dot exit enter parenthesis and semicolon there that is my exit taking care of what good practice is for us to actually use try cache just in case if you run into any error so i'm going to drag all of these or bring this down right down here all right now we need to enter our cash statement here scroll right down underneath here we need as follows press enter and say cash open up the bracket of course exception exception x and that's gonna be i'm just gonna use message box dot show and what are you showing the x dot message the will be a default message from the system itself and that is it so that is taken care of okay from here down here that's your exit i think it's good that way because it will actually prompt your user to confirm if they want to exit or not i'll just click on run let's see how that's going to look like okay click on exit there we go no i don't want to exit and yes i do want to exit there all right now that that is done let's take care of the reset double click on reset now with the reset i'm also going to use try cash come down here there and underneath here there will be a car so let's copy the cash there just place that right underneath here i'm going to use the following lines of course to clear all of my text box so that's it for each control this is c in now what number is my partner here this cylinder oh it's partner number four okay then okay let's grab that come in here and just say four panel four dots controls there come out there and let's open up the coil braces and here we're going to now enter this one should be uppercase let's get sensitive do that again right okay so let's use an if statement if c is txt c can be either whatever name you want to call it that's just my own variable that i made up then we can say textbox then right that is taken care of and i'm going to have to also let's see if i have anything in this find the search button i want that clear as well all right because that search button is on a different panel there we go that's the clear function taking care of all right good let's come in here now okay we've taken care of the club we might as well take care of the print double click on print okay with the print first thing i'm going to do is i'm going to declare a variable here maybe we should declare one here right up here and that's going to be i'm going to call it bitmap and let's just say that's going to be beaten up as well and that's it so this verb has a variable intent to use now double click on print i'm going to use try cache again so let's say try and type raises all right underneath here let's enter cash statement and do that copy crash and paste it right underneath here and inside the try i'm going to use as follows let's declare another variable i'm going to call it integer height and height equals um date of review dot height dot height okay okay so now let's say data grid view self grab this data preview equals data green view i'm going to say that would be dot row dot row count and that is let's say multiply that by because i want to be able to take the pictures more or less like i'm taking the picture on the data review itself so data review dot row template dot row template dot height multiply that by two there we go now let's beat my division team bitmap new or bitmap it was new bitmap open up a bracket and let's say little view dot width multiply that by or comma height comma basically the height and tell semicolon here okay now data review dot draw dot draw i will draw to bitmap right and in that case beats map call my new rectangle 0 0 let's review with the center dot with the comma height and that's all there is to it enthusiasm there so we now need to take care of the print preview itself now print preview print preview dialog one dot print preview control does print preview ctrl dots zone equals one now print preview the dialog dot show download really dot show down and when i have the little preview height equals height equals height there that is it all done okay so now that that is taken care of okay now let's call this object the print document i want to get this print document to do as follows first thing first let's copy this and just try crash now this let's put the cash right underneath here and that is going to be e dot graphic now we specify the position of my graphic really okay dots draw that we draw imagery draw image image and that's a bitmap it's going to be in the following coordinate that's zero comma zero and then so that should officially take care of the prints okay so let's run it and see how that's going to look like okay that's how it all looks down exit is working if i enter any whatever data in there i should be able to clear using i need to increase this font size reset that's good what about print okay parameters is not all right okay i get it all right so let's start work with the sql connection itself my sql connection so i'm going to exit out there we go and what i'm now going to do is let's do this first down go straight into the code view okay right underneath here the first thing i'm going to do is to get the sql library itself so using my sql like this you guys can see it's already there dots data dot my sql client there we go now right underneath here here we have probably the public class there i'm going to declare as follows the first thing is going to be i'm just going to call that my mysql connection okay let's do that mysql connection let's see sql connection by sql connection objects i'm going to call that sql equals mu mysql connection enter parenthesis now the next one is going to be my sql command right there and that's i'm going to just call it sql command equals new sql command okay what is the way you want let's take care of the data table we need the data table object right and that's i'm just going to call it sqldt dt uppercase and that is going to be equals name data table there okay i'm going to declare one variable here string variable which i'm going to call sql query right all right well test what about we also need to declare mysql data adapter my sql data data and let's just call it a equals mu my sql data adapter there we go okay i'm also going to declare let's say data sets yeah so that's an object for data set and let's just call out yes equals new data state there we go all right so i'm almost ready now to get this system connected so let's go first if first of all this is string server equals that would be for my local host right that's fine okay and so name i'm going to create another one for my username username and that's going to be no one as password i'm going to say my password is kcc okay that's my password and what else do i want for my database so the database this time i'm gonna just call that com there we go all right all my data for connection is all ready now okay okay now that all my connectors are in place what i'm going to do now is to create a function so let's come right underneath here just called as a private let's say private void and i'm going to call this function upload data there we go now function upload data we i'll be using that for the connection so let's come in here connect paste that in here that that will be connection string connection string yeah that's fine equals as follows the server grab the server and let's put that here there most server now that is the very first one the next one is let's enter that's there so the next one let's go for i'm going to call that user okay let's say user id plus user user id equals user that's what i'm going to call that then um yeah that's fine so the next thing is i'm going to go for the password plus speech map plus password okay let's put password here really okay let me do one thing i just press enter so i have another line here and this will be password swearing that okay that's fine so i'm going to now say sql connect dot open there now skull command dot command text get that on board what about the sql command dot connection dot connection and that is going to be equals sql right and here and tell someone colonia and sql command text equals i'm going to use my sql there let's select oh from i'm going to be using one of my old database students dot that says student count i saw yeah come out of hate and just yeah i believe you guys you are all familiar with how to create a database in my sql so okay so that is that so let's say the reader now sql reader equals sqn command dot execute execute the reader right there we go now let's see why do i have this arrow okay let's check up here for my all my declarations okay that's not there all right that's it now that's my sql data reader come back down here okay diary is gone so now let's get the sql data adapter no that's for table dot load what am i loading i'm now loading up the sql reader sql reader dot close now let's close that of connectors as well dot close what else do i need to do that we also need to close that to be for my data grid view data green view dot data source and the data source for that is equals sql data [Music] all right think of taking care of my function now let's do one thing my sql i believe let me open it up this is the that's the table i'll be using so i reckon you guys know how to create sql create a table in sql so that's it's no big deal if you don't know how to do that there are a lot of tutorial on how to create one mysql and then that's the very table i'll be using that's it right there okay minimize that now i'm going to do one thing so that i can grab all of these data here i'm going to come in here copy this and call that right inside my form load so let's click on the form load there and in fact i'll just double click on it and right there paste that in there just call the function there okay now that i've called the function here in the form load i should be able to see the data that i have right in here inside my table here my system so let's minimize my sql all right okay so let me show you guys the data one one more time okay this is the okay this is a function that i've just put together to call the database so i'm going to run it now let's see how that's going to look like oh there we go that's very good why do we have this space i need to check my database my base space on my database okay so the other thing is we now need to take care of one two three buttons and this and this even these as well okay let's see my database i'm going to have to refresh this database and see right okay if you see any problem that's taken care of now let's take care of add new data double click on that the case of our due date first of all let's copy something here instead of the event in the world so i'm going to grab all of these let me just grab that and that's what we do for now so double click on add new there and just paste those in there now let's use try try cash and the cash statement let me copy that underneath here so if there's any error that will be taken care of okay okay let's get the sql connector here to open up place that in there dot open let's open there and one other thing is let's say sql query and that's the street string character that i declare of the equals and i wanted to insert insert into student con the following values the following fields actually these fields that i want in there so that'll be student id that's the first one followed by i have first name surname address i have postcode because code and telephone yeah those are the fields that i have online my on my sql database i'll close that now let's come out here and grab the names of all my that would be the values with an s okay the very first one that will be student id no yes student id okay that's right plus tst student id that is the name of my object goes to the 90. text now i need another one for the name so let's have a comment there next one will be for the sun name now first name txt first name where is it there we go dot text and so on guys so i'm going to just speed that up and get back to you and those are the fields okay that's taken care of now and all of the data will be will be added onto the field to the record from my text box the various textbooks that i have in here where the value can be added okay now let's say command command equals new command okay come down here sql command a course name sql command my command and let's give our command this one then let's let me grab my sql query yeah that's good and grab this communication communicator okay now what about the reader i'm gonna need the reader so let's say sql read equals sql command dot execute as a cute reader now let's see close here sql okay that's fine so let's take care of this and put it semicolon there all right that is fine now that's too hard okay that is fine so let's come down here and just say finally close let's say finally i want the following to happen so finally i want sql.com dot close once that is closed i also wanted to open up my let's call my database function there first of all take care of that and right underneath here let me copy it here let's grab this and we call it right here yeah there we go so have a good look at the insert lines of code from here take it down okay guys let's take care of the update now so i'm going to double click on update here you see since a connection has been established with the mysql database the next step for me is first thing first let me just copy this then copy that and i'm going to paste that right in here okay i also need to open it up let's grab that we say mysql connect open that i'm gonna use try cache here there and let's grab this cache here this very one and i'll paste it right underneath here there we go there and like i said since a connection has been established i'm going to grab my sql mysql [Music] command there we go and that will be equals sql command equals new sql command okay now so what's happening here since a connection has been established using mysql so what i didn't want to do is to carry out the desired database operation by using this mysql command so what i'm going to then do is let's come right underneath and now we then say mysql command is very one dot connection [Music] and that will be equals to sql con there so we now go ahead and just let's use my sql command text my sql command so sql command let's say dot command takes that will be equals to the following so i wanted to update update my database called student.com so then connect dot student comment [Music] and i'm going to set these student id that is equals [Music] student id comma first name and that'll be equals first name first name that is equals first name so i'm going to speed that up and get back to you guys so it's just the same thing to do with the field that i'm taking care of there okay those are all the feeds that are meant to be updated as if required so i'm going to now say my sq mi no sql command dot [Music] command that will be command type yeah the command type in this case it was [Music] command dot command type dot text yeah that is correct now right underneath i'm going to then say sql command dot parameter we have that yes we do the parameter about add with value and this very first value is going to be my student id this little one grab that and drop it right in here there we go and we say comma and i have my text box cxt student id dot text dot text okay now i've taken care of the very first one might as well copy this i repeat it for the other thing i have about five six more five more actually [Music] now let's change it around so the next one is going to be first name grab that paste right here we have sewn in [Music] then we have address copy and paste that here [Music] grab that pistol right underneath here and finally the telephone number there we go all right okay guys and uh right underneath this i'm going to now enter sql command dot execute none query there we go and just for the non-query if you want to know more about it is actually to insert update and delete data so if you want to know about it you can just go in in here we have the sql document i think yeah this is it sql document you can see it all there and query to insert update and delete data there's a lot of information here if you want to learn okay i i'm going to include this url in my description area for you a lot all right so try to read it you understand much better okay so i'm now going to just enter semicolon there there and what i'm going to do next is i'm going to say sql con which is my connection and close that dead i'm also going to grab my database upload upload date data call it upload load data that is my function it's not a database it's my function that i have right up there okay let me show you guys this function this very one okay right up there where is it there that's the function that i used to open up my database and is then called inside my phone load so that's why the database gets loaded immediately so back to my update right here so i don't know if it works but that's there's no error so far that's the update okay i use try cache so let's run it and see how that's gonna work there we go as my database the function was officially uploaded straight into the system the database was officially uploaded straight into the system with the function so let's click on anything okay i cannot select it yet okay i would i would have liked to just update select it and update it in here okay let's take care of that i can always take care of this right now so let's come into [Music] my yeah that is it right click or select it let's go straight straight to the properties i need event and i also need self click there we go so right in here inside my myself click let's use try to help with whatever error that we're going to run into there we go i'm just going to grab cache and paste that right underneath here now the very first the very first data i'm going to take care of is going to be txt student id dot text text equals data data grid view dot select select rule and put that in an array um dots cells their first one is going to be 0 the value dot strings dot to string then there we go and that's my very first one so i'm just going to do this i'm going to copy all these and just two three four five and six and let's change it around this one is going to be one two three four and five remember it's an array okay and this one is going to be first name changes to first name [Music] followed by surname [Music] then we have address here [Music] and i have postcode right underneath here and finally telephone number and that is going to be there now is this spelling wrong or what let's see what is it called let's see the name oh now is this one postcode okay the code is uppercase all right there we go now that is done so hopefully when i click on the data grid view i should be able to see the data on every single text box that i have in there so let's run it and just see how that's going to work and we can then speed up the rest process i think the only thing left will be the delete okay just that delete that is left now click there we go remy let's see [Music] tony montana danny let's change details off let's see who can we change here let's come down yeah i'm going to change details of grace let's change race to just sally there now let's see decision time if the update worked so let's that's great i've changed it to sally click on update there we go i think it's working no it's not working that is it it's not working oh i have to okay let's go inside the code of update let's see what i've done wrong double click on update let's check this out guys oh boy that's very bad of me so that's that's the problem when you copy you just have to be careful that is supposed that was supposed to be first name okay and this is meant to be soul name and in here we have address and here that will be post code post code and finally we have the telephone you see this is the reason why this dude you always test your program okay okay let's go back save that all right let's try that again at least it does show that the update works that is good now come back right here that that was that's the update now let's find out if this update work for real so zero there we need seven eight nine ten eleven and of course good let's change the prospect to address number 74 little little big that's a big [Music] okay they're vegan okay now the surname is going to be let's say now let's click on update look at these numbers that we have in there let's click on update now there we go look at it ali khan little be gone and so on so my update works that is brilliant i like that okay so everything works here this works everything is working so i have to i just need to take delete and the search function one two okay so i may have to speed this up double click on delete okay right in here the deletes let's copy i'm going to grab all of this up to here and i'll delete what i don't want to come back into the delete paste that in here there get rid of this and come right down here get rid of these as well they i don't need this [Music] yeah um what else do i need i'm going to i have connect so i'm going to need my sql here let's see let's place that in here okay that takes care of that now let's come right down in and i'm just going to call command.txt sql dot command text and with the command text equals delete from student dot student connect where the student id no id is equals student id there we go so anything that's equal to that student id we get rid of that so i'm going to now say sql command let's get equals new mysql command this time around the new one and that is going to be query query sql query comma press ql [Music] there so let's close sql con itself so close that right underneath here dot close press close and once that is done let's also since that is done let's also get rid of first of all let me update update up here oh yeah that's correct that's it right here now this will copy that now everything is done but the thing the problem is i would not be deleting anything from my data review so let's say for each for each data data that's a great day great view the uppercase here for each database you wrote [Music] item in this dot data gravy one dot select row dot selected selected rules okay so whatever we have inside the selected rule we want it deleted so that would be data grouping one grab this paste it there dot rows dot remove item dot remove add item dot [Music] index so that will get rid of whatever we have inside it inside that there so take a good look at the lines of code for delete as well there now all that is left for me to now do is it seems to be too fast with that have a good look at it now all that is left for me to do is to take care of this seshbot button so the sesh it's not a button it's a text box come straight to the event and inside the event i'm going to go for okay key press right in there press enter there so writing here let's use try there and type the following lines of code and let's grab a cache somewhere cache around that and just paste it right underneath here and that should take care of the the search function okay from here down here those are the lines of code for the search function i'm actually session with the first name you can use the id you can use whatever you like so a name or whatever name it the choice is used and that's all there is to it so let's try this out that is the search delete after delete we have choose that is to select the selected record from data grid view then we have in here that is the update [Music] then we have finally add or insert bad there then we have as follows form formload print print reset exit and we then have my load database that's the function that load up the database and right up here is my connectors there we go and that's all there is to it so let's run okay guys now let's see how the whole system works if i click on here there we go that is fine let's see is there any name would like to delete this let's delete james then yeah come in here delete there we go james dean is gone okay but the delete did not get rid of it here okay maybe we have to attach the lines of code in here to here there okay now let's see now the lines of code in here should go in here as well so let's get rid of that i'm gonna what i'm gonna do is double click on reset and i'm just going to grab this just grab all of these yeah why not grab it on and just paste it right in here no it's not in there deletes double click on delete yeah that is delete okay we can just dump that in here there that should take care of everything to do with reset and delete so run let's see how that's going to look like now try that again okay back in here let's select danny and delete danny there we go that's fine it's working okay that's good so let's see let's come in here and we by the way let's search for someone let's say we want to search for king there we go look at the details of king you see that guys so with that i'm going to call it the end of this very lovely tutorial i suppose you guys enjoy it so just before i leave let's print let's try the print out okay that is it we can print out an item that is fine okay so with that guys i'm gonna call it the end of this very nice tutorial i suppose you guys enjoyed um please do subscribe to my channel and you can also join to become a member of the channel there's a lot of benefits if you're a member so you all have a nice day now and bye for now

Channel: DJ Oamen

Views: 16,745

Keywords: Connect to MySQL Database in C#, How to Connect to MySQL Database in Java NetBeans, create an interactive MySQL Database Connections, How to Connect MySQL database in Java NetBeans, How to Connect to MySQL Database, MySQL Insert in Java NetBeans, Update and Delete in Java NetBeans, ow to Connect MySQL database in C#, How to Connect to MySQL Database in C#

Id: V4RwXtmhcEk

Length: 70min 0sec (4200 seconds)

Published: Mon Dec 21 2020