Asynchronous call in C# using Oracle.DataAccess (ODAC)

Question

I am trying to make an asynchronous call to Oracle, but it gets executed synchronously. Please look at below code and tell me what I am doing wrong.

(I've installed ODAC (ODTwithODAC1120320_32bit.zip) and use the Oracle.DataAccess.dll assembly for my calls to Oracle. Before I used the deprecated System.Data.OracleClient with the same result.)

using System;
...
using System.Threading;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;

namespace OracleTest
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    async private void button1_Click(object sender, EventArgs e)
    {
      OracleConnection connection = new OracleConnection("User Id=myuser;Password=mypwd;Data Source=mydb");
      connection.Open();  

      OracleCommand command = new OracleCommand("select count(col) from bigtable", connection);

      Task<Object> result = command.ExecuteScalarAsync();
      label1.Text = "BEFORE" + DateTime.Now.ToLocalTime() + " - ";
      label1.Text += await result;
      label1.Text += " - AFTER " + DateTime.Now.ToLocalTime();

      connection.Close(); 
      connection.Dispose(); 
    }
  }
}

It takes some minutes for the dbms to get the count. What I expect is this: ExecuteScalarAsync gets called and it gives Oracle a call. Immediately after the BEFORE time is written to label1. Then I wait for the Oracle query to finish and take its result. Then the AFTER time is written to label1. So BEFORE and AFTER should be different. However, they are always the same time (i.e. the time when the query returned its result). Why is that?

I also tried

  CancellationToken cancellationToken = new CancellationToken();
  Task<Object> result = command.ExecuteScalarAsync(cancellationToken);

and it didn't change anything. (What is this supposed to do anyhow? Would I not simply call command.Cancel(); instead of using a CancellationToken?)

My system: Windows 8 Pro 64bit, Visual Studio Express 2013, Oracle Client 11g (32bit): OCI 11.2.0.01

score 0 · Answer 1 · answered Nov 03 '14 at 12:31

0

If you look at the docs for ExecuteScalarAsync it clearly states that it "Implements the asynchronous version of ExecuteScalar, but returns a Task synchronously, blocking the calling thread.. Thus it appears that it's doing precisely what it says it does (blocking the calling thread).

To take advantage of ExecuteScalarAsync you need to do something like

using (object obj = await command.ExecuteScalarAsync())
{
   //....
}

Best of luck.

answered Nov 03 '14 at 12:31

Bob Jarvis - Слава Україні

48,992
9
77
110

1

This doesn't explain nor fix the problem. Creation of the task is synchronous, execution of the command shouldn't be. Wrapping the `await` call in `using` should have no effect and probably doesn't even compile (`object` doesn't implement IDisposable). The problem seems to be that the provider actually *executes* the call synchronously, despite what the documentation says. – Panagiotis Kanavos Nov 03 '14 at 12:47
@Bob Jarvis: Thanks for your help. So that would mean that ExecuteScalarAsync does the same as ExecuteScalar; it works synchronously. It blocks the thread until it is finished, just like any other synchronous command. They say more or less: "We have implemented this function because inheritence forces us to do this, but we just implemented it wrong". Is that right? BTW: What would be the advantage of *using* here? I don't see any. (And it doesn't even compile (error: must be implicitly convertible to IDisposable). – Thorsten Kettner Nov 03 '14 at 12:48
@Panagiotis Kanavos: Ah, thanks for this comment. So I seem to have understood this correctly and the function simply isn't written to work asynchronously. – Thorsten Kettner Nov 03 '14 at 12:50
Note that this answer refers to the deprecated System.Data.Oracle provider, not ODAC. Documentation on ODAC is found on Oracle's site, not MSDN. There may be requirements like a specific connection string parameter in order to execute commands asynchronously – Panagiotis Kanavos Nov 03 '14 at 12:50
@PanagiotisKanavos ODAC doesn't even have `Execute...Async` methods so I don't know how OP's code even compiles unless he is still in fact using System.Data. – helrich Nov 03 '14 at 13:07
`Execute...Async` methods are implemented by ADO.NET's abstract `DbCommand` class. Providers override them with their own async implementation. The default implementation simply calls the synchronous versions. – Panagiotis Kanavos Nov 03 '14 at 13:20

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

As far as I know, Oracle's provider still doesn't implement the asynchronous methods. This was asked previously and I can't find anything newer in Oracle's OTN or the discussion forums.

As the answer to the previous question says, the default implementation of the Async methods is to call the synchronous counterparts rather than run them wrapped in Tasks (which could actually result in worse performance actually).

edited May 23 '17 at 12:23

Community

1
1

answered Nov 03 '14 at 13:07

Panagiotis Kanavos

120,703
13
188
236

Thank you very much. I didn't see the other thread; maybe because I was looking for ExecuteScalarAsync only. Yes, that explains it. A shame that Oracle simply left these functions unwritten :-( – Thorsten Kettner Nov 03 '14 at 13:23

Asynchronous call in C# using Oracle.DataAccess (ODAC)

2 Answers2