HTML code
<div id="StudentTableContainer"></div>
Javascript code
<script type="text/javascript">
$(document).ready(function () {
$('#StudentTableContainer').jtable({
title: 'The Student List',
paging: true, //Enable paging
pageSize: 10, //Set page size (default: 10)
sorting: true, //Enable sorting
defaultSorting: 'Name ASC', //Set default sorting
actions: {
listAction: '/Demo/StudentList',
deleteAction: '/Demo/DeleteStudent',
updateAction: '/Demo/UpdateStudent',
createAction: '/Demo/CreateStudent'
},
fields: {
StudentId: {
key: true,
create: false,
edit: false,
list: false
},
Name: {
title: 'Name',
width: '23%'
},
EmailAddress: {
title: 'Email address',
list: false
},
Password: {
title: 'User Password',
type: 'password',
list: false
},
Gender: {
title: 'Gender',
width: '13%',
options: { 'M': 'Male', 'F': 'Female' }
},
CityId: {
title: 'City',
width: '12%',
options: '/Demo/GetCityOptions'
},
BirthDate: {
title: 'Birth date',
width: '15%',
type: 'date',
displayFormat: 'yy-mm-dd'
},
Education: {
title: 'Education',
list: false,
type: 'radiobutton',
options: { '1': 'Primary school',
'2': 'High school',
'3': 'University' }
},
About: {
title: 'About this person',
type: 'textarea',
list: false
},
IsActive: {
title: 'Status',
width: '12%',
type: 'checkbox',
values: { 'false': 'Passive', 'true': 'Active' },
defaultValue: 'true'
},
RecordDate: {
title: 'Record date',
width: '15%',
type: 'date',
displayFormat: 'dd.mm.yy',
create: false,
edit: false,
sorting: false //This column is not sortable!
}
}
});
//Load student list from server
$('#StudentTableContainer').jtable('load');
});
</script>
Server side code
public class DemoController : Controller
{
//...
[HttpPost]
public JsonResult StudentList(int jtStartIndex = 0, int jtPageSize = 0, string jtSorting = null)
{
try
{
//Get data from database
int studentCount = _repository.StudentRepository.GetStudentCount();
List<Student> students = _repository.StudentRepository.GetStudents(jtStartIndex, jtPageSize, jtSorting);
//Return result to jTable
return Json(new { Result = "OK", Records = students, TotalRecordCount = studentCount });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
[HttpPost]
public JsonResult CreateStudent(Student student)
{
try
{
if (!ModelState.IsValid)
{
return Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." });
}
Student addedStudent = _repository.StudentRepository.AddStudent(student);
return Json(new { Result = "OK", Record = addedStudent });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
[HttpPost]
public JsonResult UpdateStudent(Student student)
{
try
{
if (!ModelState.IsValid)
{
return Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." });
}
_repository.StudentRepository.UpdateStudent(student);
return Json(new { Result = "OK" });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
[HttpPost]
public JsonResult DeleteStudent(int studentId)
{
try
{
_repository.StudentRepository.DeleteStudent(studentId);
return Json(new { Result = "OK" });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
[HttpPost]
public JsonResult GetCityOptions()
{
try
{
var cities = _repository.CityRepository.GetAllCities().Select(c => new { DisplayText = c.CityName, Value = c.CityId });
return Json(new { Result = "OK", Options = cities });
}
catch (Exception ex)
{
return Json(new { Result = "ERROR", Message = ex.Message });
}
}
}
See "Using jTable with ASP.NET MVC" tutorial for detailed usage.
Download all samples from download page.
public partial class PagingAndSorting : System.Web.UI.Page
{
//...
[WebMethod(EnableSession = true)]
public static object StudentList(int jtStartIndex, int jtPageSize, string jtSorting)
{
try
{
//Get data from database
int studentCount = _repository.StudentRepository.GetStudentCount();
List<Student> students = _repository.StudentRepository.GetStudents(jtStartIndex, jtPageSize, jtSorting);
//Return result to jTable
return new { Result = "OK", Records = students, TotalRecordCount = studentCount };
}
catch (Exception ex)
{
return new { Result = "ERROR", Message = ex.Message };
}
}
[WebMethod(EnableSession = true)]
public static object CreateStudent(Student record)
{
try
{
var addedStudent = _repository.StudentRepository.AddStudent(record);
return new { Result = "OK", Record = addedStudent };
}
catch (Exception ex)
{
return new { Result = "ERROR", Message = ex.Message };
}
}
[WebMethod(EnableSession = true)]
public static object UpdateStudent(Student record)
{
try
{
_repository.StudentRepository.UpdateStudent(record);
return new { Result = "OK" };
}
catch (Exception ex)
{
return new { Result = "ERROR", Message = ex.Message };
}
}
[WebMethod(EnableSession = true)]
public static object DeleteStudent(int StudentId)
{
try
{
_repository.StudentRepository.DeleteStudent(StudentId);
return new { Result = "OK" };
}
catch (Exception ex)
{
return new { Result = "ERROR", Message = ex.Message };
}
}
[WebMethod(EnableSession = true)]
public static object GetCityOptions()
{
try
{
var cities = _repository.CityRepository.GetAllCities().Select(c => new { DisplayText = c.CityName, Value = c.CityId });
return new { Result = "OK", Options = cities };
}
catch (Exception ex)
{
return new { Result = "ERROR", Message = ex.Message };
}
}
}
See "Using jTable with ASP.NET Web Forms" tutorial for detailed usage.
Download all samples from download page.
In the server side, we must handle these query string arguments for paging:
- jtStartIndex: Start index of records for current page.
- jtPageSize: Count of maximum expected records.
Also, we must handle these query string argument for sorting:
- jtSorting: A string which represents requested sorting. It is built from sorting field name plus sorting direction. For instance, It can be 'Name ASC', 'BirtDate DESC', 'Age ASC'... etc.
You can use such an SQL statement for paging and sorting:
--MySQL
SELECT * FROM Students ORDER BY Name ASC LIMIT 20,10
--SQL Server
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS Row, * FROM Students)
AS StudentsWithRowNumbers
WHERE Row > 20 AND Row <= 30
In this SQL statement, we are getting 3rd page (if page size is 10). We are skipping 20 records (jtStartIndex) and getting 10 records (jtPageSize). Also we used 'Name ASC' for sorting (jtSorting).
Also, one additional information is expected from server:
- TotalRecordCount: Total count of records (not only this page).
You can use such an SQL statement to get count of all students:
Select Count(*) FROM Students
Advertisement: Professional startup template for ASP.NET MVC & AngularJs by creator of jTable!
Based on metronic theme, includes pre-built pages like login, register, tenant, role, user, permission and setting management. Learn more...