C#如何手动关闭Oracle数据库连接并取消后台运行的查询任务

核心原理 #

现有逻辑仅取消了BackgroundWorker的线程调度，但已经提交到Oracle的查询仍会在数据库端运行，对应的连接资源也不会提前释放。我们需要将取消信号穿透到数据库操作层，通过Oracle官方提供的OracleCommand.Cancel()方法主动终止正在执行的查询，触发using语法自动释放所有数据库相关资源。

分步修改代码 #

1. 改造DatabaseConnector增加取消能力 #

将数据库连接、命令对象提升为类成员，新增公开取消方法：

using System.Data;
using Oracle.DataAccess.Client; // 对应你使用的Oracle驱动命名空间

class DatabaseConnector : IDatabaseFetcher
{
    private string connectionString = "";
    private OracleConnection _connection;
    private OracleCommand _command;
    private readonly object _lockObj = new object();

    public string Query { get; set; }

    public DatabaseConnector(string connectionString)
    {
        this.connectionString = connectionString;
    }

    // 新增取消方法
    public void Cancel()
    {
        lock (_lockObj)
        {
            // 终止正在执行的查询
            _command?.Cancel();
            // 关闭连接
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
        }
    }

    public void Fetch(Action<DataTable> action)
    {
        lock (_lockObj)
        {
            _connection = new OracleConnection(connectionString);
            _command = new OracleCommand(Query, _connection);
        }
        try
        {
            using (_connection)
            using (_command)
            {
                _connection.Open();
                using (var reader = _command.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        throw new Exception("查询结果为空！");
                    }
                    var dataTable = new DataTable();
                    dataTable.Load(reader);
                    action(dataTable);
                }
            }
        }
        finally
        {
            lock (_lockObj)
            {
                _command = null;
                _connection = null;
            }
        }
    }
}

2. 串联取消链路，将BackgroundWorker的取消信号传递到数据库层 #

首先修改OpearationBackgroundWorker，增加取消回调委托，调整Stop逻辑：

public class OpearationBackgroundWorker
{
    public Action<object,DoWorkEventArgs> OnDataFetch { get; set; }
    public Action<object, RunWorkerCompletedEventArgs> OnDataFetchCompleted { get; set; }
    // 新增取消回调，用于执行数据库取消逻辑
    public Action CancelCallback { get; set; }

    private BackgroundWorker backgroundWorker = new BackgroundWorker()
    {
        WorkerSupportsCancellation = true
    };

    public void Start()
    {
        // 避免重复订阅事件
        backgroundWorker.DoWork -= OnDataFetch.Invoke;
        backgroundWorker.RunWorkerCompleted -= OnDataFetchCompleted.Invoke;
        backgroundWorker.DoWork += new DoWorkEventHandler(OnDataFetch);
        backgroundWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(OnDataFetchCompleted);

        if (!backgroundWorker.IsBusy)
            backgroundWorker.RunWorkerAsync();
        else
            throw new Exception("进程正在运行！");
    }

    public void Stop()
    {
        // 先执行数据库取消逻辑
        CancelCallback?.Invoke();
        if (backgroundWorker.IsBusy)
        {
            backgroundWorker.CancelAsync();
        }
        // 不要直接Dispose，等待RunWorkerCompleted触发后再处理资源
    }
}

然后修改主窗体代码，关联取消回调：

public partial class MainForm : Form, IDataFetcher
{
    public EntryForm EntryForm { get; set; }
    public ApplicationSettings Settings { get; set; }
    private OpearationBackgroundWorker BackgroundWorker { get; set; }
    // 持有当前正在使用的DatabaseConnector实例
    private DatabaseConnector _currentDbConnector;
    private readonly object _lockObj = new object();

    enum QueryStatus { RUNNING, NOT_STARTED }
    // 修正原代码的变量声明错误
    private QueryStatus queryStatus;

    public BillingForm()
    {
        InitializeComponent();
        BackgroundWorker = new OpearationBackgroundWorker()
        {
            OnDataFetch = OnDataFetch,
            OnDataFetchCompleted = OnDataFetchCompleted,
            // 绑定取消回调
            CancelCallback = () => 
            {
                lock (_lockObj)
                {
                    _currentDbConnector?.Cancel();
                }
            }
        };
    }

    public BillingForm(EntryForm parentForm, ApplicationSettings appSettings) : this()
    {
        EntryForm = parentForm;
        Settings = appSettings;
        queryStatus = QueryStatus.NOT_STARTED;
    }

    private void ExecuteButton_Click(object sender, EventArgs e)
    {
        switch (queryStatus)
        {
            case QueryStatus.NOT_STARTED:
                queryStatus = QueryStatus.RUNNING;
                BackgroundWorker.Start();
                break;
            case QueryStatus.RUNNING:
                BackgroundWorker.Stop();
                queryStatus = QueryStatus.NOT_STARTED;
                break;
        }
    }

    public void OnDataFetchCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        queryStatus = QueryStatus.NOT_STARTED;
        lock (_lockObj)
        {
            _currentDbConnector = null;
        }
        if (e.Error != null && !e.Cancelled)
        {
            MessageBox.Show(e.Error.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    public void OnDataFetch(object sender, DoWorkEventArgs e)
    {
        var worker = sender as BackgroundWorker;
        if (worker.CancellationPending)
        {
            e.Cancel = true;
            return;
        }

        var db = new DatabaseConnector(Settings.DbConnectionString);
        lock (_lockObj)
        {
            _currentDbConnector = db;
        }

        var model = new BillingModel(db)
        {
            PostTitle = PostTitle,
            StartDate = StartDateTimePicker.Value,
            EndDate = EndDateTimePicker.Value,
        };

        try
        {
            model.Query((datatable) =>
            {
                if (!worker.CancellationPending)
                {
                    Invoke(new Action(() =>
                    {
                        QueryResultDataGridView.DataSource = null;
                        QueryResultDataGridView.DataSource = datatable;
                    }));
                }
            });
        }
        catch (Exception exception)
        {
            if (!worker.CancellationPending)
            {
                Invoke(new Action(() => 
                {
                    queryStatus = QueryStatus.NOT_STARTED;
                    MessageBox.Show(exception.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
            }
            e.Cancel = true;
        }
    }
}

3. 额外优化建议 #

原代码中直接拼接SQL字符串存在SQL注入风险，建议修改为参数化查询：

// BillingModel.Query方法修改示例
public override void Query(Action<DataTable> callback)
{
    var query = "SELECT * FROM posts WHERE post_titile = :PostTitle";
    Db.Query = query;
    // 可扩展DatabaseConnector支持参数传递，避免SQL注入
    Db.Parameters.Add(new OracleParameter("PostTitle", PostTitle));
    Db.Fetch(data => callback(data));
}

执行逻辑说明 #

用户点击Stop按钮时：

1. 首先触发CancelCallback调用DatabaseConnector.Cancel()
2. OracleCommand.Cancel()会立即终止正在执行的数据库查询，ExecuteReader抛出取消异常
3. using语法自动释放DataReader、OracleCommand、OracleConnection资源
4. BackgroundWorker收到取消信号，结束后台任务，触发RunWorkerCompleted重置状态

内容的提问来源于stack exchange，提问作者Kiazim Khutaba