Using datatables with asp.net c# ,LINQ and stored procedure having pager

Question

I am using datables with asp.net, c#, Linq and SQL Server stored procedure and my procedures have paging inside with getting parameter for current page.

My aspx code is like this (for datatables)

 $(document).ready(function () {
    function renderTable(result) {
        var dtData = [];
        $.each(result, function () {
        dtData.push([
            this.errorname,   
            this.Name,
            this.Family,
        ]);
    });
    $('#grid').dataTable({  //grid is the id of the table
        'aaData': dtData,
        'bInfo': false,
        'bLengthChange': true,
        'iDisplayLength': 2,
        'bSort': true,
        'bAutoWidth': false,
        'bProcessing': true,
        'sPaginationType': "full_numbers"
    });

and this is my c# code is

DataClassesErrorsDataContext conx = new DataClassesErrorsDataContext();
List<MK_Bad_Gridview_Paging_281_Rejected_linqResult> allresults =
    conx.MK_Bad_Gridview_Paging_281_Rejected_linq(PAGENUMBER).ToList();
return allresults;

The PAGENUMBER parameter needs to get current page which clicked by user in datatables paging result table, then send it to my stored procedure in SQL Server to get current page result.

1. how can I first load all count result to datatables to have its paging correctly?
2. how can I get current clicked page number in datatables to send it to my c# code for pass to SQL Server procedure?

Answer 1

J query Datatable Using SQL server Stored Procedure
with dynamic and efficient paging structures,
supports all server versions

Step 1:(HTML)

 <link href="../Content/css/datatables.min.css" rel="stylesheet" />
 <script src="../Scripts/datatables.min.js"></script>
 <script src="../Scripts/jQuery-2.1.4.min.js"></script>

 <script>
 $(document).ready(function () {
      if ($.fn.dataTable.isDataTable('#tbl_category')) {
        t.destroy();
    }
    t = $("#tbl_category").DataTable({
        processing: true,
        serverSide: true,
        info: true,
        ajax: {
            url: '../Ajax/Category?option=GetAllAdminCategory&user_srno='+user_srno,
            data: function (data) {
                delete data.columns;
            }
        },
        columns: [
                    { "data": "abc" },
                    { "data": "name" },
                    { "data": "baseDiscount" },
                    { "data": "additionalDiscount" },
                    { "data": "specialDiscount" },
                    {
                        "render": function (data, type, full, meta) {
                            return '<a class="btn btn-warning" onClick="editdata(' + full.srno + ',\'' + full.name + '\',\'' + full.baseDiscount + '\',\'' + full.additionalDiscount + '\',\'' + full.specialDiscount + '\',\'' + full.specialDiscount + '\')" href="javascript://">Edit</a>&nbsp;&nbsp;<a class="btn btn-danger" onClick="deletePhantom(' + full.srno + ',\'DELETE\')" href="javascript://">Remove</a>';
                        }
                    }
        ],
        order: [[0, 'desc']],
        select: true,
        dom: 'lfrtip',
        responsive: true,
        buttons: true
    });
    t.on('order.dt search.dt', function () {
        t.column(0, { search: 'applied', order: 'applied' }).nodes().each(function (cell, i) {
            cell.innerHTML = i + 1;
        });
    }).draw();

});
 </script>

 <table id="tbl_category" class="display" cellspacing="0" width="100%">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Name</th>
                                <th>Base Discount</th>
                                <th>Additional Discount</th>
                                <th>Special Discount</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tfoot>
                            <tr>
                                <th>#</th>
                                <th>Name</th>
                                <th>Base Discount</th>
                                <th>Additional Discount</th>
                                <th>Special Discount</th>
                                <th>Action</th>
                            </tr>
                        </tfoot>
                    </table>

STEP :2 (Stored Procedure)

Create procedure [dbo].[category_post]
 @srno int=null,
 @user_srno int=null,
 @catSrno int=null,
 @name varchar(200)=null,
 @baseDiscount numeric(18,2)=null,
 @additionalDiscount numeric(18,2)=null,
 @specialDiscount numeric(18,2)=null,
 @status int null,
 @Action_by int null,
 @option varchar(20) = null,
 @orderColumn  int =null,
@orderDir  varchar(20)=null,
@start  int =null,
@limit  int =null,
@searchKey varchar(20)
  as 
  BEGIN

if @option='GetAllAdminCategory'
  begin

    select IDENTITY(int,1,1) as SnoID, null as abc,specialDiscount, additionalDiscount, baseDiscount, name,cast(srno as varchar(20)) as srno
            --this method is userful for all sql server version (it can be made better by using fetch)
    into #tempCategory
     from categoryStd where [status] not in(4,14) and categoryStd.name like '%'+@searchKey+'%'

     declare @to as int = @start+@limit 

     select * from #tempCategory where SnoID>@start and SnoID<=@to

        order by
                    CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN #tempCategory.[name] END DESC,
                    CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN #tempCategory.[name] END ASC,
                    CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN #tempCategory.[name] END DESC,
                    CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN #tempCategory.[name] END ASC

             OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY

        select count(*) from #tempCategory

  end           



STEP:3 (AJAX Page) C# form

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using AppBlock;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace alfest.Ajax
{
  public partial class Category : System.Web.UI.Page
  {
    string mode, option, user, limit, start, searchKey, orderByColumn, orderByDir, estMstSrno, pnlsrno, draw, jsonString;
    CommonClass cmnCls = new CommonClass();
    protected void Page_Load(object sender, EventArgs e)
    {
      mode = Request.QueryString["mode"] == null ? "" : Request.QueryString["mode"].ToString();
      option = Request.QueryString["option"] == null ? "" : Request.QueryString["option"].ToString();
      limit = Request.QueryString["length"] == null ? "" : Request.QueryString["length"].ToString();
      start = Request.QueryString["start"] == null ? "" : Request.QueryString["start"].ToString();
      user = Request.QueryString["user"] == null ? "" : Request.QueryString["user"].ToString();
      searchKey = Request.QueryString["search[value]"] == null ? "" : Request.QueryString["search[value]"].ToString();
      orderByColumn = Request.QueryString["order[0][column]"] == null ? "" : Request.QueryString["order[0][column]"].ToString();
      orderByDir = Request.QueryString["order[0][dir]"] == null ? "" : Request.QueryString["order[0][dir]"].ToString();
      estMstSrno = Request.QueryString["estMstSrno"] == null ? "" : Request.QueryString["estMstSrno"].ToString();
      pnlsrno = Request.QueryString["pnlsrno"] == null ? "" : Request.QueryString["pnlsrno"].ToString();
      draw = Request.QueryString["draw"] == null ? "" : Request.QueryString["draw"].ToString();

      if (option == "GetAllAdminCategory")
      {

       // Cls_Category CatgObj = new Cls_Category();
       // CatgObj.orderColumn = Convert.ToInt32(orderByColumn);
       // CatgObj.limit = Convert.ToInt32(limit);
       // CatgObj.orderDir = orderByDir;
       // CatgObj.start = Convert.ToInt32(start);
       // CatgObj.searchKey = searchKey;
       // CatgObj.option = "GetAllAdminCategory";

      // or user your own method to get data (just fill the dataset)

      //  DataSet ds = cmnCls.PRC_category(CatgObj);

        dynamic newtonresult = new
          {
            status = "success",
            draw = Convert.ToInt32(draw == "" ? "0" : draw),
            recordsTotal = ds.Tables[1].Rows[0][0],
            recordsFiltered = ds.Tables[1].Rows[0][0],
            data = ds.Tables[0]
          };
        jsonString = JsonConvert.SerializeObject(newtonresult);

        Response.Clear();
        Response.ContentType = "application/json";
        Response.Write(jsonString);

      }
    }
  }
}

Answer 2

I am sure that the stored procedure - not the most convenient way to get the data for the grid with paging, because in addition to the current page you will sooner or later pass also sort and filter options, and these operations can not be done after selecting a page of data. Discard the stored procedure, convert your SQL query to link query, use the dynamic linq for using of string expressions. If you need I can write a c# sample code. And yet, in my opinion, is nothing better invented than ExtJS GridPanel