Hello,
I'm working on a service where I have to fetch data from Database. For this, service file 'ClientDBService' was created.
(Code):
----------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.ServiceModel.Activation;
using Newtonsoft.Json;
using Terrasoft.Web.Common;
namespace Terrasoft.Configuration.ClientDBService
{
[ServiceContract]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class ClientDBService : BaseService
{
// SQL Connection String
private string connectionString = "Server=my-client-sql-server;Database=client_db;User ID=root;Password=root;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
[OperationContract]
[WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, UriTemplate = "GetUsersData")]
public string GetUsersData()
{
string result = "{}"; // Default empty JSON
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM Users"; // Change the table name as needed
using (SqlCommand cmd = new SqlCommand(sql, connection))
using (SqlDataReader reader = cmd.ExecuteReader())
{
result = ConvertDataReaderToJson(reader);
}
}
}
catch (SqlException sqlEx)
{
result = JsonConvert.SerializeObject(new { Error = "SQL Error: " + sqlEx.Message });
}
catch (Exception ex)
{
result = JsonConvert.SerializeObject(new { Error = "General Error: " + ex.Message });
}
return result;
}
private string ConvertDataReaderToJson(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);
}
}
}
Query:
1. How can I connect SQL DB using script task?
2. Is it possible to keep above code `GetUsersData()` in separate service-file & keep only db-connection code in script task?
Also, I found something related to Point 2, that is following:
var userConnection = Get("UserConnection");
class_name demo= new class_name(userConnection);
demo.some_method();
return true;
Platform: Creatio:Energy 8.2v (Freedom UI)
