Xamarin and Maui Sql Server (SSMS) Connection

all right let's get started with the first installation of ssms that stands for sql server management studio and the sql server so you have to remember that we should have two package installed inside our system that is sms and the sql server you have to install the latest one that is provided by the microsoft documentation and also keep those documentation in the link below where you can download the ssms and the sql server and it doesn't mean that you have to first of all install the sql server at first and then only install the ssms you can just install the sms at first and then only install the uh sql server too as it works side by side let me minimize it and then here you can see uh it will tell us to install in the c package and i'm fast forwarding this video so that we can save some time over here now let's wait for some time so here our installation is completed i'll just click on close now if we type sql server management studio and then you can see over here we have our sql server management studio but we have to notice that we have not installed sql server that's why it won't give us the server name so in order to find our server name we have to install our sql server because our sql server is going to be handled by ssms so let's type sql server and then you can install the latest version it might be either 2019 or 2020 to that is coming up so uh install the latest one that is a express person so here our file has been downloaded now let's open the file up that is our express edition of our sql server here we are going to select the basic package and then you can just select a custom package too and then carry forward the installation process and let me click this basic installation and then after clicking on it then it should navigate me to the license agreement page and i will just click on accept and then we are going to install that in our c location let me fast forward this video so that we can save some time over here till it's downloaded now we can see over here we have connection now option which lets us to connect our sql server management studio with the sql server and then if you have not installed ssms then this dialog bar is going to tell us to install the sms and we have already installed that's why we don't need to do that uh back again as i have already told it works side by side now i can just simply close on the installer and then from the ssms let's open it up and wait for some time all right our uh dialog post is going to be open now inside our browse for more here you can see we have a database engine and the desktop version that i can see over here at first i'm just going to connect with the desktop version and later we are going to change into the sql authentication mode and how to uh create a authentication for our database system so you can see over here we have a lots of database inside our system database but here from our query we can to create a new database but we don't need to create a queue query here in the right clicking on this folder it allows me to add a database name and everything so i can just give a database name as my dv that's all and then i can click on ok to create a new database name as mydb now this my database is now going to have a table so inside the table you can see we don't have any of the table we have to create the table as like how we have created a new database so right clicking on it it will allow me to add a new table now i'm just going to fast forward this video so that we could save some time and quickly adding those fields we have successfully added three fields inside our database now our next objective is to connect our mobile app with the database so in order to do that we are going to select our favorite id either it might be the visual studio 2019 or the visual studio 2022 it might be anything just you need to connect our visual studio mobile app with the uh the existing database that we have just now created so you can just select a dot net mouse project as well as the xamarin project too so when i click on uh control and s and then it will allow me to save my table so here i'm going to save my table name as my table and then click on ok that's it my table name is my table you can see now i can just refresh my whole database by let's let's close it so or here at top i can just refresh it and then it will allow me to so all the existing table inside my my db database so here you can see now i have my table populated over here i'll just double click it so that we can see uh thousand rows and then you can see we can just see the thousand rows so like this to output the view of my table but we need to add something inside my table correct so uh right clicking on it and then we can just edit top 200 rows i will be fast forwarding this video so that we could save some time and add two row items after entering the data just you need to hit tab button to go to the next row now we are going to create a new xamarin forms project as well as the dot net mouse project that is coming up in the future of extended version of xamarin form so this is the dotnet mouse project that is running in right now visual studio 2019 and the upcoming version of visual studio is 2022. so here i'll be just selecting xamarin forms and you can just select my project also and then create a new project name as jam app now after clicking on create let's wait for some time and then you will be displayed with this ui dialog box here i'll just select a blank blank app and then you can select ios project also and then right now i'm not interested in creating the ios project but i have tested it on irs project it works fine too but you have to do some configuration and then stick with me with this video uh till the last end so that we can fix some of the box and the is too so here you can see i have just selected my hardware android device and then i'm just running my first blank xamarin forms application on my hardware android device so if you have emulators then you can just display uh the project that is running on the emulator so let's uh stop this project now our next work is to inherit some of the plugins that are required in order to connect our xamarin forms project along with the sql server database so here you can see let's go inside this dialog box in the browse section here i'll just uh type system system dot data and then you can see system.data.sql client is the plugin that is required in order to connect our uh our my app as well as the xamarin app to the ssms that is sql server management studio just click on ok and then it will try to install all the packages that are required for our application to connect with the ssms now our next work is to create one button here we will be creating one button and then whose background color is going to look like this and i'm just going to copy it and then paste it and then whenever we click on this click handler then some event is going to be triggered so whenever i click on new event handler then it will create our even handler publishes inside the dot cs part so here i am giving a text color for the button as white now let's go to the java part of xamarin.cs part of the main page now you have to stick with me till the end of this video so that we can fix lots of bugs that are going to come up until the end of the video so at first here i'm just defining a sql connection like this way so that we can add data source property initial catalog user id password to this sql connection let's define a variable named as string space server dv name that will be used inside the initial catalog section that is our initial catalog will be our db name so you can get your dv name from here and then we have already defined right so this is the tv name that will be used so let me minimize it and then let's paste it over here or just type it that is a short db name now next thing is we need a connection right in order to connect our mobile app with the uh the server database so we need something like server name so you have to understand right now is our mobile app is going to connect with ssms db so in order to do that it needs one common mediator so in order to create that common mediator we need one shared ip address so let's go to the ipconfig of our desktop and then here you can see we have ipv4 address so this is the common shared ip that is going to connect between the mobile app as well as the sms db so you are going to use this server name inside the initial catalog now our next thing is we are going to add our user id as well as the password that is server user name is the user id and the server user password is the password that will be used so server password let's go over there and then now server username and the password requires some configuration because right now we are in the desktop authentication now we have to change our authentication by going through the properties and then inside the uh security section here you can see windows authentication mode has been activated for the new user it will show like this way but if you have already connected your database with the sql authentication and have your own username and the password then uh you are good to go you don't need to follow uh this step that is selecting sql mode as well as the windows authentication mode and then you can just copy your username and password to the app itself now for the new user only i'm just going to perform some empty steps that are required in order to convert your desktop authentication mode to a sql authentication mode and grab the username and the password now let's wait for some time till it loads now our desktop has been restarted now here you can see in the login sections here we have to create a new login in order to do that we just uh click right click on it and then here uh in the new login session here you can see we have sql server authentication and then we can give a login name as uh of your own name it doesn't matter you're like you have to use my name so you can just create a new name for your uh sql server authentication and i'm just going to add the password as one two three four five six for my test sql server and don't use this type of password in the products mode app or else you are going to run it now inside server role here you can select system admin and then click on ok now we have successfully configured our database with the sql server authentication now we can just safely close this a whole ssms and then let's verify whether our database can be accessed with the username and the password that is provided by sql server authentication or not now it's safe to select our authentication mode that is sql server authentication mode and use the login username as well as the password that we have already set up and don't use my own and then you can just create a new password and then add the same password that you have used one two three four five six and then one two three four five six this is just for testing only so yeah we just successfully logged in uh to our database with the username as well as the password so now our next work is to go to the app and then use the same username as well as the password that we have used over here and then just paste the password that is submitted as well as the one two three four five six and you can just copy the server db name inside the data source section so data source is going to be our server name that is being a data source that is how the mediator works and the initial catalog is going to be our server db name so i'm just going to copy it and then let's paste it inside the initial catalog and the user id is going to be submitted so let's copy it user id yep so let me just create some semicolons and then add it and then server username is the username and the password is same thing that will be encapsulated inside the curly braces so you can just use the same string too it doesn't matter you have to follow my way in order to do it so let me paste it inside oh my god i have to paste it inside here all right so that's all now we have to create some sql connection and then uh use our plugin in order to connect our app with the ssms here sqs connection can be inherited from sql connection using system.data.sqlclient which we have used previously that is our plugin so it is going to connect our ssms with the sql connection that we are defined over here so that our mobile app is going to interact with the sms so it is going to take the connection now we have to open the connection between our mobile app and the ssms so sql connection that is our sql connection over here let's copy it and then paste it over here dot open yup now we can just open it up so if we are able to open this sql connection without any of the failures then we are confirmed that our mobile app has been connected with the ssms so let me add that whole line of code inside try cats and then we'll be catching the exceptions that will be there inside the app so here i'll just put some breakpoint and this breakpoints are really mandatory in order to uh just check whether we have successfully connected our mobile app with the ssms or not so here you can see i have just added some breakpoints over here let's refresh this catch exception to you can see over here we have just uh refresh our catch exception that is adding some console.try line any extra misses and finally we have to fix some of the bug that have created over here that is d should be gaps that was just a typo now let's rerun the app and then see whether we'll get a successful output or not let's wait for some time all right our application is running on the real android phone now whenever i click on the button then i will go through the try step by hitting f10 on my keyboard so as soon as i click on after then we can see we'll just go navigate uh to the bottom of the line of the code where every breakpoints are going to be hit and let's wait for some time because it takes some time uh to either uh give a successful output or else it is going to give us a catch exception so you can see we can catch the exception over here so let's go to the message and it is telling me that uh there was network related instance server error so in order to fix this type of error that is basically due to the iph connections so let's uh quickly go to the sql server management configurations and then uh fix it out if that successful connection was done then you could have gone to line number 32 where there is a breakpoint but we've not connected our mobile app with the ssms so in order to do it we have to first of all go to sql server 2019 configuration then inside sql server network configuration uh we have to double click in it and then go to the protocols for sql express and then tcp ip connection inside the properties section right clicking on it uh you can go over there and then inside the ip section uh let's go below and then select the tcp protocol for our all the connection let's go below so we have to just see where is the uh ipad is for all here we have to change the tcp port to 1433 and then click on apply and then our dialog box is appeared it is just telling us to restart the service in order to see the changes click on ok now inside the uh tcp ip section itself we have to just enable it yes and then click on ok so now our sql server service in the second option here you can see let's go to the second one right click on it and then restart it now again let's go back to the protocols for sql expressed and then inside the tcp ip section here inside the ip address i have to enable all the ip address over here so that we will just get a ping to all the ip address wherever our mobile app like application is connected with the ssmsdb so i'll just enable all the ip address over here so let's go down enable it all right now we are ready to apply the changes and then click on ok now again we have to restart our sql server so click on restart and wait for some time as it will stop as well as start the service now i'm going to turn off my firewall as uh this step is not recommended and then i just want you to test the app and without turning up the firewall and then see whether you are catching up with the uh another exception or not as of now so here i was facing some issue that's why i'm just closing the firewall off so let's go to the turn windows defender firewall on and off section over here then it will navigate to this dialog box and then turning it from here and then clicking on ok uh let's wait for some time and then close this dialog box so our firewall is successfully turned off so but this step is not recommended and i have just follow up with this stack overflow questions as it was telling me to turn the firewall off address so let me paste it over here that is our server name that is our data source now let's rerun the app and then see whether we are able to hit the breakpoint at line number 32 or not that is our crucial step that will be setting so whenever i click on that button clicking on continue it will just hit the api and then it is telling me a next exception you can see over here now we are getting next exception that is make sure you have connected international code set assembly installed and enabled uh or not so this is our next bug that we have to fix it out so that is a really really easy bug in order to fix this issue we have to first of all uh enable some of the property inside the android as well as the ios so in order to do it the good news is i have already done some research and then we have already made solution in our stack overflow questions so you can see uh for our ios it is just telling me to uh add the waste as an internationalization and for the android 2 uh it is telling me to support it encoding as waste that's it so let me close the app at first and then go to the android properties and the same step is going to be followed for uh ios also that is internationalization uh for our ios to be waste so uh go to the properties you can just see my videos description where i'll give those link where you can just refer it so i'll just go to the properties android option and then you can see i have just selected the waste option and this is for ios so this link i'll just give in my videos description where you will just get it out so that's all for the ios configuration now let's go to the uh app itself and then see whether our application was able to run and then go to line number 32 or not so let's wait for some time till our application loads up so we'll be expecting our line number 32b hit as soon as our connection was open between our mobile app and the ssms database all right now the application is running on my real android phone then you can see over here when i click on connect with the server and clicking on continue you can see oh my god we just connected our mobile app with the sms database you can see over here we have successfully connected it now our next work is to get all the list items that is there inside our ssms database inside our xamarin forms mobile app as well as dot in my app so uh the video has already exceeded long so i'll just continue the video on my next section where we'll just get all the content item that is our list item from the ssms database till then stay safe and take care

Channel: Xamarin Guy

Views: 35,250

Keywords:

Id: xNmIdFjXzl4

Length: 23min 37sec (1417 seconds)

Published: Mon Jul 05 2021