快速入门：使用 .NET (C#) 连接到 Azure Database for MySQL 并查询其中的数据Quickstart: Use .NET (C#) to connect and query data in Azure Database for MySQL

11/23/2020

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档，请访问此页。To view the documentation for classic MySQL Database for Azure, please visit this page.

本快速入门演示如何使用 C# 应用程序连接到 Azure Database for MySQL。This quickstart demonstrates how to connect to an Azure Database for MySQL by using a C# application. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database.

先决条件Prerequisites

对于本快速入门，你需要：For this quickstart you need:

具有活动订阅的 Azure 帐户。An Azure account with an active subscription. 创建一个试用帐户。Create a trial account.
使用 Azure 门户创建 Azure Database for MySQL 单一服务器Create an Azure Database for MySQL single server using Azure portal
或 Azure CLI 创建 Azure Database for PostgreSQL 单一服务器（如果没有）。or Azure CLI if you do not have one.
请完成以下操作之一以启用连接，具体取决于你使用的是公共访问还是私有访问。Based on whether you are using public or private access, complete ONE of the actions below to enable connectivity.

操作Action	连接方法Connectivity method	操作指南How-to guide
配置防火墙规则Configure firewall rules	公用Public	门户Portal CLICLI
配置服务终结点Configure Service Endpoint	公用Public	门户Portal CLICLI
配置专用链接Configure private link	PrivatePrivate	门户Portal CLICLI

创建数据库和非管理员用户Create a database and non-admin user

创建一个 C# 项目Create a C# project

在命令提示符处运行以下命令：At a command prompt, run:

mkdir AzureMySqlExample
cd AzureMySqlExample
dotnet new console
dotnet add package MySqlConnector

获取连接信息Get connection information

获取连接到 Azure Database for MySQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for MySQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

登录到 Azure 门户。Log in to the Azure portal.
在 Azure 门户的左侧菜单中，单击“所有资源”，然后搜索已创建的服务器（例如 mydemoserver）。From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
单击服务器名称。Click the server name.
从服务器的“概览”面板中记下“服务器名称”和“服务器管理员登录名”。 From the server's Overview panel, make a note of the Server name and Server admin login name. 如果忘记了密码，也可通过此面板来重置密码。If you forget your password, you can also reset the password from this panel.

步骤 1：连接并插入数据Step 1: Connect and insert data

通过以下代码来连接和加载数据，只需使用 CREATE TABLE 和 INSERT INTO SQL 语句即可。Use the following code to connect and load the data by using CREATE TABLE and INSERT INTO SQL statements. 代码使用 MySqlConnection 类的以下方法：The code uses the methods of the MySqlConnection class:

OpenAsync()，用于与 MySQL 建立连接。OpenAsync() to establish a connection to MySQL.
CreateCommand()，用于设置 CommandText 属性CreateCommand(), sets the CommandText property
ExecuteNonQueryAsync()，用于运行数据库命令。ExecuteNonQueryAsync() to run the database commands.

将 Server、Database、UserID、Password 参数替换为你在创建服务器和数据库时指定的值。Replace the Server, Database, UserID, and Password parameters with the values that you specified when you created the server and database.

using System;
using System.Threading.Tasks;
using MySqlConnector;

namespace AzureMySqlExample
{
    class MySqlCreate
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.chinacloudapi.cn",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "DROP TABLE IF EXISTS inventory;";
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Finished dropping table (if existed)");

                    command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Finished creating table");

                    command.CommandText = @"INSERT INTO inventory (name, quantity) VALUES (@name1, @quantity1),
                        (@name2, @quantity2), (@name3, @quantity3);";
                    command.Parameters.AddWithValue("@name1", "banana");
                    command.Parameters.AddWithValue("@quantity1", 150);
                    command.Parameters.AddWithValue("@name2", "orange");
                    command.Parameters.AddWithValue("@quantity2", 154);
                    command.Parameters.AddWithValue("@name3", "apple");
                    command.Parameters.AddWithValue("@quantity3", 100);

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows inserted={0}", rowCount));
                }

                // connection will be closed by the 'using' block
                Console.WriteLine("Closing connection");
            }

            Console.WriteLine("Press RETURN to exit");
            Console.ReadLine();
        }
    }
}

