How would add a function to a form that posts to an api to pull json data from search button?

amazeinstudios@gmail.com

I have created my serverside Datatables and they work great. Now I need a function that allows the user to type in a search form and press a search button to read specific data delivered via API to draw (or, in this case, redraw) my Datatables. The form can not simply filter already populated entries because I have to get them via API. I know its possible I just need to be pointed in the right direction. What are the best practices for creating these functions and forms?

kthorngren

You would use either search() or column().search() with draw() to send the search request via server side processing. These APIs won't use the client data since you have server side processing enabled.

Kevin

amazeinstudios@gmail.com

Can you show an example of this type of search?

kthorngren

See if this page helps:
https://datatables.net/examples/api/regex.html

Kevin

amazeinstudios@gmail.com

the search API is just a filter. I have created an ajax form in ASP.NET MVC that calls to EntityFramework to pull data. That data is required to draw the 'new' database table. I have created a similar form to that one displayed at https://datatables.net/examples/api/regex.html but it is unsuccessful and is out of scope because of the amount of data I am pulling ... I don't get a response from the ajax form. It has to be processed from submitting the form which calls the ajax. Essentially, that form you suggested is client-side ajax I'm asking for a viable serverside approach. I don't believe there is one listed that combines the two functionalities.

kthorngren

Maybe I misunderstood. Are you using serverSide processing? or your own ajax?

If you are using the serverSide then here is a simple example showing it works.
http://live.datatables.net/cezofode/1/edit

If you are using your own ajax to fetch the table data then you can either use clear() followed by rows.add() in your ajax success function. Or you can destroy() the Datataable and recreate it.

Maybe you can provide more specifics of what you are doing.

Kevin

amazeinstudios@gmail.com

I see how that solution works and I have a prior implementation similar to that. Things are a little different in the ASP.NET MVC world with Entity Framework....
Here's an example of how the file structure operates.
https://datatables.net/forums/discussion/40690/sample-implementation-of-serverside-processing-in-c-mvc-ef-with-paging-sorting-searching#latest

allan

Right - the server-side aspect is going to be significantly different for EF, but I'm not clear what is happening on the client-side here. I'd be tempted to use $.ajax() to make an Ajax request to your server to get the data (with a regular jQuery click event) and then use the API methods Kevin suggested (clear() and rows.add()) to clear out the table and then add the new data to it.

Allan

allan

Also - do you need server-side processing? Are you using 50'000 or more records?

Allan

amazeinstudios@gmail.com

There are records in the 100,000's if not more... I have a working serverside solution. What I can't get working is a dedicated column search option. I will post my code a little later on today. Hopefully, you can help.

amazeinstudios@gmail.com

Here is the code on my index.cshtml file. The name search works but the other two column searches do not. I also only need one form with several fields and one search button. Can you help? if you want me to post the controller I can.
I received the solution from here

@{
    ViewBag.Title = "Home Page";
}
<div style="background-color:#f5f5f5; padding:20px">
    <h2>Search Panel</h2>
    <table>
        <tbody>
            <tr>
                <td>Name</td>
                <td><input type="text" id="qryName" /></td>
                <td>
                    <input type="button" value="Search" id="nameSearch" />
                </td>
            </tr>
            <tr>
                <td>Price</td>
                <td><input type="text" id="qryPrice" /></td>
                <td>
                    <input type="button" value="Search" id="priceSearch" />
                </td>
            </tr>
            <tr>
                <td>Status</td>
                <td><input type="text" id="qryStat" /></td>
                <td>
                    <input type="button" value="Search" id="statSearch" />
                </td>
            </tr>
        </tbody>
    </table>
</div>
<table id="demodata" class="table table-bordered">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Quantity</th>
            <th>Price</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

<script>
    DemoDatatable();
    function DemoDatatable() {
        $("#demodata").DataTable({
            "processing": true,
            "serverSide": true,
            "filter": true,
            "orderMulti": false,
            "destroy": true,
            "ordering": true,
            "ajax": {
                "url": '/Home/GetTableData',
                "type": "POST",
                "datatype": "json"
            },

            "columns": [
                { "data": "Id", "name": "Id", "autoWidth": true }
                , { "data": "Name", "Name": "Name", "autoWidth": true }
                , { "data": "Quantity", "name": "Quantity", "autoWidth": true }
                , { "data": "Price", "name": "Price", "autoWidth": true }
                , { "data": "Status", "name": "Status", "autoWidth": true }
            ]
        });
    }
    oTable = $('#demodata').DataTable();
    $('#nameSearch').click(function () {
        oTable.columns(1).search($('#qryName').val().trim());
        oTable.draw();
    });
    $('#priceSearch').click(function () {
        oTable.columns(3).search($('#qryPrice').val().trim());
        oTable.draw();
    });
    $('#statSearch').click(function () {
        oTable.columns(4).search($('#qryStat').val().trim());
        oTable.draw();
    });
</script>

allan

Its your GetTableData code that we'd need to be able to see - that is implementing the search and ordering.

