如何使用 C#、ASP.NET、SQL Server 端处理实现 jQuery DataTables 插件？

Question

提问by Nick Benedict

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

如何使用 C#、ASP.NET、SQL Server 端处理和 ajax 和 webservices 实现 jQuery DataTables 插件？

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

想使用 c# 和 ASP.NET 实现一个 Datatables 网格，但很难找到一个工作示例。

Answer 1

采纳答案by Nick Benedict

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

使用 ajax 和 webservices 在 C#、ASP.NET、SQL Server 端处理中的 JQuery DataTables 的工作示例。

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.

从链接到 DataTables.Net 开始，Allan 用这个插件做了一个了不起的工作。

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 上测试。

Answer 2

回答by Arun Prasad E S

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
Ajax

存储过程
注入自由且易于适应的sql结构
阿贾克斯

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

STEP：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 :

最后结果：

Answer 3

回答by Arun Prasad E S

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

如何使用 C#、ASP.NET、SQL Server 端处理实现 jQuery DataTables 插件？

提问by Nick Benedict

采纳答案by Nick Benedict

回答by Arun Prasad E S

回答by Arun Prasad E S

相关推荐

最近更新

标签

如何使用 C#、ASP.NET、SQL Server 端处理实现 jQuery DataTables 插件？

提问by Nick Benedict

采纳答案by Nick Benedict

回答by Arun Prasad E S

回答by Arun Prasad E S

相关推荐

C# 检查文件是否正在使用

在 C# 中使用 LINQ 从列表中选择不同的值

C# 以毫秒精度获取 DateTime.Now

C# 使用 WHERE 子句在 Linq 中选择 AS

相关推荐

最近更新

标签