using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using XCode.DataAccessLayer;
namespace NewLife.XCoder.Tools.Sql;
/// <summary>SQL 执行结果</summary>
/// <param name="IsQuery">是否查询</param>
/// <param name="RowsAffected">影响行数</param>
/// <param name="Table">查询结果</param>
/// <param name="ElapsedMilliseconds">耗时(毫秒)</param>
public sealed record SqlExecResult(Boolean IsQuery, Int32 RowsAffected, DataTable? Table, Int64 ElapsedMilliseconds);
/// <summary>SQL 工作台服务:基于 XCode DAL 执行查询与更新</summary>
public static class SqlWorkbenchService
{
private static readonly ConcurrentDictionary<String, Byte> _registered = new();
/// <summary>判断是否查询语句</summary>
/// <param name="sql">SQL 文本</param>
/// <remarks>会先跳过前导的空白与注释(<c>--</c>、<c>/* */</c>),否则带注释的查询会被误判成写操作。</remarks>
public static Boolean IsQuery(String sql)
{
var text = StripLeadingComments(sql).TrimStart();
foreach (var prefix in new[] { "select", "with", "pragma", "explain", "show", "desc" })
{
if (text.StartsWith(prefix, StringComparison.OrdinalIgnoreCase)) return true;
}
return false;
}
/// <summary>去掉语句开头的空白与注释</summary>
/// <param name="sql">SQL 文本</param>
/// <returns>第一个真正的语句关键字起始的文本</returns>
public static String StripLeadingComments(String? sql)
{
var text = sql ?? "";
while (true)
{
text = text.TrimStart();
if (text.Length == 0) return text;
if (text.StartsWith("--", StringComparison.Ordinal))
{
var index = text.IndexOf('\n');
if (index < 0) return "";
text = text[(index + 1)..];
continue;
}
if (text.StartsWith("/*", StringComparison.Ordinal))
{
var index = text.IndexOf("*/", StringComparison.Ordinal);
if (index < 0) return "";
text = text[(index + 2)..];
continue;
}
return text;
}
}
/// <summary>执行 SQL</summary>
/// <param name="connectionString">连接串</param>
/// <param name="sql">SQL 文本</param>
public static SqlExecResult Execute(String connectionString, String sql)
{
if (String.IsNullOrWhiteSpace(connectionString)) throw new ArgumentException("请填写连接串", nameof(connectionString));
if (String.IsNullOrWhiteSpace(sql)) throw new ArgumentException("请填写 SQL", nameof(sql));
var dal = DAL.Create(Register(connectionString));
var watch = Stopwatch.StartNew();
if (IsQuery(sql))
{
var data = dal.Select(sql, CommandType.Text, Array.Empty<IDataParameter>());
watch.Stop();
var table = data != null && data.Tables.Count > 0 ? data.Tables[0] : null;
return new SqlExecResult(true, table?.Rows.Count ?? 0, table, watch.ElapsedMilliseconds);
}
var affected = dal.Execute(sql, CommandType.Text, Array.Empty<IDataParameter>());
watch.Stop();
return new SqlExecResult(false, affected, null, watch.ElapsedMilliseconds);
}
/// <summary>把纯文件路径补全为 SQLite 连接串</summary>
/// /// <param name="connectionString">连接串或文件路径</param>
public static String Normalize(String connectionString)
{
var text = (connectionString ?? "").Trim();
if (text.Length == 0) return text;
if (text.Contains('=')) return text;
return "Data Source=" + text;
}
/// <summary>根据连接串推断数据库类型</summary>
/// <param name="connectionString">连接串</param>
public static String? DetectDbType(String connectionString)
{
var text = connectionString ?? "";
if (text.Contains("sqlite", StringComparison.OrdinalIgnoreCase)) return "SQLite";
if (text.EndsWith(".db", StringComparison.OrdinalIgnoreCase) || text.Contains(".db;", StringComparison.OrdinalIgnoreCase)
|| text.Contains(".sqlite", StringComparison.OrdinalIgnoreCase)) return "SQLite";
if (text.Contains("mysql", StringComparison.OrdinalIgnoreCase) || text.Contains("3306")) return "MySql";
if (text.Contains("sqlserver", StringComparison.OrdinalIgnoreCase) || text.Contains("sql server", StringComparison.OrdinalIgnoreCase) || text.Contains("1433")) return "SqlServer";
if (text.Contains("postgres", StringComparison.OrdinalIgnoreCase) || text.Contains("5432")) return "PostgreSQL";
return null;
}
/// <summary>列出库中的表与视图,供架构树使用</summary>
/// <param name="connectionString">连接串</param>
/// <returns>表/视图名清单;失败抛出异常</returns>
public static IReadOnlyList<String> ListObjects(String connectionString)
{
if (String.IsNullOrWhiteSpace(connectionString)) throw new ArgumentException("请填写连接串", nameof(connectionString));
var dal = DAL.Create(Register(connectionString));
var dbType = DetectDbType(Normalize(connectionString)) ?? "SQLite";
// 按数据库类型选不同的元数据查询,统一只取名称一列
var sql = dbType switch
{
"MySql" => "select table_name from information_schema.tables where table_schema = database() order by table_name",
"SqlServer" => "select name from sysobjects where xtype in ('U','V') order by name",
"PostgreSQL" => "select tablename from pg_tables where schemaname = 'public' order by tablename",
_ => "select name from sqlite_master where type in ('table','view') and name not like 'sqlite_%' order by name",
};
var data = dal.Select(sql, CommandType.Text, Array.Empty<IDataParameter>());
var list = new List<String>();
if (data == null || data.Tables.Count == 0) return list;
foreach (DataRow row in data.Tables[0].Rows)
{
if (row[0] == null || row[0] is DBNull) continue;
var name = row[0].ToString();
if (!String.IsNullOrEmpty(name)) list.Add(name!);
}
return list;
}
/// <summary>把查询结果导出为 CSV 文本</summary>
/// <param name="table">结果集</param>
/// <returns>CSV 文本;无结果时返回空文本</returns>
public static String ToCsv(DataTable? table)
{
if (table == null || table.Columns.Count == 0) return "";
var sb = new System.Text.StringBuilder();
for (var i = 0; i < table.Columns.Count; i++)
{
if (i > 0) sb.Append(',');
sb.Append(Escape(table.Columns[i].ColumnName));
}
sb.AppendLine();
foreach (DataRow row in table.Rows)
{
for (var i = 0; i < table.Columns.Count; i++)
{
if (i > 0) sb.Append(',');
var value = row[i];
sb.Append(value is DBNull ? "" : Escape(value?.ToString()));
}
sb.AppendLine();
}
return sb.ToString();
}
private static String Escape(String? value)
{
var text = value ?? "";
if (!text.Contains(',') && !text.Contains('"') && !text.Contains('\n')) return text;
return "\"" + text.Replace("\"", "\"\"") + "\"";
}
private static String Register(String connectionString)
{ var connStr = Normalize(connectionString);
var name = "xcoder_" + (UInt32)connStr.GetHashCode();
if (_registered.TryAdd(name, 0))
{
try
{
DAL.AddConnStr(name, connStr, null, DetectDbType(connStr));
}
catch
{
_registered.TryRemove(name, out _);
throw;
}
}
return name;
}
}
|