How to Connect UiPath Studio to PostgreSQL Using Npgsql Package and C# Code (Without using ODBC Data Source Administrator)?

[system]

How to Connect UiPath Studio to PostgreSQL Using Npgsql Package and C# Code (Without ODBC Data Source Administrator)?

Objective

This article explains how to establish a direct connection between UiPath Studio and a PostgreSQL database using the Npgsql 9.0.3 package, C# code, and the Invoke Code activity, without relying on the Windows ODBC Data Source Administrator.

In the guide example, the query fetches the current time from the PostgreSQL database, stores the result in a variable, and prints it in the Studio Output Panel.

Advantages of connecting directly to PostgreSQL without the ODBC Data Source Administrator

• Better performance by avoiding an intermediary layer.
• Greater portability across operating systems and environments. The "ODBC Data Source" is a Windows Application.
• More control and specific features of the language or framework, like connection pooling or async queries, which ODBC may lack
• Avoids permission issues on restricted systems. Creating DSN users may be restricted in specific environments.

The following environment was used while developing this guide

• UiPath Studio 2024.10.12
• UiPath Studio project in Windows compatibility
• PostgreSQL DB 16.8-2
• Npgsql version 9.0.3 from https://api.nuget.org/v3/index.json feed
• UiPath.System.Activities 24.10.7
• All tools should be compatible with 64-bit architecture.

Step-by-Step Guide

1. Go to NuGet Gallery | Npgsql 9.0.3 and download the (.nupkg) package file from the right side panel.
2. Move the .nupkg file to a repository that the UiPath Studio has access to, for example, the Orchestrator or the Local repository. For more information, please visit: Studio - Managing activity packages.
3. Create a new UiPath Studio process, go to the Manage Packages feature, navigate to the repository where the package was saved, and add it as a project dependency.

4. Open the Imports Panel, and add the System and Npgsql namespaces.

5. Add an Invoke Code Activity to the workflow and set the activity language to C# (CSharp) in the activity properties.

6. Define Input/Output arguments according to the automation needs and the SQL query to be executed.
   This will contain the query result. In this example, one argument was used: outputTime (Type: String, Direction: Out, stored in variable outQueryResult).

7. Paste and configure the following C# code in the Invoke Code activity. The code should include a connection string with the PostgreSQL database details and the SQL query to be executed.
   

Security Note: Avoid hard-coding credentials in production environments. Use Orchestrator Assets, Windows Credential Manager, or secure configuration management practices.

string connectionString = "Host=your_host;Port=5432;Username=your_username;Password=your_password;Database=your_database";
try

{

// Using 'using' to automatically manage the connection and ensure it is closed when done

using (var connection = new Npgsql.NpgsqlConnection(connectionString))

{

connection.Open();  // Open the connection to the database
    // Create the SQL command to execute (fetch current time from DB)
    using (var cmdSelect = new Npgsql.NpgsqlCommand("SELECT NOW()", connection))
    {
        // Execute the query and retrieve the result (current time)
        var resultNow = cmdSelect.ExecuteScalar();
        outputTime = resultNow.ToString();  // Store the result in the output variable
    }  // The command is automatically disposed and closed here
}  // The connection is automatically closed when the 'using' block ends

}

catch (Npgsql.NpgsqlException npgEx)

{

// Handle specific database-related errors

outputTime = $"Database Error: {npgEx.Message}";

}

catch (Exception ex)

{

// Handle any other general errors

outputTime = $"General Error: {ex.Message}";

}

8. To confirm the connection and query worked, add a Log Message Activity to display the output variable.
   In this example, the result is stored in outQueryResult and will be printed to the Output Panel of Studio. Use the data stored in this variable according to the automation needs.

Troubleshooting Issues

1. In case of an error about namespaces or unresolved types, ensure the package is correctly installed and namespaces are properly imported.
2. Check the firewall or PostgreSQL connection settings if the connection fails.
3. Make sure PostgreSQL is running and accessible from the UiPath Studio machine before trying these guide steps.

Summary

This approach allows connection to PostgreSQL from UiPath Studio without configuring ODBC User or System DSN in the ODBC Data Source Administrator, offering a more portable and direct integration with PostgreSQL using the robust Npgsql package.