Tag Archives: SqlDataAdapter

Cancelling ADO.NET DataAdapter.Fill Method On The Fly By Throwing An Error

ADO.NET DataAdapter is a bridge between data source and DataSet. The implementation of DataAdapter can be SQLDataAdapter, OLEDBDataAdapter, iDB2DataAdapter etc. DataAdapter.fill method fills data source recordset to a DataSet or DataTable returned by a ‘select’ query.
After populating to a DataSet and DataTable, database connection can be closed but DataTable data still able to access. This is known as Disconnected ADO.Net operation.
We do not have to re-connect to database if we want to retrieve same data with previously DataAdapter.Fill work. We can access the DataTable’s data like an array.
Because of that performance of disconnected operation is better compared with connected one in some cases.

If the ‘select’ query has result of thousands or millions record DataAdapter.Fill method will populate them all thus long run might occur, DataAdapter.Fill looks like slow.
However there is no default method to cancel DataAdapter.Fill. So to terminate long Fill process we have to kill it manually and the application will close.
We need to know how to cancel DataAdapter.Fill on the fly for particular reason.

Continue reading

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