none
Is there any way to cancel a DataAdapter.Fill command while it's processing? RRS feed

  • Question

  • Background: I have a form that executes a query to a very large database. Typically, the query takes ~4 minutes to return. To make sure that the rest of the GUI doesn't hang while this query is executing, I have the query running on a seperate thread.

    Goal: I would like to be able to cancel the query before it has been completed.

    Problem: Because the query is the only thing that runs in the BackgroundWorker_DoWork event handler, I can't find any way to cancel the background worker before it completes the query. There's no real entry point, becasuse the DoWork event only runs once; it doesn't loop. Thus, I've been looking for a way to directly cancel the DataAdapter.Fill(DataSet, "TableName") command. Is there anyway I can do this?

    This has been causing problems when the user tries to close the form before the query is complete. The background worker continues to process in the background even though I call its dispose method in the FormClosing event handler. At present, the only solution I've found is delaying the form close until the query is complete. This, however, seems very amature. Isn't there any way I can shut this down without waiting the 4 minutes for the DataAdapter.Fill command to complete?

    Monday, May 3, 2010 4:16 PM

All replies

  • You can pass a DataReader to DataAdapter.Fill. Maybe you can work with that.
    Tuesday, May 4, 2010 7:56 AM
  • Hi

    I was able to successfully cancel the fill operation by handeling the DataTable RowChanged event and either throwing an exception, or closing the data adapters connection which will cause a InvalidOperationException to be thrown.

    Here's an example of throwing an exception to stop the fill.

     

    private BackgroundWorker worker;
    private DataTable table;
    
    private void button2_Click(object sender, EventArgs e)
    {
      if (worker != null)
      {
        worker.CancelAsync();
      }
    }
    
    private void button1_Click(object sender, EventArgs e)
    {  
      this.worker = new BackgroundWorker();
      worker.WorkerReportsProgress = true;
      worker.WorkerSupportsCancellation = true;
    
      worker.DoWork += new DoWorkEventHandler(worker_DoWork);
      worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
    
      worker.RunWorkerAsync();
    }
    
    void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
      MessageBox.Show(this.table.Rows.Count.ToString());
    }
    
    [System.Diagnostics.DebuggerStepThrough]
    void worker_DoWork(object sender, DoWorkEventArgs e)
    {
      this.table = new DataTable();
    
      using (SqlConnection connection= new SqlConnection())
      using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM table", connection))
      {
        table.RowChanged += new DataRowChangeEventHandler(table_RowChanged); 
        da.Fill(table);        
      }
    }
    
    [System.Diagnostics.DebuggerStepThrough]
    void table_RowChanged(object sender, DataRowChangeEventArgs e)
    {
      if (worker.CancellationPending)
      {
        throw new ApplicationException("Canceled"); // throw a spanner in the works
      }
      Thread.Sleep(5); // Just slow things down for testing
    }
    Paul.

    Tuesday, May 4, 2010 12:30 PM
  • Great idea; appreciate the thought you put into it. Unfortunately, it doesn't really work for my situation.

    In my case, I'm querying a very large database, but returning only 20 or so records from the db. So, the fill itself does not take very long to execute, as it only needs to add 20 rows to the table. The problem is that the fill doesn't actually begin until the query on the db has completed. Is there anyway to cxl the operation before the query on the db has completed?

    Thanks again for your help!

    Update: I've tried a few different things and none of them seem to work. I've tried myDataAdapter = Nothing and myDataAdapter.Dispose() as ways of killing the in-progress query, but neither of those seems to work either. I am curious why this doesn't throw some kind of error, but mostly just need to find a way to cxl the query that is being done on the db. Any help that anyone can provide would be greatly appreciated!

    Tuesday, May 4, 2010 3:37 PM
  • - I also thought that you wanted to kill the BackgroundWorker and it seemed that that link could help.

     

    - Have you tried executing the query in the database server to verify that it doesn't take that long? I mean, if it is that fast, I don't understand why you say that it is the query that it is taking time to finish executing.

     

    - If you only want to return 20 rows every time why don't you add TOP to your query (select top 20 name from table)?

    Wednesday, May 5, 2010 9:07 AM
  • Thanks for the ideas, Zegion. Just to answer your questions:

    Yes, I have tried executing the query in the database server and it still takes ~4 minutes to complete the query there. Unfortunately it's a big, big database on a slow, slow server and there's nothing I can do to change that.

    Returning only the top 20 won't work for me, b/c that's not what I'm doing. When I said that the query only returns a small set of records, I didn't mean that it was returning only 20 random records, or the latest 20, or anything like that. It's actually just that the query will return only about 20 of the several million records because those are the only records that match the criteria.

    If anyone else has any ideas on this, I'd sure love to hear them. I've struggled with this for some time now and still have not found a solution. Thanks!

    Thursday, May 6, 2010 6:16 PM
  • Hi,

    How about just using abort?

    http://msdn.microsoft.com/en-us/library/system.threading.thread.abort(v=VS.80).aspx

    Harry


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 7, 2010 4:20 AM
  • The problem is that he's using BackgroundWorker which doesn't have the Abort method.

     

    Maybe this will work. Try to call Cancel on the SqlCommand object: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel%28v=VS.100%29.aspx

     

    Friday, May 7, 2010 10:57 AM
  • Thanks for the suggestion. I've tinkered around with it for a while, but haven't been able to come up with a suitable way of implementing what's described in the link you provided.

    Here's the code that I'm using for my DataAdapter.Fill command. Anyone think they could find a way of re-writing this so that it could work with the SqlCommand.Cancel method referenced above? Any help would be greatly appreciated:

      Dim strSQLCommand As String = "SELECT * FROM tblMainLI WHERE Name='GivenName' AND Date=#04/05/2010#"
    
      Dim objDBConnection As New OleDb.OleDbConnection
      Dim objDA As OleDb.OleDbDataAdapter
    
      Try
       objDBConnection.ConnectionString = My.Resources.LifeDBConnectionString
       objDBConnection.Open()
       objDA = New OleDb.OleDbDataAdapter(strSQLCommand, objDBConnection)
       objDA.Fill(objDS, "LIFE")
       objDBConnection.Close()
      Catch ex As Exception
       objDBConnection.Close()
       MessageBox.Show("Error")
       Exit Sub
      End Try

     

    Note: The code given above is in vb.net. I posted this in the C# forum originally b/c the answers in this forum tend to be better. If necessary, I can translate this to C# and repost, but I think it should be pretty evident what the code is doing. Responses in C# are, of course, more than welcome. I can take care of translating it back to VB for my application.

    Wednesday, May 12, 2010 1:31 AM
  • Late to the party and I hope you have figured out already, it's actually quite simple:

    adapter.SelectCommand.Cancel();


    • Proposed as answer by Waterine Friday, August 2, 2013 2:17 AM
    Friday, August 2, 2013 2:16 AM