Xamarin Forms SSMS CRUD Operations

this is the exact place from where we have left from our previous tutorial that is connecting our mobile app with the ssms sqldb in our previous video what i found was people were not implementing the videos box fixes fully and trying to connect their mobile app with the sql sms dv and found that lots of bugs are coming up of their own so i really really recommend you to watch my full video you can see over here i'll just scroll it up and then you have to watch and fix all the box in windows system and then you can see over here and finally we have fixed issue on the android side too so you can see we have a source code for this video and i'll just give this videos link in this videos uh description below and you can just check it out the part one for connecting our mobile app with the sql sms db with crude functionality let's go to the main page dot xml which is our starting page that is our app.xaml gets initialized from the main page and we know that and you can see we are going to redesign this whole ui and this ui is going to a very basic one and i know that you are really expert on designing uis and i really don't want to mess around here i'll just create four buttons and then remove this lineup code because we don't need anything you can see over here we just need a button that's create clicked and then i will just get a secure connection correct now i'm just going to create a grid view and then i'll just separate uh our mobile screen to three parts that is grid row definition as auto auto and auto and then we have to put our whole item inside the scroll view right that is that's how our whole ui gets scrolled whenever whenever uh we have a different type of orientation too now you can see i have just divided my screen with the help of this grid uh row definition line and then i have put a button inside it on grid dot row zero will be adding a stack layout which will wrap our three entry fields and on grid.row one will be adding four buttons which will perform all the crude operation that is get post put and delete now here i have already told you we'll be adding three entry fields and you can see on grid.row one will be adding four buttons but right now i'm just adding only one button over here so there is a gap in middle of our gita to zero and get it dot row one that's because of a stack layout i just placed now here we have a entry field and we'll be adding three controls with id property title property and the body property and which will be sent to the sms sql db you can see over here it requires id title and body which will be collected from our mobile ui so we'll be adding a entry field and giving a x name for this first entry field that is our id and giving us user id and the same goes to our title and the body too because we are sending three values right if you are sending five values 10 band is no issues you can just create entry fields and then pass it to the uh ssms sqldb okay so here enter the body and we'll be giving different name for our different entry field but our stack layout should be oriented as vertical so that will be placing three item uh on orientation as vertical that will be aligned now we can see i'm just giving different axe name that is uh mandatory step in order to uh give different uh name to the uh different uh control field that is our entry control now our next work is to create four buttons that's why i'm just going to create a grid.row one i have already told uh that will be adding four buttons that is our crude operation buttons that is uh get post put and delete buttons over here so that will perform all the steps as well as functionality for completing our crude operation in this video so this video is going to be long you have to be patient and then follow along with me now let's create two rows as well as the two columns so that we will be placing uh our corresponding buttons in each of the row items and the column items so here we'll be differentiating our grid into four parts that is two rows as well as two columns and we'll be placing our button so now let's remove this uh clicked even handler from here as we don't need as of now because we are going to create four buttons that will help us to perform our crude operation that is post put delete as well as uh our gate operation now here we can see i have just copied and pasted this button uh code over here now i'll just place our first uh button on grid.row 0 and then uh we'll be placing that second button on grid.row one so you can see our first button will be up on the grid.row zero now i'm going to paste the column code in all our four buttons so that we'll be placing our first button on grid.row zero column zero second button on git dot row zero column one and uh third button on git dot row one column zero and grid fourth button on grid dot uh row one column one that is you can see how we're gonna separate our different buttons on different columns so here i'll just re-modify the text for each button that is our crude operation that is get post operation that is on second button third on put as well as update operation and the fourth is our delayed operation correct that is our crude operation now we are going to give different names for these buttons so you can see we will be giving axe name for each button that is our get button post button put as well as delete button so this our first get button this our second button that is our post button this is our put as well as update button you can just give x name as update button third button and fourth button is our delete operator now creating click handler allows us to do any operations regarding that button clicked so whenever this first button click then do anything as you like so i'm just going to create another even handler for the button click that is our second button this is our third button that whenever it's clicked then we will be performing is functionality so this is our fourth button so what happens when delete button is clicked now let's go to the dot cs part because we have completed our xml now you can see we have all the even handler properties that are automatically added whenever we created a new even handler for each button click now our next work is to create a new button like how we have done in our previous video that is checking our connection whenever the button was clicked now here we have created a button and i'll be putting that button on grid.row one you can see how the ui gets updated and we'll be placing our background color as well as text color on this button now our next work is to create even handler that is our same event handler that we have created in our previous video that is whenever that button is clicked then we were checking the connection correct so that is the same thing that will be performed on that grid.row one button now here i'm just going to give column definition as star comma star so that we are equally dividing our button on each column so that is g dot column zero and grid.column one and we was going to give uh our update button name as update operation that's all let me scroll to the left now we have next objective that is adding a list view and showing all the list items from our table that is inside our sms sql dv so here i'm just going to give a list view acts name as my list view and it will it is basically just like consuming a rest api that is grabbing all the contents from our sms sms sql db so this all items will be displayed like inside our list view so here i'm just going to place that list view inside git dot row 3 you can see over here that will be placed in that blank area now it has something called as list view dot item template and inside item template we have data template so it has closing tag too now we will be adding a view cell inside the data template and then adding three controls that is our level control over here so that it will display all the three items that is id title and the body from our ssms sql db table so here i'm just going to again create row definition as auto comma auto comma auto that is three items we'll be adding right if you have five item or ten item no worries you can just create as much as row definition as you can and then put it inside the list view now let me expand this whole ide that is our visual studio 2019 sf now and if you have visual studio 22 2022 then no issues you can just continue with it now let's go to the main piece.xaml.cs i'll just expand this id as of now and then uh it is my stop button so this is the stop button i just need to uh drag that thing uh below so let me just drag it below okay over here all right so i can just stop the project from here now we have to share our sql connection between the uh button click event that's why we are not going to repeat this line of code but it is highly recommended you to create some different classes to handle this sql connection but in order to make this video really simple i'm just going to share this sql connection so that will be using this sql connection at line number 22 throughout the button click events now we can just safely copy this line of code that is sql connection space the variable name and then use this variable in line number 23 back again so that will be using that connection string throughout the app now it's safe to remove this line of code because we have already created a variable that will be used throughout the button click events so by pressing ctrl k and c we can just comment the code now you can see we have already a variable that opens up the connection whenever our check connection button is clicked that is from our main page.example you can see we have already checked connection button so this is a get button which when clicked grabs all the list items from our sms sql db table and then adds it inside the modal class that we will create right now so we'll be creating a model class which will be a list which will be grabbing all the list item from our ssms sqldb so let's create a modal class which will have properties name as id you can see over here that is id so i'm just going to give a name as id see the same as it is and then i'll just copy and paste in order to save some time so public string and the second field is the title property will be adding it and then we'll be adding our third field that is our body field you can see over here so this is the body field that we have so this model class is going to collect all the items from our ssms sqldb and then add it inside the list view of this own modal class so we'll be creating a motor class which will be grabbing these three items so you can see over here thing is i want your research on ssms sqldb internally like how we gonna create some primary keys foreign keys and here i am just creating manual way to add the ids but here the recommended way is adding the primary key and then incrementing the ids and then you can see over here we can just create the new uh database diagram so this is your own research and this video was made in order to help the beginners to learn about the sms sql db and integrate inside our mobile app so i want your own research to investigate more dig into more create some primary key foreign key attached two or three tables and then play around with the mobile app by connecting it directly to the sms sql db so here i'm just manually adding the id title and the body so the recommended way is creating some primary key which will auto increment it now our next work that i have already told is creating a list view of this modal class and then attaching all the list items from our sql db to this list so that our ui is going to collect this variable name that is my table list variable name and then grab all the items that is title id as well as the body property now our next work is to open up the connection so you can see over here i'm just going to open the connection whenever our get button get operation is click that is our get button that is our crude operation now we are going to create a sql query and then we will be using our query string and you should know about the query string in order to play around with ssms sqldb and it's really really simple and if you have already studied some computer uh courses as well as science courses then you will be really familiar with this type of statement that is select star from dbo dot my table so dbo dot my table is the table name that i have so there are lots of uh as well as youtube videos or twitch videos regarding ssms sql dv which helps us to understand and then dig uh deep inside the sql connection using the c sub and the ssms connection you can see over here so lots of uh tutorials are there i really recommend you to play around with more but in this video we are just going to make our life easy by just implementing some basic queries in order to get our list items from our dbo dot my table so you can see over here after selecting top thousand rows then we are go able to get our list item from db dot my table but implementing same thing like same query uh from here i'm just going to copy it that is select start from dbo dot my table is going to confirm that here after pasting it over here then clicking on execute helps us to execute as well as get all the list items from our dbo dot my table from that this query string works now our next work is to create command and then use a variable name as command for sql command and then create a new sql command and then you can see we are going to paste our query string inside the sql command and then separated with the connection that we have already defined is it as a global variable so now we are all done our next work is to read the data from our ssms sqldb table with the help of this reader so it will just execute the reader with the help of a sql data reader then we are going to read it with the reader dot read operation so it will be inside a while loop that is it will keep on reading the values uh till it gets end that's why we use a while loop and then we'll be adding the class you can see over here a class name as my table list uh it has some values that is id title as well as body these three fields will be added with the red value from our ssms sqldb that is we know that i id is inside my table list right so this id is going to be fed with the reader read value from our ssms sqldb and in order to do that the id is string as f now that's why we are going to convert it to a in 32 that is reader dot it's not dot it will be on inside the parameters and then it will just get the parameter field name as id from our sms sql db that is this id so you have to watch out for id that is again going to get the value as well as uh the id that is going to be sent from the uh sms sql db so we have another property name as body inside my table lead that's why it will be assigned with the reader value from our ssms sqldb so it will be named it as title so the uh title it will be title the second field and then two string so this is the second field that we have and i have done something mystic over here let's uh refix it whenever i paste it over here so we have another field name as title uh in our my table list which will be fed with the title and the third field is the body and the body uh that is inside my table list um is going to be fed with the body from our ssms sqldb that which is going to be converted to a string now we have successfully created an object from the smsqldb and then added it inside the list now our next work is to close the connections that we have established previously so this step is really mandatory now our next work is to add the list view with the item source this item source property is the property so you have to watch out it's item s and then source and then we will be adding this list view with the source that will create out of the sms sql db table now we are all good to go with the uh implementation but before that let's head back to the main page dot jammel here we have not uh created the label and the binding that how uh the list view is going to get the and grab the data we have only provided the source right now our next work is to uh provide our controls that is our level control with the values that will be binded that means our ui is going to call our id title and the body in the form of list so our ui that is our list view is going to grab our this modal item content that we have fed data uh from our ssms sql sqldb from the reader that's why we are going to bind this uh ui each individual controls now i'm just going to paste it to make my life easier over here so our list view is going to uh bind our id title and the body so we are going to place uh three uh rows uh items that is grid.row zero and greater row zero that will be id grid.row one there will be title and on third row or we'll be having our body now let's go to the main page dot xaml.cs mean to you to use some tricast technique in order to handle any exception that will arise whenever we are doing some any operations like data fetching as well as data posting or anything and then uh here let's create a catch exception with ex variable so that which we which will get uh grabbed whenever uh our gate operation is not successfully done so here await app.current.mainpage.displayalert that is we are going to show the user that uh what is the error whenever it try to get the item from ssmsqldb table now it requires a sync operation over here so i'm just going to create a sync operation now just add a variable name as alert and then ex dot message is going to give the message of the error uh the error message which get arised whenever it tries to create the connection now let's copy this line of code and then paste it wherever it's needed and in our previous tutorial whenever one button was clicked then we were able to check the connection right so again we are going to copy that same uh alert message code and then paste it inside this button clicked that is whenever that button was clicked that is our check connection button is click then we are going to alert the user that the connection was successfully done that's all okay so this button click resembles to the check connection button so you have to um clearly mention about the check button button connection clicked okay and then uh every time we have to close the sql connection all right here we have done some mistake also during the time of closing the connection i'll just cut it and then paste it after the while loop that is till our whole loop ends we are not going to close the connection and then keep on adding it inside the my table list now we can just successfully run the app and then see whether we are going to get our uh some authentic output or not first step is to check the connection the second step is to get the items from the uh sql ssmsdb we just establish the connection after clicking on ok then our sqlconnection.close will just uh enable it now you can see we got our uh my list table from our ssms sql db we have successfully called out two items that is inside the uh ssms sql db table so you can see dbo dot my table is being called inside our mobile app and then the gate operation has been successfully completed now our next work is to perform some post operation that is we are going to send data to the sms sql db table so this is the exact place where uh everything will happen so here we have a post button click right so now we are going to copy this ssms sql db connection code that is a global variable and then first of all we are going to create uh try cats operation that i have already mentioned above and then we are just going to uh copy and then paste it over here so that will just get some exceptions whenever uh our post operation fails now uh let's copy that same line of code that is sqlconnection.open that is we are going to create sql connection i'll just zoom it a little bit so that it is visible now our next work is to create the ssms sql uh queries as well as the connection strings so here you can see we can use just using statement also and then put a command and we have what we have uh previously initialized for our gate operation right so same thing we can just initialize a sql command and then encapsulate inside the using statement two now our next work is to the same thing that we have done for our gating operation that is passing the sql connection whenever a command is executed then our next task is to send the data from our user ui to the ssms sqldb table so in order to do that we should have something called as the query string so same thing here also we are going to create some query string or you can just create a variable name as string and then query string and then add this line of code that is we are going to insert so i just recommend you to watch lots of web urls as well as video videos in order to get the ssms sql db connection using sysap that is highly recommended before you go ahead so here we are going to insert one query that is sms sql query in order to insert our data inside our uh dbo dot my table so we are going to insert our uh table with the data that we collect from the uh mobile ui so in order to do that we'll just uh insert our table with some values that is our id's title and the body so id is going to be encapsulated with add sign another title is going to be encapsulated and then our title and the body is also going to be encapsulated now our next task is to uh get this command so command dot parameters then add each parameter that is id title and body that is sql parameters then we are going to add our id with the uh the text field the entry field id that is the user is going to add the id and then you can see over here the user id is the name that that's why we have given uh the entry field with the user id dot text so in order to capture the data out of the uh entry text we are going to uh use some user id dot text and same quest for three fields that is id title this title should be same as sms sql db um this thing uh the title and the body all right so we are going to grab our user title user body and then send it to the sms sql db table that is our dbu dot my table with id title and the body i think you have understood it what we are going to do uh that is sending our title id and the body now our last work is to execute the non query and then end it you need to avoid the operation and tell the user that you have successfully posted the data to the accessibility sql dvd table that is our dbo dot my table so congrats you have just posted data now let's close the connection with sqlconnection.close over here clsc close now let's stop the project and then now just add some breakpoint so that you can just catch the exceptions anywhere in the project by hitting f5 now you can see over here our next objective is to add the data that is id title and the body first of all we have to check the connection as we know that right so checking the connection is going to uh first of all establish the connection now our next work is to just remove this uh breakpoint as we don't need as of now our next work is to add the id with three that is we are going to send third data inside the uh sql sms db table that is debuted at my table and then the title is title 3 and this is just third body that is going to be sent that is a post operation is going to be hit and you can see over here we have uh successfully send the data ssms sql db table that's all now you can see we can just perform a get operation to get the third item from the table now we are all done with the gate as well as the post operation that is sending as well as uh getting the operation now our next objective is to perform some update operation that is that specific id is going to be updated that respected id so i really recommend you to use some primary keys because you don't uh have to do this type of id addition whenever you use primary key because you can grab that id and then send it while whenever you are updating any of the contents so same operations i am just going to follow up for our update button click that is try catch operation and then catching any exceptions as well as the error handling and then our next work is to uh use the same line of code that is sqlconnection.open because every time we need to open the connection and uh as soon as the connection is completed as well as all the operation is finished then we are just going to close the connection now our uh in order to perform this uh update operation we have to write another query and then in order to do that we are just going to uh create a variable name as int space that needs to be updated so whenever user sends this id uh dynamically to this operation then uh the corresponding title and the body is also to be updated so you can see over here we are going to update this id number two with some updated title and the body so in order to do that we are just going to uh add the ids the updated title as well as the body so here in order to do that we'll just create a variable name as id to be updated and then this id is the integer right that's why we will just collect id from the user input field that is uh the user input field is going to provide us the id then what we are going to do we are going to update the title also right so the updated title will be so this is the title so field that will be updated from our user entry field so user user title dot text is the text that need to be updated on to that corresponding id from the ui all right so i'm just going to copy paste to make my life easier over here so body to be updated that is we are also updating the body right so here we are just going to change this variable name as user body that's all now our next work is to create a connection string like what we have done for our gate as well as the update operation so string space query string equals going to add sql query uh that is initialized from dollar sign at first in order to update our id with the corresponding values that is provided by the user so this is the table that is to be added over here that is we are going to update the table and then we are going to set our id at first so id will be initialized with single quote and then curly braces that is starting as well as ending and which will add the id that is to be updated so first of all the ids uh is also going to be updated along with that we are going to update our title then the body so comma title equals this is the title so same uh name should be there you shouldn't change that thing so title is going to be uh using the title that is to be updated from our user ui so the title is going to be passed and then separated by the body so body equals and then single quote and thus curly braces and the body that is to be updated so we have successfully completed the thing that is to be updated but we have to set our id that is going to be acting as like a mediator to update those content so whenever the id is 2 then we are going to update our id title and the body so this is how the query works so in order to understand more about query i really recommend you to watch some your youtube videos or any uh social media platforms in order to get uh knowledge regarding the sql query and how it's used in order to connect our c sub code along with the uh sms sql tv connection for our web app or any any other platform so this is the query string that will be using in order to connect our mobile app to the ssms sql db so same thing goes over here so uh the same operation we can just copy and paste and then uh using statement is going to create a command and then we can just directly uh i'll just zoom it in to show it uh to the viewers more clearly and then you can see we are just going to execute the known queue query after it's completed now let's copy this line of code and then paste it over here we are going to notify the user that the update operation was successfully completed whenever that update button was clicked with the the data that was provided by the user so congrats you have successfully updated row item all right so the road item is updated whenever uh the user clicks on update button so here you can see i'm just putting some brickword and then running the app just wait for some time till it loads so it is loading the app now we can just check the connection at first and the connection was established successfully now our next work is to update the id number two with updated title and the updated body so bingo the title i'll just rename that title that is a typo so uh this is updated body now if i click on post operate this update operation then we have to get a successful output okay we got a successful output that the row item was successfully updated that's great now we can just get the operation okay the connection was not closed that means we have done something issue over here but we have successfully updated our row items so i'm just going to close the connection like same thing we have just done some mistake over here so i'm just going to close the connection and can you can just run the app in order to get the successful output so after running the app back again we can just get the uh data items to see that the second row item was successfully updated that's great now our uh next thing that we need to implement is the delete operation that is user is sending the id that is to be deleted so same thing we'll just follow up same pattern that is tricat's technique and then exception ex and then we are just going to copy and paste the code over here the same thing we can just remove this throw operation because that was crashing the app previously so a sync operation then a connection dot open i'll just zoom it a little bit so that it is visible to the audience over here so uh pasting over here that is the sql connection dot open and then we have to close it all right so now we are going to write some queries for our delete operation too the same thing that uh we have done for our update operation that is the id that need to be updated right so same thing the id to that is to be deleted is going to be got from the user entry field that is user id dot text so uh the id is done now our next thing is to copy the same sql command using statement over here so let's close it now our next thing is to i'll just put some tab over here so that it will format it well now our next operation is to add the query for our delete operation so in order to do that we are just going to add some dollar sign that we have used for our update operation so delete from dvo dot my table [Music] id equals the id that needs to be deleted that is line number 155 so i'll just put that inside the curly braces and then end it with the sql connection that's all now the id is going to be deleted and then we have to just command that execute query finally after the using statement is done then we have successfully closed the connection right now next thing is to update the user that the operation was completed congrats you have successfully deleted the deleted item put some break point and then whenever the app is running then you need to press f5 in order to go through the debugging mode so let's wait for some time till the app loads so the item number three is going to be deleted from the table so i'm just going to add id first of all get the connection that is successfully done then the id number three is going to be deleted so you can see over here the id that needs to be deleted and the sql query was successfully executed and then we have successfully deleted the row item we can just get the operation to see if the item was deleted or not from the ssms sql db table so we have successfully completed the crude operation and i really want you to investigate more uh dig down uh more in order to implement the sms sql query and then use it inside your mobile app and it's really a fun

Channel: Xamarin Guy

Views: 14,373

Keywords:

Id: _zN_Z9rNIiU

Length: 40min 16sec (2416 seconds)

Published: Thu Jul 15 2021