Mvc cancel Jquery Ajax request from server side

Question

I have one scenario where I need to perform search operation from database and show result on webpage. User have webform to type & search food from Sql Server Database. So here I want to cancel previous ongoing search operation and continue with new search.

For e.g. first User type tea and request sent to Mvc ActionResult using Ajax. So now request is in process and user type Tea with milk so at that time I want to cancel previous request and continue with new request.

I have below Jquery code to send request to Mvc ActionResult with abort() functionality.

var xhr = null;
function searchFood(o, q, f) {
    if(xhr && xhr.readyState != 4){
        console.log(xhr);
        xhr.abort();
    }
    xhr = $.ajax({
        type: "GET",
        url: "/food/foodsearch/",
        data: {
            o: o,
            q: q,
            filters: f
        },
        beforeSend: function () {
            showSpinner();
        },
        success: function (a) {
            try {                        
                xhr = null;
            } catch (c) {
                xhr = null;
            }
        },
        error: function (a, b, c) {
            xhr = null;
        },
    });
}

When user finish typing I call this searchfood method. Take a look at my server side c# code

[HttpGet]
[AjaxOnly]
public async Task<PartialViewResult> foodsearch(int o, string q, string filters, CancellationToken cancellationToken)
{
    CancellationToken disconnectedToken = Response.ClientDisconnectedToken;
    var source = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, disconnectedToken);

    //some local veriable declared
    model.foods = filters.ToLower() == FoodFilterTypes.b_food || filters.ToLower() == all_categories ? await SearchFoodAsync(o, maxLimit, FoodModel.b_food, q, null, null, source.Token) : new List<Food>();
}

/// <summary>
/// search foods
/// </summary>
/// <param name="offset"></param>
/// <param name="limit"></param>
/// <param name="q"></param>
/// <param name="filters"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
[NonAction]
protected async Task<List<Food>> SearchFoodAsync(int offset, int limit, string filters, string q, CancellationToken cancellationToken)
{
    DataTable dtblFood = await LSocialBL.SearchFoodAsync(offset, limit, filters, q, cancellationToken);
    //--- few more code lines
    if (dtblFood != null)
    {
        foods = dtblFood.ToList<Food>();
        //dispose object
        dtblFood.Dispose();
        dtblFood = null;

        Parallel.ForEach(foods, new ParallelOptions { CancellationToken = cancellationToken }, async (f) =>
        {
            f.images = await GetFoodImagesAsync(f.id, cancellationToken);
        });
        //for (int i = 0; i < foods.Count; i++)
        //{
        //  foods[i].images = await GetFoodImagesAsync(foods[i].id);
        //}
    }
}

Here my LSocialBL.SearchFoodAsyncmethod execute database operation like below. I am executing stored procedure to fetch results.

using (IDataReader drdFood = await objSqlDatabase.ExecuteReaderAsync(objSqlCommand, cancellationToken))
{
    dtblFoods.Load(drdFood);
}
return dtblFoods;

Here I am sending cancellationtoken to cancel existing db operation. It is necessary for me to cancel db operation as I have huge data of foods. I debug client request and it shows like below

I think this is really simple scenario so I have searched a lot for this but didn't find any useful links or examples. I found this but I am not getting how this implementation help me to cancel db operations or cancel previous old request. Can someone help me to workout this?

Answer 1

I've created the following example using ASP.NET MVC 5 and I checked both SQL Profiler and Network tab in developer tools and I can confirm it is working well and the database server also receives cancellation and cancels query execution.

Please consider, the same solutions will work with EF as well, but since in the post DataTable has been used, I also wrote the example using DataTable.

ASP.NET MVC 5 - Cancellation Example

In the following example, I've created a simple index page, containing a textbox. When you type in TextBox, it waits for 500 milliseconds to detect if you have stopped typing. Then after if detected you have stopped typing, it sends an ajax request for search.

