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);
}
}
|