I'm trying to display record from the database using Asp.Net Core 6 but the my code display all JSON records in the cshtml page directly.
Here is my code please help me how to display records in JQuery datatable. Kindly help me to resolve this
Controller Code
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using CRUDAdo.Net.Models;
namespace CRUDAdo.Net.Controllers
{
[Route("Employee")]
//[ApiController]
public class EmployeeController : Controller
{
private readonly IConfiguration _configuration;
public EmployeeController(IConfiguration configuration)
{
_configuration = configuration;
}
[HttpGet]
[Route("GetEmployeeRecords")]
public JsonResult Index()
{
List<EmployeeDto> employeesdto = new List<EmployeeDto>();
string connection = _configuration.GetConnectionString(("CRUD"));
using (SqlConnection connectionString = new SqlConnection(connection))
{
connectionString.Open();
using (SqlCommand cmd = new SqlCommand("p_GetAllEmployeeRecords", connectionString))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDto empRecord = new EmployeeDto
{
EmployeeCode = reader.GetString(0),
EmployeeName = reader.GetString(1),
Gender = reader.GetString(2),
Designation = reader.GetString(3)
};
employeesdto.Add(empRecord);
}
}
connectionString.Close();
}
//return Json(employeesdto);
return Json(employeesdto);
}
}
}
HTML Code
<div class="row">
<div class="card">
<div class="card-header">
<h3> Employee Records</h3>
</div>
<div class="card-body">
<table id="dataTableRecords" class="display" style="width:100%">
<thead>
<tr>
<th>EmployeeCode</th>
<th>EmployeeName</th>
<th>Gender</th>
<th>Designation</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
JQuery Code
$(document).ready(function () {
debugger
$(function () {
$.ajax({
type: "POST",
url: "/Employee/GetEmployeeRecords",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
});
function OnSuccess(response) {
$("#dataTableRecords").DataTable(
{
bLengthChange: true,
lengthMenu: [[5, 10, -1], [5, 10, "All"]],
bFilter: true,
bSort: true,
bPaginate: true,
data: response,
columns:
[
{ "data": "EmployeeCode", "name": "EmployeeCode" },
{ "data": "EmployeeName", "name": "EmployeeName" },
{ "data": "Gender", "name": "Gender" },
{ "data": "Designation", "name": "Designation" },
]
});
}
});