If you start typing again (or you have delays more than 500 ms) between keystorkes, it cancels the request and query execution will also cancels at database level. You can see it using profiler.

Table1Business.cs

I suppose you have a database containing Table1 which has Id and Name columns. So I create a business logic class for search between records of Table1 by making a bit delay in the query.

Note: The delay is just for example, to simulate a long running query.

In the following code, connection has been opened async, reader executed async, and also as reader has been read async:

using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
public class Table1Business
{
    public async Task<DataTable> Search(string name,
        CancellationToken cancellationToken = default(CancellationToken))
    {
        using (var connection = new SqlConnection(@"Your connection string"))
        using (var command = new SqlCommand("WAITFOR DELAY '00:00:10'; " +
            "SELECT TOP 10 [Id], [Name] " +
            "FROM [Table1] WHERE [Name] LIKE '%' + @Name + '%'", connection))
        {
            var table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("Name", typeof(string));
            command.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
            await connection.OpenAsync(cancellationToken);
            var reader = await command.ExecuteReaderAsync(cancellationToken);
            while (await reader.ReadAsync(cancellationToken))
            {
                object[] values = new object[2];
                reader.GetValues(values);
                table.Rows.Add(values);
            }
            return table;
        }
    }
}

HomeController.cs

The following controller, has a simple Index action to return Index view and a Search action which is responsible for performing the search and also receiving the cancel pulse. In case of cancellation, the execution will be cancelled even in database. In this post, you can find more information about the cancellation.

using CancellationExample.Models;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Web.Mvc;
public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View();
    }
    public async Task<ActionResult> Search(string name, CancellationToken cancellationToken)
    {
        CancellationToken disconnectedToken = Response.ClientDisconnectedToken;
        var source = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, 
            disconnectedToken);
        DataTable dt = null;
        var business = new Table1Business();
        dt = await business.Search(name, source.Token);
        return PartialView(dt.AsEnumerable().Select(x => x.Field<string>("Name")));
    }
}

Index.cshtml

The way that I detect end of typing borrowed from this post. It's just for example, you can use any other solution based on your preference. I personally prefer to rely on an enter rather than trying to detect change of typing.

Anyway, in the following code, we check if xhr is not null, it means there is another ajax request in progress, and then we abort it:

@{
    Layout = null;
}
<div>
    <form action="/home/search" method="get" id="form">
        <input type="text" name="name" id="name" />
    </form>
    <div id="result"></div>
</div>

<script src="~/Scripts/jquery-3.3.1.js"></script>
<script>
    $(function () {
        var xhr = null;
        var timeout = null;
        $("#name").keyup(function () {
            clearTimeout(timeout);
            if (xhr != null)
                xhr.abort();
            timeout = setTimeout(function () {
                xhr = $.get("/home/search?name=" + $("#name").val(), function (data) {
                    $("#result").html(data);
                });
            }, 500);
        });
    });
</script>

Search.cshtml

@model IEnumerable<string>
<ul>
    @foreach (var item in Model)
    {
        <li>@item</li>
    }
</ul>

Answer 2

Here the code

   [HttpGet]
    [AjaxOnly]
    public async Task<PartialViewResult> foodsearch(int o, string q, string filters)
    {
        //some local veriable declared
        model.foods = new List<Food>();


        if (filters.ToLower() == FoodFilterTypes.b_food
        || filters.ToLower() == all_categories)
        {
            var gotResult = false;
            var tokenSource1 = new CancellationTokenSource();
            var tokenSource2 = new CancellationTokenSource();
            CancellationToken ct1 = tokenSource.Token;
            CancellationToken ct2 = tokenSource.Token;
            Task.Factory.StartNew(() =>
            {
                await SearchFoodAsync(o, maxLimit, FoodModel.b_food, q, null, null, ct2);
                gotResult = true;
            }, ct1);

            while (!gotResult)
            {
                // When you call abort Response.IsClientConnected will = false
                if (!Response.IsClientConnected)
                {
                    tokenSource1.Cancel();
                    tokenSource2.Cancel();
                    break;
                }
                Thread.Sleep(1000 * 10);
            }

        };
        return PartialView();
    }
    /// <summary>
    /// search foods
    /// </summary>
    /// <param name="offset"></param>
    /// <param name="limit"></param>
    /// <param name="q"></param>
    /// <param name="filters"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    [NonAction]
    protected async Task<List<Food>> SearchFoodAsync(int offset, int limit, string filters, string q, CancellationToken ct2)
    {
        var dtblFood = await LSocialBL.SearchFoodAsync(offset, limit, filters, q, ct2);
        //--- few more code lines
    }

