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.
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. Continue reading