ASP.NET8 using DataTables.net – Part5 – Passing additional parameters in AJAX

A practical guide to using jQuery DataTables.net component in Asp.Net 8 MVC application.

Abstract: A practical guide to building an Asp.Net 8 MVC application that uses jQuery component DataTables.net. This is a continuation of article Part4.

1 ASP.NET8 using jQuery DataTables.net

I was evaluating the jQuery DataTables.net component [1] for usage in ASP.NET8 projects and created several prototype (proof-of-concept) applications that are presented in these articles.

1.1 Articles in this series

Articles in this series are:

ASP.NET8 using DataTables.net – Part1 – Foundation
ASP.NET8 using DataTables.net – Part2 – Action buttons
ASP.NET8 using DataTables.net – Part3 – State saving
ASP.NET8 using DataTables.net – Part4 – Multilingual
ASP.NET8 using DataTables.net – Part5 – Passing additional parameters in AJAX
ASP.NET8 using DataTables.net – Part6 – Returning additional parameters in AJAX
ASP.NET8 using DataTables.net – Part7 – Buttons regular
ASP.NET8 using DataTables.net – Part8 – Select rows
ASP.NET8 using DataTables.net – Part9 – Advanced Filters

2 Final result

The goal of this article is to create a proof-of-concept application that demos DataTables.net component passing additional parameters in AJAX. Let us present the result of this article.

What you need to notice is that we passed the preselected value to AJAX to indicate we wanted only Employees from Brazil. The point is, the component DataTables.net lets you pass some additional parameters to AJAX call to tailor back-end processing per your need. That value can come from some sub-form on the client side, for example.

3 Client-side DataTables.net component

Here I will just show what the ASP.NET view using DataTables component looks like.

<!-- Employees.cshtml -->
<partial name="_LoadingDatatablesJsAndCss" />

@{
        <div class="text-center">
            <h3 class="display-4">Employees from Brazil table</h3>
        </div>

    <!-- Here is our table HTML element defined. JavaScript library Datatables
    will do all the magic to turn it into interactive component -->
        <table id="EmployeesTable01" class="table table-striped table-bordered ">
        </table>
}

<script>
    // Datatables script initialization=========================================
    // we used defer attribute on jQuery so it might not be available at this point
    // so we go for vanilla JS event

    document.addEventListener("DOMContentLoaded", InitializeDatatable);

    function InitializeDatatable() {
        $("#EmployeesTable01").dataTable({
            //processing-Feature control the processing indicator.
            processing: true,
            //paging-Enable or disable table pagination.
            paging: true,
            //info-Feature control table information display field
            info: true,
            //ordering-Feature control ordering (sorting) abilities in DataTables.
            ordering: true,
            //searching-Feature control search (filtering) abilities
            searching: true,
            //search.return-Enable / disable DataTables' search on return.
            search: {
                return: true
            },
            //autoWidth-Feature control DataTables' smart column width handling.
            autoWidth: true,
            //lengthMenu-Change the options in the page length select list.
            lengthMenu: [10, 15, 25, 50, 100],
            //pageLength-Change the initial page length (number of rows per page)
            pageLength: 10,

            //order-Initial order (sort) to apply to the table.
            order: [[1, 'asc']],            

            //serverSide-Feature control DataTables' server-side processing mode.
            serverSide: true,

            //stateSave-State saving - restore table state on page reload.
            stateSave: true,
            //stateDuration-Saved state validity duration.
            //-1 sessionStorage will be used, while for 0 or greater localStorage will be used.
            stateDuration: -1,

            //Load data for the table's content from an Ajax source.
            ajax: {
                url: "@Url.Action("EmployeesDT", "Home")",
                type: "POST",
                datatype: "json",
                data: {
                    countryFixed: "Brazil",
                    testNumber: 333
                }
            },

            //Set column specific initialization properties
            columns: [
                //name-Set a descriptive name for a column
                //data-Set the data source for the column from the rows data object / array
                //title-Set the column title
                //orderable-Enable or disable ordering on this column
                //searchable-Enable or disable search on the data in this column
                //type-Set the column type - used for filtering and sorting string processing
                //visible-Enable or disable the display of this column.
                //width-Column width assignment.
                //render-Render (process) the data for use in the table.
                //className-Class to assign to each cell in the column.

                {   //0
                    name: 'id',
                    data: 'id',
                    title: "Employee Id",
                    orderable: true,
                    searchable: false,
                    type: 'num',
                    visible: true
                },
                {
                    //1
                    name: 'givenName',
                    data: "givenName",
                    title: "Given Name",
                    orderable: true,
                    searchable: true,
                    type: 'string',
                    visible: true
                },
                {
                    //2
                    name: 'familyName',
                    data: "familyName",
                    title: "Family Name",
                    orderable: true,
                    searchable: true,
                    type: 'string',
                    visible: true
                },
                {
                    //3
                    name: 'town',
                    data: "town",
                    title: "Town",
                    orderable: true,
                    searchable: true,
                    type: 'string',
                    visible: true
                },
                {
                    //4
                    name: 'country',
                    data: "country",
                    title: "Country",
                    orderable: false,
                    searchable: false,
                    type: 'string',
                    visible: true
                },
                {
                    //5
                    name: 'email',
                    data: "email",
                    title: "Email",
                    orderable: true,
                    searchable: true,
                    type: 'string',
                    visible: true
                },
                {
                    //6
                    name: 'phoneNo',
                    data: "phoneNo",
                    title: "Phone Number",
                    orderable: false,
                    searchable: true,
                    type: 'string',
                    visible: true
                },
                {
                    //7
                    name: 'actions',
                    data: "actions",
                    title: "Actions",
                    orderable: false,
                    searchable: false,
                    type: 'string',
                    visible: true,
                    render: renderActions
                },
                    {
                    //8
                    name: 'urlForEdit',
                    data: "urlForEdit",
                    title: "urlForEdit",
                    orderable: false,
                    searchable: false,
                    type: 'string',
                    visible: false
                }
            ]
        });

        function renderActions(data, type, row, meta) {
            //for Edit button we get Url from the table data
            let html1 =
                '<a class="btn btn-info" href="' +
                row.urlForEdit + '">Edit</a>';

            //for Info button we create Url in JavaScript
            let infoUrl = "@Url.Action("EmployeeInfo", "Home")" +
                "?EmployeeId=" + row.id;
            let html2 =
                '<a class="btn btn-info"  style="margin-left: 10px" href="' +
                infoUrl + '">Info</a>';

            return html1 + html2;
        }
    }