Allan

amazeinstudios@gmail.com

Here's the entire project including database.. Make sure you change the .config connection strings to match your database environment and initialize migrations and update database in Package console.
https://github.com/SunRhythms/DatatablewCustomSearch...
The DB can also be found on Mockaroo..

tangerine

It would much simpler for you to provide your GetTableData code, as requested.

amazeinstudios@gmail.com

https://github.com/SunRhythms/DatatablewCustomSearch
https://github.com/SunRhythms/DatatablewCustomSearch/blob/master/DatatablePlugin/Controllers/HomeController.cs
...

using DatatablePlugin;
using DatatablePlugin.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Linq.Dynamic;


public class HomeController : Controller
{
    private readonly Context _context = new Context();
    public ActionResult Index()
    {
        return View();
    }
    [HttpPost]
    public ActionResult GetTableData()
    {
        JsonResult result = new JsonResult();
        try
        {
            string search = Request.Form.GetValues("search[value]")[0];
            string draw = Request.Form.GetValues("draw")[0];
            string order = Request.Form.GetValues("order[0][column]")[0];
            string orderDir = Request.Form.GetValues("order[0][dir]")[0];
            var empName = Request.Form.GetValues("columns[1][search][value]")[0];
            int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
            int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
            List<DemoTable> data = _context.DemoTables.ToList();
            int totalRecords = data.Count;
            if (!string.IsNullOrEmpty(search) &&
                !string.IsNullOrWhiteSpace(search))
            {
                data = data.Where(p => p.Id.ToString().ToLower().Contains(search.ToLower()) ||
                    p.Name.ToString().Contains(search.ToLower()) ||
                    p.Quantity.ToString().Contains(search.ToLower()) ||
                    p.Price.ToString().Contains(search.ToLower()) ||
                    p.Status.ToString().Contains(search.ToLower())
                 ).ToList();
            }
            if (!string.IsNullOrEmpty(empName))
            {
                data = data.Where(a => a.Name.ToString().ToLower().Contains(empName.ToLower())).ToList();
            }
            data = SortTableData(order, orderDir, data);

            int recFilter = data.Count;
            data = data.Skip(startRec).Take(pageSize).ToList();
            var modifiedData = data.Select(d =>
                new
                {
                    d.Id,
                    d.Name,
                    d.Quantity,
                    d.Price,
                    d.Status
                }
                );
            result = this.Json(new
            {
                draw = Convert.ToInt32(draw),
                recordsTotal = totalRecords,
                recordsFiltered = recFilter,
                data = modifiedData
            }, JsonRequestBehavior.AllowGet);
        }
        catch (Exception ex)
        {
            Console.Write(ex);
        }
        return result;
    }
    private List<DemoTable> SortTableData(string order, string orderDir, List<DemoTable> data)
    {
        List<DemoTable> lst = new List<DemoTable>();
        try
        {
            switch (order)
            {
                case "0":
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
                                                                                             : data.OrderBy(p => p.Id).ToList();
                    break;
                case "1":
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Name).ToList()
                                                                                             : data.OrderBy(p => p.Name).ToList();
                    break;
                case "2":
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList()
                                                                                             : data.OrderBy(p => p.Quantity).ToList();
                    break;
                case "3":
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Price).ToList()
                                                                                             : data.OrderBy(p => p.Price).ToList();
                    break;
                case "4":
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Status).ToList()
                                                                                               : data.OrderBy(p => p.Status).ToList();
                    break;
                default:
                    lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
                                                                                             : data.OrderBy(p => p.Id).ToList();
                    break;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex);
        }
        return lst;
    }

}

amazeinstudios@gmail.com

I know its these lines but I don't know how to write the object to create the input that pulls the reflected column. So I am asking how can I rewrite this for a form group with 4 inputs and one submit button. The inputs are queries for the columns in the datatable.

1:

var empName = Request.Form.GetValues("columns[1][search][value]")[0];

and 2:

if (!string.IsNullOrEmpty(empName))
            {
                data = data.Where(a => a.Name.ToString().ToLower().Contains(empName.ToLower())).ToList();
            }

allan

The name search works but the other two column searches do not

I see in your code above where you are doing the name search:

var empName = Request.Form.GetValues("columns[1][search][value]")[0];

But I don't see a search on the other two columns, which would explain why they aren't working. Similar code would need to be added for them.

Allan

amazeinstudios@gmail.com

Once again, I know its these lines but I don't know how to write the object to create the input that pulls the reflected column. So I am asking how can I rewrite this for a form group with 4 inputs and one submit button. The inputs are queries for the columns in the datatable.

allan

You mean on the server-side? You've already got the employee name column working, so can you not just employ the same approach for the other columns?

var qryPrice= Request.Form.GetValues("columns[3][search][value]")[0];

for example. Sorry if I'm just not getting the issue - it looks like you are 99% of the way through and it just needs those other conditions added to your code. I believe DataTables, from your code above, is already sending the data you need, you just need to access it and then use it as part of your query construction.

Allan