Hello Community,

I’m working on creating a custom web service to delete a lead record, but I’ve run into an issue that I can’t seem to resolve. Whenever I attempt to delete a lead using its ContactId, I receive the following error:
"Error occurred while deleting: Item cannot be deleted because it is being used in a process."

Here’s what I’ve tried so far:

1. Removing the Process Connection:
   The lead is connected to a business process (Lead Processing) through a "Connect process to object process element." I attempted to remove this connection from the base process, but doing so broke other functionalities that are dependent on this process.
2. Calling the Process Cancel Execution Service:
   I attempted to call the CancelExecution service (ProcessEngineService.svc/CancelExecution) to cancel the execution of the process. However, this service requires processDataIds in the body, which I cannot retrieve within the delete contact web service.
3. Deleting the Connected Record:
   I explored deleting the record connecting the process to the lead, but I couldn’t locate the object holding this connection.

Below is the source code for my delete operation. The logic works perfectly when I stop the "Lead Processing" business process, but that’s not a sustainable solution.

Source Code:
 using System;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.ServiceModel.Activation;
using Terrasoft.Core;
using Terrasoft.Web.Common;
using Terrasoft.Core.Entities;
using System.Collections.Generic;
 
namespace Terrasoft.Configuration.UsrDeleteLeadNamespace
{
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class UsrDeleteLead : BaseService
    {
        [OperationContract]
        [WebInvoke(Method = "DELETE", RequestFormat = WebMessageFormat.Json, 
                  BodyStyle = WebMessageBodyStyle.Wrapped,
                  ResponseFormat = WebMessageFormat.Json)]
        public string DeleteLead(string LeadId)
        {
            if (!Guid.TryParse(LeadId, out Guid parsedLeadId))
            {
                return "Invalid Lead ID format or Lead doesn't exist.";
            }
 
            UserConnection userConnection = GetUserConnection();
            EntitySchemaManager entitySchemaManager = userConnection.EntitySchemaManager;
 
            try
            {
                // First check and delete connected Lead Products
                EntitySchema leadProductSchema = entitySchemaManager.GetInstanceByName("LeadProduct");
                var leadProductEsq = new EntitySchemaQuery(leadProductSchema);
                leadProductEsq.AddAllSchemaColumns();
                leadProductEsq.Filters.Add(leadProductEsq.CreateFilterWithParameters(FilterComparisonType.Equal, "Lead", parsedLeadId));
                var leadProductCollection = leadProductEsq.GetEntityCollection(userConnection);
 
                // Manually convert EntityCollection to a List
                var leadProductCollectionToDelete = new List<Entity>();
                foreach (var leadProductEntity in leadProductCollection)
                {
                    leadProductCollectionToDelete.Add(leadProductEntity);
                }
 
                // Delete the Lead Products
                foreach (var leadProductEntity in leadProductCollectionToDelete)
                {
                    leadProductEntity.Delete();
                }
 
                // First check and delete connected Activity
                EntitySchema activitySchema = entitySchemaManager.GetInstanceByName("Activity");
                var activityEsq = new EntitySchemaQuery(activitySchema);
                activityEsq.AddAllSchemaColumns();
                activityEsq.Filters.Add(activityEsq.CreateFilterWithParameters(FilterComparisonType.Equal, "Lead", parsedLeadId));
                var activityCollection = activityEsq.GetEntityCollection(userConnection);
 
                // Manually convert EntityCollection to a List
                var activityCollectionToDelete = new List<Entity>();
                foreach (var activityEntity in activityCollection)
                {
                    activityCollectionToDelete.Add(activityEntity);
                }
 
                // Delete the Lead Products
                foreach (var activityEntity in activityCollectionToDelete)
                {
                    activityEntity.Delete();
                }
 
                // Now check and delete the Lead
                EntitySchema leadSchema = entitySchemaManager.GetInstanceByName("Lead");
                Entity leadEntity = leadSchema.CreateEntity(userConnection);
 
                if (!leadEntity.FetchFromDB("Id", parsedLeadId))
                {
                    return "Lead not found.";
                }
 
                if (leadEntity.Delete())
                {
                    return $"Lead and {leadProductCollection.Count} related product(s) have been deleted successfully.";
                }
                else
                {
                    return "Failed to delete Lead.";
                }
            }
            catch (Exception ex)
            {
                return $"Error occurred while deleting: {ex.Message}";
            }
        }
    }
}

I would greatly appreciate any suggestions or guidance on how to resolve this issue. Specifically:

• Is there a way to identify and cancel the process dynamically before deleting the lead?
• Can I identify and delete the connecting record that prevents the deletion?
• Are there alternative approaches I should consider?

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