我需要為 iOS 和 android 創建一個在線商店型別的應用程式。我想使用 MySQL 作為資料庫，我認為 Xamarin 是開發應用程式的最佳選擇。這可能嗎？我可以使用 Xamarin 連接到 MySQL 資料庫嗎？

我只用 Android Studio (Java) 和 Firebase 資料庫制作過應用程式，所以我還是新手，還在學習。

我也將不勝感激任何提示或建議！謝謝！

uj5u.com熱心網友回復：

第一個想法很危險，第二個更安全

感謝 Xamarin.MySQL.Data 插件，我們可以從 Xamarin.Android 應用程式連接到 MySQL 資料庫。

重要提示：在我們開始之前，請記住，任何應用程式都可以被反編譯，黑客可以從您的連接中獲取資料，請采取預防措施或自擔風險使用它。

1. 下載并安裝 Xamarin.MySQL.Data 插件。

為此，我們轉到 NuGet 包管理器，它位于 Tools -> NuGet Package Manager 和 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!

URL : https://code.tutsplus.com/es/tutorials/code-your-first-api-with-nodejs-and-express-connect-a-database--cms-31699

uj5u.com熱心網友回復：

是的，您可以通過 Xamarin 制作應用程式并連接 MySQL 資料庫

標籤：安卓 IOS mysql 数据库 xamarin

上一篇：檢查多個列的單個值并僅回傳單個值

下一篇：如何撰寫mysql查詢，我需要在groupby之前放置orderby