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