AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in


jTable overview
A full-featured jTable instance. Try a live demo here.

Contents

  • Introduction to the problem
  • What is jTable?
  • Live demo
  • A sample page with ASP.NET MVC 3 and jTable
    • Using the page
    • Model
    • Controller
      • Getting the list
      • Creating
      • Updating
      • Deleting
      • Getting options
    • View
  • Paging
  • Sorting
  • Selecting
  • Master/child tables
  • ASP.NET Web Forms support
  • Details
    • Methods
    • Actions
    • Field options
    • Other options
    • Events
    • Localization
    • Styling
  • Combining with validation
  • Future works
  • More
  • History
  • References

Introduction to the problem

When we are developing a ‘data manipulation page‘, we almost always do the same thing: A ‘table/grid’ that is used to ‘show records‘ of a table in a database, a ‘create new record‘ page/dialog to add a new record to the database, an ‘edit record‘ page/dialog to edit a record, and finally a way of ‘deleting a record‘ in the database.

Also, using AJAX, we can create more fast and interactive pages. Especially, jQuery and jQueryUI are invaluable libraries to perform manipulation in an HTML page and perform AJAX requests to the server.

Users no longer need to leave the ‘list of records’ page to create/edit or delete a record. Also, the page never refreshes itself to reflect a change in records. When the user deletes a record in the table, we can delete the corresponding row from the table without refreshing the whole page. When the user edits a record and saves it, we can change the corresponding values in the table, and so on… Also, we can do some animations while deleting, creating, or updating records.

All of the subjects I mentioned above are known techniques and we have all implemented them. But the problem is that, we are developing/coding almost the same page for every type of record. Surely, we can copy/paste/modify it! But is it a solution or another mess? All we know is, copy/paste is not desirable for all kinds of programming, it is an evil!

What is jTable

jTable [1] is a jQuery plug-in that addresses the problem mentioned above. It takes a list and properties of fields of a record and does all the job! It has several features:

  • Automatically creates an HTML table and loads records from the server using AJAX.
  • Automatically creates a ‘create new record‘ jQueryUI dialog form. When the user creates a record, it sends data to the server using AJAX and adds the same record to the table in the page.
  • Automatically creates an ‘edit record‘ jQueryUI dialog form. When the user edits a record, it updates the server using AJAX and updates all the cells on the table in the page.
  • Allow the user to ‘delete a record‘ by jQueryUI dialog based confirmation. When the user deletes a record, it deletes the record from the server using AJAX and deletes the record from the table in the page.
  • Supports server side AJAX based paging and sorting.
  • Allows user to select rows from table.
  • Supports unlimited level of master/child tables.
  • Shows animations for create/delete/edit operations on the table.
  • Exposes some events to enable validation with forms.
  • It can be localized easily.
  • Table, forms and other elements are styled in a well defined and commented CSS file.
  • It comes with four pre-defined color themes: blue, red, green and purple, for now.
  • It is browser/platform independent and works on all common browsers.
  • It is not dependent on any server-side technology such as ASP.NET MVC, and can be used with others.
  • It has direct support for ASP.NET Web Forms Page Methods.

Live demo

You can try out a demonstration here: http://www.jtable.org.

A sample page with ASP.NET MVC 3 and jTable

Here I will show how to develop a data manipulation page with ASP.NET MVC 3 and jTable. This sample project is included in the download file.

Assume that we are manipulating a Person list that has a lot of information: name, city, email, password, gender, birth date, an ‘about’ text, and education.

Using the page

First, I will show the capabilities of jTable. Here is the list of people:

The Person List

This table is automatically created by jTable. (Don’t worry about the style of the table. The HTML code of the table is not styled and is a clean HTML table. You can edit the CSS files easily.) Also, it shows only the desired fields. Edit/Delete images (buttons) are completely optional, and they are also automatically added to each row. Title is also optional and the add new record link is changeable by the user with another element in the page. While the records are being loaded, a ‘loading…’ animation is shown.

When the user clicks the add new record link, a jQueryUI dialog based form is opened:

Creating new record

This form is also completely automatically created based on the fields of the record! When you fill the form and save, jTable serializes the form and performs an AJAX call to the server. If the server responds ‘OK‘, it adds the record to the table with an animation:

Added new record animation

In the animation above, the row is highlighted with green. It turns back to normal color after a few seconds. This animation is just a CSS class transition and can be changed in the CSS file easily. So you can change the animation to whatever you want. If the server returns error while adding the record, jTable automatically shows an error dialog message and does not add the record to the table.

If you click the edit image (button) in a row, jTable automatically creates an editing jQuery dialog form:

Editing a record

jTable automatically creates and fills the form with the selected record’s values. When the user saves the form, just like creating a new record, the record is saved to the server. If the AJAX call is a success, the record values are updated in the table and an ‘edited’ animation is shown:

Editing Record Animation

As I mentioned above when creating the record, same animation mechanism does exist while updating an edited record in the table. An edited row turns to normal style in a few seconds.

When the user clicks the delete image (button), jTable shows a confirmation dialog:

Delete confirmation dialog

If the user clicks the delete button, the record is deleted from the server using an AJAX call. If the operation succeeds, it is also removed from the table automatically with a deleting animation:

Deleting animation

The deleting row is highlighted for a second and removed from the table.

