using System.Collections.Concurrent;
|
using System.Data;
|
using System.Text.RegularExpressions;
|
using BoardApp.Config;
|
using Microsoft.Data.SqlClient;
|
|
namespace BoardApp.Services;
|
|
public sealed class SqlDataSourceService
|
{
|
private static readonly Regex NamedParamRegex = new(@"(?<![@])@([a-zA-Z_][a-zA-Z0-9_]*)", RegexOptions.Compiled);
|
private readonly BoardConfigLoader _cfg;
|
|
private readonly ConcurrentDictionary<string, string[]> _sqlParamCache = new(StringComparer.Ordinal);
|
private SqlConnection? _conn;
|
private readonly object _lock = new();
|
|
public SqlDataSourceService(BoardConfigLoader cfg)
|
{
|
_cfg = cfg;
|
}
|
|
public static string[] ExtractNamedSqlParams(string sqlText)
|
{
|
var seen = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
|
foreach (Match m in NamedParamRegex.Matches(sqlText ?? ""))
|
{
|
if (m.Groups.Count > 1)
|
{
|
seen.Add(m.Groups[1].Value);
|
}
|
}
|
return seen.ToArray();
|
}
|
|
private SqlConnection GetOrCreateConnection()
|
{
|
lock (_lock)
|
{
|
if (_conn is { State: ConnectionState.Open }) return _conn;
|
|
var c = _cfg.LoadSqlServerConfig();
|
var csb = new SqlConnectionStringBuilder
|
{
|
DataSource = c.Port.HasValue ? $"{c.Server},{c.Port.Value}" : c.Server,
|
InitialCatalog = c.Database,
|
UserID = c.User,
|
Password = c.Password,
|
Encrypt = c.Options?.Encrypt ?? false,
|
TrustServerCertificate = c.Options?.TrustServerCertificate ?? true
|
};
|
|
_conn?.Dispose();
|
_conn = new SqlConnection(csb.ConnectionString);
|
_conn.Open();
|
return _conn;
|
}
|
}
|
|
private static void BindInput(SqlCommand cmd, string name, object? value)
|
{
|
if (value is null) throw new InvalidOperationException($"缺少 SQL 参数: @{name}");
|
|
var p = cmd.Parameters.Add("@" + name, SqlDbType.NVarChar);
|
|
if (value is DateTime dt)
|
{
|
if (string.Equals(name, "startDate", StringComparison.OrdinalIgnoreCase) ||
|
string.Equals(name, "endDate", StringComparison.OrdinalIgnoreCase))
|
{
|
p.SqlDbType = SqlDbType.Date;
|
p.Value = dt.Date;
|
}
|
else
|
{
|
p.SqlDbType = SqlDbType.DateTime2;
|
p.Value = dt;
|
}
|
return;
|
}
|
|
if (value is long l)
|
{
|
p.SqlDbType = SqlDbType.BigInt;
|
p.Value = l;
|
return;
|
}
|
|
if (value is int i)
|
{
|
p.SqlDbType = SqlDbType.Int;
|
p.Value = i;
|
return;
|
}
|
|
if (value is double d)
|
{
|
p.SqlDbType = SqlDbType.Float;
|
p.Value = d;
|
return;
|
}
|
|
if (value is bool b)
|
{
|
p.SqlDbType = SqlDbType.Bit;
|
p.Value = b;
|
return;
|
}
|
|
p.SqlDbType = SqlDbType.NVarChar;
|
p.Value = value.ToString() ?? "";
|
}
|
|
public async Task<List<Dictionary<string, object?>>> ExecuteDataSourceById(string id, Dictionary<string, object?> @params)
|
{
|
var cfg = _cfg.LoadDataSrcConfig();
|
var src = cfg.Sources.FirstOrDefault(s => string.Equals(s.Id, id, StringComparison.Ordinal));
|
if (src is null) throw new InvalidOperationException($"datasrc 未定义 id: {id}");
|
|
var paramNames = _sqlParamCache.GetOrAdd(src.Sql, ExtractNamedSqlParams);
|
|
var conn = GetOrCreateConnection();
|
using var cmd = conn.CreateCommand();
|
cmd.CommandText = src.Sql;
|
|
foreach (var name in paramNames)
|
{
|
@params.TryGetValue(name, out var value);
|
BindInput(cmd, name, value);
|
}
|
|
var rows = new List<Dictionary<string, object?>>();
|
using var reader = await cmd.ExecuteReaderAsync();
|
while (await reader.ReadAsync())
|
{
|
var row = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
|
for (var i = 0; i < reader.FieldCount; i++)
|
{
|
row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
|
}
|
rows.Add(row);
|
}
|
return rows;
|
}
|
|
public static Dictionary<string, object?> CoerceParamsFromRequest(Dictionary<string, string?> raw)
|
{
|
var o = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
|
foreach (var (k, v) in raw)
|
{
|
if (string.IsNullOrWhiteSpace(k)) continue;
|
if (string.IsNullOrWhiteSpace(v)) continue;
|
var s = v.Trim();
|
|
if (k.EndsWith("date", StringComparison.OrdinalIgnoreCase) ||
|
string.Equals(k, "startDate", StringComparison.OrdinalIgnoreCase) ||
|
string.Equals(k, "endDate", StringComparison.OrdinalIgnoreCase))
|
{
|
if (DateTime.TryParse(s, out var dt))
|
{
|
o[k] = dt;
|
continue;
|
}
|
}
|
|
if (Regex.IsMatch(s, @"^-?\d+$"))
|
{
|
if (int.TryParse(s, out var i)) { o[k] = i; continue; }
|
if (long.TryParse(s, out var l)) { o[k] = l; continue; }
|
}
|
|
if (double.TryParse(s, out var d))
|
{
|
o[k] = d;
|
continue;
|
}
|
|
if (string.Equals(s, "true", StringComparison.OrdinalIgnoreCase) ||
|
string.Equals(s, "false", StringComparison.OrdinalIgnoreCase))
|
{
|
o[k] = string.Equals(s, "true", StringComparison.OrdinalIgnoreCase);
|
continue;
|
}
|
|
o[k] = s;
|
}
|
return o;
|
}
|
}
|
|
