MSSQL Connection using Script Task or User Task

Hi Team,

I'm working on a use case where I have to make database connection & perform some actions. For this, I created source-code file with C# scripting that is properly working.

Query: How can I use below scripting in Process Library using Script task or another node to make db connection & perform the required action.

namespace Terrasoft.Configuration.UsrExternalMSSQLService {

    using System;                  // Basic types (e.g., Exception, etc.)

    using System.Collections.Generic;  // For collections like List, Dictionary, etc.

    using System.Data;             // For DataReader, DataTable, etc.

    using System.Data.SqlClient;   // For SqlConnection, SqlCommand, SqlDataReader

    using System.ServiceModel;     // For WCF (Web Service) contracts

    using System.ServiceModel.Web; // For handling WebInvoke, UriTemplate, etc.

    using System.ServiceModel.Activation; // For ASP.NET compatibility with WCF

    using Newtonsoft.Json;          // For JSON serialization and deserialization

    using Terrasoft.Core;           // For Terrasoft core functionality (e.g., UserConnection)

    using Terrasoft.Core.DB;        // For database-related queries in Terrasoft

    using Terrasoft.Web.Common;     // For web-related functionality (if needed for services)

    [ServiceContract]

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]

    public class UsrExternalMSSQLService : BaseService

    {

        private string connectionString = "Server=client.server.net;Database=Client_Database;User Id=root;Password=root;";

        // Test Database Connection

        [OperationContract]

        [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, UriTemplate = "TestDBConnection")]

        public string TestDBConnection()

        {

            try

            {

                using (SqlConnection connection = new SqlConnection(connectionString))

                {

                    connection.Open();

                    return "DB Connection Successful!";

                }

            }

            catch (Exception ex)

            {

                return "Connection Failed: " + ex.Message;

            }

        }

        // Get Contact Detail

        [OperationContract]

        [WebInvoke(Method = "GET", RequestFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped,

            ResponseFormat = WebMessageFormat.Json, UriTemplate = "GetEntityDetail")]

        public string GetEntityDetail()

        {

            string result = "{}";

            try

            {

                using (SqlConnection connection = new SqlConnection(connectionString))

                {

                    connection.Open();

                    string sql= @"select TOP 10 c.AffilaitionName, c.CustomerKey, c.CustomerEmail, c.CustomerFirstName from Customer c";

                    using (SqlCommand cmd = new SqlCommand(sql, connection))

                    using (SqlDataReader reader = cmd.ExecuteReader())

                    {

                        result = CreateJson(reader);  // Using external method

                    }

                }

            }

            catch (Exception ex)

            {

                return JsonConvert.SerializeObject(new { error = "Error fetching data: " + ex.Message });

            }

            return result;

        }      

       private string CreateJson(IDataReader dataReader)

        {

            var list = new List>();

            while (dataReader.Read())

            {

                var record = new Dictionary();

                for (int i = 0; i < dataReader.FieldCount; i++)

                {

                    string fieldName = dataReader.GetName(i);

                    object fieldValue = dataReader.IsDBNull(i) ? null : dataReader.GetValue(i);

                    record.Add(fieldName, fieldValue);

                }

                list.Add(record);

            }

            return JsonConvert.SerializeObject(list);

        }

        // End; Get Contact Detail 

    }

}