Connect to Amazon RDS for PostgreSQL Using C#
Amazon RDS for PostgreSQL is a fully managed relational database service provided by Amazon Web Services (AWS). It takes care of many complex and time-consuming administrative tasks, making it easier to deploy and manage PostgreSQL databases, even for users without deep database administration experience. With its strong scalability, high availability, and reliable performance, Amazon RDS is a popular choice among PostgreSQL users who want to combine PostgreSQL's flexibility with the advantages of a fully managed cloud platform.
This guide will show you how to connect a C# application to a PostgreSQL database hosted on Amazon RDS. You'll learn how to configure the connection, access data, and perform basic data operations from a .NET environment. To streamline the process and avoid common connectivity issues, we'll also use dotConnect for PostgreSQL. Let's begin.
Why dotConnect for PostgreSQL?
dotConnect for PostgreSQL is a powerful ADO.NET data provider that allows .NET developers to establish connections to PostgreSQL databases directly and interact with the data efficiently, without any additional client libraries or complex configurations. dotConnect for PostgreSQL supports dynamic (on-the-fly) connector creation, flexible configuration options, and smooth integration with Visual Studio. You also get advanced ORM support, which includes compatibility with Entity Framework and Dapper and simplifies development and data access in .NET applications.
Prerequisites
The following prerequisites are necessary to follow this guide:
- Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
- dotConnect for PostgreSQL: A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
- Amazon RDS for PostgreSQL: A managed PostgreSQL database hosted on AWS for learning and testing.
Download and activate dotConnect for PostgreSQL
Depending on your requirements, you may choose a fully-functional free trial or a full version.
30-day free trial version
You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the installation options.
- Installer: Download and install dotConnect for PostgreSQL directly on your machine.
- NuGet: Add the Devart.Data.PostgreSql package to your project via NuGet.
No license key is necessary for the trial version — just install it and explore.
Full version
After purchasing the full version, follow these steps:
1. Go to the Products section in your Devart profile.
2. Click the product name, or hover over it and click Details. The License details page opens. Find the Activation Key there:
3. To activate dotConnect for PostgreSQL in your application, copy the Activation Key and paste it into your connection string as the License Key parameter.
Create a .NET project
Our first step is to create a demo application that will connect to Amazon RDS for PostgreSQL and access the data. It is necessary to illustrate our tutorial. To create a simple console application in Visual Studio and install the required NuGet packages for dotConnect, do the following:
1. Launch Visual Studio and click Create a new project.
2. Choose Console App (.NET Core) and click Next.
3. Give this project a name (in our tutorial, we name it RdsPgTest ), specify the path to its folder, and click Create.
4. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.
5. Go to the Browse tab, find and install the Devart.Data.PostgreSql package.
Optionally, you can run the following command in the terminal:
dotnet add package Devart.Data.PostgreSql
Connect to Amazon RDS for PostgreSQL with Data Explorer
Our first option is connecting to Amazon RDS for PostgreSQL via the built-in Data Explorer in Visual Studio. Click Tools, then select Connect to Database, and choose PostgreSQL as the data source.
By default, AWS RDS for PostgreSQL requires an SSL connection. Open Advanced Properties in the connection settings in your application and enable the SSLMode for connecting.
Enter your server details and credentials, and click Connect.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Connect and retrieve data from the database
Let us establish a connection with Amazon RDS for PostgreSQL and then run a simple SELECT query to pull records from the Actor table. It confirms the connection is established correctly and the data is accessible.
Create the DatabaseConnection.cs class (connection strings)
The DatabaseConnection.cs class keeps connection details separate for better maintainability.
using Devart.Data.PostgreSql;
public static class DatabaseConnection
{
public static PgSqlConnection CreateConnection()
{
string connectionString = "" +
"Server=*********;" +
"User Id=TestUser;" +
"Password=********;" +
"Database=TestDatabase;" +
"Initial Schema=public;" +
"SSLMode=Allow;" +
"License key=**********";
return new PgSqlConnection(connectionString);
}
}
Make sure to replace the placeholders in the code with your actual PostgreSQL database credentials. If you own a paid license of dotConnect for PostgreSQL, include the license key in the code.
Update the connection string
Add the code below to your Program.cs file.
using Devart.Data.PostgreSql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!\n");
string sql = "SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id LIMIT 10";
using (PgSqlCommand command = new PgSqlCommand(sql, connection))
using (PgSqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("+---------+----------------+----------------+");
Console.WriteLine("| ActorId | First Name | Last Name |");
Console.WriteLine("+---------+----------------+----------------+");
while (reader.Read())
{
int actorId = reader.GetInt32(0);
string firstName = reader.GetString(1);
string lastName = reader.GetString(2);
Console.WriteLine($"| {actorId,-7} | {firstName,-14} | {lastName,-14} |");
}
Console.WriteLine("+---------+----------------+----------------+");
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
Run the application
Build and run your application: select Start from the menu or click F5. It displays the data retrieved from the database.
Insert new records into Amazon RDS for PostgreSQL
This section walks you through inserting a new row into the Actor table using parameterized SQL to interact with the database safely.
using Devart.Data.PostgreSql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
string insertSql = "INSERT INTO actor(first_name, last_name) VALUES(@first, @last) RETURNING actor_id";
using (PgSqlCommand insertCmd = new PgSqlCommand(insertSql, connection))
{
insertCmd.Parameters.AddWithValue("@first", "TestFirst");
insertCmd.Parameters.AddWithValue("@last", "TestLast");
object result = insertCmd.ExecuteScalar();
long newActorId = 0;
if (result != null && result != DBNull.Value)
{
newActorId = Convert.ToInt64(result);
}
Console.WriteLine("+---------+----------------+----------------+");
Console.WriteLine("| ActorId | First Name | Last Name |");
Console.WriteLine("+---------+----------------+----------------+");
Console.WriteLine($"| {newActorId,-7} | {"TestFirst",-14} | {"TestLast",-14} |");
Console.WriteLine("+---------+----------------+----------------+");
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
We can see the results in the application:
Update rows in Amazon RDS for PostgreSQL
Next, we want to update an existing record: let us change the actor's first and last name. It demonstrates how you can change data directly from your application.
using Devart.Data.PostgreSql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
long actorId = 201;
string firstName = "James";
string lastName = "Johnson";
string updateSql = "UPDATE actor SET first_name = @first, last_name = @last WHERE actor_id = @id";
using (PgSqlCommand updateCmd = new PgSqlCommand(updateSql, connection))
{
updateCmd.Parameters.AddWithValue("@first", firstName);
updateCmd.Parameters.AddWithValue("@last", lastName);
updateCmd.Parameters.AddWithValue("@id", actorId);
int rowsAffected = updateCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("+---------+----------------+----------------+");
Console.WriteLine("| ActorId | First Name | Last Name |");
Console.WriteLine("+---------+----------------+----------------+");
Console.WriteLine($"| {actorId,-7} | {firstName,-14} | {lastName,-14} |");
Console.WriteLine("+---------+----------------+----------------+");
}
else
{
Console.WriteLine($"No actor found with actor_id: {actorId}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
The output we can see in the application proves that the operation is successful:
Delete rows from Amazon RDS for PostgreSQL
Finally, let us see how to remove the record for that actor we have just added to the database. This section shows how to delete data from the PostgreSQL table based on a specific condition.
using Devart.Data.PostgreSql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (PgSqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
long actorId = 201;
string deleteSql = "DELETE FROM actor WHERE actor_id = @id";
using (PgSqlCommand deleteCmd = new PgSqlCommand(deleteSql, connection))
{
deleteCmd.Parameters.AddWithValue("@id", actorId);
int rowsAffected = deleteCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine($"+---------+");
Console.WriteLine("| ActorId |");
Console.WriteLine("+---------+");
Console.WriteLine($"| {actorId,-7} |");
Console.WriteLine("+---------+");
Console.WriteLine($"Actor with actor_id {actorId} has been removed.");
}
else
{
Console.WriteLine($"No actor found with actor_id: {actorId}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
The command is successful. We can see that the record is deleted.
Conclusion
Now you can connect to Amazon RDS for PostgreSQL, access your data in PostgreSQL databases, and manipulate that data as necessary. This tutorial explained and illustrated the most fundamental data operations: reading, inserting, updating, and deleting data. With this knowledge, you can move further and build robust and scalable data-driven applications that flawlessly integrate with the AWS infrastructure.