Using PostgreSQL in C# and .NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

PostgreSQL is a free and open-source relational database management system (RDBMS). Postgres is also one of the most important relational database systems in the open-source world. In this C# programming tutorial, we will demonstrate how a developer can access the PostgreSQL database system using C#. Code examples will be provided to help the learning process go more smoothly.

How to Create a Postgres Database in Azure Cloud

For the purpose of demonstrating PostgreSQL’s capabilities, we will create a Postgres server in Microsoft’s Azure Cloud. To begin, login to https://portal.azure.com and search for PostgreSQL. Select the Azure Database for PostgreSQL servers option, as shown here:

Azure Database for PostgreSQL Servers in Azure

Next, click Create Azure Database for PostgreSQL server:

Create New Database for PostgreSQL Servers

From the Select Azure database for PostgreSQL options, choose the Single Server option and click Create:

PostgreSQL Single Server Option

Next, select Basic from the Configuration page. By default, Compute Generation will be Gen 5. VCore 1 and 5 GB storage should be good enough. Keep Backup Retention options at their default setting:

PostgreSQL Basic Configuration Options

Next, we need to add a new Resource Group, as shown below:

PostgreSQL New Resource Group

Then, add a user and password under the Administrator account section:

PostgreSQL Administrative Account Details

Finally, review the Estimated Cost and click Create to create your new PostgreSQL server.

PostgreSQL Database: Review and Create

Read: Best Relational Database Software

C# Connect String to Connect to a PostgreSQL Database

Note the connection string in the code below, once the server instance is created. Connect the Azure PostgreSQL server from the Visual Studio SQL Server Object explorer and create the following employee database and table, using the C# code example here:

CREATE TABLE employee  
(  
employeeid   integer NOT NULL,  
employeename varchar(50),  
employeesalary numeric(6,2),  
CONSTRAINT emp_pkey PRIMARY KEY (empno)  
)  
INSERT INTO employee  (employeeid , employeename, employeesalary ) VALUES (1, 'Tapas', 3400.99);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (2, 'Laltu', 2400.55);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (3, 'Biltu', 1900.99);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (4, 'Pintu', 1920.99);  

Creating a Console App in Visual Studio<//h3>

Open Visual Studio 2019 and Create a new project:

Visual Studio New Console Project

Select a Console App project, as depicted below:

New VS Console Project

Next, add the new Project Name and choose the Framework version. Click Create and a new console application will be created:

Console Project Name and Framework Version

A new project will be created. It should look similar to how ours looks in the Solution Explorer below:

New Console Project in VS Solution Explorer

The next step is to right-click on the project and select Manage NuGet Packages. Then, search for PostgreSQL and choose Npgsql. Install the Npqsql data provider as indicated below:

Npgsql Data Provider for PostgreSQL Installed

Finally, implement the following C# PostgreSQL connection code, written using the NpgsqlConnection in the Program.cs file of the console application, which will connect the Azure PostgreSQL database and import data from the employee table:

static void PostgreSQLConnection()
        {
            string strConnString = "Server=remote_server;Port=5432;User Id=user;Password=mypassword;Database=test";
            try
            {
                NpgsqlConnection objpostgraceConn = new NpgsqlConnection(strConnString);
                objpostgraceConn.Open();
                string strpostgracecommand = "select employeeid , employeename , employeesalary  from employee";
                NpgsqlDataAdapter objDataAdapter = new NpgsqlDataAdapter(strpostgracecommand, objpostgraceConn);
                objpostgraceConn.Close();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message, "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Read: Postgres Tricks in .NET

Conclusion to PsotgreSQL and C# Database Connection

We hope the above C# programming tutorial has illustrated how to access a PostgreSQL Azure database using C# and the Microsoft.NET framework. That’s all for today. Happy reading!

Read more database programming tutorials and product reviews.