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:
- JQuery DataTable
- NET6 API dan Razor View
- SQL Server Stored Procedure
- Template AdminLTE
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";
}
Aksi
NPP
NAMA
EMAIL
KODE UNIT
NAMA UNIT
PRODI
@section JavaScript
{
}
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 _options;
public string _ConnString { get; set; }
public DBAccess(IOptions options)
{
_options = options;
_ConnString = _options.Value.DefaultConnection;
}
public async Task> ExecuteReaderAsync(string storedProcedureName, SqlParameter[] sqlParameters = null) where T : class, new()
{
var newListObject = new List();
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(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> getPersonList(string search, string role = "Admin/Auditee/Auditor");
}
// Repository
public class PersonRepository: IPerson
{
private string _roleType;
public IOptions _options;
public PersonRepository(IOptions 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> 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 list = await dbAccess.ExecuteReaderAsync(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 : Controller where T : BaseController
{
public readonly IAuthInterface _authRepository;
public readonly IConfiguration _configuration;
public String _applicationName;
public UserLoggedIn _userLoggedIn;
///
/// No Desc
///
private ILogger _logger;
///
/// No Desc
///
protected ILogger Logger => _logger ?? (_logger = HttpContext.RequestServices.GetService>());
///
/// Nama Module
///
public string baseModuleName;
///
/// Nama Controller
///
public string baseCtrlName;
///
/// IP Address
///
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
{
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 AuditorList([FromBody] DataTableFilter filter)
{
List 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:
Referensi
- https://datatables.net/forums/discussion/55772/how-can-i-get-asp-net-c-to-load-ajax-json-data-returned-from-the-server
- Stack Overflow - get json post data in c sharp
- https://www.youtube.com/watch?v=4AbnoOdZaQI
- https://docs.microsoft.com/en-us/aspnet/core/mvc/views/tag-helpers/built-in/partial-tag-helper?view=aspnetcore-6.0
- https://www.c-sharpcorner.com/article/display-data-in-Asp-Net-using-jquery-datatables-plugin/
- https://www.aspsnippets.com/Articles/Simple-jQuery-DataTables-Tutorial-with-example-in-ASPNet.aspx