</script>

Note that in ajax property we passed data property object containing properties we want to pass to the server side. Properties passed are countryFixed and testNumber .

More about these properties can be found in the manual at [1]. The application here is just a proof of concept for ASP.NET environment.

4 Sample AJAX call

To better understand what is going on, I used Chrome DevTools to capture data going out in a sample AJAX call.

Here is the request:

draw: 1
columns[0][data]: id
columns[0][name]: id
columns[0][searchable]: false
columns[0][orderable]: true
columns[0][search][value]: 
columns[0][search][regex]: false
columns[1][data]: givenName
columns[1][name]: givenName
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]: 
columns[1][search][regex]: false
columns[2][data]: familyName
columns[2][name]: familyName
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: 
columns[2][search][regex]: false
columns[3][data]: town
columns[3][name]: town
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]: 
columns[3][search][regex]: false
columns[4][data]: country
columns[4][name]: country
columns[4][searchable]: false
columns[4][orderable]: false
columns[4][search][value]: 
columns[4][search][regex]: false
columns[5][data]: email
columns[5][name]: email
columns[5][searchable]: true
columns[5][orderable]: true
columns[5][search][value]: 
columns[5][search][regex]: false
columns[6][data]: phoneNo
columns[6][name]: phoneNo
columns[6][searchable]: true
columns[6][orderable]: false
columns[6][search][value]: 
columns[6][search][regex]: false
columns[7][data]: actions
columns[7][name]: actions
columns[7][searchable]: false
columns[7][orderable]: false
columns[7][search][value]: 
columns[7][search][regex]: false
columns[8][data]: urlForEdit
columns[8][name]: urlForEdit
columns[8][searchable]: false
columns[8][orderable]: false
columns[8][search][value]: 
columns[8][search][regex]: false
order[0][column]: 1
order[0][dir]: asc
order[0][name]: givenName
start: 0
length: 10
search[value]: 
search[regex]: false
countryFixed: Brazil
testNumber: 333

I of course extracted only relevant data parts. Every better Web programmer should be able to understand what is going on from the above data.

5 ASP.NET back-end processing

So, we are now at C#/.NET part, writing our ASP.NET code. Here is the solution I came up with. I will not pretend it is easy, it assumes a good understanding of libraries DataTables.AspNet.Core and System.Linq.Dynamic.Core.

Here is the code:

