Konsultan IT Bandung - NET6 - JQuery DataTable

Menggunakan JQuery DataTable Ajax Request Pada CSharp

suhendrayputra 22/01/2022 ASP.NET, jQuery Leave a comment 109 Views

Data Tables merupakan sebuah plug-in jQuery untuk memanipulasi data dalam tabel HTML. Data Tables memungkinkan kita melakukan membuat tabel dengan fitur pencarian, membuat pagination, menampilkan data sebanyak yang kita mau, mengambil data dari ajax, dsb.

Biasanya Plugin ini sering dipakai Web Developer yang menggunakan baha pemrograman PHP, karena kemudahaannya dalam Integrasi.

Kali ini kita coba membuat 1 buah tampilan data menggunakan DataTable dengan rincian sebagai berikut:

Beberapa poin yang perlu diingat, bahwa ada beberapa hal yang kita skip karena diasumsikan hanya butuh bentuk data yang akan dikonsumsi oleh DataTable.

Selain itu, kita asumsikan setup Standar untuk pembuatan layout sudah dibuat karena disini hanya memperlihatkan cara Menampilkan data ke dalam DataTable menggunakan Ajax Request yang mengakses ke API pada NET6.

Razor View

Hal pertama yang kita persiapkan adalah sebuah View, tempat dimana kita akan menampilkan halaman tersebut.

				
					@{
    ViewData["placeHolderFilter"] = "Pencarian berdasarkan Nama/Email/Prodi/NPP";
}

<partial name="~/Views/Shared/_PageHeaderTitle.cshtml" />

<div class="content">
    <div class="container-fluid">

        <partial view-data="ViewData" name="~/Views/Shared/Filter/Filter_1Field.cshtml" />

        <div class="row">
            <div class="col-md-12">
                <div class="card card-default card-sm">

                    <div class="card-body card-body-sm">
                        <table class="table table-bordered table-striped table-responsive-sm" style="width:100%" id="gridAgent">
                            <thead>
                                <tr>
                                    <th>Aksi</th>
                                    <th>NPP</th>
                                    <th>NAMA</th>
                                    <th>EMAIL</th>
                                    <th>KODE UNIT</th>
                                    <th>NAMA UNIT</th>
                                    <th>PRODI</th>
                                </tr>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

@section JavaScript
{
<script type="text/javascript" src="@Url.Content("/js/modules/auditor.js")"></script>
<script type="text/javascript">console.log("in the js");</script>
}

728x90 COID webhosting - Konsultan IT Bandung - Suhendra Yohana Putra

Javascript - JQuery DataTable

Pada bagian Javascript, jika dilihat pada View yang diatas kita membutuhkan 1 buah File Javascript auditor.js, maka dari itu buat 1 buah 1 Javascript dengan isi sebagai berikut:

				
					var tableDOM = $("#gridAuditor");

var filters = {
    searchType: 'Auditor',
    search: _getVal('inputSearch')
}

$(document).ready(function () {
    vmAuditor.grid.init();
});

