Canceling DataAdapter.Fill()

Question

Scenario: We have a DataGridView which is attached to DataAdapter (datatable), we load the data in datatable using (adapter.fill(query, datatable)) in a separate thread (using delegate and beginInvoke) and once the data is loaded we attached that datatable to datagridview (in the main thread)

Is there a way we can check if fill() is still executing and cancel it.

Real scenario: User click on the user name and corresponding data is loaded in the datagrid. Sometime, user is impatient and click on the another user (here I want to cancel the previous fill and start a new fill)

UPDATE: We keep two DataApdaters (and two DataTables) and we attach one datatable to datagridview and start loading data to another datatable asynchronously. When data is loaded we simply bind the datagridview to DataTable which we just filled (and start loading the previous datable asynchronously) This way UI will always get the current data (without user waiting on UI to refresh or hang)

Answer 1

You can provide a SqlCommand to adapter constructor and invoke a Cancel method on it. There is a raw template :

class Model 
{
    private SqlCommand loadUserCommand;
    private DataTable userData;

    public void LoadUser(string userId) 
    {
        loadUserCommand = GetUserLoadCommandForUserID(userId);
        userData = new DataTable("userData");
        using (var adapter = new SqlDataAdapter(loadUserCommand)) 
        {
            adapter.Fill(userData);
        }
    }

    public void AbortLoadUser()
    {
        if (loadUserCommand!= null)
            loadUserCommand.Cancel();
    }


    private SqlCommand GetUserLoadCommandForUserID(string userId)
    {
        var connection = new SqlConnection("...");
        var command = connection.CreateCommand();
        ...
    }
}