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;
}
}
}