let vmAuditor = {
    grid: {
        redraw: function () {
            filters.search = _getVal('inputSearch');
            filters.searchType = 'Auditor';

            tableDOM.DataTable().ajax.reload();
            tableDOM.DataTable().draw(true);
        },
        init: function () {
            dtTableExportButtons = [
                {
                    "text": 'Auditor Baru',
                    "attr": {
                        "class": "btn btn-primary btn-sm"
                    },
                    "action": function (e, dt, node, config) {
                        vmNet.goToURL("/configuration/person/auditor/add");
                    }
                },
                {
                    extend: 'excelHtml5',
                    title: "Data Auditor",
                    text: 'Export Sebagai Excel',
                    titleAttr: 'Excel',
                    exportOptions: { columns: [1, 2, 3, 4, 5,6] }
                }
            ];
            

            let grid = tableDOM.DataTable({
                "ordering": true,
                "searching": false,
                "pageLength": 10,
                "lengthChange": false,
                "paging": true,
                "select": "single",
                "destroy": true,
                "scrollX": true,
                "responsive": true,
                "ajax": {
                    "url": "/api/configuration/person/auditor",
                    "type": "POST",
                    "dataType": "json",
                    "contentType": "application/json;charset=utf-8",
                    "dataSrc": function (data) {
                        return data;
                    },
                    'data': function (data) {
                        return JSON.stringify(filters) ;
                    }
                },

                'autoWidth': false,
                "dom": 'Bfrtip',
                "buttons": dtTableExportButtons,
                "order": [],
                "processing": true,
                "columns": [
                    {
                        "searchable": false,
                        "orderable": false,
                        "width": "10%",
                        "data": null,
                        render: function (data, type, row) {
                            let actions = [
                                {
                                    "class": "edit",
                                    "label": "Edit",
                                    "color": "info",
                                    "icon": "pencil",
                                    "url": null,
                                },
                                {
                                    "class": "delete",
                                    "label": "Delete",
                                    "color": "danger",
                                    "icon": "pencil",
                                    "url": null,
                                }
                            ];

                            let btnLink = vmNet.grid.generateActionLink(actions, row, BLANK_STRING);
                            return btnLink;
                        }
                    },
                    {
                        "width": "10%",
                        "data": "NPP"
                    },
                    {
                        "width": "10%",
                        "data": "NAMA_LENGKAP_GELAR"
                    },
                    {
                        "width": "10%",
                        "data": "EMAIL"
                    },
                    {
                        "width": "10%",
                        "data": "KODE_UNIT"
                    },
                    {
                        "width": "10%",
                        "data": "NAMA_UNIT"
                    },
                    {
                        "width": "10%",
                        "data": "PRODI"
                    }
                ] // end fo columns
            });

            return grid;
        }
    }
};

$(document).off(EVENT_CLICK, '#btnFilterSearch');
$(document).on(EVENT_CLICK, '#btnFilterSearch', function (e) {
    
    vmAuditor.grid.redraw();
})

$(document).off(EVENT_CLICK, '#btnFilterClear');
$(document).on(EVENT_CLICK, '#btnFilterClear', function (e) {
    _setVal('inputSearch', null);
    vmAuditor.grid.redraw();
});

Terdapat beberapa bagian penting. Pertama kita akan membuat custom filter yang diatur oleh 1buah teksfield.

				
					var filters = {
    searchType: 'Auditor',
    search: _getVal('inputSearch')
}

Kedua, bagian Ajax yang disematkan sebagai property DataTable

				
					"ajax": {
	"url": "/api/configuration/person/auditor",
    "type": "POST",
    "dataType": "json",
    "contentType": "application/json;charset=utf-8",
    "dataSrc": function (data) {
		return data;
	},
    'data': function (data) {
		return JSON.stringify(filters) ;
	}
},

yang terakhir, event setelah menekan tombol Cari atau Reset Filter.

				
					$(document).off(EVENT_CLICK, '#btnFilterSearch');
$(document).on(EVENT_CLICK, '#btnFilterSearch', function (e) {
    
    vmAuditor.grid.redraw();
})

$(document).off(EVENT_CLICK, '#btnFilterClear');
$(document).on(EVENT_CLICK, '#btnFilterClear', function (e) {
    _setVal('inputSearch', null);
    vmAuditor.grid.redraw();
});

Jika Anda melihat variable yang aneh, itu adalah variable yang bisa Anda dapatkan disini JSCore dan disini: KiNET Underscore.js

Server Side - Service

Karena kita menggunakan API maka perlu membuat 1 buah EndPoint.

Class DBAccess

