How to perform an ExecuteScalarAsync on several queries and manipulate data

Question

I'm using ADO.NET and in one of my methods I need to return boolean value if a client is using any of the company products. For that reason I need to execute several queries since the different products have different joint tables with the client so I end up with this :

SqlCommand firstProduct= new SqlCommand(firstQuery, connection);
                firstProduct.CommandTimeout = 300;
IAsyncResult numberOfUsedProducts = firstProduct.ExecuteScalarAsync();
//second product
SqlCommand secondProduct= new SqlCommand(secondQuery, connection);
                secondProduct.CommandTimeout = 300;
                IAsyncResult numberOfUsedProducts1 = secondProduct.ExecuteScalarAsync();
//third rpoduct
SqlCommand thirdProduct = new SqlCommand(thirdQuery, connection);
                thirdProduct.CommandTimeout = 300;
                IAsyncResult numberOfUsedProducts2 = thirdProduct .ExecuteScalarAsync();

Basically what I would like to know is how to proceed and collect the result from each query so I can execute some additional logic?

From my investigation I saw that I can use something like this:

WaitHandle waitHandle1 = firstProduct.AsyncWaitHandle;
WaitHandle waitHandle2 = secondProduct.AsyncWaitHandle;
WaitHandle waitHandle3 = thirdProduct.AsyncWaitHandle;

System.Threading.WaitHandle[] waitHandles = {
           waitHandle1, waitHandle2, waitHandle3
      };

and then

index = WaitHandle.WaitAny(waitHandles, 
                    60000, false);
switch (index)
               {
..

but honestly I'm not sure what the above code is doing. What I want is handle two scenarios - collect the result from all three queries and examine it. And if possible, since it seems like it from what I read, at the moment any of the queries return result different than zero to stop further execution, since sometimes I only care if the client is using any product or not, and no how many of each prodcuts.

`WaitAny` is "waiting for any of the tasks to complete", is that what you want or do you need the result of them all regarless if they are zero or not? — DavidG, Oct 10 '14 at 13:15
As I wrote, in my real scenario I'm gonna need both. I just tried something with `WaitAny` but for some reason in the `MSDN` example the `switch` is inside ` for (int countWaits = 0; countWaits <= 2; countWaits++)` and I go through the `for` loop with no effect. Index has vaue of zero and it stays so until I exit it. But I need both. — Leron, Oct 10 '14 at 13:19
I think you perhaps are confusing `return 0` with `completing the task`? — DavidG, Oct 10 '14 at 13:22
Well, I would like some sample code because right now I don't get any exceptions but `index = WaitHandle.WaitAny(waitHandles, 60000, false);` is not changing the value of index, and also I go through the `for` loop too fast, if it was my real code the queries would not be completed eitehr way. I guess some kind of timeout is needed. In fact I thought that `60000` stand for the timeout but guess I'm wrong. — Leron, Oct 10 '14 at 13:25
`index` is just the item in your array that completed first. And since you didn't post your for loop, I can't help with that, — DavidG, Oct 10 '14 at 13:35
Sorry, my mistake. However this index is never changed. But if you have read what `usr` wrote maybe I should switch to `Task` and `await` I'm learning right now so it doesn't really makes a much of a difference to me but it seems that this is more up-to-date? — Leron, Oct 10 '14 at 13:40
Well if `index` is zero, that means it's the first item in your array that completed first. — DavidG, Oct 10 '14 at 13:42

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Your use of IAsyncResult is obsolete. ExecuteScalarAsync returns a Task (which implements IAsyncResult, but implicitly so that nobody uses it).

Next problem is that you are using the same connection concurrently which is not allowed. You must use multiple connections, or use MARS and be very careful with your threading.

Your code probably should look like this:

var results = await Task.WhenAll(firstProduct, ...);

You can add a timeout to this code as well.

If you want to stop all other queries as soon as any returns, use Task.WhenAny and cancel the other outstanding queries using the CancellationToken infrastructure.

edited May 23 '17 at 10:33

Community

1
1

answered Oct 10 '14 at 13:25

usr

168,620
35
240
369

I'm looking here - http://msdn.microsoft.com/en-us/library/yws6262h(v=vs.110).aspx and the target version is `4.5` so I thought is up to date. However in lot of examples (mostly when I read about the `WebAPI 2` I see that there `Task` is exensively used so I was really wondering. So should I stick to `IAsyncResult` or somethng like `Task` or similar which is more up to date? – Leron Oct 10 '14 at 13:28
MSDN gives a small sample for all important APIs. This sample does not imply that this API *should* be used. Use `Task`, it has no disadvantage over `IAsyncResult`. `Task` is available with .NET 4.0, so is `await`. – usr Oct 10 '14 at 13:32
Agreed, was searching for concrete examples as the one provided on the `MSDN's` page. Gonna try to find one which is implemented suing `Task`. Thanks for the information! – Leron Oct 10 '14 at 13:35

How to perform an ExecuteScalarAsync on several queries and manipulate data

1 Answers1