1

I have a long running stored procedure, spLongRunningProcesswhen I call it using the Async Await it runs and executes just fine, However I need to call it without waiting for the response because it is a very long running process.. Here is my code below I am missing something but can't quite nail it down. 

public async Task<ResultObject> LongSPCallAsync()
{
    ResultObject ro = new ResultObject();
    try
    {
        using (_context = new DbContext())
        {
            SqlParameter returnVal2 = new SqlParameter("@ReturnVal2", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };

            _context.Database.ExecuteSqlCommandAsync("EXEC @ReturnVal2 = [spLongRunningProcess]", returnVal2).ConfigureAwait(false);  //<-- This NEVER runs unless I await it.? 
            var oooo = returnVal2.Value;  //<-- just orphan code for debugging.  
        }
    }
    catch (Exception e)
    {
        await _logService.AddLogAsync(e, $"Error running SP:", "spLongRunningProcess");
    }
    return ro;
}

In the Conn string I have specified (Asynchronous Processing=True)

To Verify if it actually is running I'm just checking the database through another process. Just looking for what I missed.

maccettura
  • 10,514
  • 3
  • 28
  • 35
Joe Ricklefs
  • 584
  • 4
  • 24
  • 1
    await `ExecuteSqlCommandAsync`. – mm8 Oct 04 '19 at 14:25
  • I'm using that... As I posted. it works when I AWAIT the dbase call but I can't wait for it,it needs to be Fire and Forget. – Joe Ricklefs Oct 04 '19 at 14:27
  • 1
    Why? The method returns when you hit the `await`. – mm8 Oct 04 '19 at 14:28
  • Yup works perfect if I put await in front of it. However the actual call takes 20 minutes. so.. I can't wait for the response. – Joe Ricklefs Oct 04 '19 at 14:28
  • @mm8. Umm. When you `await` a task, you "wait" for the task to complete. If you don't await it, then the task is fire and forget, but you still have a reference to the task that you can pass around. – Flydog57 Oct 04 '19 at 14:30
  • @JoeRicklefs the control will return to the caller when you `await` – maccettura Oct 04 '19 at 14:30
  • I would like it to be fire and forget – Joe Ricklefs Oct 04 '19 at 14:32
  • 1
    `I can't wait` - then make sure you do not dispose the `_context`, can't really have a query executing without one. Otherwise you did correctly do a [fire and forget](https://stackoverflow.com/q/46053175/11683). – GSerg Oct 04 '19 at 14:32
  • 3
    Remember that "fire and forget" not only means "I don't want to wait", but also "I don't care if this succeeds". Are you sure it's ok if it fails and you don't know about it? – Gabriel Luci Oct 04 '19 at 14:33
  • @Flydog57: You could return the `Task` returned from `ExecuteSqlCommandAsync` without awaiting in the method, but this won't make much of a difference. The method will return when and the calling thread won't be blocked during the time it takes to execute the SQL command. If you don't want to do anything after the query has been executed, you could just return from the method. You should still catch exceptions though. – mm8 Oct 04 '19 at 14:34
  • Also, why does your method return a `Task` if you want it to be fire and forget? – maccettura Oct 04 '19 at 14:36
  • It truely needs to be Fire and Forget, there is another process on a different systems that handles all error handling and logging. I really just need to kick off the process and forget about it. – Joe Ricklefs Oct 04 '19 at 14:36
  • Fire-and-forget basically means `await ExecuteSqlCommandAsync("")..ConfigureAwait(false)` and do nothing else afterwards here. – mm8 Oct 04 '19 at 14:36
  • @maccettura, Its an old method I was hacking up. It doesn't need to return anything. – Joe Ricklefs Oct 04 '19 at 14:36
  • @JoeRicklefs Then don't await and don't make the method `async`. But then you can remove the `try`/`catch`, since it won't be able to catch any exceptions. – Gabriel Luci Oct 04 '19 at 14:37
  • So you don't need to process any result from the query? – Gabriel Luci Oct 04 '19 at 14:38
  • @GabrielLuci: And await the `LongSPCallAsync()`. – mm8 Oct 04 '19 at 14:39
  • Nope I don't need to do anything with the response. It doesn't even matter if there is one. – Joe Ricklefs Oct 04 '19 at 14:39
  • @mm8 Why? If it's fire and forget, there's no reason to make `LongSPCallAsync()` async at all. – Gabriel Luci Oct 04 '19 at 14:40
  • @JoeRicklefs: If you don't care about the result, why is the return type `Task` rather than just `Task`? – mm8 Oct 04 '19 at 14:41
  • I think were getting offf topic here, If I call . await _context.Database.ExecuteSqlCommandAsync("EXEC @ReturnVal2 = [spLongRunningProcess]", returnVal2).ConfigureAwait(false); The code waits for the completion. – Joe Ricklefs Oct 04 '19 at 14:42
  • @GabrielLuci: It is given the implementation. If you return the `Task` from `ExecuteSqlCommandAsync`, you shouldn't dispose the context in the method. – mm8 Oct 04 '19 at 14:42
  • @JoeRicklefs: Yes, but it waits *asynchronously* before it disposes the context, just like it should. It doesn't block. Why can't you `await` the `ExecuteSqlCommandAsync` method? If you don't you'll dispose the context before the command has finished which makes no sense at all. – mm8 Oct 04 '19 at 14:44
  • @JoeRicklefs: If you don't want the calling to thread to wait for `LongSPCallAsync()` to complete, you may avoid awaiting the call to this method though. – mm8 Oct 04 '19 at 14:51
  • @mm8 I think this is where I messed up. i was awaiting the calling method from the controller further up the Call chain. The next question is and I may start another thread for this but It looks like when it called this way that the SP runs like a trans? Is that expected. – Joe Ricklefs Oct 04 '19 at 14:57
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/200419/discussion-on-question-by-joe-ricklefs-failure-when-trying-to-call-long-running). – Samuel Liew Oct 04 '19 at 20:58

