Azure Database for MySQL：使用 .NET (C#) 连接和查询数据Azure Database for MySQL: Use .NET (C#) to connect and query data
备注
将要查看的是 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. 本主题假设你熟悉如何使用 C# 进行开发，但不太熟悉 Azure Database for MySQL 的用法。This topic assumes that you are familiar with developing using C# and that you are new to working with Azure Database for MySQL.
先决条件Prerequisites
此快速入门使用以下任意指南中创建的资源作为起点：This quickstart uses the resources created in either of these guides as a starting point:
- 使用 Azure 门户创建用于 MySQL 服务器的 Azure 数据库Create an Azure Database for MySQL server using Azure portal
- 使用 Azure CLI 创建用于 MySQL 服务器的 Azure 数据库Create an Azure Database for MySQL server using Azure CLI
还需要：You also need to:
- 安装 .NET。Install .NET. 按照链接文章中的步骤来安装专用于平台（Windows、Ubuntu Linux 或 macOS）的 .NET。Follow the steps in the linked article to install .NET specifically for your platform (Windows, Ubuntu Linux, or macOS).
- 安装 Visual Studio。Install Visual Studio.
重要
确保已使用 Azure 门户或 Azure CLI 将服务器的防火墙规则添加到连接的 IP 地址Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI
创建一个 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.
进行连接，创建表，然后插入数据Connect, create table, 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 类，通过 OpenAsync() 方法与 MySQL 建立连接。The code uses the MySqlConnection class with method OpenAsync() to establish a connection to MySQL. 然后，代码使用 CreateCommand() 方法，设置 CommandText 属性，再调用 ExecuteNonQueryAsync() 方法来运行数据库命令。Then the code uses method CreateCommand(), sets the CommandText property, and calls method 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 MySql.Data.MySqlClient;
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();
}
}
}
读取数据Read data
使用以下代码进行连接，并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement. 代码使用 MySqlConnection 类，通过 OpenAsync() 方法与 MySQL 建立连接。The code uses the MySqlConnection class with method OpenAsync() to establish a connection to MySQL. 然后，代码使用 CreateCommand() 方法和 ExecuteReaderAsync() 方法运行数据库命令。Then the code uses method CreateCommand() and method ExecuteReaderAsync() to run the database commands. 接下来，代码使用 ReadAsync() 转到结果中的记录。Next the code uses 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.
将 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 MySql.Data.MySqlClient;
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();
}
}
}
更新数据Update data
使用以下代码进行连接，并使用 UPDATE SQL 语句读取数据。Use the following code to connect and read the data by using an UPDATE SQL statement. 代码使用 MySqlConnection 类，通过 OpenAsync() 方法与 MySQL 建立连接。The code uses the MySqlConnection class with method OpenAsync() to establish a connection to MySQL. 然后，代码使用 CreateCommand() 方法，设置 CommandText 属性，再调用 ExecuteNonQueryAsync() 方法来运行数据库命令。Then the code uses method CreateCommand(), sets the CommandText property, and calls method 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 MySql.Data.MySqlClient;
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();
}
}
}
删除数据Delete data
使用以下代码进行连接，并使用 DELETE SQL 语句删除数据。Use the following code to connect and delete the data by using a DELETE SQL statement.
代码使用 MySqlConnection 类，通过 OpenAsync() 方法与 MySQL 建立连接。The code uses the MySqlConnection class with method OpenAsync() to establish a connection to MySQL. 然后，代码使用 CreateCommand() 方法，设置 CommandText 属性，再调用 ExecuteNonQueryAsync() 方法来运行数据库命令。Then the code uses method CreateCommand(), sets the CommandText property, and calls method 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 MySql.Data.MySqlClient;
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();
}
}
}