Now we will see how to implement the page above in ASP.NET MVC 3.

Model

We have two classes here: Person (represents a record in the People database table) and City (represents a record in the Cities database table). A person lives in a city. So the Person class has a CityId that is the ID of a city row in the Cities table.

The Person class is shown below:

Collapse | Copy Code
public class Person
{
    public int PersonId { get; set; }

    // Id of a City in Cities
    [Required]
    public int CityId { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string EmailAddress { get; set; }

    [Required]
    public string Password { get; set; }

    // "M" for mail, "F" for female.
    [Required]
    public string Gender { get; set; }

    [Required]
    public DateTime BirthDate { get; set; }

    public string About { get; set; }

    // 0: Unselected, 1: Primary school,
    // 2: High school 3: University
    [Required]
    public int Education { get; set; }

    //true: Active, false: Passive
    [Required]
    public bool IsActive { get; set; }

    [Required]
    public DateTime RecordDate { get; set; }

    public Person()
    {
        RecordDate = DateTime.Now;
        Password = "123";
        About = "";
    }
}

The [Required] attributes are not related to jTable as you probably know. They are used by ASP.NET MVC and Entity framework for validation.

City is a simple class. It is designed to show the combobox feature of jTable (as you’ve seen above).

Collapse | Copy Code
public class City
{
    public int CityId { get; set; }

