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.

FYI, to handle large recordset we need to optimize the sql query with record ranking and if you are doing Web App then use ObjectDataSource control, please visit this blog post Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function.

Although DataAdapter.Fill method has overload function Fill(startrow as integer, rowno as integer, dt as datatable) that populate only numbers of records and iterate until all records retrived unless we break the loop, it is not actually cancelling the Fill method.
Using this overload function is applicable to fill in a DataGrid Control data so that it doesnt consume a lot of resources because we can break the loop. I already have a blog post explained this, please click Fill DataGridView and cancel process using DataAdapter and BackgroundWorker

There is a way to cancel DataAdapter.Fill method by throwing an error at DataTable_RowChanged Event. At a brief you can look at this thread: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ad4f5811-a08d-4342-bcf1-368ceac834fc/.

DataAdapter.Fill maps datasource to DataTable. When Record added to a DataTable, DataTable_RowChanged event fires.
We can write a code inside this event like throwing error. If an error occur then the application will terminate but off course we can do error handling using try catch.

Here’s a common VB.NET example to use Fill to populate a DataTable with all returned recordset:

Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=true")
conn.Open()
cmd = New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "select ProductID, Name from Production.Product"

Dim da = New SqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)

Console.WriteLine("Filling DataTable done")
For Each dr As DataRow In dt.Rows
	Console.WriteLine("ProductID: {0}  Name: {1}", dr(0).ToString(), dr(1).ToString())
Next

da.Dispose()
cmd.Dispose()
conn.Close()

Cancelling DataAdapter.Fill

I will use BackgroundWorker so populating records process will run on separate thread or asynchronous with main program.
Also I have DataTable.RowChanged Event to throwing an error if process is aborted.

Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Module Module1
	Friend bgw_da As BackgroundWorker
    Friend cmd As SqlCommand
    Friend cancelled, filldone As Boolean

    Sub Main()

        DataAdapter_Work()
        Console.ReadLine()
    End Sub

    Sub DataAdapter_Work()
       
        cancelled = False
        filldone = False

        bgw_da = New BackgroundWorker()
        AddHandler bgw_da.DoWork, AddressOf bgw_da_DoWork

        Console.WriteLine("Starting")
        bgw_da.RunWorkerAsync()

        System.Threading.Thread.Sleep(900)
        
        cancelled = True
        Console.WriteLine("Cancelling...")
       

    End Sub

    Private Sub bgw_da_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs)
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=true")
        conn.Open()
        cmd = New SqlCommand()
        cmd.Connection = conn
        cmd.CommandText = "select ProductID, Name from Production.Product"

        Dim da = New SqlDataAdapter(cmd)

        Dim dt As New DataTable()
        AddHandler dt.RowChanged, AddressOf dt_rowchanged
        Try

            da.Fill(dt)
            filldone = True
            Console.WriteLine("Filling DataTable done")
           
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        da.Dispose()
        cmd.Dispose()
        conn.Close()

    End Sub

    Private Sub dt_rowchanged(ByVal sender As Object, ByVal e As DataRowChangeEventArgs)
        If cancelled Then
            
            If e.Action = DataRowAction.Commit Then
                Throw New Exception("Cancelled")
            End If
        Else
            If e.Action = DataRowAction.Commit Then
                Console.WriteLine("Row " & e.Row(0).ToString() & " Added")
            End If
        End If

        
    End Sub
End Module

After background process is working asynchronously, the cancelled variable set to true 900 ms later to give chances DataAdapter.Fill method to start.

DataTable.RowChanged Event runs when record has been added by Fill method. In case of cancel occurs and at current row commits an add, error is throwed with its message.
Try Catch used so that the application do not close at error but it writes an error message.

Regards,
Agung Gugiaji

Advertisements

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