Cancel long running SQL Command in ASP.NET WebForm application

It’s an all too common scenario when your ASP.NET page takes too long to load and the culprit is slow, long running SQL query. It shouldn’t come to this, you should optimize your DB stuff to minimize delays, but if you’re trying to decode feline genome or find alien live in the neighboring galaxies – that’s unavoidable. So the page is running and at some point you decide enough is enough and decide you need to cancel it. But you want to do it gracefully, for example slow page is in an IFRAME and you want to remain in the parent page and you don’t want to close/reload the whole thing.

There’s a way. The idea is, every time you create an SqlCommand – you add it to static (shared in VB.NET) list. If command runs successfully – you remove it from the list. But if it takes too long – you can issue an AJAX call from client page to cancel the command stored in that list.

Thanks Arsalan Tamiz for posting this solution to my question on StackOverflow. His demo project was in C# (you can download it from the above link). but since most of my projects are in VB.NET – I did a conversion with some adjustments.

First we need to create a class that would hold our command. It’s pretty straightforward and has just 2 properties: One to store the name of the page where command came from and another for actual command:

Public Class RunningCommand
   Public Property PageName() As String
      Get
         Return m_PageName
      End Get
         Set(value As String)
            m_PageName = value
         End Set
   End Property
   Private m_PageName As String

   Public Property SqlCommand() As System.Data.SqlClient.SqlCommand
      Get
         Return m_SqlCommand
      End Get
      Set(value As System.Data.SqlClient.SqlCommand)
         m_SqlCommand = value
      End Set
   End Property
   Private m_SqlCommand As System.Data.SqlClient.SqlCommand
End Class

Next we need a manager class that would add/remove instances of the above class as well as be able to cancel command given page name:

Public NotInheritable Class RunningCommandManager
   
    Private Shared _runningCommands As New List(Of RunningCommand)()
    Private Shared ReadOnly locker As New Object()

    Public Shared Sub AddCommand(pageName As String, sqlCmd As System.Data.SqlClient.SqlCommand)
        Dim objNew = New RunningCommand() With { _
             .PageName = pageName, _
             .SqlCommand = sqlCmd _
        }
        SyncLock locker
            _runningCommands.Add(objNew)
        End SyncLock
    End Sub

    Public Shared Sub RemoveCommand(sqlCmd As System.Data.SqlClient.SqlCommand)
        SyncLock locker
            _runningCommands.RemoveAll(Function(x) x.SqlCommand Is sqlCmd)
        End SyncLock
    End Sub

    Public Shared Sub CancelCommand(pageName As String)
        SyncLock locker
            _runningCommands.Where(Function(x) x.PageName = pageName).ToList().ForEach(Sub(x) x.SqlCommand.Cancel())
        End SyncLock
    End Sub
End Class

Again this is pretty straightforward – the class has just three methods to add, remove and cancel SqlCommand respectfully. We’re almost ready, now all we need is some kind of entry point that the client could call.

The easiest way to do it is to add a WebAPI Controller class to your WebForm application. This way all you client has to to is issue an AJAX call to API URL. Here’s the basic class:

Public Class CancelLoadingController : Inherits ApiController

   'POST api/<controller>
   Public Sub Post(<FromBody> value As String)
      RunningCommandManager.CancelCommand(value)
   End Sub

End Class

As you can see it handles POST request and passing name of the page to cancel command from to RunningCommandManager.CancelCommand method. Ok your backend is set and ready to go.

Now let’s say you have a page with a long running SQL Command oSqlCommand that you would like to be able to cancel. In order to implement cancellation using above defined classes you need to first add it to the list of command (and remove it upon either successful execution or exeption)

Try
   '...
   'define and open your connection
   'define your query and command
   '...

    RunningCommandManager.AddCommand("MyPage", oSqlCommand)
    oReader = oSqlCommand.ExecuteReader

    '...
    'use results upon successful execution
    '...

Catch ex As Exception

    '...
    'handle the exceptions (including cancelation!)
    '...

Finally

    RunningCommandManager.RemoveCommand(cDB.m_dbSel) 

End Try

Here Line 7 adds command to the list and Line 8 executes it. Then you either use the returned data if command executed successfully or handle the exception. Note that if SQL Command is canceled – exception is thrown which you should handle. In either case Line 22 removes command from the list.

To issue cancel command, you need a client-side code. I am using jQuery here, because it’s convenient, but it’s just as easy to use plain-vanilla JS:

function cancelLoading(pageID) {
    $.ajax({
        type: "POST",
        url: "api/CancelLoading",
        data: JSON.stringify(pageID),
        contentType: "application/json",
        success: function (x) {
            console.log("Function Executed Successfully");
        },
        error: function (x) {
            console.log(x.responseText);
        }
    });
}

All it does is uses string ID you pass to it to call the controller defined above. You can define a simple button on the page like

<button onclick="cancelLoading('MyPage')">Cancel Loading</button>

Here’s what happens when you click that button during page execution:

  1. JavaScript function is called
  2. Function issues AJAX call to the controller
  3. Controller calls RunningCommandManager.CancelCommand method
  4. Original page throws “User Canceled” exception

Note that in multiuser environment instead giving your commands static names like “MyPage” provide unique ones like GUIDs otherwise you will be canceling other users’ commands as well.

Useful links:

Leave a Reply

Your email address will not be published. Required fields are marked *