//HomeController.cs
//this is target of AJAX call and provides data for
//the table, based on selected input parameters
public IActionResult EmployeesDT(DataTables.AspNet.Core.IDataTablesRequest request)
{
    // There is dependency in this method on names of fields
    // and implied mapping. I see it almost impossible to avoid.
    // At least, in this method, we avoided dependency on the order
    // of table fields, in case order needs to be changed
    //Here are our mapped table columns:
    //Column0 id -> Employee.Id
    //Column1 givenName -> Employee.FirstName
    //Column2 familyName -> Employee.LastName
    //Column3 town -> Employee.City
    //Column4 country -> Employee.Country
    //Column5 email -> Employee.Email
    //Column6 phoneNo -> Employee.Phone
    //Column7 actions 
    //Column8 urlForEdit 

    try
    {
        IQueryable<Employee> employees = MockDatabase.MockDatabase.Instance.EmployeesTable.AsQueryable();

        int totalRecordsCount = employees.Count();

        var iQueryableOfAnonymous = employees.Select(p => new
        {
            id = p.Id,
            givenName = p.FirstName,
            familyName = p.LastName,
            town = p.City,
            country = p.Country,
            email = p.Email,
            phoneNo = p.Phone,
        });

        //getting additional parameters
        if (request.AdditionalParameters != null)
        {
            request.AdditionalParameters.TryGetValue("countryFixed", out object? CountryFixed);

            if (CountryFixed != null)
            {
                string? CountryFixedName = CountryFixed.ToString();

                if (!System.String.IsNullOrEmpty(CountryFixedName))
                {
                    iQueryableOfAnonymous = iQueryableOfAnonymous.Where(p => p.country == CountryFixedName);
                }
            }
        }

        iQueryableOfAnonymous = FilterRowsPerRequestParameters(iQueryableOfAnonymous, request);

        int filteredRecordsCount = iQueryableOfAnonymous.Count();

        iQueryableOfAnonymous = SortRowsPerRequestParamters(iQueryableOfAnonymous, request);

        iQueryableOfAnonymous = iQueryableOfAnonymous.Skip(request.Start).Take(request.Length);

        var dataPage = iQueryableOfAnonymous.ToList();
        var dataPage2 = dataPage.Select(p => new
        {
            p.id,
            p.givenName,
            p.familyName ,
            p.town ,
            p.country ,
            p.email ,
            p.phoneNo ,
            actions = System.String.Empty,
            urlForEdit = Url.Action("EmployeeEdit", "Home", new { EmployeeId = p.id }),
        }).ToList();

        var response = DataTablesResponse.Create(request, totalRecordsCount, filteredRecordsCount, dataPage2);

        return new DataTablesJsonResult(response, false);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        var response = DataTablesResponse.Create(request, "Error processing AJAX call on server side");
        return new DataTablesJsonResult(response, false);
    }
}

//Program.cs
namespace Example05
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllersWithViews();

            // DataTables.AspNet registration 
            DataTablesAspNetRegistration(builder);

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (!app.Environment.IsDevelopment())
            {
                app.UseExceptionHandler("/Home/Error");
            }
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthorization();

            app.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");

            app.Run();
        }

        //DataTables.AspNet registration
        public static void DataTablesAspNetRegistration(WebApplicationBuilder? builder)
        {
            if (builder == null) { throw new Exception("builder == null"); };

            var options = new DataTables.AspNet.AspNetCore.Options()
               .EnableRequestAdditionalParameters()
               .EnableResponseAdditionalParameters();

            var binder = new DataTables.AspNet.AspNetCore.ModelBinder();
            binder.ParseAdditionalParameters = Parser;

            // DataTables.AspNet registration 
            builder.Services.RegisterDataTables(options, binder);

            //inner method
            IDictionary<string, object> Parser(ModelBindingContext modelBindingContext)
            {
                //for string
                string? countryFixedValue = modelBindingContext.ValueProvider.GetValue("countryFixed").FirstValue;
                //for int
                int? testNumberValue = null;
                {
                    string? tmp1 = modelBindingContext.ValueProvider.GetValue("testNumber").FirstValue;
                    if(tmp1 != null)
                    {
                        testNumberValue = System.Convert.ToInt32(tmp1);
                    }
                }

                var myDic = new Dictionary<string, object>();

                if(countryFixedValue!=null)
                {
                    myDic.Add("countryFixed", countryFixedValue);
                }

                if (testNumberValue != null)
                {
                    myDic.Add("testNumber", testNumberValue);
                }

                return myDic;
            }
        }
    }
}