webentwicklung-frage-antwort-db.com.de

Wie kann ich das jQuery DataTables-Plugin mithilfe der Verarbeitung von C #, ASP.NET und SQL Server implementieren?

Wie kann ich das jQuery DataTables-Plugin mithilfe von c #, ASP.NET und SQL Server-seitiger Verarbeitung mit Ajax und Webservices implementieren?

Sie möchten ein Datatables-Grid mit c # und ASP.NET implementieren, es ist jedoch schwierig, ein funktionierendes Beispiel zu finden.

9
Nick Benedict

Ein funktionierendes Beispiel für JQuery DataTables in der Verarbeitung von C #, ASP.NET und SQL Server mit Ajax und Webservices.

http://jquerydatatablessamp.codeplex.com/

https://github.com/benni12/jquerydatatablesExample

(hat es zweimal gepostet, um es für jemanden zu erleichtern, der es sucht)

Beginnen Sie mit einem Link zu DataTables.Net. Allan hat mit diesem Plugin eine erstaunliche Arbeit geleistet. 

Ich benutze dieses Plugin seit etwa einem Jahr. Als ich zum ersten Mal versuchte, es in C # und .NET mit serverseitiger Verarbeitung, ajax und Webservices zu implementieren, gab es wenig Dokumentation und keine Arbeitsbeispiele, also entschied ich mich, eine zusammenzustellen.

Der bereitgestellte Quellcode stammt von einem Website-Projekt von Visual Studio 2012 (getestet in Ultimate 2012 und Pro 2012). Ich warf dies als einfaches Arbeitsbeispiel zusammen, in der Hoffnung, jemand anderem dort draußen beim Versuch zu helfen, so etwas zusammenzustellen. Ich wünschte, ich hätte es vor einem Jahr gehabt.

Die Datenbank-MDF ist im Ordner App_Data enthalten. Ich führe zurzeit eine Instanz von MS SQL Server Express 2012 aus.

Erforderliche Installation Installation von SQL Server Express 2012 oder einer anderen Standard- oder Vollversion von MS SQL Server. Getestet mit Standard 2008 R2 und 2012 Express.

11
Nick Benedict

Diese Version ist für ältere SQL Server gedacht. Für neuere Versionen versuchen Sie die andere Version.

Diese Technik verwendet eine gespeicherte Prozedur. Sie können die Leistung mithilfe anderer Methoden als #temp .__ verbessern

  • Gespeicherte Prozedur
  • Injection Freie und leicht anpassbare SQL-Struktur
  • Ajax

Funktionell und sehr nützlich

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

SCHRITT: 2 (gespeicherte Prozedur)

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 '%'[email protected]+'%'

    declare @to as int = @[email protected]  

     select * from #tempCategory where SnoID>@start and SnoID<[email protected]

        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

SCHRITT: 3 (AJAX-Seite) C # -Formular

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

    }
  }
}

ENDERGEBNIS :  enter image description here

12
Arun Prasad E S

Implementierung in MVC, Entity Framework, Stored Procedure mit der neuesten Seitenabruf-Paginierung 

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

Schritt 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; }
        }
    }
}

Schritt 3 - Gespeicherte Prozedur

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 = @[email protected]  

    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 '%'[email protected]+'%'

        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       
1
Arun Prasad E S