借助网络配置,控制Modbus工具的日志显示,避免卡死界面
智能大石头 authored at 2021-12-11 22:09:19
5.87 KiB
XCoder
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Avalonia.Controls;
using Avalonia.Input;
using Avalonia.Interactivity;
using Avalonia.Platform.Storage;
using NewLife.XCoder.Services;

namespace NewLife.XCoder.Tools.Sql;

/// <summary>SQL 工作台工具</summary>
public partial class SqlWorkbenchToolView : UserControl
{
    private const Int32 MaxDisplayRows = 500;
    private DataTable? _table;

    /// <summary>实例化</summary>
    public SqlWorkbenchToolView()
    {
        InitializeComponent();

        RunButton.Click += (_, _) => _ = RunAsync();
        ClearButton.Click += (_, _) =>
        {
            ResultBox.Text = "";
            _table = null;
            SetStatus("等待操作", false);
        };
        SampleButton.Click += (_, _) =>
        {
            ConnBox.Text = "Data Source=xcoder.db";
            SqlBox.Text = "select 1 as id, 'hello' as name";
        };
        SchemaButton.Click += (_, _) => _ = LoadSchemaAsync();
        ExportButton.Click += (_, _) => _ = ExportAsync();
        ObjectBox.SelectionChanged += (_, _) =>
        {
            if (ObjectBox.SelectedItem is not String name) return;

            SqlBox.Text = $"select * from {name} limit 50";
            SetStatus($"已填入表 {name}", false);
        };
        SqlBox.KeyDown += (_, e) =>
        {
            if (e.Key == Key.Enter && e.KeyModifiers == KeyModifiers.Control)
            {
                _ = RunAsync();
                e.Handled = true;
            }
        };
    }

    private async Task LoadSchemaAsync()
    {
        var conn = ConnBox.Text ?? "";
        if (String.IsNullOrWhiteSpace(conn))
        {
            SetStatus("请填写连接串", true);
            return;
        }

        SchemaButton.IsEnabled = false;
        SetStatus("正在读取架构…", false);
        try
        {
            var list = await Task.Run(() => SqlWorkbenchService.ListObjects(conn));

            ObjectBox.Items.Clear();
            foreach (var item in list) ObjectBox.Items.Add(item);

            SetStatus(list.Count == 0 ? "库中没有表或视图" : $"架构:{list.Count} 个对象", false);
        }
        catch (Exception ex)
        {
            SetStatus("读取架构失败:" + ex.Message, true);
        }
        finally
        {
            SchemaButton.IsEnabled = true;
        }
    }

    private async Task ExportAsync()
    {
        var table = _table;
        if (table == null || table.Rows.Count == 0)
        {
            SetStatus("没有可导出的结果,请先执行查询", true);
            return;
        }

        var top = TopLevel.GetTopLevel(this);
        if (top == null) return;

        var file = await top.StorageProvider.SaveFilePickerAsync(new FilePickerSaveOptions
        {
            Title = LocalizationService.T("Export query results"),
            SuggestedFileName = "query-result.csv",
            DefaultExtension = "csv",
            FileTypeChoices = [new FilePickerFileType("CSV") { Patterns = ["*.csv"] }],
        });
        if (file == null) return;

        try
        {
            await using var stream = await file.OpenWriteAsync();
            await using var writer = new StreamWriter(stream, new UTF8Encoding(true));
            await writer.WriteAsync(SqlWorkbenchService.ToCsv(table));
            SetStatus("已导出:" + file.Name, false);
        }
        catch (Exception ex)
        {
            SetStatus("导出失败:" + ex.Message, true);
        }
    }

    private async Task RunAsync()
    {
        var conn = ConnBox.Text ?? "";
        var sql = SqlBox.Text ?? "";
        if (String.IsNullOrWhiteSpace(conn) || String.IsNullOrWhiteSpace(sql))
        {
            SetStatus("请填写连接串与 SQL", true);
            return;
        }

        RunButton.IsEnabled = false;
        SetStatus("执行中…", false);
        try
        {
            var result = await Task.Run(() => SqlWorkbenchService.Execute(conn, sql));

            if (result.IsQuery)
            {
                _table = result.Table;
                ResultBox.Text = Format(result.Table);
                var more = result.RowsAffected > MaxDisplayRows ? $"(仅显示前 {MaxDisplayRows} 行)" : "";
                SetStatus($"查询完成:{result.RowsAffected} 行 · {result.ElapsedMilliseconds} ms {more}", false);
            }
            else
            {
                _table = null;
                ResultBox.Text = "";
                SetStatus($"执行完成:影响 {result.RowsAffected} 行 · {result.ElapsedMilliseconds} ms", false);
            }
        }
        catch (Exception ex)
        {
            _table = null;
            ResultBox.Text = "";
            SetStatus("执行失败:" + ex.Message, true);
        }
        finally
        {
            RunButton.IsEnabled = true;
        }
    }

    private static String Format(DataTable? table)
    {
        if (table == null || table.Columns.Count == 0) return "";

        var sb = new StringBuilder();
        for (var i = 0; i < table.Columns.Count; i++)
        {
            if (i > 0) sb.Append('\t');
            sb.Append(table.Columns[i].ColumnName);
        }
        sb.AppendLine();

        var count = Math.Min(table.Rows.Count, MaxDisplayRows);
        for (var r = 0; r < count; r++)
        {
            var row = table.Rows[r];
            for (var i = 0; i < table.Columns.Count; i++)
            {
                if (i > 0) sb.Append('\t');
                var value = row[i];
                sb.Append(value is DBNull ? "NULL" : value?.ToString());
            }
            sb.AppendLine();
        }

        return sb.ToString().TrimEnd();
    }

    private void SetStatus(String text, Boolean error)
    {
        StatusText.Text = NewLife.XCoder.Services.LocalizationService.Translate(text);
        StatusText.Classes.Set("error", error);
        StatusText.Classes.Set("ok", !error);
    }
}