Numer Sorting issue

kp161983

I am using datatables in MVC. Its getting sorting in the below order. But I just want default number sorting asc and desc for number and html formatted number (1,2,3....10,11....19,20,21..). Any help would be highly appreciated.

1
10
11
12
13
14
2
20
21
22

I have just tried the following options. "sType": "numeric" or "sType": "num-html" . I did not try numeric-comma.

//HTML

Index

CompanyId	Company name	Address	Town	RegDate	Action

//JS
var oTable = $('#myDataTable').dataTable({
"bServerSide": true,
"sAjaxSource": "Home/AjaxHandler",
"bProcessing": true,
"columnDefs": [
{ 'bSortable': true, 'aTargets': [0, 1, 2, 3, 4, 5] }
],
"aoColumns": [
{ "sName": "COMPANYID", "sType": "numeric"},
{ "sName": "COMPANY_NAME"},
{ "sName": "ADDRESS"},
{ "sName": "TOWN" },
{ "sName": "REGDATE" },
{
"sName": "Action",
"bSearchable": false,
"bSortable": true,
"fnRender": function (oObj) {
var companyId = oObj.aData[0];
return '<a href=\"/Company/Details/' + companyId + '\' target="_blank">' + companyId + '</a>'
}
}
]
});

//HomeController
public ActionResult AjaxHandler(JQueryDataTableParamModel param)
{
var allCompanies = DataRepository.GetCompanies();
IEnumerable<Company> filteredCompanies;
//Check whether the companies should be filtered by keyword
if (!string.IsNullOrEmpty(param.sSearch))
{
//Used if particulare columns are filtered
var companyIdFilter = Convert.ToString(Request["sSearch_1"]);
var nameFilter = Convert.ToString(Request["sSearch_2"]);
var addressFilter = Convert.ToString(Request["sSearch_3"]);
var townFilter = Convert.ToString(Request["sSearch_4"]);
var regDateFilter = Convert.ToString(Request["sSearch_5"]);

            //Optionally check whether the columns are searchable at all 
            var isCompanyIdSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
            var isNameSearchable = Convert.ToBoolean(Request["bSearchable_2"]);
            var isAddressSearchable = Convert.ToBoolean(Request["bSearchable_3"]);
            var isTownSearchable = Convert.ToBoolean(Request["bSearchable_4"]);
            var isRegDateSearchable = Convert.ToBoolean(Request["bSearchable_5"]);

            filteredCompanies = DataRepository.GetCompanies()
               .Where(c => isNameSearchable && c.Name.ToLower().Contains(param.sSearch.ToLower())
                           ||
                           isAddressSearchable && c.Address.ToLower().Contains(param.sSearch.ToLower())
                           ||
                           isTownSearchable && c.Town.ToLower().Contains(param.sSearch.ToLower())
                           ||
                           isCompanyIdSearchable && c.CompanyId.ToString().Contains(param.sSearch.ToLower())
                           ||
                           isRegDateSearchable && c.RegDate.ToString().Contains(param.sSearch.ToLower()));
        }
        else
        {
            filteredCompanies = allCompanies;
        }

        var isCompanyIdSortable = Convert.ToBoolean(Request["bSortable_0"]);
        var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
        var isAddressSortable = Convert.ToBoolean(Request["bSortable_2"]);
        var isTownSortable = Convert.ToBoolean(Request["bSortable_3"]);
        var isRegDateSortable = Convert.ToBoolean(Request["bSortable_4"]);
        var isCompanyIdLinkSortable = Convert.ToBoolean(Request["bSortable_5"]);


        var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
        Func<Company, string> orderingFunction = (c => sortColumnIndex == 0 && isCompanyIdSortable ? c.CompanyId.ToString() :
                                                       sortColumnIndex == 1 && isNameSortable ? c.Name :
                                                       sortColumnIndex == 2 && isAddressSortable ? c.Address :
                                                       sortColumnIndex == 3 && isTownSortable ? c.Town :
                                                       sortColumnIndex == 4 && isRegDateSortable ? c.RegDate.ToString() : 
                                                       sortColumnIndex == 5 && isCompanyIdLinkSortable ? c.CompanyId.ToString() : "");

        var sortDirection = Request["sSortDir_0"]; // asc or desc
        if (sortDirection == "asc")
            filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
        else
            filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);

        var displayedCompanies = filteredCompanies.Skip(param.iDisplayStart).Take(param.iDisplayLength);
        var result = from c in displayedCompanies select new[] { c.CompanyId.ToString(), c.Name, c.Address, c.Town, c.RegDate.ToString(), c.CompanyId.ToString() };
        return Json(new
                        {
                            sEcho = param.sEcho,
                            iTotalRecords = allCompanies.Count(),
                            iTotalDisplayRecords = filteredCompanies.Count(),
                            aaData = result
                        },
                    JsonRequestBehavior.AllowGet);
    }

I have tried the below links as well.
http://datatables.net/forums/discussion/367/bug-sort-number-column-and-stype

kp161983

As Allan mentioned in the above link, sorting should be done at the server side. Here is working code in the controller. Yet to be optimized :)

if (sortDirection == "asc")
filteredCompanies = (sortColumnIndex == 0 && isCompanyIdSortable ? filteredCompanies.OrderBy(x => x.CompanyId).ToList() :
sortColumnIndex == 1 && isNameSortable ? filteredCompanies.OrderBy(x => x.Name).ToList() :
sortColumnIndex == 2 && isAddressSortable ? filteredCompanies.OrderBy(x => x.Address).ToList() :
sortColumnIndex == 3 && isTownSortable ? filteredCompanies.OrderBy(x => x.Town).ToList() :
sortColumnIndex == 4 && isRegDateSortable ? filteredCompanies.OrderBy(x => x.RegDate).ToList() :
sortColumnIndex == 5 && isCompanyIdLinkSortable ? filteredCompanies.OrderBy(x => x.CompanyId).ToList() : filteredCompanies.OrderBy(x => x.ID).ToList());
else
filteredCompanies = (sortColumnIndex == 0 && isCompanyIdSortable ? filteredCompanies.OrderByDescending(x => x.CompanyId).ToList() :
sortColumnIndex == 1 && isNameSortable ? filteredCompanies.OrderByDescending(x => x.Name).ToList() :
sortColumnIndex == 2 && isAddressSortable ? filteredCompanies.OrderByDescending(x => x.Address).ToList() :
sortColumnIndex == 3 && isTownSortable ? filteredCompanies.OrderByDescending(x => x.Town).ToList() :
sortColumnIndex == 4 && isRegDateSortable ? filteredCompanies.OrderByDescending(x => x.RegDate).ToList() :
sortColumnIndex == 5 && isCompanyIdLinkSortable ? filteredCompanies.OrderByDescending(x => x.CompanyId).ToList() : filteredCompanies.OrderBy(x => x.ID).ToList());