Fill DataGridView and cancel process using DataAdapter and BackgroundWorker

Introduction

Filling data to DataGridView with SqlDataAdapter is a common task but to cancel it in the middle of fill process need a tricky way. Especially when SqlDataAdapter returning large DataSet then no progress status informed to user since SqlDataAdapter need time to fetch DataSet. Also this project does not need to have button like “More records” to fetch more dataset. We can retrieve as much records you like and cancel it any time.

Background

We need to show records immediately and user can cancel the retrieval process whenever they like. The basic idea is using int recordcount = SqlDataAdapter.Fill(int startrow, int maxrecords, DataTable dt) method at BackgroundWorker.DoWork event. That Fill method is similar to paging in web page. BackgroundWorker execute SqlDataAdapter.Fill in loop until it has no more record to fetch and we can cancel the process before or after SqlDataAdapter.Fill.

Using the code

In this project, we have two buttons, one datagridview. Also we have BackgroundWorker that created programmatically. Button: btnFill, btnCancel, DataGridView: dgv and BackgroundWorker: bgworker. I had Introduction to BackgroundWorker on my previous post to refresh on how to use it.

Button Fill Click:

private void btnFill_Click(object sender, EventArgs e)
        {
            bgworker = new BackgroundWorker();
            bgworker.WorkerReportsProgress = true;
            bgworker.WorkerSupportsCancellation = true;
            bgworker.DoWork += new DoWorkEventHandler(bgworker_DoWork);
            bgworker.ProgressChanged  += new ProgressChangedEventHandler(bgworker_ProgressChanged);
            bgworker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgworker_RunWorkerCompleted);

            dgv.Rows.Clear();
            dgv.Columns.Clear();
            System.Threading.Thread.Sleep(10);

            bgworker.RunWorkerAsync();

        }

Above code creating new instance of bgworker along with its methods, clearing DataGridView rows and columns and run the bgworker.

Button Cancel Click:

private void btncancel_Click(object sender, EventArgs e)
        {
            bgworker.CancelAsync();
        }

BackgroundWorker DoWork Event:

private void bgworker_DoWork(object sender, DoWorkEventArgs e)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=true");
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter("select * from Sales.SalesOrderHeader", conn);
            int resda, startrow, pagesize, pageno;

            pagesize = 10;
            pageno = 1;

            while (true)
            {
                if (!bgworker.CancellationPending)
                {
                    DataTable dt = new DataTable();
                    startrow = (pageno - 1) * pagesize;
                    resda = da.Fill(startrow, pagesize, dt);
                    if (resda == 0)
                    {
                        e.Cancel = true;
                        break;
                    }
                    else
                    {

                        System.Threading.Thread.Sleep(10);
                        bgworker.ReportProgress(pageno, dt);
                        pageno++;
                    }
                }
                else
                {
                    e.Cancel = true;
                    break;
                }

            }
            da.Dispose();
            conn.Close();
            conn.Dispose();
        }

The key idea is da.Fill(startrow, pagesize, dt) so that we limit dataset retrieval and we send reportprogress to fill the DataGridView with data. Cancelling the process is quite trivial with BackgroundWorker.

BackgroundWorker ProgressChanged Event:

private void bgworker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            DataTable dt = e.UserState as DataTable;

            if (e.ProgressPercentage == 1)
            {
                dgv.ColumnCount = dt.Columns.Count;
                for (int i=0;i= 1)
            {
                dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
            }

        }

RunWorkerCompleted Event:

private void bgworker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

            if (e.Cancelled)
            {
                MessageBox.Show("Cancelled");
            }
        }

 

Points of Interest

Cancelling SqlDataAdapter.Fill on large dataset is one of issue on desktop programming. We can try above approach without have to add similar to “more record” button.

Regards,
Agung Gugiaji

Advertisements

2 responses to “Fill DataGridView and cancel process using DataAdapter and BackgroundWorker

  1. Pingback: Cancelling DataAdapter.Fill Method On The Fly By Throwing An Error | Enlighten Application Developer Journals

  2. Hello,

    i found your solution helpful but i need more info maybe you could help me.

    how to use reportprogress of datatable

    backgroundWorker1.ReportProgress(pageno, dt);

    without paging?

    thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s