Get Total Rows binded with Model JQuery Data Table and make search in datatable working

Nies

Hi, I am using DataTables version 1.10.12 how can I get the total rows of the result and also with the datatable search working? Right now, I only got the first page of the result which I already filter out with: OFFSET AND FETCH, which it will be binded to the model, and I can get the result of the first page with: MyModel.Count().

My Code:

DataAccess.cs

private IEnumerable<T> Find<T>(string query, T models)
{
    using (IDbConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        return conn.Query<T>(query, models);
    }
}

public IEnumerable<MemberRepository> RetrieveData(MemberRepository repository)
{
        string query = "SELECT * FROM [Member] AS m"
            + " WITH (NOLOCK) INNER JOIN [MemberStatus] AS ms WITH (NOLOCK) ON m.[Status] = ms.[StatusIdentity] INNER JOIN [MemberAccount] AS ma WITH (NOLOCK) ON m.[Account] = ma.[AccountIdentity]"
            + " WHERE m.[GroupAccount] = 'Group' ORDER BY m.[Name] ASC OFFSET (0) * 10 ROWS FETCH NEXT 10 ROWS ONLY";

        return Find<MemberRepository>(query, repository);
}

And I access it like below:

Retrieve.ashx

public int sEcho { get; set; }
        public string sSearch { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public int iTotalRecords { get; set; }
        public int iTotalDisplayRecords { get; set; }
        public IList<string[]> aaData;

        public void SetResponse(int echo, string search, int records, int totalRecords, List<string[]> aaData)
        {
            this.sEcho = echo;
            this.sSearch = search;
            this.recordsTotal = records;
            this.recordsFiltered = records;
            this.iTotalRecords = totalRecords;
            this.iTotalDisplayRecords = totalRecords;
            this.aaData = aaData;
        }

 public override void ProcessRequest(HttpContext context)
 { 
    base.ProcessRequest(context);

    MemberRepository models = new MemberRepository();

    DataAccess access = new DataAccess();

    IEnumerable<MemberRepository> MemberLists = access.RetrieveData(models);

    List<string[]> aaData = repository.Select(r => new[]
        {
            r.Name
        }).ToList();

    SetResponse(echo, string.Empty, models.Count(), models.Count(), aaData);
  }

Retrieve.aspx

  $("#myTable").DataTable({
        processing: true,
        serverSide: true,
        pageLength: 10,
        ajax: "../Retrieve.ashx",
        fnServerData: function (sSource, aoData, fnCallback) {

            $.ajax({
                type: "POST",
                data: aoData,
                url: sSource,
                dataType: "json",
                success: function (msg) {
                    fnCallback(msg);
                }
            });
        }

For the total records, what should I replace it with? Right now I just same it with the total records after cut off for the pagination.

Your answer much appreciated.

Thank you.

Nies

Sorry, the <a href="//legacy.datatables.net/ref#aaData"></a> and <a href="//legacy.datatables.net/ref#oLanguage.sSearch"></a> shouldn't be there.