Unable to cancel Async/Awaiting call on button click

Question

I am unable to cancel async/await call on cancel button click. I want to cancel query if it will take long time to load data in grid. I am new to use async/await method and referred many examples, but unable workout if. Pleas have look on my below code and Please help. thanks

    namespace MyFirstAsync
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        CancellationTokenSource cts ;
        private async void btnExecute_Click(object sender, EventArgs e)
        {
            cts = new CancellationTokenSource();
            try
            {
                string connectionString = "Data Source=G50-80;Initial Catalog=HPSF_Compdb;User ID=sa;Password=sql@2012";
                string sqlQuery = "select top 900000 * from requestevent.result";
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                await conn.OpenAsync(cts.Token);
                SqlDataReader reader = await cmd.ExecuteReaderAsync(cts.Token);
                DataTable dt = new DataTable();
                //cts.Cancel();
                await Task.Run(() => dt.Load(reader), cts.Token);
                gvData.Invoke(new gvDelegate(UpdateDataToGrid), new object[] { dt });
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public delegate void gvDelegate(DataTable dt);
        public void UpdateDataToGrid(DataTable dt)
        {
            gvData.DataSource = dt;
        }



        private void btncancel_Click(object sender, EventArgs e)
        {
            cts.Cancel();
        }



    }
}

So what happens when you run this code? Do you get any exceptions? The more detail you supply, the more likely you'll get your question answered. — Ruben Steins, Dec 11 '16 at 19:28
Hi Ruben,No, it is not throwing any error, just completed query and loaded data to grid view without accept cancel request. I don't know to how to debug it and trouble shoot. — Awadhesh, Dec 12 '16 at 03:37

score 1 · Answer 1 · answered Dec 19 '16 at 02:14

The problem is with this line of code:

await Task.Run(() => dt.Load(reader), cts.Token);

Contrary to what you may expect, the CancellationToken passed to Task.Run only cancels the starting of that task, not the task itself. In other words, once Load has started, it can't be canceled.

Ideally, you should be using something other than DataTables in modern code; other database APIs have been updated to support cancellation. If this is not possible, then you can use the CancellationTokenRegistration as in your answer. However, you should not use Result - use await instead:

using (CancellationTokenRegistration ctr = cts.Token.Register(() => cmd.Cancel()))
using (var reader = await cmd.ExecuteReaderAsync(cts.Token))
    dt.Load(reader);

score -1 · Answer 2 · answered Dec 12 '16 at 14:47

I found solution of above issue, just changed the way of data loading to datatable. Pls refer commented old code replaced with new code and it is working excellent as expected. New code I just copy and paste and still I don't know how to describe new code the way of it working as I am new on this. But it is working for me. Please explain someone if you differentiate between both.

using (CancellationTokenRegistration ctr = cts.Token.Register(() => cmd.Cancel()))
            {
                using (var reader = cmd.ExecuteReaderAsync(cts.Token))
                {
                    dt.Load(reader.Result);
                }
            }
            //SqlDataReader reader = await cmd.ExecuteReaderAsync(cts.Token);
            //DataTable dt = new DataTable();
            //await Task.Run(() => dt.Load(reader), cts.Token);

I found this solution on no other than Stackoverflow, great place to get your queries answered. :) http://stackoverflow.com/questions/39166619/will-this-cancel-an-executereaderasync — Awadhesh, Dec 12 '16 at 15:04

Unable to cancel Async/Awaiting call on button click

2 Answers2