2 Answers2

3

You should await the call to ExecuteSqlCommandAsync:

await _context.Database.ExecuteSqlCommandAsync(...).ConfigureAwait(false);

When the await statement is executed, LongSPCallAsync() will return and the calling thread can continue to do something else until the SQL command has been executed and the remainder of the method is executed.

You may choose not to await LongSPCallAsync(), but you need to await ExecuteSqlCommandAsync() in order for the DbContext not to be disposed before the command has finished. Also, if you don't await ExecuteSqlCommandAsync(), you won't be able to catch and log any exceptions thrown from it.

mm8
  • 163,881
  • 10
  • 57
  • 88
  • 1
    I'm going to mark this as solved, with a caveat. This basically answred what I was looking for but the real solution was that the Preceding Method call was being awaited and it was the thing holding up the UI from working. However this is technically correct. – Joe Ricklefs Oct 04 '19 at 17:55
0

If you truly:

  1. Don't care whether the stored procedure succeeds or fails, and
  2. Don't need any data back from the stored procedure

Then,

  • Because the command would still be running when you exit the method, you cannot use using. But that doesn't matter since you don't actually need to dispose a DbContext object.

  • Since you aren't waiting for it complete, you won't be able to see if any exception happened, so the try/catch won't help you.

  • Since you don't want the result (and can't get it anyway), there's no point using the returnVal2 variable.

So you can condense the method down to just this:

public void LongSPCallAsync()
{
    new DbContext().Database.ExecuteSqlCommandAsync("EXEC [spLongRunningProcess]");
}

Of course, you could just use await here and don't use await where you call LongSPCallAsync(), if you still want the error handling and logging you already have here.

Also, this might be a good read for you: Fire and Forget on ASP.NET

Gabriel Luci
  • 38,328
  • 4
  • 55
  • 84