Issue
I need to create an online store type app for iOS and android. I want to use MySQL for the database and I think Xamarin is the best option to develop the app. Is this possible? Can I connect to a MySQL database using Xamarin?
I've only ever made apps with Android Studio (Java) and Firebase databases, so I'm still new and learning.
I would appreciate any tips or advice, too! Thanks!
Solution
The first idea is very dangerous, the second one is safer
Thanks to the Xamarin.MySQL.Data plugin we can make a connection to a MySQL database from our Xamarin.Android application.
Important: Before we start, remember that as any application can be decompiled and hackers can get the data from your connection, take your precautions or use it at your own risk.
1. Download and install the Xamarin.MySQL.Data plugin.
To do this we go to the NuGet package manager, which is located in Tools -> NuGet Package Manager and Manage NuGet packages for the solution...
In the window that opens, we change to Browse, there we will look for 'Xamarin.MySQL.Data' and we install it in our project (We accept the license).
Now if we check in the dependencies of our project, we will see that the .dll is already there.
2. Make the connection to the MySQL database.
For this example I am going to create a method called TryConnection that receives the username and password of the database user that will allow me to establish a connection to my database.
using System;
using Android.Content;
using Android.Widget;
using MySql.Data.MySqlClient;
namespace MySQL_Xamarin
{
public class Conexion
{
/// <summary>
/// Prueba la conexión a la base de datos utilizando las credenciales correspondientes
/// </summary>
/// <param name="context"></param>
/// <param name="Usuario"></param>
/// <param name="Contrasenia"></param>
/// <returns></returns>
public bool TryConnection(Context context, string Usuario,string Contrasenia)
{
MySqlConnectionStringBuilder Builder = new MySqlConnectionStringBuilder();
Builder.Port = 3306;
//Al ser una BD Online debes usar la ip de tu servidor y no localhost
Builder.Server = "tu.servidor.mysql";
Builder.Database = "tu_base_de_datos";
Builder.UserID = Usuario; //Es el usuario de la base de datos
Builder.Password = Contrasenia; //La contraseña del usuario
try
{
MySqlConnection ms = new MySqlConnection(Builder.ToString());
ms.Open(); //Debes agregar la referencia System.Data
return true;
}
catch (Exception ex)
{
Toast.MakeText(context,ex.ToString(), ToastLength.Long).Show(); //Muestra un Toast con el error (Puede ser muy largo)
return false;
}
}
}
}
Remember that you cannot use 'localhost' as server unless you configure your PC to be a MySQL server, in my case I will use the scrapywar.com server.
3. Check the database connection
For that I have this layout that admits a user and a password that are the credentials to make the connection to the database.
In the MainActivity we are going to assign the elements of the Layout to objects of the same type and we are going to give an action to the button, the result is the following one.
//Declaración de los componentes.
Button access;
EditText User, Password;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
Xamarin.Essentials.Platform.Init(this, savedInstanceState);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.activity_main);
//Se asignan los controles del Layout a una varaible del mismo tipo
access = FindViewById<Button>(Resource.Id.btn_entrar);
User = FindViewById<EditText>(Resource.Id.et_user);
Password = FindViewById<EditText>(Resource.Id.et_password);
//La accion de presionar el boton
access.Click += delegate {
//Se crea una instancia de la clase Conexion
Conexion con = new Conexion();
//Se prueba la conexion pasando los datos de los EditText que son usuario y contraseña
if (con.TryConnection(this,User.Text,Password.Text)){
Toast.MakeText(this, "Conexion Exitosa!", ToastLength.Long).Show();
}
else{
Toast.MakeText(this, "Error!", ToastLength.Long).Show();
}
};
}
4. Implement in a simulator or device
If you have the official Android emulator or any other you can directly implement the application, if you want to generate an APK visit my post: 'How to generate an apk in Xamarin.Android'.
I implemented it on my device and as we can see when entering the correct username and password I get a 'successful connection' message otherwise it will give error.
URL : https://scrapywar.com/conectar-aplicacion-xamarin-android-a-bd-mysql-online/
Note : The page is in Spanish because it is my native language but I have translated it.
OTHER OPTION
Get Data from MySQL API
Currently, our routes.js file is manually creating a JSON array of users, which looks like this.
const users = [{ ... Since we are no longer going to use static data, we can remove any array and replace it with a link to our MySQL pool.
// Load the MySQL pool connection
const pool = require('../data/config');
Previously, the GET on the /users path sent static user data. Our updated code is going to query the database for that data instead. We are going to use a SQL SELECT query for the entire users table, which looks like this.
SELECT * FROM users
Here is what our new /users path will look like, using the pool.query() method.
// Display all users
app.get('/users', (request, response) => {
pool.query('SELECT * FROM users', (error, result) => {
if (error) throw error;
response.send(result);
});
});
Here, we are running the SELECT query and then sending the result as JSON to the client via the /users endpoint. If you restart the server and go to the /users page, you will see the same data as before, but now it is dynamic.
Using URL Parameters
So far, our endpoints have been static paths, either /root or /users - but what happens when we want to see data only about a specific user? We will need to use a variable endpoint.
For our users, we might want to retrieve information about each individual user based on their unique identifier. To do this, we would use a colon (:) to indicate that it is a path parameter.
// Display a single user by ID
app.get('/users/:id', (request, response) => {
...
});
});
We can retrieve the parameter for this path with the request.params property. Since our is called id, it will be how we refer to it.
const id = request.params.id;
Now let's add a WHERE clause to the SELECT statement to get only results that have the specified id.
We will use ? as a placeholder to avoid SQL injection and pass the id as a parameter, instead of constructing a concatenated string, which would be less secure.
pool.query('SELECT * FROM users WHERE id = ?', id, (error, result) => {
if (error) throw error;
response.send(result);
});
The complete code of our individual user resource now looks like this:
// Display a single user by ID
app.get('/users/:id', (request, response) => {
const id = request.params.id;
pool.query('SELECT * FROM users WHERE id = ?', id, (error, result) => {
if (error) throw error;
response.send(result);
});
});
Now you can restart the server and go to https://localhost/users/2 to see only the information for Gilfoyle. If you get an error like Cannot GET /users/2, it means you will have to restart the server.
Go to this URL which returns a single result.
[{
id: 2,
name: "Bertram Gilfoyle",
email: "[email protected]"
}]
If that's what it looks like, congratulations: you have successfully configured a dynamic route parameter!
Send a POST Request
So far, everything we have been doing has been using GET requests. These requests are secure, meaning they don't alter the state of the server. We've simply been looking at JSON data.
Now let's start making the API truly dynamic by using a POST request to add new data.
I mentioned earlier in the rest understanding article that you don't use verbs like add or delete in the URL to perform actions. To add a new user to the database, POST to the same URL to view them from, but just program a separate path for it.
// Add a new user
app.post('/users', (request, response) => {
...
});
Note that we are using app.post() instead of app.get() now.
Since we are creating instead of reading, we are going to use an INSERT query, just as much as we did in the database initialization. It will deliver the entire request.body through the SQL query.
pool.query('INSERT INTO users SET ?', request.body, (error, result) => {
if (error) throw error;
We will also specify the status of the response as 201, which means Created. In order to get the id of the last inserted element, we will use the insertId property.
response.status(201).send(`User added with ID: ${result.insertId}`);
Our entire POST when receiving code will look like this.
// Add a new user
app.post('/users', (request, response) => {
pool.query('INSERT INTO users SET ?', request.body, (error, result) => {
if (error) throw error;
response.status(201).send(`User added with ID: ${result.insertId}`);
});
});
Now we can send a POST request through. Most of the time when you send a POST request, you do it through a web form. We will learn how to configure that at the end of this article, but the easiest and fastest way to send a POST test with cURL, use the -d(--data) flag.
We run curl -d, followed by a query string containing all the key/value pairs and the endpoint of the request.
curl -d "name=Dinesh Chugtai&[email protected]" http://localhost:3002/users
Once you send this request through, you should get a response from the server.
User added with ID: 3
If you access http://localhost/users, you will see the most recent entry added to the list.
Sending a PUT Request
POST is useful for adding a new user, but you want to use PUT to modify an existing user. PUT is idempotent, meaning you can send the same request through multiple times and it will perform a single action. This is different from POST, because if we send our new user request through more than once, we keep creating new users.
For our API, we are going to configure PUT to be able to handle editing a single user, so we are going to use the :id path parameter this time.
Let's create an UPDATE query and make sure it only applies to the required id with the WHERE clause. We are using two ? placeholders and the values we pass will go in sequential order.
// Update an existing user
app.put('/users/:id', (request, response) => {
const id = request.params.id;
pool.query('UPDATE users SET ? WHERE id = ?', [request.body, id], (error, result) => {
if (error) throw error;
response.send('User updated successfully.');
});
});
For our test, we edit user 2 and update the email address from [email protected] to [email protected]. We can use curly braces again, with the [-X (--request)] flag, to explicitly specify that we are sending a post request through.
curl -X PUT -d "name=Bertram Gilfoyle" -d "[email protected]" http://localhost:3002/users/2
Be sure to restart the server before sending the request, or else you will get the Cannot PUT /users/2 error.
You should see this:
User updated successfully.
The user with id 2 should now be updated.
Send a DELETE request
Our last task to complete the CRUD functionality of the API is an option to delete a user from the database. This request uses the SQL DELETE query with WHERE, and will delete an individual user specified by a path parameter.
// Delete a user
app.delete('/users/:id', (request, response) => {
const id = request.params.id;
pool.query('DELETE FROM users WHERE id = ?', id, (error, result) => {
if (error) throw error;
response.send('User deleted.');
});
});
We can use -X again with the rollup to send the cancellation through. Let's delete the most recent user we created.
curl -X DELETE http://localhost:3002/users/3
You will see the success message.
User deleted.
Go to http://localhost:3002 and you will see that there are now only two users.
Send requests through the request module.
At the beginning of this article, we installed four dependencies, and one of them was the request module. Instead of using rollup requests, I could make a new file with all the data and send it through. I will create a file called post.js that will create a new user via POST.
const request = require('request');
const json = {
"name": "Dinesh Chugtai",
"email": "[email protected]",
};
request.post({
url: 'http://localhost:3002/users',
body: json,
json: true,
}, function (error, response, body) {
console.log(body);
});
We can call this using node post.js in a new terminal window, while the server is running, and it will have the same effect as using cURL. If something is not working with the rollup, the request module is useful as we can see the error, response and body.
Submitting Applications through the Web Form
Typically, POST and other HTTP methods that alter server state are sent using HTML forms. In this very simple example, we can create an index.html file anywhere and make a field for a name and email. The form's action will point to the resource, in this case http//localhost:3002/users and specify the method as post.
Create the index.html and add the following code to it:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Node.js Express REST API</title>
</head>
<body>
<form action="http://localhost:3002/users" method="post">
<label for="name">Name</label>
<input type="text" name="name">
<label for="email">Email</label>
<input type="email" name="email">
<input type="submit">
</form>
</body>
</html>
Open this static HTML file in your browser, fill it out and submit while the server is running in the terminal. You should see the response User added with ID: 4, and you should be able to see the new list of users.
Conclusion
In this tutorial we have learned how to connect an Express server to a MySQL database and set routes that correspond to the GET, POST, PUT and DELETE methods and dynamic path parameters. We also learned how to send HTTP requests to an API server using rollup, the Node.js request module and HTML forms.
At this point, you should have a very good understanding of how RESTful APIs work, and can now create your own full-fledged API in Node.js with Express and MySQL!
Answered By - Javier G.Raya
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.