借助网络配置,控制Modbus工具的日志显示,避免卡死界面
智能大石头 authored at 2021-12-11 22:09:19
7.95 KiB
XCoder
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;
    }
}