Azure Database for PostgreSQL: Use .NET (C#) to connect and query data

This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a C# application. It shows how to use SQL statements to query, insert, update, and delete data in the database. 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:

  • Install .NET Framework. Follow the steps in the linked article to install .NET specifically for your platform (Windows, Ubuntu Linux, or macOS).
  • Install Visual Studio or Visual Studio Code to type and edit code.
  • Install Npgsql library as described below.

Install Npgsql references into your Visual Studio solution

To connect from the C# application to PostgreSQL, use the open source ADO.NET library called Npgsql. NuGet helps download and manage the references easily.

  1. Create a new C# solution, or open an existing one:

    • Within Visual Studio, create a solution, by clicking File menu New > Project.
    • In the New Project dialogue, expand Templates > Visual C#.
    • Choose an appropriate template such as Console App (.NET Core).
  2. Use the Nuget Package Manager to install Npgsql:

    • Click the Tools menu > NuGet Package Manager > Package Manager Console.
    • In the Package Manager Console, type Install-Package Npgsql
    • The install command downloads the Npgsql.dll and related assemblies and adds them as dependencies in the solution.

Get connection information

Get the connection information needed to connect to the Azure Database for PostgreSQL. You need the fully qualified server name and login credentials.

  1. Log in to the Azure portal.
  2. From the left-hand menu in Azure portal, click All resources and search for the server you have created, such as mypgserver-20170401.
  3. Click the server name mypgserver-20170401.
  4. Select the server's Overview page. Make a note of the Server name and Server admin login name. Azure Database for PostgreSQL - Server Admin Login
  5. If you forget your server login information, navigate to the Overview page to view the Server admin login name and, if necessary, reset the password.

Connect, create table, and insert data

Use the following code to connect and load the data using CREATE TABLE and INSERT INTO SQL statements. The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. Then the code uses method CreateCommand(), sets the CommandText property, and calls method ExecuteNonQuery() to run the database commands.

Replace the Host, DBName, User, and Password parameters with the values that you specified when you created the server and database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace Driver
{
    public class AzurePostgresCreate
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mypgserver-20170401.postgres.database.azure.com";
        private static string User = "mylogin@mypgserver-20170401";
        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}; SSL Mode=Prefer; Trust Server Certificate=true",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            var conn = new NpgsqlConnection(connString);

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

            var command = conn.CreateCommand();
            command.CommandText = "DROP TABLE IF EXISTS inventory;";
            command.ExecuteNonQuery();
            Console.Out.WriteLine("Finished dropping table (if existed)");

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

            command.CommandText =
                String.Format(
                    @"
                                INSERT INTO inventory (name, quantity) VALUES ({0}, {1});
                                INSERT INTO inventory (name, quantity) VALUES ({2}, {3});
                                INSERT INTO inventory (name, quantity) VALUES ({4}, {5});
                            ",
                    "\'banana\'", 150,
                    "\'orange\'", 154,
                    "\'apple\'", 100
                    );

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

            Console.Out.WriteLine("Closing connection");
            conn.Close();

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

Read data

Use the following code to connect and read the data using a SELECT SQL statement. The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. Then the code uses method CreateCommand() and method ExecuteReader() to run the database commands. Next the code uses Read() to advance to the records in the results. Then the code uses GetInt32() and GetString() to parse the values in the record.

Replace the Host, DBName, User, and Password parameters with the values that you specified when you created the server and database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace Driver
{
    public class AzurePostgresRead
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mypgserver-20170401.postgres.database.azure.com";
        private static string User = "mylogin@mypgserver-20170401";
        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};",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            var conn = new NpgsqlConnection(connString);

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

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

            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.Out.WriteLine("Closing connection");
            conn.Close();

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

Update data

Use the following code to connect and read the data using a UPDATE SQL statement. The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. Then the code uses method CreateCommand(), sets the CommandText property, and calls method ExecuteNonQuery() to run the database commands.

Replace the Host, DBName, User, and Password parameters with the values that you specified when you created the server and database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace Driver
{
    public class AzurePostgresUpdate
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mypgserver-20170401.postgres.database.azure.com";
        private static string User = "mylogin@mypgserver-20170401";
        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};",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            var conn = new NpgsqlConnection(connString);

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

            var command = conn.CreateCommand();
            command.CommandText =
            String.Format("UPDATE inventory SET quantity = {0} WHERE name = {1};",
                200,
                "\'banana\'"
                );

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

            Console.Out.WriteLine("Closing connection");
            conn.Close();

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

Delete data

Use the following code to connect and read the data using a DELETE SQL statement.

The code uses NpgsqlCommand class with method Open() to establish a connection to PostgreSQL. Then the code uses method CreateCommand(), sets the CommandText property, and calls method ExecuteNonQuery() to run the database commands.

Replace the Host, DBName, User, and Password parameters with the values that you specified when you created the server and database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace Driver
{
    public class AzurePostgresDelete
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "mypgserver-20170401.postgres.database.azure.com";
        private static string User = "mylogin@mypgserver-20170401";
        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};",
                    Host,
                    User,
                    DBname,
                    Port,
                    Password);

            var conn = new NpgsqlConnection(connString);

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

            var command = conn.CreateCommand();
            command.CommandText =
            String.Format("DELETE FROM inventory WHERE name = {0};",
                "\'orange\'");
            int nRows = command.ExecuteNonQuery();
            Console.Out.WriteLine(String.Format("Number of rows deleted={0}", nRows));

            Console.Out.WriteLine("Closing connection");
            conn.Close();

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

Next steps