CODEWITHSUNDEEP
c#asynchronousado.net
Leron
Leron

Reputation: 9866

How to perform an ExecuteScalarAsync on several queries and manipulate data

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.

Upvotes: 0

Views: 1959

Answers (1)

usr
usr

Reputation: 171178

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.

Upvotes: 1

Related Questions