none
Disconnecting from DB after cmd.ExecuteNonQueryAsync() RRS feed

  • Question

  • Hi

    From a Windows application I am calling cmd.ExecuteNonQueryAsync(); and I don;t want to wait for the query to complete, nor i want to wait for its output.  I just want to initiate the call to a procedure and then close connection and exit the function/program. 

    But i guess, moment i call cnn.close() connection, the Query is aborted.  

    Is there a way to trigger a procedure and close connection.  yet the procedure keeps executing itself until its complete.

    Regards

    Jiya.

    Sunday, March 12, 2017 12:04 PM

All replies

  • No, you can't close the connection before the action query or stored procedure has completed. Keep in mind that if you were to do this there would be no way to know if it failed.

    I don't know what type of database you are working with, but with SQL Server you could have your code run sp_start_job and have that run a job for your stored procedure. You would not need to run async from your app in this instance.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 13, 2017 12:39 PM
  • Hi Paul,

    Thanks for the response.  

    Meanwhile i had a look at jobs. Unfortunately I can run only one instance of Job at a time. 

    The procedure are long running code that would in the end produce report into a file on DB server.
    The long running procedure can be triggered by anyone, any number of times, from an asp.net application and asp.net would timeout before procedure is finished. Hence i don;t want to wait for the result. I was hoping something like Fire and Forget kind. 

    But i see no way of doing so, unless you suggest some idea. 

    Regards

    Jiya

    Monday, March 13, 2017 4:26 PM
  • It sounds like you have a "fire-and-forget" type of scenario, and the ThreadPool.QueueUserWorkItem() is perfect for that. You could set up your SqlCommand (or OleDbCommand, depending on your database) and pass that to the callback. You probably won't be able to exit the application when you fire off that query, though, but as long as the application is running, it should continue to run the Sql procedure. I wanted to do a little more testing of this scenario before I replied, but I a critical work situation came up that I have to attend to.

    But, something like this:

    Utils util = new Utils();
    SqlCommand sc = new SqlCommand();
    // --- set up the command
    ThreadPool.QueueUserWorkItem(util.FireAndForget, sc);

    And then, in Utils:

     public void FireAndForget(object query)
    {
        SqlCommand sc = query as SqlCommand;
        // ... and the rest of it
    }

    Sorry I don't have time for more right now ...  =0(

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, March 13, 2017 4:46 PM
  • For .NET 4.5 and above you should use a Task for this.
    #pragma warning disable 4014
    Task.Run(() =>
    {
        SomeFireAndForgetmethod();
    }).ConfigureAwait(false);
    #pragma warning restore 4014
    The pragma is to disable the warning that tells you you're running this Task as fire and forget.

    william xifaras

    Monday, March 13, 2017 5:20 PM
  • Hi

    Thanks for the inputs. I will try this. 

    Regards

    Tuesday, March 14, 2017 4:31 AM