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:
- JavaScript function is called
- Function issues AJAX call to the controller
- Controller calls
RunningCommandManager.CancelCommandmethod - 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: