jTable.org

Setting a URL string as listAction

If you defined listAction as a URL string, then, when you use the load method, jTable makes an AJAX POST to this URL address to get list of records. This URL must return a JSON object. Here, there is a sample return value for this URL:

{
 "Result":"OK",
 "Records":[
  {"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
 ]
}

Result property can be "OK" or "ERROR". If it is "OK", Records property must be an array of records. If Result is "ERROR", Message property must explain reason of the error to show to the user.

Spesifically, a date field (that is transferred between jTable and server side code) must be one of the formats below:

• "/Date(...)/"
  Example: /Date(1320259705710)/ (Note: The number inside Date(...) is the milliseconds passed from 01.01.1970)
• "YYYY-MM-DD HH:MM:SS"
  Example: 2012-01-03 21:40:42
• "YYYY-MM-DD"
  Example: 2012-01-03

[HttpPost]
public JsonResult PersonList()
{
    try
    {
        List<Person> persons = _repository.PersonRepository.GetAllPeople();
        return Json(new { Result = "OK", Records = persons });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

[WebMethod(EnableSession = true)]
public static object PersonList()
{
	try
	{
		List<Person> persons = _repository.PersonRepository.GetAllPeople();
		return new { Result = "OK", Records = persons };
	}
	catch (Exception ex)
	{
		return new { Result = "ERROR", Message = ex.Message };
	}
}

//Get records from database
$result = mysql_query("SELECT * FROM people;");
//Add all records to an array
$rows = array();
while($row = mysql_fetch_array($result))
{
	$rows[] = $row;
}
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);

Paging

If paging is enabled, jTable sends two query string parameters to the server on listAction AJAX call:

• jtStartIndex: Start index of records for current page.
• jtPageSize: Count of maximum expected records.

Also, one additional information is expected from server:

• TotalRecordCount: Total count of records (not only this page).

Sorting

If sorting is enabled, jTable sends one additional query string parameter to the server on listAction AJAX call:

• jtSorting: A string represents requested sorting. It is built from sorting field name plus sorting direction. For instance, It can be 'Name ASC', 'BirtDate DESC', 'Age ASC' or can be 'CityId DESC,Name ASC' if multiSotring is enabled..

[HttpPost]
public JsonResult StudentList(int jtStartIndex = 0, int jtPageSize = 0, string jtSorting = null)
{
    try
    {
        var studentCount = _repository.StudentRepository.GetStudentCount();
        List<Student> students = _repository.StudentRepository.GetStudents(jtStartIndex, jtPageSize, jtSorting);
        return Json(new { Result = "OK", Records = students, TotalRecordCount = studentCount });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

[WebMethod(EnableSession = true)]
public static object StudentList(int jtStartIndex, int jtPageSize, string jtSorting)
{
    try
    {
        int studentCount = _repository.StudentRepository.GetStudentCount();
        List<Student> students = _repository.StudentRepository.GetStudents(jtStartIndex, jtPageSize, jtSorting);
        return new { Result = "OK", Records = students, TotalRecordCount = studentCount };
    }
    catch (Exception ex)
    {
        return new { Result = "ERROR", Message = ex.Message };
    }
}

//Get record count
$result = mysql_query("SELECT COUNT(*) AS RecordCount FROM people;");
$row = mysql_fetch_array($result);
$recordCount = $row['RecordCount'];
//Get records from database
$result = mysql_query("SELECT * FROM people ORDER BY " . $_GET["jtSorting"] . " LIMIT " . $_GET["jtStartIndex"] . "," . $_GET["jtPageSize"] . ";");
//Add all records to an array
$rows = array();
while($row = mysql_fetch_array($result))
{
	$rows[] = $row;
}
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['TotalRecordCount'] = $recordCount;
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);

Setting a function as listAction

You can set a function to this action. Your function can return one of the followings:

• The data (result) itself
• A promise (using jQuery.Deferred) to return the data (result)

To return the data itself, you can write such an action definition:

listAction: function (postData, jtParams) {
    return {
        "Result": "OK",
        "Records": [
            { "StudentId": 39, "ContinentalId": 1, "CountryId": 18, "CityId": 55, "Name": "Agatha Garcia", "EmailAddress": "[email protected]", "Password": "123", "Gender": "F", "BirthDate": "\/Date(-1125111600000)\/", "About": "", "Education": 2, "IsActive": true, "RecordDate": "\/Date(1369083600000)\/" },
            { "StudentId": 61, "ContinentalId": 4, "CountryId": 1, "CityId": 1, "Name": "Agatha Lafore", "EmailAddress": "[email protected]", "Password": "123", "Gender": "F", "BirthDate": "\/Date(1017694800000)\/", "About": "", "Education": 3, "IsActive": true, "RecordDate": "\/Date(1393192800000)\/" }],
        "TotalRecordCount": 2
    };
}

While returning the data is usable in demos, it's not the common case for real world scenarios. Probably you want to make an ajax call and get the data from the server. In this case, you can return a promise using jQuery.Deferred object as shown example below:

listAction: function (postData, jtParams) {
    return $.Deferred(function ($dfd) {
        $.ajax({
            url: '/Demo/StudentList?jtStartIndex=' + jtParams.jtStartIndex + '&jtPageSize=' + jtParams.jtPageSize + '&jtSorting=' + jtParams.jtSorting,
            type: 'POST',
            dataType: 'json',
            data: postData,
            success: function (data) {
                $dfd.resolve(data);
            },
            error: function () {
                $dfd.reject();
            }
        });
    });
}

• postData: If you called load method with a post data, this is the data you provided.
• jtParams: Used to pass jTable-specific parameters as an object. This object can has 3 properties:
  • jtStartIndex: If you used paging, this is the start index to get records from server.
  • jtPageSize: If you used paging, this is the page size (max record count) to get records from server.
  • jtSorting: If you used sorting, this is the sorting information as described above (previous section).

In the function, you can change format of returning data before use $dfd.resolve(data). Data must be formatted as expected format as jTable waits for. So, all of the written in 'Setting a URL string as listAction' are also valid here.

Setting a URL string as createAction

If you defined createAction as a URL, then, when you create and save a record, jTable makes an AJAX POST call to this URL address to save the record to the server.

createAction is optional. If it is not defined, user can not add new records to the table. This URL must return a JSON object. Here, there is a sample return value for this URL:

{
 "Result":"OK",
 "Record":{"PersonId":5,"Name":"Dan Brown","Age":55,"RecordDate":"\/Date(1320262185197)\/"}
}

Result property can be "OK" or "ERROR". If it is "OK", Record property must be the newly created record. This is needed by jTable to add record to the table. If Result is "ERROR", Message property must explain reason of the error to show to the user.

[HttpPost]
public JsonResult CreatePerson(Person person)
{
    try
    {
        Person addedPerson = _repository.PersonRepository.AddPerson(person);
        return Json(new { Result = "OK", Record = addedPerson });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

[WebMethod(EnableSession = true)]
public static object CreatePerson(Person record)
{
	try
	{
		Person addedPerson = _repository.PersonRepository.AddPerson(record);
		return new { Result = "OK", Record = addedPerson };
	}
	catch (Exception ex)
	{
		return new { Result = "ERROR", Message = ex.Message };
	}
}

//Insert record into database
$result = mysql_query("INSERT INTO people(Name, Age, RecordDate) VALUES('" . $_POST["Name"] . "', " . $_POST["Age"] . ",now());");
//Get last inserted record (to return to jTable)
$result = mysql_query("SELECT * FROM people WHERE PersonId = LAST_INSERT_ID();");
$row = mysql_fetch_array($result);
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Record'] = $row;
print json_encode($jTableResult);

Setting a function as createAction

You can set a function to this action. Your function can return one of the followings:

• The data (result) itself
• A promise (using jQuery.Deferred) to return the data (result)

Since it's very similar to listAction, it will not be defined in detailed here. You can see the demo.

Setting a URL string as updateAction

When you edit and save a record, jTable makes an AJAX POST to this URL address to update record on the server.

updateAction is optional. If it is not defined, edit image (button) is not shown. This URL must return a JSON object. Here, there is a sample return value for this URL:

{"Result":"OK"}

Result property can be "OK" or "ERROR". If Result is "ERROR", Message property must explain reason of the error to show to the user.

updateAction can also return a Record value (optional). This record is overwriten to updating record in the table. It's not completely replaced, overwritten field by field. So, for instance, if you return a record with just a single field, only this field is overwritten.

{
 "Result":"OK",
 "Record":{"Name":"Dan Brown","Age":55,"LastUpdateDate":"\/Date(1320262185197)\/"}
}

[HttpPost]
public JsonResult UpdatePerson(Person person)
{
    try
    {
        _repository.PersonRepository.UpdatePerson(person);
        return Json(new { Result = "OK" });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

[WebMethod(EnableSession = true)]
public static object UpdatePerson(Person record)
{
	try
	{
		_repository.PersonRepository.UpdatePerson(record);
		return new { Result = "OK" };
	}
	catch (Exception ex)
	{
		return new { Result = "ERROR", Message = ex.Message };
	}
}

//Update record in database
$result = mysql_query("UPDATE people SET Name = '" . $_POST["Name"] . "', Age = " . $_POST["Age"] . " WHERE PersonId = " . $_POST["PersonId"] . ";");
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
print json_encode($jTableResult);

Setting a function as updateAction

You can set a function to this action. Your function can return one of the followings:

• The data (result) itself
• A promise (using jQuery.Deferred) to return the data (result)

Since it's very similar to listAction, it will not be defined in detailed here. You can see the demo.

Setting a URL string as deleteAction

When you delete a record, jTable makes an AJAX POST to this URL address to delete the record on the server.

deleteAction is optional. If it is not defined, delete image (button) is not shown. This URL must return a JSON object. Here, there is a sample return value for this URL:

{"Result":"OK"}

Result property can be "OK" or "ERROR". If Result is "ERROR", Message property must explain reason of the error to show to the user.

[HttpPost]
public JsonResult DeletePerson(int personId)
{
    try
    {
        _repository.PersonRepository.DeletePerson(personId);
        return Json(new { Result = "OK" });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

[WebMethod(EnableSession = true)]
public static object DeletePerson(int PersonId)
{
	try
	{
		_repository.PersonRepository.DeletePerson(PersonId);
		return new { Result = "OK" };
	}
	catch (Exception ex)
	{
		return new { Result = "ERROR", Message = ex.Message };
	}
}

//Delete from database
$result = mysql_query("DELETE FROM people WHERE PersonId = " . $_POST["PersonId"] . ";");
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
print json_encode($jTableResult);

Setting a function as updateAction

You can set a function to this action. Your function can return one of the followings:

• The data (result) itself
• A promise (using jQuery.Deferred) to return the data (result)

Since it's very similar to listAction, it will not be defined in detailed here. You can see the demo.