步骤 2：读取数据Step 2: Read data

使用以下代码进行连接，并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement. 该代码将 MySqlConnection 类与以下方法配合使用：The code uses the MySqlConnection class with methods:

OpenAsync()，用于与 MySQL 建立连接。OpenAsync() to establish a connection to MySQL.
CreateCommand()，用于设置 CommandText 属性。CreateCommand() to set the CommandText property.
ExecuteReaderAsync()，用于运行数据库命令。ExecuteReaderAsync() to run the database commands.
ReadAsync()，用于转到结果中的记录。ReadAsync() to advance to the records in the results. 然后，代码使用 GetInt32 和 GetString 分析记录中的值。Then the code uses GetInt32 and GetString to parse the values in the record.

using System;
using System.Threading.Tasks;
using MySqlConnector;

namespace AzureMySqlExample
{
    class MySqlRead
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.chinacloudapi.cn",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM inventory;";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine(string.Format(
                                "Reading from table=({0}, {1}, {2})",
                                reader.GetInt32(0),
                                reader.GetString(1),
                                reader.GetInt32(2)));
                        }
                    }
                }

                Console.WriteLine("Closing connection");
            }

            Console.WriteLine("Press RETURN to exit");
            Console.ReadLine();
        }
    }
}

步骤 3：更新数据Step 3: Update data

使用以下代码进行连接，并使用 UPDATE SQL 语句读取数据。Use the following code to connect and read the data by using an UPDATE SQL statement. 该代码将 MySqlConnection 类与以下方法配合使用：The code uses the MySqlConnection class with method:

OpenAsync()，用于与 MySQL 建立连接。OpenAsync() to establish a connection to MySQL.
CreateCommand()，用于设置 CommandText 属性CreateCommand() to set the CommandText property
ExecuteNonQueryAsync()，用于运行数据库命令。ExecuteNonQueryAsync() to run the database commands.

using System;
using System.Threading.Tasks;
using MySqlConnector;

namespace AzureMySqlExample
{
    class MySqlUpdate
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.chinacloudapi.cn",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "UPDATE inventory SET quantity = @quantity WHERE name = @name;";
                    command.Parameters.AddWithValue("@quantity", 200);
                    command.Parameters.AddWithValue("@name", "banana");

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows updated={0}", rowCount));
                }

                Console.WriteLine("Closing connection");
            }

            Console.WriteLine("Press RETURN to exit");
            Console.ReadLine();
        }
    }
}

步骤 4：删除数据Step 4: Delete data

使用以下代码进行连接，并使用 DELETE SQL 语句删除数据。Use the following code to connect and delete the data by using a DELETE SQL statement.

该代码将 MySqlConnection 类与以下方法配合使用The code uses the MySqlConnection class with method

OpenAsync()，用于与 MySQL 建立连接。OpenAsync() to establish a connection to MySQL.
CreateCommand()，用于设置 CommandText 属性。CreateCommand() to set the CommandText property.
ExecuteNonQueryAsync()，用于运行数据库命令。ExecuteNonQueryAsync() to run the database commands.

using System;
using System.Threading.Tasks;
using MySqlConnector;

namespace AzureMySqlExample
{
    class MySqlDelete
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.chinacloudapi.cn",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "DELETE FROM inventory WHERE name = @name;";
                    command.Parameters.AddWithValue("@name", "orange");

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows deleted={0}", rowCount));
                }

                Console.WriteLine("Closing connection");
            }

            Console.WriteLine("Press RETURN to exit");
            Console.ReadLine();
        }
    }
}

清理资源Clean up resources

若要清理本快速入门中使用的所有资源，请使用以下命令删除该资源组：To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

后续步骤Next steps

使用门户管理 Azure Database for MySQL 服务器Manage Azure Database for MySQL server using Portal

使用 CLI 管理 Azure Database for MySQL 服务器Manage Azure Database for MySQL server using CLI