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.
Posted in Visual Basic .Net
Tagged Abort DataAdapter.Fill, BackgroundWorker, Cancel DataAdapter.Fill, DataAdapter.Fill, DataTable RowChanged, Disconnected operation, SQLCommand, SQLConnection, SqlDataAdapter, VB .Net, Visual Basic
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
Posted in C#
Tagged BackgroundWorker, C#, C# Example, DataGridView, dataset, DoWork, ProgressChanged, RunWorkerCompleted, SqlDataAdapter, Windows Form, WinForm