How to Connect to Google Cloud SQL for PostgreSQL in .NET With C#
Google Cloud SQL for PostgreSQL is an enterprise-grade, developer-friendly service that offers powerful AI-enhanced tools, helping developers reduce the time spent on database management. It provides a reliable and advanced platform that combines the convenience of a fully managed cloud service with the flexibility and performance of PostgreSQL.
With full compatibility across major PostgreSQL versions, support for popular extensions, more than 100 configurable database flags, and integration with key Google Cloud services such as Google Kubernetes Engine (GKE), BigQuery, and Cloud Run, Cloud SQL for PostgreSQL is a top choice for developers building cloud-native applications that demand enterprise-level features.
This tutorial demonstrates how to connect C# applications to a PostgreSQL instance hosted on Google Cloud using ADO.NET and dotConnect for PostgreSQL. You'll learn how to set up the connection and perform essential database operations directly from your .NET codebase.
Why dotConnect for PostgreSQL?
dotConnect for PostgreSQL is a high-performance ADO.NET data provider that allows .NET developers to connect directly to PostgreSQL databases in the Google Cloud and interact with the data efficiently, with no need for any additional client libraries and configurations.
With features like on-the-fly connector creation and flexible configuration, integration into Visual Studio, enhanced ORM support, and more, dotConnect for PostgreSQL is ideal for .NET specialists who work with Cloud SQL for PostgreSQL, as it enhances functionality, performance, security, and ease of development.
Prerequisites
The following prerequisites are necessary to follow this guide:
- Visual Studio 2022: An integrated development environment of choice. If you don't have it on your machine, visit the official website where you can 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.
- Cloud SQL for PostgreSQL: A sample PostgreSQL cloud database provided for learning and testing.
Download and activate dotConnect for PostgreSQL
Depending on your conditions, 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
To illustrate our tutorial, we need a demo application that will connect to Cloud SQL for PostgreSQL and access the PostgreSQL database hosted there to work with its data. Let us create a simple console application in Visual Studio and install the required NuGet packages for dotConnect.
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 GooglePgTest), 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 Cloud SQL for PostgreSQL with Data Explorer
Let us connect to Cloud SQL for PostgreSQL with the help of the built-in Data Explorer. Click Tools, then select Connect to Database, and choose PostgreSQL as the data source.
By default, Google Cloud for PostgreSQL requires an SSL connection. To enable it, go to the Google Cloud Console > SQL > your PostgreSQL instance > Connections > Security > Certificates, and download the Server CA certificate.
Open Advanced Properties in your application and specify the downloaded certificate file under the SSL configuration before 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 Cloud SQL for PostgreSQL and then run a simple SELECT query to pull records from the Actor table. It confirms the connection is working properly 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=127.0.0.1;" +
"User Id=TestUser;" +
"Password=TestPassword;" +
"Database=TestDatabase;" +
"Initial Schema=public;" +
"SSLMode=Allow;" +
"SSL CA Cert=file://C:\\server-ca.pem" +
"License key=**********";
return new PgSqlConnection(connectionString);
}
}
Update the connection string
Replace the placeholders in the connection string with your actual PostgreSQL database credentials. If you own a paid license of dotConnect for PostgreSQL, include the license key in the connection strings.
Add the below code 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 Cloud SQL for PostgreSQL
Let's add a new row to the Actor table by executing an INSERT command. See how to prepare and execute an INSERT statement from C# using standard ADO.NET code.
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 Cloud SQL for PostgreSQL
Next, we want to update an existing record: let us change the actor's first and last name. The following code shows how to build and run an UPDATE command from the application to modify data already stored in the database.
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 = 202;
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);
}
}
}
Our application shows that the operation is successful:
Delete rows from Cloud SQL for PostgreSQL
Finally, let us see how to delete the record for that actor we have just added to the database. We run the DELETE statement and remove rows based on a condition in a query.
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 = 202;
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 executed correctly, and the output in the application proves that the record is deleted.
Conclusion
Now that you know the essentials of connecting to and working with databases hosted in Google's Cloud SQL for PostgreSQL from a .NET application, you can connect, query, insert, update, and delete records easily.
With these fundamentals in place, you can proceed to more complex techniques and build applications that take full advantage of PostgreSQL in the cloud, while dotConnect for PostgreSQL ensures a smooth, secure, and efficient connection experience.