Answer 3

Step 1 : Get the Session ID

Select @@SPID ;

Step 2 : Kill The Session ID

    Kill 50 ;

Step 3 : Proof that the sql request is killed

if you run the code below you will get

Cannot continue the execution because the session is in the kill state.

severe error occurred on the current command. The results, if any, should be discarded.

Step 4 : Full code

crete new console app and cheange "Server=.;Database=Test;Trusted_Connection=True;"

using System;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static   void Main(string[] args)
        {
            SqlConnection connection = new SqlConnection("Server=.;Database=Test;Trusted_Connection=True;;Connection Timeout=0");
            connection.Open();
            var SPID = GetSPID(connection);
            var task1 = new Task(() => doLongSql(connection),
               TaskCreationOptions.LongRunning);
            task1.Start();



            Thread.Sleep(1000 * 10);//wait 10 seconds

            if (!task1.IsCompleted)
            {

               KillSPID( SPID);

            }

            Task.WaitAll(task1);

        }
        static int GetSPID(SqlConnection connection)
        {
            SqlCommand command = new SqlCommand("Select @@SPID ",connection);
            int SPID = Convert.ToInt32( command.ExecuteScalar()) ;
            return SPID;
        }

        static void KillSPID( int SPID)
        {
            SqlConnection connection = new SqlConnection("Server=.;Database=Test;Trusted_Connection=True;");
            connection.Open();
            SqlCommand command = new SqlCommand($"KILL {SPID}", connection);
            command.ExecuteNonQuery();
            connection.Close();
        }

        static void doLongSql(SqlConnection connection)
        {

            using (connection)
            {
                SqlCommand command = new SqlCommand(
                  "WAITFOR DELAY '01:00'", //wait 1 minute
                  connection);
                 command.ExecuteNonQuery();
              }
        }


    }
}

Ref:

• Session ID of ADO.NET connection

• sql session_id c# - Google Search

• KILL (Transact-SQL) - SQL Server | Microsoft Docs

• isq sql KILL bad - Google Search

• Terminate, Stop, or Kill Long Running SQL Server Query - Discussion Forums - National Instruments

• Options for Killing All Connections for a Database

• BREAK (Transact-SQL) - SQL Server | Microsoft Docs

• sql server - Sleep Command in T-SQL? - Stack Overflow

• c# execute command in new thread async - Google Search

• c# - Executing I command asynchronously - Stack Overflow

• Should I use Tasks for long running processes?

• c# - Running multiple async tasks and waiting for them all to complete - Stack Overflow

• c# - Connection timeout for SQL server - Stack Overflow

• How to: Cancel a Task and Its Children | Microsoft Docs

• [MS-TDS]: Attention

• [Attention events can cause open transactions and blocking in SQL Server – Pankaj Mittal

• [MSFT]](https://blogs.msdn.microsoft.com/pamitt/2010/11/07/attention-events-can-cause-open-transactions-and-blocking-in-sql-server/)

• Retrieving Data Using a DataReader | Microsoft Docs

• SQL Server connection strings - ConnectionStrings.com

Extra

SELECT conn.session_id, host_name, program_name,
    nt_domain, login_name, connect_time, last_request_end_time 
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn
   ON sess.session_id = conn.session_id;

---

Select @@SPID

---

 KILL 56;  

---