83 lines
2.6 KiB
C#
83 lines
2.6 KiB
C#
using System.Data;
|
|
using System.Diagnostics;
|
|
using Microsoft.Data.SqlClient;
|
|
using Microsoft.Extensions.Configuration;
|
|
using Microsoft.Extensions.Logging;
|
|
|
|
namespace Registration.Data;
|
|
|
|
/// <summary>
|
|
/// Stored procedure executor — same @action/@rqst/@resp OUTPUT pattern
|
|
/// used by Gateway and Management.
|
|
///
|
|
/// Uncomment registration in Program.cs when dbRegistration is ready.
|
|
/// Connection string: ConnectionStrings__Sql (env var) or ConnectionStrings:Sql (appsettings).
|
|
/// </summary>
|
|
public class SqlService
|
|
{
|
|
private readonly IConfiguration _config;
|
|
private readonly ILogger<SqlService> _logger;
|
|
|
|
public SqlService(IConfiguration config, ILogger<SqlService> logger)
|
|
{
|
|
_config = config;
|
|
_logger = logger;
|
|
}
|
|
|
|
private string GetConnectionString()
|
|
{
|
|
var cs = _config.GetConnectionString("Sql");
|
|
if (string.IsNullOrWhiteSpace(cs))
|
|
throw new InvalidOperationException("Missing ConnectionStrings:Sql");
|
|
return cs;
|
|
}
|
|
|
|
public async Task<string> ExecProcAsync(
|
|
string procName,
|
|
string action,
|
|
string rqstJson,
|
|
int commandTimeoutSeconds = 30,
|
|
CancellationToken ct = default)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(procName))
|
|
throw new ArgumentException("procName is required.", nameof(procName));
|
|
if (string.IsNullOrWhiteSpace(rqstJson))
|
|
rqstJson = "{}";
|
|
|
|
var sw = Stopwatch.StartNew();
|
|
|
|
try
|
|
{
|
|
await using var conn = new SqlConnection(GetConnectionString());
|
|
await conn.OpenAsync(ct);
|
|
|
|
await using var cmd = new SqlCommand(procName, conn)
|
|
{
|
|
CommandType = CommandType.StoredProcedure,
|
|
CommandTimeout = commandTimeoutSeconds
|
|
};
|
|
|
|
cmd.Parameters.Add(new SqlParameter("@action", SqlDbType.VarChar, 50) { Value = action });
|
|
cmd.Parameters.Add(new SqlParameter("@rqst", SqlDbType.NVarChar, -1) { Value = rqstJson });
|
|
|
|
var pResp = new SqlParameter("@resp", SqlDbType.NVarChar, -1) { Direction = ParameterDirection.Output };
|
|
cmd.Parameters.Add(pResp);
|
|
|
|
await cmd.ExecuteNonQueryAsync(ct);
|
|
|
|
var resp = pResp.Value as string ?? "";
|
|
|
|
sw.Stop();
|
|
_logger.LogInformation("SQL ok: {Proc}.{Action} ms={Ms}", procName, action, sw.ElapsedMilliseconds);
|
|
|
|
return resp;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sw.Stop();
|
|
_logger.LogError(ex, "SQL error: {Proc}.{Action} ms={Ms}", procName, action, sw.ElapsedMilliseconds);
|
|
throw;
|
|
}
|
|
}
|
|
}
|