Question 282168 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – How to execute a totally asynchronous query on an Azure SQL Database via VB.Net?

I have an ASP.Net website connected to an Azure SQL Server.

I need to execute a stored procedure which mostly consists of an insert statement, that takes about 8 minutes to run.

I would normally execute this via an SqlCommand.ExecuteNonQuery in the website’s VB.Net code.

However, in this case, because of the 8 minute run time, doing it that way causes the website to time out.

I need a way to execute this query asynchronously, in such a way that the website sends the command to the server and immediately resumes operation without waiting for the results. It will be obvious if something has gone wrong when I later check the relevant tables.

I can’t use the service broker, because Azure doesn’t allow for it, and I can’t set up jobs without an Azure Managed Instance.

I’ve tried SqlCommand.BeginExecuteNonQuery, which does allow me to click the button the code is behind and then immediately return to doing other things in the website, but does not seem to actually run the query, and I’m guessing this is because my code reaches the end of the event and cancels the query before it actually finishes.

Is there a way to do this?

The current code…

Protected Sub Button_Click(sender As Object, e As System.EventArgs) Handles Button.Click

    txtUsrID.Value = Session.Item("s_lngUsrID")
    txtUsrTypIDfk.Value = Session.Item("s_lngUsrTypIDfk")


    'Set up the command variables
    Dim Cnn As SqlConnection
    Dim Cmd As SqlCommand

    Cnn = New SqlConnection
    Cnn.ConnectionString = "TheString"
    Cmd = New SqlCommand
    Cmd.CommandType = Data.CommandType.StoredProcedure

    'Retrieve the record count
    Cmd.CommandText = "sptblTemp_GndrRprtPrGrp_Insrt"
    Cmd.Parameters.Add("@UsrIDfk", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Input
    Cmd.Parameters("@UsrIDfk").Value = txtUsrID.Value

    Try
        Cnn.Open()
        Cmd.Connection = Cnn
        Cmd.CommandTimeout = 0
        Cmd.BeginExecuteNonQuery()


    Catch Excptn As Exception

        txtMssgs.Value = Excptn.Message

    End Try

    'clean up
    Cmd.Parameters.RemoveAt("@UsrIDfk")


    'close the connection if it's open
    If Cnn.State = Data.ConnectionState.Open Then
        Cnn.Close()
    End If
    Cmd.Dispose()
    Cnn.Dispose()

End Sub
Tags:

2

Answers


  1. 0
    1. Add a module to your ASP project, call it something like
      "PublicModule".
    2. In that module you’re going to put the code and the result as
      Public, subroutine and the Public result objects.
    3. In your button click, you check to see if the Result object is
      nothing or empty. If it is, create a thread to call that subroutine that does
      the query and populates the Public Result. The first click will create the Thread/Query/Results. The thread will continue
      for 8 minutes and do it’s thing.

    That Public Results object will remain in that IIS process forever – until the process recycles.

    Since this is taking 8 minutes, you may have to set a variable to tell the next user/next click that the query is in progress.

    Login or Signup to reply.
  2. 0

    In your code with BeginExecuteNonQuery as presented, the problem will be that the SqlCommand object that hosts the execution is only in the local procedure scope, and you are closing the connection and disposing the connection and command object at the end of the routine—in this case, without doing anything to wait for the query to finish. You wouldn’t want to call EndExecuteNonQuery in that scope to hold it until it finishes, because it would block and end up functionally the same as what you were doing before.

    The solution with BeginExecuteNonQuery is to make sure that the SqlCommand and SqlConnection object are both hosted at a higher level scope so that you can wait to close and dispose until after the query finishes execution. I would be suspicious of using a Module since that won’t be instanced, so their might be issues with collisions between multiple users. Some sort of Class instance would probably make more sense.

    You could also use an Async/Await workflow with ExecuteNonQueryAsync. This would require less refactoring of your code, as the compiler would generate the class to persist the SqlCommand behind the scenes for you. You could do this by adding Async to the Sub declaration and then changing the line Cmd.BeginExecuteNonQuery to Await Cmd.ExecuteNonQueryAsync.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search