How to Connect External PostgreSQL DB in Creatio Custom Web Service (Npgsql Issue)

Hi community,

I’m working on a custom web service in Creatio that connects to an external PostgreSQL database deployed on Neon. My goal is to query data from this external database, acting like a custom ORM. However, I’m running into an issue with using the Npgsql package inside Creatio’s source code.

I’ve added the necessary references, but I keep getting the following error:

The type or namespace name 'Npgsql' could not be found (are you missing a using directive or an assembly reference?)

I’ve already added the Npgsql.dll to the Terrasoft.web/bin folder and modified the web.config to include a binding redirect for Npgsql. After restarting the IIS server and flushing Redis, I now get a 'System.Data.Common' assembly error.

Here’s the code I’m using:

using Npgsql;
using System;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.ServiceModel.Activation;
using System.Data;
using System.Collections.Generic;
using Terrasoft.Core;
using Terrasoft.Web.Common;
 
namespace Terrasoft.Configuration
{
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class PostgresService : BaseService
    {
        private string connectionString;
 
        public PostgresService()
        {
            // PostgreSQL connection string
            connectionString = "Host=your_host;Port=5432;Database=your_database;Username=your_username;Password=your_password";
        }
 
        [OperationContract]
        [WebInvoke(Method = "GET", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "GetData")]
        public List<Dictionary<string, object>> GetData()
        {
            var result = new List<Dictionary<string, object>>();
            try
            {
                using (var connection = new NpgsqlConnection(connectionString))
                {
                    connection.Open();
                    string sql = "SELECT * FROM users LIMIT 100";
 
                    using (var cmd = new NpgsqlCommand(sql, connection))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var row = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                row.Add(reader.GetName(i), reader.GetValue(i));
                            }
                            result.Add(row);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error retrieving data: {ex.Message}");
            }
 
            return result;
        }
 
 
 
    }
}