c# - 如何手动关闭与数据库的连接并取消在后台进程中运行的查询数据？

Question

我有使用BackgrwoundWorker.

public partial class MainForm : Form, IDataFetcher
{
    public EntryForm EntryForm { get; set; }
    public ApplicationSettings Settings { get; set; }

    private DbDataBackgroundWorker BackgroundWorker { get; set; }

    enum QueryStatus { RUNNING, NOT_STARTED }

    private QueryStatus;
    


    public BillingForm()
    {
        InitializeComponent();


        // configure background actual opearations
        BackgroundWorker = new OpearationBackgroundWorker()
        {
            OnDataFetch = OnDataFetch,
            OnDataFetchCompleted = OnDataFetchCompleted
        };
    }



    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:
                BackgroundWorker.Start();
                break;

            case QueryStatus.RUNNING:
                BackgroundWorker.Stop();
                break;
        }
    }

    

    public void OnDataFetchCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        queryStatus = QueryStatus.NOT_STARTED;
    
        if (e.Error != null)
        {
            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;
            //MessageBox.Show("Запрос остановлен");
            return;
        }


        // create database handler
        var db = new DatabaseConnector(Settings.DbConnectionString);


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


        try
        {
            
            model.Query((datatable) =>
            {
                Invoke(new Action(() =>
                {

                    QueryResultDataGridView.DataSource = null;

                    QueryResultDataGridView.DataSource = datatable;
                    
                }));
            });

        }
        catch (Exception exception)
        {
            Invoke(new Action(() => {

                queryStatus = QueryStatus.NOT_STARTED;
            }));
        }

    }
}

当我单击“开始”按钮（方法ExecuteButton_Click）时，程序会在后台获取数据并将它们放入DataGridView.

// this class starts operations in background
public class OpearationBackgroundWorker
{

    public Action<object,DoWorkEventArgs> OnDataFetch { get; set; }
    public Action<object, RunWorkerCompletedEventArgs> OnDataFetchCompleted { get; set; }
    //public Action<object, ProgressChangedEventArgs> OnDataFetchProgress { get; set; }

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

    public void Start()
    {
        backgroundWorker.DoWork += new DoWorkEventHandler(OnDataFetch);
        backgroundWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(OnDataFetchCompleted);

        if ( !backgroundWorker.IsBusy )
            backgroundWorker.RunWorkerAsync();
        else
            throw new Exception("Process running!");
    }


    public void Stop()
    {
        backgroundWorker.CancelAsync();
        backgroundWorker.Dispose();
    }
}



public class SimpleModel : AbstractModel
{
    public IDatabaseFetcher Db { get; set; }

    public string PostTitle { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }



    public BillingModel(IDatabaseFetcher db)
    {
        Db = db;
    }


    public override void Query(Action<DataTable> callback)
    {
        var query = "SELECT  * FROM posts WHERE post_titile = " + PostTitle;

        Db.Query = query;
        Db.Fetch(data => callback(data));
    }
}

该程序使用 C#using构造来自动处理数据库连接。

public interface IDataFetcher
{
    void OnDataFetch(object sender, DoWorkEventArgs e);
    void OnDataFetchCompleted(object sender, RunWorkerCompletedEventArgs e);
}


class DatabaseConnector : IDatabaseFetcher
{

    private string connectionString = "";

    public string Query { get; set; }


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


    // here i query data from db
    public void Fetch(Action<DataTable> action)
    {
        using (var connection = new OracleConnection(connectionString))
        using (var cmd = new OracleCommand(Query, connection))
        {
            connection.Open();

            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.HasRows)
                {
                    throw new Exception("Empty result!");
                }

                var dataTable = new DataTable();
                dataTable.Load(reader);

                // run callback processor
                action(dataTable);
            }
        }
    }
}

但现在，我想稍微改变程序的行为，并添加手动取消操作的能力，并关闭与数据库的连接。

我已经知道如何取消BackgrwoundWorker执行，但是当用户单击“停止”按钮时，我找不到如何手动关闭与数据库的连接并取消查询在后台进程中运行的数据的解决方案。

这个怎么做？

标签： c#oraclebackgroundworker