How can I implement jQuery DataTables plugin using C#, ASP.NET, SQL Server side processing?

Question

How can I implement jQuery DataTables plugin using C#, ASP.NET, SQL Server side processing with ajax and webservices?

Would like to implement a Datatables grid using c# and ASP.NET, but it is difficult to find a working example.

Answer 1

A working example of JQuery DataTables in C#, ASP.NET, SQL Server side processing with ajax and webservices.

http://jquerydatatablessamp.codeplex.com/

https://github.com/benni12/jquerydatatablesExample

(posted it twice to make it easier for someone looking to find it)

Start off by linking to DataTables.Net, Allan has done an amazing job with this plugin.

I've been using this plugin for about one year. When I first attempted to implement it in C# and .NET with server side processing, ajax and webservices, there was little documentation and no working examples so I decided to put one together.

The source code provided is of a Visual Studio 2012 (tested in Ultimate 2012 and Pro 2012) website project. I threw this together as a simple working example in hopes of helping someone else out there trying to put something like this together. It's something I wish I had a year ago.

The database mdf is included in the App_Data folder. I am currently running an instance of MS SQL Server Express 2012.

Required install SQL Server Express 2012 or other standard or better full version installs of MS SQL Server. Tested on Standard 2008 R2 and 2012 Express.

Answer 2

This version is for older SqlServer, For newer version ,try the other one.

This technique uses stored procedure, you can improve the performance by using other methods than #temp Main features are

• Stored Procedure
• Injection Free and easily adaptable sql structure
• Ajax

Functional and Very Useful

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, -- from here 
 @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, -- to here personnel parameters
 @orderColumn  int =null, 
 @orderDir  varchar(20)=null,
 @start  int =null,
 @limit  int =null,
 @searchKey varchar(20) -- personnel parameter
  as 
  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



        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();



       // 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);

    }
  }
}

FINAL RESULT :