jQuery Bootgrid sorting, pagination and search functionality not working

Question

I have a jQuery bootgrid implemented into my ASP.Net application which is filled using a Generic Handler.

I fill the bootgrid using the Generic Handler as follows:

$(function () {
    var grid = $("#grid").bootgrid({
        ajax: true,
        ajaxSettings: {
            method: "GET",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            cache: false
        },
        url: "/MyHandler.ashx",
        rowCount: [10, 50, 75, 100, 200, -1]
    });
}

Here's MyHandler.ashx code:

public class RolesHandler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/json";
        context.Response.Write(GetData());
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    public string GetData()
    {
        var result = string.Empty;
        var con = new SqlConnection();
        var cmd = new SqlCommand();
        var dt = new DataTable();
        string sSQL = @"SELECT Id, Name
                        FROM dbo.AspNetRoles;";

        try
        {
            using (var connection = THF.Models.SQLConnectionManager.GetConnection())
            {
                using (var command = new SqlCommand(sSQL, connection))
                {
                    connection.Open();
                    command.CommandTimeout = 0;
                    var da = new SqlDataAdapter(command);
                    da.Fill(dt);
                }
            }

            var sNumRows = dt.Rows.Count.ToString();
            var sDT = JsonConvert.SerializeObject(dt);
            result = "{ \"current\": 1, \"rowCount\": 10, \"rows\": " + sDT + ", \"total\": " + sNumRows + " }";
        }
        catch (Exception ex)
        {
        }
        finally
        {
            cmd.Dispose();
            THF.Models.SQLConnectionManager.CloseConn(con);
        }

        return result;
    }
}

Basically all the important functionality of my bootgrid that worked before I implemented it the ajax way doesn't work anymore. Specifically the ordering, searching and pagination functionality aren't working at all without any errors.

As far as I know from a bit of research. This is because every time a search phrase is made, or a header is clicked (for ordering) etc. The bootgrid performs an ajax call.

Any idea on how to fix the functionality here?

Answer 1

After much work I ended up getting it working and this is the final code result:

public class RolesHandler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/json";

        var current = context.Request.Params["current"];
        var rowCount = context.Request.Params["rowCount"];
        var orderById = context.Request.Params["sort[Id]"];
        var orderByName = context.Request.Params["sort[Name]"];
        var searchPhrase = context.Request.Params["searchPhrase"];

        var orderBy = "Id";
        var orderFrom = "ASC";

        if (orderById != null)
        {
            orderBy = "Id";
            orderFrom = orderById;
        }
        else if (orderByName != null)
        {
            orderBy = "Name";
            orderFrom = orderByName;
        }

        context.Response.Write(GetData(current, rowCount, orderBy, orderFrom, searchPhrase));
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    public string GetData(string current, string rowCount, string orderBy, string orderFrom, string searchPhrase)
    {
        var result = string.Empty;

        var currentNum = Convert.ToInt32(current) - 1;
        var temp = 0;
        if (!"Id".Equals(orderBy, StringComparison.OrdinalIgnoreCase)
            && !"Name".Equals(orderBy, StringComparison.OrdinalIgnoreCase))
            throw new ArgumentException("orderBy is not a valid value");
        if (!"desc".Equals(orderFrom, StringComparison.OrdinalIgnoreCase) && !"asc".Equals(orderFrom, StringComparison.OrdinalIgnoreCase))
            throw new ArgumentException("orderFrom is not a valid value");
        if (!int.TryParse(rowCount, out temp))
            throw new ArgumentException("Rowcount is not a valid number");

        var dt = new DataTable();
        string sSQL = @"SELECT Id, Name
                        FROM dbo.AspNetRoles
                        WHERE Id LIKE @searchPhrase
                            OR Name LIKE @searchPhrase
                        ORDER BY " + orderBy + " " + orderFrom + @"
                        OFFSET ((" + currentNum.ToString() + ") * " + rowCount + @") ROWS
                        FETCH NEXT " + rowCount + " ROWS ONLY;";

        using (var connection = THF.Models.SQLConnectionManager.GetConnection())
        {
            using (var command = new SqlCommand(sSQL, connection))
            {
                command.Parameters.Add(new SqlParameter("@searchPhrase", "%" + searchPhrase + "%"));
                command.Parameters.Add(new SqlParameter("@orderBy", orderBy));

                connection.Open();
                command.CommandTimeout = 0;
                var da = new SqlDataAdapter(command);
                da.Fill(dt);
                connection.Close();
            }
        }

        var total = string.Empty;

        string sSQLTotal = @"SELECT COUNT(*)
                             FROM dbo.Log
                             WHERE Id LIKE @searchPhrase
                                OR Name LIKE @searchPhrase;";

        using (var connection = THF.Models.SQLConnectionManager.GetConnection())
        {
            using (var command = new SqlCommand(sSQLTotal, connection))
            {
                command.Parameters.Add(new SqlParameter("searchPhrase", "%" + searchPhrase + "%"));

                connection.Open();
                command.CommandTimeout = 0;
                total = command.ExecuteScalar().ToString();
                connection.Close();
            }
        }

        var rows = JsonConvert.SerializeObject(dt);

        return result = "{ \"current\": " + current + ", \"rowCount\": " + rowCount + ", \"rows\": " + rows + ", \"total\": " + total + " }";
    }
}