如何使用c#、ASP实现jQuery DataTables插件。NET、SQL服务器端处理?

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

如何使用c#、ASP实现jQuery DataTables插件。使用ajax和webservices进行NET、SQL服务器端处理?

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

想要实现一个使用c#和ASP的数据网格。但很难找到一个有效的例子。

3 个解决方案

#1

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

此版本适用于较老的SqlServer，对于较新的版本，请尝试另一个版本。

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

这种技术使用存储过程，您可以通过使用#temp主要特性之外的其他方法来提高性能

• Stored Procedure
• 存储过程
• Injection Free and easily adaptable sql structure
• 注入*和容易适应的sql结构。
• Ajax
• Ajax

Functional and Very Useful

功能和非常有用的

Step 1:(HTML)

步骤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)

一步:2(存储过程)

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

步骤:3 (AJAX页面)c#表单

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 :

最终结果:

#2

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

c#中JQuery DataTables的一个工作示例。使用ajax和webservices进行NET、SQL服务器端处理。

http://jquerydatatablessamp.codeplex.com/

http://jquerydatatablessamp.codeplex.com/

https://github.com/benni12/jquerydatatablesExample

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.

从链接到datatable开始。Net，艾伦在这个插件上做的很好。

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.

我使用这个插件已经有一年了。当我第一次尝试在c#和。net中使用服务器端处理、ajax和webservices实现它时，几乎没有文档，也没有工作示例，所以我决定把它们放在一起。

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.

提供的源代码是一个Visual Studio 2012(在Ultimate 2012和Pro 2012测试)网站项目。我把它作为一个简单的工作示例放在一起，希望能帮助其他人把这样的东西放在一起。这是我一年前希望拥有的。

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

数据库mdf包含在App_Data文件夹中。我目前正在运行一个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.

需要安装SQL Server Express 2012或其他标准或更好的完整版本的MS SQL Server。测试标准2008 R2和2012 Express。

#3

Implementation in MVC, Entity Framework , Stored Procedure with Latest fetch offset pagination

在MVC、实体框架、存储过程中实现最新的获取偏移分页

Step 1 - HTML

步骤1 - HTML

<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>

@*<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs/dt-1.10.13/r-2.1.1/sc-1.4.2/datatables.min.css" />

<script type="text/javascript" src="https://cdn.datatables.net/v/bs/dt-1.10.13/r-2.1.1/sc-1.4.2/datatables.min.js"></script>*@
<link href="~/Scripts/JqueryDatatable/Datatable.css" rel="stylesheet" />
<script src="~/Scripts/JqueryDatatable/DataTable.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: '../Client/SearchMis',
            data: function (data) {
                delete data.columns;
            }
        },
        scrollY:        300,
        deferRender:    true,
        scroller:       true,
        columns: [
                    { "data": "abc" },
                    { "data": "name" },
                    { "data": "Address" },
                    { "data": "name" },
                    { "data": "name" },
                    {
                        "render": function (data, type, full, meta) {
                            return '<a class="btn btn-warning" onClick="editdata(' + full.name + ',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\')" href="javascript://">Edit</a>&nbsp;&nbsp;<a class="btn btn-danger" onClick="deletePhantom(' + full.name + ',\'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>

Step 2 - C#

步骤2 - c#

using EmployeeTrackingSystemAndMIS.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Mvc;

namespace EmployeeTrackingSystemAndMIS.Controllers
{
    public class ClientController : Controller
    {
        private EmployeeTrackingSystemAndMISEntities db = new EmployeeTrackingSystemAndMISEntities();

        public string SearchMis()
        {
            string limit, start, searchKey, orderColumn, orderDir, draw, jsonString;
            limit = Request.QueryString["length"] == null ? "" : Request.QueryString["length"].ToString();
            start = Request.QueryString["start"] == null ? "" : Request.QueryString["start"].ToString();
            searchKey = Request.QueryString["search[value]"] == null ? "" : Request.QueryString["search[value]"].ToString();
            orderColumn = Request.QueryString["order[0][column]"] == null ? "" : Request.QueryString["order[0][column]"].ToString();
            orderDir = Request.QueryString["order[0][dir]"] == null ? "" : Request.QueryString["order[0][dir]"].ToString();
            draw = Request.QueryString["draw"] == null ? "" : Request.QueryString["draw"].ToString();


            var parameter = new List<object>();
            var param = new SqlParameter("@orderColumn", orderColumn);
            parameter.Add(param);
            param = new SqlParameter("@limit", limit);
            parameter.Add(param);
            param = new SqlParameter("@orderDir", orderDir);
            parameter.Add(param);
            param = new SqlParameter("@start", start);
            parameter.Add(param);
            param = new SqlParameter("@searchKey", searchKey);
            parameter.Add(param);

            var CompanySearchList = db.Database.SqlQuery<CompanySearch>("EXEC SearchCompany @orderColumn,@limit,@orderDir,@start,@searchKey ", parameter.ToArray()).ToList();

            dynamic newtonresult = new
            {
                status = "success",
                draw = Convert.ToInt32(draw == "" ? "0" : draw),
                recordsTotal = CompanySearchList.FirstOrDefault().TotalCount,
                recordsFiltered = CompanySearchList.FirstOrDefault().TotalCount,
                data = CompanySearchList
            };
            jsonString = JsonConvert.SerializeObject(newtonresult);

            return jsonString;
        }

        private class CompanySearch
        {
            public int TotalCount { get; set; }
            public string abc { get; set; }
            public string Address { get; set; }
            public int? ClientID { get; set; }
            public int? EmployeeID { get; set; }
            public string name { get; set; }
            public int CompanyID { get; set; }
        }
    }
}

Step 3 - Stored Procedure

步骤3 -存储过程

USE [EmployeeTrackingSystemAndMIS]
GO
/****** Object:  StoredProcedure [dbo].[category_post]    Script Date: 22-02-2017 10:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter  procedure [dbo].SearchCompany

@orderColumn  int ,
@limit  int,
@orderDir  varchar(20),
@start  int,
@searchKey varchar(20)
  as 
  BEGIN

     declare @to as int = @start+@limit  

    select TotalCount = COUNT(c.CompanyID) OVER(), null as abc,c.Address,c.ClientID ,c.EmployeeID  , name,
    c.CompanyID 


     from CompanyTbl c  where c.Name like '%'+@searchKey+'%'

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


                    OFFSET @start ROWS
                              FETCH NEXT @to ROWS ONLY

  End