快速入门:使用 .NET (C#) 连接到 Azure Database for PostgreSQL 并查询其中的数据 - 单一服务器Quickstart: Use .NET (C#) to connect and query data in Azure Database for PostgreSQL - Single Server

本快速入门演示了如何使用 C# 应用程序连接到 Azure Database for PostgreSQL。This quickstart demonstrates how to connect to an Azure Database for PostgreSQL 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 PostgreSQL。The steps in this article assume that you are familiar with developing using C#, and that you are new to working with Azure Database for PostgreSQL.

先决条件Prerequisites

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

  • 具有活动订阅的 Azure 帐户。An Azure account with an active subscription. 创建试用帐户Create an account for trial.

  • 使用 Azure 门户Create an Azure Database for PostgreSQL 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
  • 安装适用于平台(Windows、Ubuntu Linux 或 macOS)的 .NET FrameworkInstall the .NET Framework for your platform (Windows, Ubuntu Linux, or macOS).

  • 安装 Visual Studio 以生成项目。Install Visual Studio to build your project.

  • 在 Visual Studio 中安装 Npgsql NuGet 包。Install Npgsql NuGet package in Visual Studio.

获取连接信息Get connection information

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

  1. 登录到 Azure 门户Log in to the Azure portal.
  2. 在 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).
  3. 单击服务器名称。Click the server name.
  4. 从服务器的“概览”面板中记下“服务器名称”和“服务器管理员登录名”。 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. Azure Database for PostgreSQL 服务器名称

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

通过以下代码进行连接,然后使用 CREATE TABLEINSERT INTO SQL 语句加载数据。Use the following code to connect and load the data using CREATE TABLE and INSERT INTO SQL statements. 代码使用 NpgsqlCommand 类和以下方法:The code uses NpgsqlCommand class with method:

重要

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

using System;
using Npgsql;

namespace Driver
{
    public class AzurePostgresCreate
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mydemoserver.postgres.database.chinacloudapi.cn";
        private static string User = "mylogin@mydemoserver";
        private static string DBname = "mypgsqldb";
        private static string Password = "<server_admin_password>";
        private static string Port = "5432";

        static void Main(string[] args)
        {
            // Build connection string using parameters from portal
            //
            string connString =
                String.Format(
                    "Server={0};Username={1};Database={2};Port={3};Password={4};SSLMode=Prefer",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);


            using (var conn = new NpgsqlConnection(connString))

            {
                Console.Out.WriteLine("Opening connection");
                conn.Open();

                using (var command = new NpgsqlCommand("DROP TABLE IF EXISTS inventory", conn))
                {
                    command.ExecuteNonQuery();
                    Console.Out.WriteLine("Finished dropping table (if existed)");

                }

                using (var command = new NpgsqlCommand("CREATE TABLE inventory(id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER)", conn))
                {
                    command.ExecuteNonQuery();
                    Console.Out.WriteLine("Finished creating table");
                }

                using (var command = new NpgsqlCommand("INSERT INTO inventory (name, quantity) VALUES (@n1, @q1), (@n2, @q2), (@n3, @q3)", conn))
                {
                    command.Parameters.AddWithValue("n1", "banana");
                    command.Parameters.AddWithValue("q1", 150);
                    command.Parameters.AddWithValue("n2", "orange");
                    command.Parameters.AddWithValue("q2", 154);
                    command.Parameters.AddWithValue("n3", "apple");
                    command.Parameters.AddWithValue("q3", 100);

                    int nRows = command.ExecuteNonQuery();
                    Console.Out.WriteLine(String.Format("Number of rows inserted={0}", nRows));
                }
            }

            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 using a SELECT SQL statement. 代码使用 NpgsqlCommand 类和以下方法:The code uses NpgsqlCommand class with method:

重要

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

using System;
using Npgsql;

namespace Driver
{
    public class AzurePostgresRead
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mydemoserver.postgres.database.chinacloudapi.cn";
        private static string User = "mylogin@mydemoserver";
        private static string DBname = "mypgsqldb";
        private static string Password = "<server_admin_password>";
        private static string Port = "5432";

        static void Main(string[] args)
        {
            // Build connection string using parameters from portal
            //
            string connString =
                String.Format(
                    "Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Prefer",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            using (var conn = new NpgsqlConnection(connString))
            {

                Console.Out.WriteLine("Opening connection");
                conn.Open();


                using (var command = new NpgsqlCommand("SELECT * FROM inventory", conn))
                {

                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine(
                            string.Format(
                                "Reading from table=({0}, {1}, {2})",
                                reader.GetInt32(0).ToString(),
                                reader.GetString(1),
                                reader.GetInt32(2).ToString()
                                )
                            );
                    }
                    reader.Close();
                }
            }

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

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

使用以下代码进行连接,并使用 UPDATE SQL 语句更新数据。Use the following code to connect and update the data using an UPDATE SQL statement. 代码使用 NpgsqlCommand 类和以下方法:The code uses NpgsqlCommand class with method:

重要

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

using System;
using Npgsql;

namespace Driver
{
    public class AzurePostgresUpdate
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mydemoserver.postgres.database.chinacloudapi.cn";
        private static string User = "mylogin@mydemoserver";
        private static string DBname = "mypgsqldb";
        private static string Password = "<server_admin_password>";
        private static string Port = "5432";

        static void Main(string[] args)
        {
            // Build connection string using parameters from portal
            //
            string connString =
                String.Format(
                    "Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Prefer",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            using (var conn = new NpgsqlConnection(connString))
            {

                Console.Out.WriteLine("Opening connection");
                conn.Open();

                using (var command = new NpgsqlCommand("UPDATE inventory SET quantity = @q WHERE name = @n", conn))
                {
                    command.Parameters.AddWithValue("n", "banana");
                    command.Parameters.AddWithValue("q", 200);
                    int nRows = command.ExecuteNonQuery();
                    Console.Out.WriteLine(String.Format("Number of rows updated={0}", nRows));
                }
            }

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


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

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

代码使用 NpgsqlCommand 类,通过 Open() 方法建立与 PostgreSQL 数据库的连接。The code uses NpgsqlCommand class with method Open() to establish a connection to the PostgreSQL database. 然后,代码使用 CreateCommand() 方法,设置 CommandText 属性,再调用 ExecuteNonQuery() 方法来运行数据库命令。Then, the code uses the CreateCommand() method, sets the CommandText property, and calls the method ExecuteNonQuery() to run the database commands.

重要

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

using System;
using Npgsql;

namespace Driver
{
    public class AzurePostgresDelete
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mydemoserver.postgres.database.chinacloudapi.cn";
        private static string User = "mylogin@mydemoserver";
        private static string DBname = "mypgsqldb";
        private static string Password = "<server_admin_password>";
        private static string Port = "5432";

        static void Main(string[] args)
        {
            // Build connection string using parameters from portal
            //
            string connString =
                String.Format(
                    "Server={0}; User Id={1}; Database={2}; Port={3}; Password={4};SSLMode=Prefer",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            using (var conn = new NpgsqlConnection(connString))
            {
                Console.Out.WriteLine("Opening connection");
                conn.Open();

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

                    int nRows = command.ExecuteNonQuery();
                    Console.Out.WriteLine(String.Format("Number of rows deleted={0}", nRows));
                }
            }

            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