快速入门:使用 .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

此快速入门使用以下任意指南中创建的资源作为起点:This quickstart uses the resources created in either of these guides as a starting point:

还需要:You also need to:

  • 安装 .NET FrameworkInstall the .NET Framework. 按照链接文章中的步骤来安装专用于平台(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 或 Visual Studio Code,用于键入和编辑代码。Install Visual Studio or Visual Studio Code to type and edit code.
  • 添加对 Npgsql Nuget 包的引用。Add a reference to the Npgsql Nuget package.

获取连接信息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 服务器名称Azure Database for PostgreSQL server name

进行连接,创建表,然后插入数据Connect, create table, 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 类,通过 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 method CreateCommand(), sets the CommandText property, and calls the ExecuteNonQuery() method 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 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();
        }
    }
}

读取数据Read data

使用以下代码进行连接,并使用 SELECT SQL 语句来读取数据。Use the following code to connect and read the data using a SELECT SQL statement. 代码使用 NpgsqlCommand 类,通过 Open() 方法建立到 PostgreSQL 的连接。The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. 然后,代码使用 CreateCommand() 方法和 ExecuteReader() 方法运行数据库命令。Then the code uses the methods CreateCommand() and ExecuteReader() to run the database commands. 接下来,代码使用 Read() 转到结果中的记录。Next, the code uses Read() to advance to the record in the results. 最后,代码使用 GetInt32()GetString() 分析记录中的值。Finally, the code uses GetInt32() and GetString() to parse the values in the record.

将 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()
                                )
                            );
                    }
                }
            }

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

更新数据Update data

使用以下代码进行连接,并使用 UPDATE SQL 语句更新数据****。Use the following code to connect and update the data using an UPDATE SQL statement. 代码使用 NpgsqlCommand 类,通过 Open() 方法建立到 PostgreSQL 的连接。The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. 然后,代码使用 CreateCommand() 方法,设置 CommandText 属性,再调用 ExecuteNonQuery() 方法来运行数据库命令。Then, the code uses method CreateCommand(), sets the CommandText property, and calls the ExecuteNonQuery() method 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 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();
        }
    }
}


删除数据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();
        }
    }
}

后续步骤Next steps