[RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

**nmadd** · Apr 18th, 2008, 11:38 AM

I'm querying my Oracle 9i database following the methods in the Data Access Tutorials on www.asp.net (TableAdapter query->BLL->DataTable bound to GridView). I'm also using an UpdatePanel and UpdateProgress for my query.

I have a long running query and I would like to give the user an option to cancel the query. I realize the OracleClient has a .Cancel method for a Command but I'm not directly using and OracleCommand.

How do I go about attempting to cancel my asynchronous query through the TableAdapter safely?

Any help appreciated as usual. Thanks!

**mendhak** · Apr 18th, 2008, 05:12 PM

Poor documentation, but it exists

Command's cancel method.

**nmadd** · Apr 19th, 2008, 07:27 AM

Thanks mendhak but I don't think that's what I'm looking for. I did find that above, but I'm never creating an OracleCommand. I'm creating an instance of my Business Logic Layer class and returning a DataTable from a TableAdapter.

vb.net Code:

Dim myBLL as New myBLL()
Dim dt as DataTable = myBLL.GetDataByParam(someParam)

In this instance it simply calls the TableAdapter's query and returns a DataTable.

If it's as simple as canceling the request, posting back to the page or something, I'm happy to do that. I guess I just want to make sure that this won't affect Oracle. Will the TableAdapter "clean up" - i.e. stop the query, close the connection and whatever else?

**mendhak** · Apr 20th, 2008, 11:27 AM

I should have paid closer attention. Once the fill method gets called, you won't be able to cancel it AFAIK. You are, after all, calling a business layer and so the call to that method becomes a single entity. The client code (not javascript, I mean the bit that's doing the calling) doesn't know if it's taking a long time or not. All it knows is that there is a method that it wants to call which will return a datatable.

Because this is happening somewhere in your codebehind, the user will also not be able to issue a cancel command even if it were there for you to utilize, the reason being that the page will only 'reload' once all the data has returned from that long running query.

Your alternative is to create an asynchronous method which gets kicked off. You can kick it off using AJAX. You may then be able to 'cancel' a request if it's taking too long, but behind the scenes, the code will complete execution, just not return a result. So yes, the objects will be disposed of properly.

**nmadd** · Apr 21st, 2008, 02:15 PM

Originally Posted by mendhak

Your alternative is to create an asynchronous method which gets kicked off. You can kick it off using AJAX. You may then be able to 'cancel' a request if it's taking too long, but behind the scenes, the code will complete execution, just not return a result. So yes, the objects will be disposed of properly.

Thanks mendhak. After reading this, I did find an article on aborting an asynchronous request. I'll give it a whirl.

**mendhak** · Apr 22nd, 2008, 08:55 AM

Would be good if you shared some of your code too for future searchers.

**nmadd** · Apr 22nd, 2008, 09:06 AM

Originally Posted by mendhak

Would be good if you shared some of your code too for future searchers.

As soon as I get something worked out, you bet I will!

**nmadd** · Apr 22nd, 2008, 06:13 PM

Here's a sample of what I ended up with.

First, the Web Form. Everything is fairly self-explanatory save for the JavaScript variable.

Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <script type="text/javascript">
    // This will hold the cancelButton ClientID for the CancelAsynch.js
    // external JavaScript file. In case the button is inside another control.
    var cancelButtonID = '<%=cancelButton.ClientID%>'
    </script>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Scripts>
            <asp:ScriptReference Path="CancelAsynch.js" />
        </Scripts>    
    </asp:ScriptManager>
    <asp:Button ID="startButton" runat="server" Text="Start" />
    <asp:Button ID="cancelButton" runat="server" Text="Cancel" />
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <asp:UpdateProgress ID="UpdateProgress1" runat="server">
                <ProgressTemplate>
                    Loading...
                </ProgressTemplate>                
            </asp:UpdateProgress>
            <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
        </ContentTemplate>
        <Triggers>
            <asp:AsyncPostBackTrigger ControlID="startButton" EventName="Click" />
            <asp:AsyncPostBackTrigger ControlID="cancelButton" EventName="Click" />
        </Triggers>
    </asp:UpdatePanel>
    </form>
</body>
</html>

The Web Form code behind. Putting the thread to sleep to resemble an asynchronous request.

vb.net Code:

Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub startButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles startButton.Click
        System.Threading.Thread.Sleep(5000)
        Me.Label1.Text = "You won't see me if you click cancel."
    End Sub
End Class

And, finally, the CancelAsynch.js file.

javascript Code:

Sys.Application.add_load(LoadHandler)
function LoadHandler(sender, args) {
    // Get the instance of the PageRequestManager and tell it to call CancelAsynch
    // when it initializes.
    Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(CancelAsynch);
}
 
function CancelAsynch(sender, args) {
    // PRM variable.
    var prm = Sys.WebForms.PageRequestManager.getInstance();
    // If the PRM is currently asynchronously posting back and the element that was
    // clicked has the ID of our Cancel Button (from the global variable inline in
    // the Web Form) then abort the PostBack.
    if (prm.get_isInAsyncPostBack() & args.get_postBackElement().id(cancelButtonID) {
        prm.abortPostBack();
    }
}

Hope that helps somebody!

**mendhak** · Apr 23rd, 2008, 02:15 AM

I'd rate you for that but it looks like I've done that recently! Good work.

Thread: [RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Thread Tools

Display

[RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Re: [RESOLVED] [2008] Oracle 9i, TableAdapter, AJAX and cancelling a query

Posting Permissions