How to make OleDb code run asynchronous?

Question

I've seen a lot of examples for asynchronous when trying to do Web downloading/reading. But I fail to find a sample or anything for OleDb (or is there a better equivalent?), I'd like to use the new and simplified Async and Await features of C# 5.0.

This is just an example of how I use OleDb now:

public void insertTafelloc(int tafelnr, string datum, string tijd)
{
tafelsupdate = false;
try
{
    db.cmd.Connection = db.connection;
    db.connection.Open();
    db.cmd.CommandText = "SELECT * FROM tafels WHERE tafelnr = ? AND datum = ?";
    db.cmd.Parameters.Add(new OleDbParameter("1", tafelnr));
    db.cmd.Parameters.Add(new OleDbParameter("2", datum));
    OleDbDataReader dataReader;
    dataReader = db.cmd.ExecuteReader(CommandBehavior.CloseConnection);
    while (dataReader.Read())
    {
        if (dataReader["tafelnr"].ToString() != "")
        {
            tafelsupdate = true;
        }
    }
    dataReader.Close();
    db.cmd.Parameters.Clear();
    db.connection.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}

I do run a few data readers after each other, multiple times on request, and it's taking quite a while before the new results are showing on the Form. Also, I'm using OleDb to access a Access database.

Lasse Christiansen · Accepted Answer · 2013-12-31T02:27:40.107

5

A simple approach would be to wrap the DB operations in a Task:

public async Task DoDbOperationsAsync()
{
    await Task.Run(async () =>
    {
         // Your DB operations goes here

         // Any work on the UI should go on the UI thread

         // WPF
         await Application.Current.Dispatcher.InvokeAsync(() => {
              // UI updates
         });

         // WinForms
         // To do work on the UI thread we need to call invoke on a control
         // created on the UI thread..
         // "this" is the Form instance
         this.Invoke(new Action(() =>
         {
             button1.Text = "Done";
         }));
    });
}

As mentioned in the comments, if this method is invoked from the UI, you can simply do your async operations in the Task, and when await resumes, there is no need to look for a Dispatcher, since await in this case is resuming on the UI thread. An example of that is given here:

public async void OnButtonClick_DoDbOperationsAsync()
{
    await Task.Run(() =>
    {
         // Your DB operations goes here
    });

    // You are now back at the UI thread and can update the UI..
}

edited Dec 31 '13 at 02:27

answered Dec 31 '13 at 00:54

Lasse Christiansen

9,735
7
43
72

Don't nest async invocation; just put that code after the `await Task.Run()` call. – SLaks Dec 31 '13 at 01:02
@SLaks Thanks for the heads up - can you expand on the reason why nesting async invocation is not the best approach here and/or in general? – Lasse Christiansen Dec 31 '13 at 01:04
2

When called from the UI thread, `await` will resume code after the async operation on the UI thread. That's part of the whole point. – SLaks Dec 31 '13 at 01:07
Thanks for your fast and clean answer. But I have a question about the 'Application.Current.Dispatcher' part, I can't figure out why .Current doesn't exist? I tried the code both inside the Form1.cs and in my separate class with my oledb code. The Error says: 'System.Windows.Forms.Application' does not contain a definition for 'Current' – Arndroid Dec 31 '13 at 01:25
@Arndroid I have now updated the answer to cover both WinForms and WPF :) – Lasse Christiansen Dec 31 '13 at 01:46
Thanks! That works! All though, I run a OleDb code in a for loop (hence why it delays the Form stuff) and it says that the connection is still open. (while already trying to work on another new one) This is obviously because of how Async works, but is there someway to open multiple connections in that case? – Arndroid Dec 31 '13 at 02:08
@Arndroid Well, if the connection is already open, you should not try to open it again. If you have multiple tasks using the same connection, I would suggest that you create a connection per task instead. Make sure to close the connection when done. – Lasse Christiansen Dec 31 '13 at 02:16
You should do the UI updates *after* the `Task.Run`, as SLaks pointed out; the resulting code is much clearer, has better separation of concerns, and is more portable (WPF/WinForms/Win8/Droid/iOS/WP/SL) than nested lambdas. Also, you should point out that the ADO.NET types have been updated in .NET 4.5 with TAP methods which should be the first attempt at a solution in this scenario; the only reason `Task.Run` is necessary is because the OleDb provider has not (yet) been updated. P.S. `DoDbOperations` should be `DoDbOperationsAsync`. – Stephen Cleary Dec 31 '13 at 02:18
@StephenCleary Thanks for your input :) I agree that would make better separation of concerns, but, if the method is not invoked from an UI thread, what you and @SLaks are suggesting wont work - since `await` will then continue on a thread different from the UI thread, and you would need to ask for the UI thread before doing any UI updates. Anyway, I have updated the answer to take both perspectives into account. Please correct me if I'm wrong :) – Lasse Christiansen Dec 31 '13 at 02:22
Alright, thanks! I just got to figure out what to do about the multiple connection problem. Maybe something along the lines of 'CancellationTokenSource' and doing a connection.Close() along with it or something. :) – Arndroid Dec 31 '13 at 13:56

How to make OleDb code run asynchronous?

1 Answers1

Linked