    [Required]
    public string CityName { get; set; }
}

Controller

jTable always uses the POST method while making AJAX calls to the server and expects a JSON object. URLs (Controller/Action names in ASP.NET MVC) can be arbitrary and they are set while creating a jTable instance (we will see this soon).

Getting the list

You must supply an action to jTable to get a list of records:

Collapse | Copy Code
[HttpPost]
public JsonResult PersonList()
{
    try
    {
        List<Person> persons = _personRepository.GetAllPersons();
        return Json(new { Result = "OK", Records = persons });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

All methods must return a JSON object. Result property must be “OK” if operation is successful. If an error occurs, Message property will contain an error message to show to the user. If Result is “OK“, the Records property will contain an array of records to show in the table.

You could pass some parameters to the action that can be used to get records based on some filters. Also, you can paginate or sort the table. We will see this later.

Creating

Creating a record is optional (we will see soon). If you allow user to create a record, you must supply an action to jTable to create a new record:

Collapse | Copy Code
[HttpPost]
public JsonResult CreatePerson(Person person)
{
    try
    {
        if (!ModelState.IsValid)
        {
            return Json(new { Result = "ERROR", 
              Message = "Form is not valid! " + 
              "Please correct it and try again." });
        }

        var addedPerson = _personRepository.AddPerson(person);
        return Json(new { Result = "OK", Record = addedPerson });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

CreatePerson method must return the newly created object as the Record property. This is needed since newly inserted record will has a key (PersonId in this sample) and automatically generated values (such as RecordDate here).

Updating

Editing a record is optional (we will see soon). If you allow user to edit a record, you must supply an action to jTable to update a record:

Collapse | Copy Code
[HttpPost]
public JsonResult UpdatePerson(Person person)
{
    try
    {
        if (!ModelState.IsValid)
        {
            return Json(new { Result = "ERROR", 
              Message = "Form is not valid! " + 
                "Please correct it and try again." });
        }

        _personRepository.UpdatePerson(person);
        return Json(new { Result = "OK" });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

Deleting

Deleting a record is optional (we will see soon). If you allow user to delete a record, You must supply an action to jTable to delete a record:

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

Getting options

jTable can automatically download and fill comboboxes from a URL. For instance, the City combobox in the Person create/edit form above uses this feature. In such cases, you must supply an action to get the option list:

Collapse | Copy Code
[HttpPost]
public JsonResult GetCityOptions()
{
    try
    {
        var cities = _personRepository.GetCities().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 });
    }
}

The returning JSON object must have the Options property. It is an array of objects and every object has two properties: DisplayText and Value.

View

C# codes above was not directly related to jTable and specific to the ASP.NET MVC implementation. View side is completely about jTable. When you download jTable, you will have a folder structure as shown below:

jTable folder structure

jquery.jtable.js file is the main and only JavaScript file which you must include in your project. Other files (CSS and images files) are used for styling the table and forms. We will come back to styling later.

First, we add the jtable_blue.css file (my favourite style ­čÖé to the HEAD section of the HTML document (Razor view in ASP.NET MVC3):

Collapse | Copy Code
<link href="http://www.codeproject.com/Scripts/jtable/themes/standard/blue/jtable_blue.css" 
      rel="stylesheet" type="text/css" />

You can add red or purple style files instead of blue, or you can write your own style file. Then we must add the jquery.jtable.js script file to the page:

Collapse | Copy Code
<script type="text/javascript" src="http://www.codeproject.com/Scripts/jtable/jquery.jtable.js">
</script>

Note that jTable is dependent on jQuery and jQueryUI (included UI effects). So, you must add those scripts to your page before jTable. If you don’t have these libraries, go to http://jqueryui.com/download to download jQueryUI (it includes jQuery).

Finally, we can create the jTable instance like this:

Collapse | Copy Code
<div id="PersonTable" style="width: 580px; margin: auto;"></div>

<script type="text/javascript">

    $(document).ready(function () {

        //Prepare jtable plugin
        $('#PersonTable').jtable({
            title: 'The Person List',
            actions: {
                listAction: '/Home/PersonList',
                deleteAction: '/Home/DeletePerson',
                updateAction: '/Home/UpdatePerson',
                createAction: '/Home/CreatePerson'
            },
            fields: {
                PersonId: {
                    key: true,
                    create: false,
                    edit: false,
                    list: false
                },
                Name: {
                    title: 'Name',
                    width: '15%'
                },
                EmailAddress: {
                    title: 'Emal address',
                    list: false
                },
                Password: {
                    title: 'User Password',
                    type: 'password',
                    list: false
                },
                Gender: {
                    title: 'Gender',
                    width: '12%',
                    options: { 'M': 'Male', 'F': 'Female' }
                },
                CityId: {
                    title: 'Living city',
                    width: '15%',
                    options: '/Home/GetCityOptions'
                },
                BirthDate: {
                    title: 'Birth date',
                    width: '18%',
                    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: '10%',
                    type: 'checkbox',
                    values: { 'false': 'Passive', 'true': 'Active' },
                    defaultValue: 'true'
                },
                RecordDate: {
                    title: 'Record date',
                    width: '18%',
                    type: 'date',
                    displayFormat: 'dd.mm.yy',
                    create: false,
                    edit: false
                }
            }
        });

        //Load person list from server
        $('#PersonTable').jtable('load');
    });

</script>

Yes, it’s a long definition but that’s all! jTable does not need anything else to create tables, forms, and animations. I’ll explain all options in the Details section but I want to explain some basics now.

As you can see, jTable just needs a div container as the only HTML tag. It gets options:

  • title: Title of the table.
  • actions: URLs of actions that are used to create/delete/update/list records.
  • fields: All fields of the record. A field entry has properties that define the field.

Finally, the load method of jTable is used to get records from the server (we will see this in detail). You can always call this method to load/refresh table data from the server.

Paging

jTable allows you server side paging with AJAX. See a demo here. It looks like the sample below:

Paging with jTable

To enable paging, paging option must set to true. You can also set pageSize option (default value is 10).

Collapse | Copy Code
$('#PersonTable').jtable({
    //...
    paging: true, //Set paging enabled
    actions: {
        //...
    },
    fields: {
        //...
    }
});

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).

An ASP.NET MVC action that is used for paging is shown below:

Collapse | Copy Code
[HttpPost]
public JsonResult PersonList(int jtStartIndex, int jtPageSize)
{
    try
    {
        int personCount = _personRepository.GetPersonCount();
        List<Person> persons = _personRepository.GetPersons(jtStartIndex, jtPageSize);
        return Json(new { Result = "OK", Records = persons, TotalRecordCount = personCount });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

Sorting

jTable allows you server side sorting with AJAX. See a demo here. It looks like the sample below:

jTable sorting

To enable sorting, sorting option must set to true. You can also set defaultSorting option. It can be a field name of a column of the table. For instance, if you want table sorted by Name by default, defaultSorting can be ‘Name ASC‘ or ‘Name DESC‘.

Collapse | Copy Code
$('#PersonTable').jtable({
    //...
 sorting: true, //Enable sorting
 defaultSorting: 'Name ASC', //Sort by Name by default
    actions: {
        //...
    },
    fields: {
        //...
    }
});

If sorting is enabled, jTable sends a 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‘… etc.

An ASP.NET MVC action that is used for sorting is shown below:

Collapse | Copy Code
[HttpPost]
public JsonResult PersonList(int jtStartIndex = 0, int jtPageSize = 0, string jtSorting = null)
{
    try
    {
        int personCount = _personRepository.GetPersonCount();
        List<person> persons = _personRepository.GetPersons(jtStartIndex, jtPageSize, jtSorting);
        return Json(new { Result = "OK", Records = persons, TotalRecordCount = personCount });
    }
    catch (Exception ex)
    {
        return Json(new { Result = "ERROR", Message = ex.Message });
    }
}

Note that while sorting can be used with paging (as in this sample), it is completely independed from paging.

Selecting

jTable allows you client side selecting rows. See a demo here. It looks like the sample below:

jTable selecting

To enable selecting, selecting option must set to true. You can set multiselect option to true to allow user to select multiple rows at once. You can set selectingCheckboxes option to true to show checkboxes as the sample above. Finally, you can set selectOnRowClick to false to prevent row selecting on clicking anywhere on the row (it’s true as default).

To get list of selected rows, you can call selectedRows method of jTable anytime (see sample usage). Also, you can get notified when selection changed by selectionChanged event.

Collapse | Copy Code
//Prepare jtable plugin
$('#PersonTable').jtable({
    //...
 selecting: true, //Enable selecting
 multiselect: true, //Allow multiple selecting
 selectingCheckboxes: true, //Show checkboxes on first column
 //selectOnRowClick: false, //Enable this to only select using checkboxes
    actions: {
        //...
    },
    fields: {
        //...
    },
    //Register to selectionChanged event
    selectionChanged: function () {
        //Get all selected rows
        var $selectedRows = $('#PersonTable').jtable('selectedRows');

        $('#SelectedRowList').empty();
        if ($selectedRows.length > 0) {
            //Show selected rows
            $selectedRows.each(function () {
                var record = $(this).data('record');
                $('#SelectedRowList').append(
                    'PersonId: ' + record.PersonId +
                    'Name:' + record.Name
                    );
            });
        } else {
            //No rows selected
            $('#SelectedRowList').append('No row selected! Select rows to see here...');
        }
    }
});

In the sample above, we are registering to selectionChanged event. In the event handler, we are getting selected rows by selectedRows method. It returns a jQuery selection, so we can call any jQuery method on it. We can get the record by record data property. Then we can get fields of record as record.Name, record.PersonId… etc.

Master/Child tables

jTable supports unlimited level of child tables for a table. Child tables can also have their children and so on… A child table is releated in a row in master table. Look at the screen below:

Master Child table on jTable

Click here to see a live demo. When you click phone icon at left of a row, a new table slides down below the clicked row and you can manipulate phones of the selected person. You can do everything just as a regular jTable. When you click close button, child table slides up and closed.

To implement a child table, first we must understand custom (computed) column support of jTable. Green phone icon on the figure above is created in a custom column. To create a custom column, we use display option of jtable field definition as like below:

Collapse | Copy Code
//...
Phones: {
    title: '',
    width: '3%',
    sorting: false,
    edit: false,
    create: false,
    display: function (personData) {
        var $img = $('<img src="http://www.codeproject.com/Content/images/Misc/phone.png" title="Edit phone numbers" />');
        return $img;
    }
}
//...

Phones is a field definition as Name or Gender columns. But Person record has not a field named Phones. So, we define a function that will create value of this field to show on the table. display function is used for that. It is called by jTable for each row. It must return a text, HTML code or jQuery object. In the sample above, I created an image (green phone icon) as a jQuery object and returned it. Then jTable showed this image in the row. personData argument (you can change name of the argument of course) has record property that can be used to get record values for current row. So, if you want to use Name of the person, you can get it using personData.record.Name.

So far so good. But, how to open child table when user clicks this image. jTable defines two methods: openChildTable and closeChildTable to control child tables. So, web can open a child table when user clicks the phone icon (in the display method above):

Collapse | Copy Code
$img.click(function() {
    $('#PersonTable').jtable('openChildTable',
        $img.closest('tr'),
        {
            title: personData.record.Name + ' - Phone numbers',
            actions: {
                listAction: '/PagingPerson/PhoneList?PersonId=' +
                            personData.record.PersonId,
                deleteAction: '/PagingPerson/DeletePhone',
                updateAction: '/PagingPerson/UpdatePhone',
                createAction: '/PagingPerson/CreatePhone'
            },
            fields: {
                StudentId: {
                    type: 'hidden',
                    defaultValue: studentData.record.StudentId
                },
                PhoneId: {
                    key: true,
                    create: false,
                    edit: false,
                    list: false
                },
                PhoneType: {
                    title: 'Phone type',
                    width: '30%',
                    options: { '1': 'Home phone', 
                               '2': 'Office phone', 
                               '3': 'Cell phone'}
                },
                Number: {
                    title: 'Phone Number',
                    width: '30%'
                },
                RecordDate: {
                    title: 'Record date',
                    width: '20%',
                    type: 'date',
                    displayFormat: 'dd.mm.yy',
                    create: false,
                    edit: false
                }
            }
        }, function(data) { //opened handler
            data.childTable.jtable('load');
        });
});

openChildTable has three parameters. First one is used to indicate the row which is used as master row of child table. Here, I got container tr element of the image, so it gives the current row. Second parameter is a regular jTable initialization options. You can pass any option that can be passed to any jTable instance. So, you can define a custom column and open a second level child. Third and the last parameter is a callback method that is called by jTable after child table successfully created and opened. Here, I loaded records after child table is opened.

Look at the listAction. It’s something like that: ‘/PagingPerson/PhoneList?PersonId=’ + personData.record.PersonId. Thus, we are getting phone numbers those are related to current person (personData comes from display method, see codes above). Also, we need PersonId in the server side while creating a new phone number for a person. It’s done with a hidden field (See StudentId definition). Given default value is used for new records.

When you create a child table, jTable automatically closes it when user clicks close icon of the table. You can change/override these functionalities. See details to learn in deep.

ASP.NET Web Forms support

jTable has direct support for ASP.NET Web Forms Page Methods since jTable v1.4.1. While jTable is already platform independed, I have built an extension to jTable to support Page Methods in most proper way. For instance, see the code below. This page method is used to get student list as paged and sorted. It’s pretty much similar to it’s MVC version.

Collapse | Copy Code
[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 };
    }
}

For more information on ASP.NET Web Forms support, see the tutorial in jTable.org. Also, the download file in this article includes samples in ASP.NET Web Forms.

Details

Now I will explain detailed usage of jTable.

Methods

jTable defines fallowing methods:

load(postData, completeCallback)

Loads records from the server. All parameters are optional. If you want to pass some parameters to the server, you can pass them in the postData argument while calling the load method, like this:

Collapse | Copy Code
$('#PersonTable').jtable('load', { CityId: 2, Name: 'Halil' });

You can get people who are living in city 2 and whose name is Halil like shown above. Surely, you must handle these parameters in the server side. Also, you can pass a callback method as completeCallback, that is called when loading of data is successfully completed.

reload(completeCallback)

Re-loads records from server with last postData. This method can be used to refresh table data from server since it does not change current page, sorting and uses last postData (passed on last load call). Also, you can pass a callback method as completeCallback, that is called when loading of data is successfully completed.

selectedRows()

Gets all selected rows as jQuery selection. See the sample above.

deleteRows(rows)

Deletes given rows from server and table. rows parameter must be a jQuery selection. This method can be combined with selectedRows method. Thus, you can get selected rows and pass to deleteRows method to delete them.

openChildTable(row, tableOptions, opened)

This method is used to create and open a child table for a data row (See Master/Child section above). rowargument is a data row on the table, tableOptions are standard jTable options that is used to initialize child table. opened is a callback that is called by jTable when the child table is shown (After opening animation is finished).

closeChildTable(row, closed)

This method is used to close an open child table for a table row. row is a jQuery row object (tr element) on the table. closed is a callback function that is called when child table is closed.

openChildRow(row)

This method is used to open child row for a table row. Child rows generally used to show child tables. If you want to show child tables, you don’t need to use this method, use openChildTable method instead. If you want to open a custom child row, use this method. It returns the opened child row. Thus, you can fill it with a custom content. A child row is related to a specific data row in the table (which is passed as row agrument). If the data row is removed from table, it’s child is also automatically removed.

closeChildRow(row)

This method is used to close an open child row for a table row. See openChildRow method. (This method is internally used by jTable to close child tables.)

getChildRow(row)

This method is used to get child row (tr element) for a table row. Thus, you can add content to the child row. See openChildRow method.

isChildRowOpen(row)

This method returns true if child row is open for specified row. See openChildRow method.

addRecord(options), removeRecord(options), updateRecord(options)

These methods are used to manipulate table data programmatically. Since they have a bit detailed usage, please see reference documentation for these methods.

Actions

There are four main actions that are used by jTable to perform AJAX requests to the server:

  • listAction: A URL address to get the list of records.
  • createAction: A URL address to submit a create new record form.
  • updateAction: A URL address to submit an edit record form.
  • deleteAction: A URL address to delete a record.

If you don’t want to use an action, just don’t define it. For instance, if you don’t want to allow user to delete a row, don’t supply a deleteAction URL. Thus, jTable will not put a delete button for the records.

Field options

Fields are the core of jTable. They define the shape and behavior of your page. A field can have these properties:

    • title: A string as header in the table and forms for this field.
    • width: Width of the column for this field in the table. Can be any CSS unit (15%, 120px.., so on).
    • key: A boolean value that indicates whether this field is the key field of the record. Every record must has one and only one key field that is used as key on update and delete operations.
    • list: A boolean value that indicates whether this field is shown in the table.
    • create: A boolean value that indicates whether this field is shown in the create record form.
    • edit: A boolean value that indicates whether this field is shown in the edit record form.
    • options: If this field’s value will be selected in an option list (combobox as default, can be radio button list), you must supply a source. An option source can be one of these values:
      • URL string: A URL to download the option list for this field (as we’ve seen above).
      • object: Property names are values, property values are display texts (see sample jTable instance in the View section above).
      • array: An array of values. If values of options are same as display texts, you can use this type.
    • type: Type of the field. If field is a string or number, no need to set the type. Other types are:
      • password: Show a password textbox for this field on edit/create forms.
      • textarea: Shows a textarea for this field on edit/create forms.
      • date: A date (not including time). You can also set the displayFormat option.
      • radiobutton: If field is a value from option, it can be a combobox (default) or radio button list. If it is a radio button list, set type as radiobutton. You must supply options as mentioned above.
      • checkbox: To show a checkbox while editing this field. You must supply values option for checked/unchecked states (see the sample page in the View section). By default, a checkbox’s text changes when user changes the state of the checkbox. If you want to fix the text, you can set the formText option for this field. By default, when the user clicks the checkbox’s text, the state of the checkbox changes. If you do not want that, you can set setOnTextClick to false.
      • hidden: A hidden field can be used hidden fields in edit and create forms. It is not shown on the table. You may want to use defaultValue option with hidden types, thus given default value is automatically set to the hidden field on creating form. See master/child demo for sample usage.
    • displayFormat: If the type is date, you can set its format. See jQueryUI datepicker formats [2].
    • defaultValue: You can set a default value for a field. It must be a valid value. For instance, if the field is an option list, it must be one of these options.
    • listClass: A string value that can be set as the class of a cell (td tag) of this field in the table. This way you can style the fields in the table.
    • inputClass: A string value that can be set as the class of an input item for this field in create/edit forms. So you can style input elements in the forms for this field. This can be useful when working with validation plug-ins (we will see soon).
    • sorting: Indicates that whether this column will be used to sort the table (If table is sortable).
    • display: This option is a function that allows you to define a fully custom column for table. jTable directly shows return value of this function on the table. See the sample below:
      Collapse | Copy Code
      TestColumn: {
          title: 'Test',
          display: function (data) {
              return 'test';
          }
      }

      This sample Test column returns a bold ‘test’ string for all rows. You can return any text, html code or jQuery object that will be shown on the table. This method is called for each row. You can get record of the row using data.record. So, if your record has Name property, you can use data.record.Name property to get the Name.
      display function can be used for many purposes such as creating calculated columns, opening child tables for a row… etc. See demos for detailed usage.

    • input: This option is a function that allows you to define a fully custom input element for create and edit forms. jTable directly shows return value of this function on the form.
      Collapse | Copy Code
      Name: {
          title: 'Name',
          width: '20%',
          input: function (data) {
              if (data.record) {
                  return '<input type="text" style="width:200px" value="' + data.record.Name + '" />';
              } else {
                  return '<input type="text" style="width:200px" value="enter your name here" />';
              }
          }
      }
    • Here, we return a simple text input for Name field of the record. If this input is being created for edit form, you can get editing record using data.record property. If this input is being created for create form, it is undefined. You can also use data.value to get current value of the field. This is default value (if defined) of field for create form, current value of field for edit form.

 

    While jTable automatically created appropriate input element for each field, you can use input option to create custom input elements.

Other options

jTable defines some other options that determine the general shape and behavior of the table and forms:

  • title: A string that is shown on top of the table. This is optional, if you don’t supply the title option, no title is displayed.
  • addRecordButton: A jQuery object that can be used instead of the ‘add new record’ link. Thus you can set any element on the page to open the ‘add new record’ dialog. If you set this option, the bottom panel of jTable is not shown (since it will be empty).
  • deleteConfirmation: This option can be a boolean value or a function. If it is a boolean value, that indicates whether a confirmation dialog is shown when user clicks delete button/icon for a record (default value is true). If this option is a function, It can fully control the delete confirmation process. It must be a function like that: deleteConfirmation: function(data) { … }
    data argument has some properties to control confirmation process:

    • row: A jQuery selection for deleting row element.
    • record: Actual deleting record. For example, you can get Name property of record asdata.record.Name.
    • cancel: You can set data.cancel to true to cancel delete process (default value is false).
    • cancelMessage: If you cancelled delete process, you can show a message to user that explains cancellation reason. If you don’t want to show any message, just don’t set it.
    • deleteConfirm: A boolean value indicates whether to show a delete confirmation message or not (default value is true).
    • deleteConfirmMessage: If confirmation enabled, you can set a custom confirmation message.

    For example, if you want to show some additional information to user on delete confirmation, you can write a function like that:

    Collapse | Copy Code
    deleteConfirmation: function(data) {
        data.deleteConfirmMessage = 'Are you sure to delete person ' + data.record.Name + '?';
    }
  • defaultDateFormat: Default format of a date field. See jQueryUI datepicker formats [2]. Default: ‘yy-mm-dd’.
  • dialogShowEffect: jQueryUI effect to be used while opening a jQueryUI dialog. Some options are ‘blind’, ‘bounce’, ‘clip’, ‘drop’, ‘explode’, ‘fold’, ‘highlight’, ‘puff’, ‘pulsate’, ‘scale’, ‘shake’, ‘size’, ‘slide’… etc. See jQueryUI documentation for all options. Default value is ‘fade’.
  • dialogHideEffect: jQueryUI effect to be used while opening a jQueryUI dialog. Some options are ‘blind’, ‘bounce’, ‘clip’, ‘drop’, ‘explode’, ‘fold’, ‘highlight’, ‘puff’, ‘pulsate’, ‘scale’, ‘shake’, ‘size’, ‘slide’… etc. See jQueryUI documentation for all options. Default value is ‘fade’.
  • messages: All messages that are shown by jTable. You can localize these messages (see the Localization section).
  • paging: Indicates that whether jTable uses paging or not.
  • pageSize: If paging enabled, this value indicates number of rows in a page.
  • sorting: Indicates that whether jTable will sort the table or not.
  • defaultSorting: Default sorting of table. It can be a field name of a column of the table. For instance, if you want table sorted by Name by default, defaultSorting can be ‘Name ASC‘ or ‘Name DESC‘.
  • selecting: Indicates that whether jTable allows user to select rows on the table.
  • multiselect: Indicates that whether jTable allows user to select multiple rows at once.
  • selectingCheckboxes: Indicates that whether jTable shows checkbox column for selecting.
  • selectOnRowClick: Indicates that whether jTable allows user to select a row by clicking anywhere on the row. This can be set as false to allow user selecting a row only by clicking to the checkbox (see selectingCheckboxes option).
  • showCloseButton: Indicates that whether jTable will show a close button/icon for the table. When user clicks the close button, closeRequested event is raised. This option is used internally by jTable to close child tables. Default value is false.
  • openChildAsAccordion: If this options is set to true, jTable automatically closes other open child tables when a child table is opened (accordion style). Default value is false.
  • animationsEnabled: Indicates that whether jTable shows animations when user creates, updates or deletes a row.

Events

jTable defines some events when certain conditions occur.

  • formCreated(event, data): This event is raised when an edit or create form is created for a record. You can reach the form using the data.form argument. You can get the type of the form using the data.formType argument. It can be ‘edit’ or ‘create’. If formType is edit, you can reach the editing record using the data.record argument (for example, you can get the name of the editing person as data.record.Name).
  • formSubmitting(event, data): This event is raised when save/submit button is clicked for a form. You can reach the form using the data.form argument. You can get the type of the form using the data.formType argument. It can be ‘edit’ or ‘create’. You can validate the form on this event. If you return false from this event callback, the submit operation is cancelled.
  • formClosed(event, data): This event is raised when an edit/create form dialog is closed. You can reach the form using the data.form argument. You can get the type of the form using the data.formType argument. It can be ‘edit’ or ‘create’.
  • loadingRecords(event, data): This event is raised just before AJAX request to load records from server. It has no arguments.
  • recordAdded(event, data): This event is raised when user successfully creates and saves a new record. You can get the added record using data.record arguement. You can get the response JSON object returned from server as data.serverResponse. If jTable is not running on paging mode, you can also get the added table row by data.row argument.
  • recordDeleted(event, data): This event is raised when user successfully deletes a record. You can get the deleted record using data.record argument. You can get the deleted table row by data.row argument. You can get the response JSON object returned from server as data.serverResponse.
  • recordsLoaded(event, data): This event is raised when jTable loads records from server and refreshes the table (If paging enabled, this event is also raised when user changes the current page). You can get all records loaded from server by data.records argument. You can get the response JSON object returned from server as data.serverResponse.
  • recordUpdated(event, data): This event is raised when user successfully updates a record. You can get the updated record using data.record arguement. You can get the updated table row by data.row argument. You can get the response JSON object returned from server as data.serverResponse.
  • rowsRemoved(event, data): This event is raised when either user deletes row/rows (actual record deletion from server) or during re-loading records from server (all rows cleared but not deleted from server). You can get all removed rows by data.rows as jQuery selection. You can get remove reason by data.reason (can be ‘deleted’ or ‘reloading’).
  • selectionChanged(event, data): This event is raised when selected rows on the table changes in anyway. It may change when user selects/deselects a row, a selected row is deleted, page changed while some rows are selected… etc. You can get selected rows by selectedRows method.
  • closeRequested(event, data): This event is raised when user clicks close button/icon of the table. Close button is shown if showCloseButton is set to true. This event has no argument.
  • rowInserted(event, data): This event is raised when a row is inserted to the shown table. A new row can be inserted either when user added a new record or records loaded from server. When records loaded from server, rowInserted event is called for each row. So, you can modify row or do whatever you need. You can get the row using data.row, you can get related record with data.record. Finally, if this is a new record (that’s added by user) data.isNewRow is set to true by jTable.
  • rowUpdated(event, data): This event is raised when a row updated. A row is updated when user updates a record. You can get the updated row using data.row, you can get related record with data.record. This event is raised after recordUpdated.

NOTE: formCreated, formSubmitting, and formClosed events are suitable to inject validation logic. See the ‘Combining with validation’ section in this article.

Localization

jTable can be easily localized. You can use the messages option to localize a jTable instance on initialization.

Default value of the messages option is shown below:

Collapse | Copy Code
messages: {
    serverCommunicationError: 'An error occured while communicating to the server.',
    loadingMessage: 'Loading records...',
    noDataAvailable: 'No data available!',
    addNewRecord: '+ Add new record',
    editRecord: 'Edit Record',
    areYouSure: 'Are you sure?',
    deleteConfirmation: 'This record will be deleted. Are you sure?',
    save: 'Save',
    saving: 'Saving',
    cancel: 'Cancel',
    deleteText: 'Delete',
    deleting: 'Deleting',
    error: 'Error',
    close: 'Close',
    cannotLoadOptionsFor: 'Can not load options for field {0}',
    pagingInfo: 'Showing {0} to {1} of {2} records',
    canNotDeletedRecords: 'Can not deleted {0} of {1} records!',
    deleteProggress: 'Deleted {0} of {1} records, processing...'
}

Here, a sample localization for Turkish language:

Collapse | Copy Code
<div id="PersonTable" style="width: 580px; margin: auto;">
</div>
<script type="text/javascript">

    $(document).ready(function () {

        //Localization messages for Turkish
        var turkishMessages = {
            serverCommunicationError: 
              'Sunucu ile ileti┼čim kurulurken bir hata olu┼čtu.',
            loadingMessage: 'Kay─▒tlar y├╝kleniyor...',
            noDataAvailable: 'Hi├ž kay─▒t bulunmamaktad─▒r!',
            addNewRecord: '+ Yeni kay─▒t ekle',
            editRecord: 'Kay─▒t d├╝zenle',
            areYouSure: 'Emin misiniz?',
            deleteConfirmation: 'Bu kay─▒t silinecektir. Emin misiniz?',
            save: 'Kaydet',
            saving: 'Kaydediyor',
            cancel: '─░ptal',
            deleteText: 'Sil',
            deleting: 'Siliyor',
            error: 'Hata',
            close: 'Kapat',
            cannotLoadOptionsFor: '{0} alan─▒ i├žin se├ženekler y├╝klenemedi!',
            pagingInfo: '{2} Kay─▒ttan {0} ile {1} aras─▒ g├Âsteriliyor',
            canNotDeletedRecords: '{1} kay─▒ttan {0} adedi silinemedi!',
            deleteProggress: '{1} kay─▒ttan {0} adedi silindi, devam ediliyor...'
        };

        //Prepare jtable plugin
        $('#PersonTable').jtable({
            messages: turkishMessages, //Set messages as Turkish
            title: '...',
            actions: {
                ...
            },
            fields: {
                ...
            }
        });

        //Load person list from server
        $('#PersonTable').jtable('load');
    });

</script>

Result of the localization:

Turkish Localization

Styling

All styles of jTable are defined in the related CSS files. There are three pre-defined styles other than blue:

jTable Styles

You can set any style by including its CSS file in your HTML document:

Collapse | Copy Code
<!-- BLUE style -->
<link href="http://www.codeproject.com/Scripts/jtable/themes/standard/blue/jtable_blue.css" 
           rel="stylesheet" type="text/css" />

<!-- RED style -->
<link href="http://www.codeproject.com/Scripts/jtable/themes/standard/red/jtable_red.css" 
           rel="stylesheet" type="text/css" />

<!-- GREEN style -->
<link href="http://www.codeproject.com/Scripts/jtable/themes/standard/green/jtable_green.css" 
           rel="stylesheet" type="text/css" />

<!-- PURPLE style -->
<link href="http://www.codeproject.com/Scripts/jtable/themes/standard/purple/jtable_purple.css" 
           rel="stylesheet" type="text/css" />

If you want to use your own styles, you can start with the jtable_empty.css file. It defines all CSS selectors for jTable as empty. You can fill in the CSS selectors.

If you did like the jTable standard theme (defined in jtable_standard_base.css) but not happy with the colors, you can copy jtable_blue.css and change the colors.

Combining with validation

Validation is a common task while working with forms. jTable exposes some events (described above, in the Events section) to inject validation logic to jTable auto-created forms. You can use your own validation logic or a jQuery validation plug-in.

Validation engine [3] is a powerful validation plug-in for jQuery. I definitely recommend it. Here I will show how to inject validation engine to a jTable instance. First, see the validation engine in action:

Validatin Engine Integration

As you can see, when I try to save the form, the validation engine shows some error messages and prevent the submission of the form.

To be able to use the validation engine, first we must add style and JavaScript files to our HTML page:

Collapse | Copy Code
<!-- Validation engine style file -->
<link href="@Url.Content("~/Scripts/validationEngine/validationEngine.jquery.css")" 
      rel="stylesheet" type="text/css" />

<!-- Validation engine script file and english localization -->
<script type="text/javascript" 
  src="@Url.Content("~/Scripts/validationEngine/jquery.validationEngine.js")">
</script>
<script type="text/javascript" 
  src="@Url.Content("~/Scripts/validationEngine/jquery.validationEngine-en.js")">
</script>

Then we register to the events of jTable to inject validation engine to jTable forms:

Collapse | Copy Code
<script type="text/javascript">

    $(document).ready(function () {

        //Prepare jtable plugin
        $('#PersonTable').jtable({
            title: '...',
            actions: {
                ...
            },
            fields: {
                ...
            },
            formCreated: function (event, data) {
 data.form.find('input[name="Name"]').addClass(
 'validate[required]');
 data.form.find('input[name="EmailAddress"]').addClass(
 'validate[required,custom[email]]');
 data.form.find('input[name="Password"]').addClass(
 'validate[required]');
 data.form.find('input[name="BirthDate"]').addClass(
 'validate[required,custom[date]]');
 data.form.find('input[name="Education"]').addClass(
 'validate[required]');
 data.form.validationEngine();
            },
            formSubmitting: function (event, data) {
 return data.form.validationEngine('validate');
            },
            formClosed: function (event, data) {
 data.form.validationEngine('hide');
 data.form.validationEngine('detach');
            }
        });

        //Load person list from server
        $('#PersonTable').jtable('load');
    });

</script>

The validation engine works with CSS classes for validation. validate[required] indicates that this input element can not be empty. Take a look at the validation engine’s web site [3] for details.

In the formCreated event, I add the required classes to input elements and call the validationEngine() plug-in method on the form. In the formSubmitting event, I call the Validate method of the validation engine. It returns false if the form is not valid. So I use the same value as the return value of the formSubmitting event. jTable does not submit the form if this event returns false. Finally, in the formClosed event, I detach the validation engine from the form.

This was the first method for validation. Since jTable allows you to inject classes to your input fields using the inputClass property in field definitions, we could define a field with the validate[required] class as default:

Collapse | Copy Code
//...
Name: {
    title: 'Name',
    width: '15%',
 inputClass: 'validate[required]'
},
//...

Thus, jTable automatically adds the validate[required] class to the input element of this field. So we don’t need to add classes to inputs in the formCreated event.

See the “Using with ‘validation engine’ plugin – 2” demo page in the download file or the demo web site

4 thoughts on “AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in

  1. You mention a download for this article in the text many times, but I don’t see a link for it…

  2. Why I keep getting the No data available! when adding Jtable to MVC application.
    I also get the error “An error occurred while communicating with the server” when I use the add new record to the empty table

    • Got the data to show on the screen – Now the table shows row ID as undefined. I have used different combination on the field identified as Key: true – the impact I cannot delete or update current rows ’cause ID cannot be sent to the server.

  3. This is a very useful JQuery plugin. I’m having once small problem. It seems that the default action for the Create / Edit dialog is that the dialog stays open on successful insertion (Result = OK), and you have to click the “Cancel” button to close the Create or Edit dialog. This is counter intuitive and confuses users. If you successfully updated or created a record, does clicking “Cancel” roll back the insertion/update query? Obviously no, but “cancel’ doesn’t mean the same thing as “close” and regardless, upon success, the dialog should automatically close.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s