Class ini berfungsi sebagai pengatur koneksi dan Data Access Wrapper

				
					public class DBAccess
    {
        public IConfiguration Configuration { get; }
        public IOptions<AppSettings> _options;
        public string _ConnString { get; set; }
        public DBAccess(IOptions<AppSettings> options)
        {
            _options = options;
            _ConnString = _options.Value.DefaultConnection;
        }
        public async Task<List<T>> ExecuteReaderAsync<T>(string storedProcedureName, SqlParameter[] sqlParameters = null) where T : class, new()
        {
            var newListObject = new List<T>();

            using (var conn = new SqlConnection(_ConnString))
            {
                using (SqlCommand sqlCommand = new SqlCommand(storedProcedureName, conn))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Clear();
                    if (sqlParameters != null)
                    {
                        for (int idx = 0; idx < sqlParameters.Length; idx++)
                        {
                            sqlCommand.Parameters.Add(sqlParameters[idx].ParameterName, sqlParameters[0].SqlDbType).Value = sqlParameters[idx].SourceColumn;
                        }
                    }

                    await conn.OpenAsync();
                    using (var dataReader = await sqlCommand.ExecuteReaderAsync(CommandBehavior.Default))
                    {
                        if (dataReader.HasRows)
                        {
                            while (await dataReader.ReadAsync())
                            {
                                var newObject = new T();
                                dataReader.MapDataToObject(newObject);
                                newListObject.Add(newObject);
                            }
                        }
                    }
                }
            }

            return newListObject;
        }
        public async Task ExecuteQuery(string storedProcedureName, SqlParameter[] sqlParameters = null)
        {
            using (var conn = new SqlConnection(_ConnString))
            {
                using (SqlCommand sqlCommand = new SqlCommand(storedProcedureName, conn))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Clear();
                    if (sqlParameters != null)
                    {
                        for (int idx = 0; idx < sqlParameters.Length; idx++)
                        {
                            sqlCommand.Parameters.Add(sqlParameters[idx].ParameterName, sqlParameters[0].SqlDbType).Value = sqlParameters[idx].SourceColumn;
                        }
                    }

                    await conn.OpenAsync();
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

    }
    public class DBOutput
    {
        public bool status { get; set; }
        public string message { get; set; }
        public dynamic data { get; set; }
        public int lastid { get; set; }
    }

    public static class MyDataReader
    {
        public static void MapDataToObject<T>(this SqlDataReader dataReader, T newObject)
        {
            if (newObject == null) throw new ArgumentNullException(nameof(newObject));

            // Fast Member Usage
            var objectMemberAccessor = TypeAccessor.Create(newObject.GetType());
            var propertiesHashSet =
                    objectMemberAccessor
                    .GetMembers()
                    .Select(mp => mp.Name)
                    .ToHashSet(StringComparer.InvariantCultureIgnoreCase);

            for (int i = 0; i < dataReader.FieldCount; i++)
            {
                var name = propertiesHashSet.FirstOrDefault(a => a.Equals(dataReader.GetName(i), StringComparison.InvariantCultureIgnoreCase));
                if (!String.IsNullOrEmpty(name))
                {
                    //Attention! if you are getting errors here, then double check that your model and sql have matching types for the field name.
                    //Check api.log for error message!
                    objectMemberAccessor[newObject, name]
                        = dataReader.IsDBNull(i) ? null : dataReader.GetValue(i);
                }
            }
        }
    }

Class Model Repository dan Interface

Class ini berfungsi sebagai Model atau Data Logic

				
					// Model Entity
public class AuditorAuditee
{
	public int? ID { get; set; }
	public int? ID_JADWAL { get; set; }
	public int? ID_UNIT { get; set; }
	public string NPP { get; set; }
	public string KODE { get; set; }
	public string PRODI { get; set; }
	public string EMAIL { get; set; }
	public string NAMA_UNIT { get; set; }
	public string KODE_UNIT { get; set; }
	public string NAMA_LENGKAP_GELAR { get; set; }
}

// Class Interface    
public interface IPerson
{
	public Task<List<AuditorAuditee>> getPersonList(string search, string role = "Admin/Auditee/Auditor");
}

// Repository
public class PersonRepository: IPerson
{
	private string _roleType;
	
	public IOptions<AppSettings> _options;

	public PersonRepository(IOptions<AppSettings> options)
	{
		_options = options;
	}

	private string useDBObject(string role)
	{
		if (role == "Admin")
		{
			_roleType = "SP_GET_AUDITOR_LIST";
		}
		else if(role == "Auditee")
		{
			_roleType = "SP_GET_AUDITEE_LIST";
		}
		else if (role == "Auditor")
		{
			_roleType = "SP_GET_AUDITOR_LIST";
		}
		return _roleType;
	}
	public async Task<List<AuditorAuditee>> getPersonList(string search, string role = "Admin/Auditee/Auditor") {
		DBOutput output = new DBOutput();
		output.status = true;
		SqlParameter[] parameters = {
			new SqlParameter("@search", System.Data.SqlDbType.VarChar, 50, search)
		};

		DBAccess dbAccess = new DBAccess(_options);
		List<AuditorAuditee> list = await dbAccess.ExecuteReaderAsync<AuditorAuditee>(this.useDBObject(role), parameters);

		return list;
	}
}

Page Contoller dan API Controller

Page Controller berfungsi sebagai Controller Halaman, sementara API controller digunakan untuk mengolah request AJAX atau endpoint.

Base Contoller

				
					public abstract class BaseController<T> : Controller where T : BaseController<T>
    {
        public readonly IAuthInterface _authRepository;
        public readonly IConfiguration _configuration;
        public String _applicationName;
        public UserLoggedIn _userLoggedIn;

        /// <summary>
        /// No Desc
        /// </summary>
        private ILogger<T> _logger;
        /// <summary>
        /// No Desc
        /// </summary>
        protected ILogger<T> Logger => _logger ?? (_logger = HttpContext.RequestServices.GetService<ILogger<T>>());
        /// <summary>
        /// Nama Module
        /// </summary>
        public string baseModuleName;
        /// <summary>
        /// Nama Controller
        /// </summary>
        public string baseCtrlName;
        /// <summary>
        /// IP Address
        /// </summary>
        public string clientIPAddress;

        public void setupPage() {
            _userLoggedIn = CommonHelper.userLoggedIn((ClaimsIdentity)User.Identity);
            ViewBag.ApplicationName = _applicationName;
            ViewBag.UserName = _userLoggedIn.name;
            ViewBag.UserEmail = _userLoggedIn.email;
            ViewBag.UserNPP = _userLoggedIn.npp;
            ViewBag.Title = "Dashboard " + _userLoggedIn.role;
            MenuBuilder menuBuilder = new MenuBuilder();
            ViewBag.Menus = menuBuilder.renderMenuList(_userLoggedIn.role);
        }

        public BaseController(IAuthInterface authRepository, IConfiguration configuration)
        {
            _authRepository = authRepository;
            _configuration = configuration;
            _applicationName = configuration.GetSection("Application").GetSection("Name").Value;
        }

        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            base.OnActionExecuting(filterContext);
        }

Page Contoller

				
					public class PersonController : BaseController<PersonController>
{
	public PersonController(IAuthInterface authRepository, IConfiguration configuration) : base(authRepository, configuration)
	{
	}

	public IActionResult Index()
	{
		if (!User.Claims.Any())
		{
			return RedirectToAction("Login", "Account");
		}

		this.setupPage();

		return View();
	}
	[Route("/configuration/person/auditor")]
	public IActionResult Auditor()
	{
		if (!User.Claims.Any())
		{
			return RedirectToAction("Login", "Account");
		}

		this.setupPage();
		ViewBag.PageDescription = "Manage Auditor";

		return View();
	}
}

API Contoller

				
					[Route("api/configuration/[controller]")]
    [ApiController]
    public class PersonController : ControllerBase
    {
        private readonly IAuthInterface _authRepository;
        private readonly IPerson _personRepository;

        public PersonController(IPerson personRepository, IAuthInterface authRepository)
        {
            _authRepository = authRepository;
            _personRepository = personRepository;
        }


        [HttpPost]
        [Route("auditor")]
        public async Task<IActionResult> AuditorList([FromBody] DataTableFilter filter)
        {
            List<AuditorAuditee> result = await _personRepository.getPersonList(filter.search, filter.searchType);
            var json = System.Text.Json.JsonSerializer.Serialize(result);
            return Ok(json);
        }
    }

Dengan konfigurasi diatas akan menampilkan hasil sebagai berikut:

Suhendra Yohana Putra Konsultan IT – Bandung Freelance Web Developer