This article shows how a datatable with standard CRUD functionalites (select/delete data, update single cell, and add new record) can be created in ASP.NET MVC using the jQuery DataTables Editable plug-in.
Table of Content
- Introduction
- Background
- Using the Code
- Model
- View
- Controller
- Example - Implementation of CRUD Actions
- Updating Cells
- Deleting Rows
- Adding Records
- Configuration in the Server Side Processing Mode
- Full Customization
- Setting action URLs
- Configure DOM
- Customize Error Messages
- Custom Column Editors
- Summary
- History
Introduction
The purpose of this article is to show how you can implement a table with the full set of data management functionalities in ASP.NET MVC using jQuery/AJAX with various plug-ins. Here is a list of the aimed functionalities:
- Client side pagination, filtering, sorting
- CRUD operations - deleting/editing/adding records
- Effective client-side functionalities where most of the interaction is done by AJAX
My intention is to show how you can implement these functionalities with minimal effort using a jQuery DataTables Editable plug-in and thereby easily extending DataTable CRUD functionalities. Example of such kind of table is shown in the figure below:
All functionalities you see in the figure are pure JavaScript enhancement - on the server-side, you just need to generate a pure HTML table. Everything you see in the table is implemented on the client-side using the following JavaScript call:
$('table#myDataTable').dataTable().makeEditable();
This line of code finds a table with id "myDataTable", and applies two JQuery plugins that add to the table all functionalities shown above. In the rest of the article, I will show you how you can implement and customize this plugin.
This article might be considered as a second part in the series of articles that describes how to implement effective Web 2.0 interfaces with jQuery, ASP.NET MVC, and the jQuery DataTables plugin. In my previous article, I described how you can implement a DataTable with server-side pagination, filtering, and sorting which enables you to implement high-performance table operations. In this article, server-side actions are not described again, and focus is on the data management functionalities only. These two articles can help you to create effective Web 2.0 data tables with fully AJAXified functionalities.
Background
A common requirement in web projects is to create a table where besides listing data, the user should be able to edit information, and add new or delete existing records. When a fully functional data table/data grid needs to be implemented, my choice is the jQuery DataTables plug-in. This plug-in takes a plain HTML table and adds several functionalities such as pagination, ordering by column, filtering by keyword, changing the number of records that should be displayed per page, etc. All you need to do is to include a single JavaScript call:
<script language="javascript" type="text/javascript">
$(document).ready(function () {
$('#myDataTable').dataTable();
});
</script>
In the example, myDataTable is the ID of the table that should be enhanced with the DataTables plug-in. Full description of the jQuery DataTables features can be found here. The picture that follows shows a plain HTML table after applying the DataTables plug-in.
DataTables itself provides a very good API for data manipulation (adding rows, deleting rows, etc.). However, a drawback is that you will need to learn the API functions and implement CRUD functionalities yourself because there is no out-of-the-box solution that enables you to easily implement CRUD functionalities. This might make one think of moving from DataTables to some other plug-in such as jqGrid (which is also a good plug-in, similar to DataTables) just because it has out-of-the-box configuration for CRUD functionalities. Therefore, my goal was to encapsulate the jQuery DataTables functionalities that are needed for the standard CRUD operations into a separate plug-in which adds CRUD functionalities on top of the standard set of DataTables functionalities and makes it possible for a developer to activate it as easily as possible. Code to initialize an editable data table is shown below:
<script language="javascript" type="text/javascript">
$(document).ready(function () {
$('#myDataTable').dataTable().makeEditable();
});
</script>
This line of code would result in a table that allows the user to edit data by double clicking on a cell, select and delete any row in the table, and add a new record. An example of the enhanced table can be found on the live demo site. Beside this, you'll need to create server-side code that accepts AJAX calls sent by the plug-in when the user changes some record and this article will guide you through this task.
Using the Code
For illustrative purposes, we'll use a simple ASP.NET MVC web application to list companies, delete them, add new or update existing company information. The first thing you need to do is to create a standard ASP.NET Model-View-Controller structure. There are three steps required for this setup:
- Creating the model classes that represent the data structure that will be shown
- Creating the controller class that will react on the user events
- Creating the view that will render data and create HTML code that is sent to the browser window
In the beginning, we'll just display company information in a table. Then, this simple table will be enhanced with the jQuery DataTables Editable plug-in. The following JavaScript components need to be downloaded:
- jQuery library v1.4.4., containing standard classes used by the DataTables plug-in
- jQuery UI library v1.8.7., containing classes for handling dialogs
- jQuery DataTables plug-in v1.7.5., including the optional DataTables CSS style-sheets used for applying the default styles on the page
- jQuery Jeditable plug-in v1.6.2., required for inline cell editing
- jQuery validation plug-in v1.7., for implementation of client-side validation
- jQuery DataTables Editable plug-in that integrates all these mentioned plug-ins into a fully functional editable datatable.
These files should be stored in the local file system and included in the HTML page that is rendered on the client. Example of usage of these files is explained below.
Model
The model comes to a simple class containing company data. The fields that we need are company ID, name, address, and a town. The source code for the company model class is shown below:
public class Company
{
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Town { get; set; }
}
View
View is used to render data on the server-side and send HTML code to the browser. The example includes three different view pages that show different usage and configuration of the plug-in. There's one layout page that will be used by all these pages. This layout page is shown below:
<!DOCTYPE html>
<html>
<head>
<title>Customization of Editable DataTable</title>
<link href="@Url.Content("~/Content/dataTables/demo_table.css")"
rel="stylesheet" type="text/css" />
<link href="@Url.Content("~/Content/dataTables/demo_table_jui.css")"
rel="stylesheet" type="text/css" />
<link href="@Url.Content("~/Content/themes/base/jquery-ui.css")"
rel="stylesheet" type="text/css" media="all" />
<link href="@Url.Content("~/Content/themes/smoothness/jquery-ui-1.7.2.custom.css")"
rel="stylesheet" type="text/css" media="all" />
<script src="@Url.Content("~/Scripts/jquery-1.4.4.min.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.dataTables.min.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.jeditable.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery-ui.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.dataTables.editable.js")"
type="text/javascript"></script>
@RenderSection("head", required: false);
</head>
<body>
<div id="container">
<a href="/Company/Index">Basic Example</a>
<a href="/Company/Ajax">Getting data with Ajax</a>
<a href="/Company/Customization">Customization</a>
@RenderBody()
</div>
</body>
</html>
This layout page does not have any presentation logic - it just includes all the necessary JavaScript files and contains links to all the pages used in this example. Page specific content will be rendered when the @RenderBody() call is executed. In addition, this layout page allows you to include custom JavaScript that is specific to the pages in the "head" section. Note that the last JavaScript file is the DataTables Editable plug-in which covers the CRUD functionalities that will be presented in this example. The layout page is not required for your projects, but it allows to simplify the views so that they contain only code that is relevant for the examples. The view that renders the table is shown in the listing below:
@{
Layout = "~/Views/Company/JQueryDataTableEditableLayout.cshtml";
}
@section head{
<script language="javascript" type="text/javascript">
$(document).ready(function () {
$('#myDataTable').dataTable().makeEditable();
});
</script>
}
<div id="demo">
<h1>Basic Example</h1>
<table id="myDataTable" class="display">
<thead>
<tr>
<th>Company name</th>
<th>Address</th>
<th>Town</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr id="@item.ID">
<td>@item.Name</td>
<td>@item.Address</td>
<td>@item.Town</td>
</tr>
}
</tbody>
</table>
<div class="add_delete_toolbar" />
</div>
This view uses the layout page described above, and puts the initialization JavaScript in the header that initializes the data table and makes it editable. The body contains a table with a company name, address, and town. The ID of each company is placed in an ID attribute of the surrounding <tr> tag - this is a place where the DataTables Editable plug-in expects to find the ID of the record that will be edited or deleted. Also, there is a <div> element with a class "add_delete_toolbar" which tells the DataTables Editable plug-in where it should place the auto-generated add and delete buttons.
Controller
When a page is required by the user, the controller returns a company list that will be rendered in the view. Instead of a database, there is the DataRepository class that just returns a list of all the companies. An example of the controller with one action method that reacts to the /Company/Index request is shown below:
public class CompanyController : Controller
{
public ActionResult Index()
{
var companies = DataRepository.GetCompanies();
return View(companies);
}
}
When the "/Company/Index" request is sent from the client, this action method is executed and a set of all the companies from the repository is sent to the view.
The only thing you need to do is to create server-side actions that handle the add, delete, and update requests sent by the plug-in. These actions can be added as controller actions:
public class CompanyController : Controller
{
public string DeleteData(int id){ ... }
public string UpdateData(int id, string value, int? rowId,
int? columnPosition, int? columnId, string columnName){ ... }
public int AddData(string name, string address, string town, int? country){ ... }
}
The DeleteData action accepts the ID of the deleted row as a parameter and returns an "ok" string if the update is successful. Any other string value represents an error message that will be shown to the user.
The UpdateData action accepts the ID of the updated cell, a value entered by the user, and the position of the cell (column and row ID). The action should return text equal to the value input parameter. Any other string value represents an error message that will be shown to the user.
The AddData action has custom parameters representing all the information that should be saved when a new record is added. The given example uses the name, address, town, and country in the new company record, but in the other implementations, you will use arbitrary parameters, so you will need to create a custom form for adding a new record. The DataTables Editable plug-in handles opening your custom form in the dialog and posting form values to the server-side action shown above.
In the next section, I will explain how this plug-in is integrated in the example attached to this article.
Example - Implementation of the Controller Action Methods
The code described in the previous sections is necessary for rendering data and initializing the DataTables Editable plug-in. Once it's initialized, the plug-in allows you to perform the following functionalities:
- Updating cell values - When the user double clicks on a cell, it will be converted into a textbox and when the user finishes editing data and presses Enter, an AJAX call will be sent to the server-side.
- Deleting rows - When the user selects a row and presses the Delete button, an AJAX request will be sent to the server so the selected record can be deleted on the server-side.
- Adding a new row - When the user adds a new row, an AJAX request is sent with the information about the new record.
The picture below shows the trace of AJAX calls that are sent to the server when these operations are performed by the user. The actions "DeleteData", "AddData", and "UpdateData" are the default AJAX URLs that are called by the plug-in and can be modified if necessary.
The following sections describe the implementation of the needed server-side actions that actually perform these operations on the real data.
Updating Cells
Updating cells is done by using an inline editable plug-in called Jeditable. The DataTables Editable plug-in is internally configured to replace the cell content with an editable textbox when the user double clicks on the cell. The following figure shows how the user can edit data:
When the user finishes cell editing and presses Enter, the plug-in sends the AJAX call with the information about the edited value. The new cell content is sent to the server with a new value, the ID of the record, and the coordinates of the cell. The AJAX call that is sent to the server-side is shown below:
The AJAX request contains the following parameters:
id of the row taken from the ID attribute of the <tr> tag that surrounds the cell that has been edited. Use this value to find a record that should be updated.value that is entered in the cell. This value should be written in the company record.columnName - name of the column (e.g., text found in the column heading). You can use this information to determine which property should be updated.rowId from the table. If 10 rows are shown per page, this will be a value between 0 and 9.columnPosition - position of the column value from 0 to the number of columns you see in the table - 1. Hidden columns are not counted. This value can be used instead of the column name to identify the property that should be updated. Use this value if names of the columns can be dynamically changed.columnId - ID of the column from 0 to the total number of columns - 1. Hidden columns are counted. This value can be used instead of the column name to identify the property that should be updated. You should use columnId instead of columnPosition if you have hidden columns in the table (either initially hidden or dynamically hidden).
You will also need a controller action that will accept the request described above, receive information sent from the plug-in, update actual data, and return response. Example:
public class CompanyController : Controller
{
public string UpdateData(int id, string value, int? rowId,
int? columnPosition, int? columnId, string columnName)
{
var companies = DataRepository.GetCompanies();
if (columnPosition == 0 && companies.Any(
c => c.Name.ToLower().Equals(value.ToLower())))
return "Company with a name '" + value + "' already exists";
var company = companies.FirstOrDefault(c => c.ID == id);
if (company == null)
{
return "Company with an id = " + id + " does not exists";
}
switch (columnPosition)
{
case 0:
company.Name = value;
break;
case 1:
company.Address = value;
break;
case 2:
company.Town = value;
break;
default:
break;
}
return value;
}
}
This action accepts information about the ID of the updated record, the iD of the row where the updated cell is placed, and ID, position, and name of the column where the updated cell is placed. The code is simple. The company is found by using the ID, and one of the properties of the found record is updated depending on the position of the column. Instead of the column position, column name can be used - it depends on the server-side logic. If everything is fine, the returned value should be the same as a value sent by the plug-in in the request. Otherwise, the DataTables Editable plug-in will assume that the update has failed and that the returned text is an error message that should be shown to user. Hence, to notify the plug-in that an error occurred, the only thing you need to do is to return an error message (as shown in the example).
Deleting Rows
The DataTables Editable plug-in enables row selection and initializes a delete button. When the user selects a row, the delete button gets enabled, and after it's pressed, an AJAX request with an ID of the currently selected row will be sent to the server. The ID is taken from the id attribute of the <tr> tag. The AJAX request that is sent by the plug-in to the server-side page is shown below:
The server-side page should return an "ok" string if the record is successfully deleted, or an error message that should be shown to the user.
The Controller action that accepts an ID of the row that needs to be deleted and actually deletes a row is given in the following example:
public class CompanyController : Controller
{
public string DeleteData(int id)
{
try
{
var company =
DataRepository.GetCompanies().FirstOrDefault(c => c.ID == id);
if (company == null)
return "Company cannot be found";
DataRepository.GetCompanies().Remove(company);
return "ok";
}
catch (Exception ex)
{
return ex.Message;
}
}
}
If everything is fine, an "ok" string is returned back. Any other string that is returned from the code such as "Company cannot be found" or an exception message will be shown on the client-side as an error message, and deleting will be cancelled.
Adding New Records
Adding a new record is a bit complicated - in this case, it is not enough just to add one action in the controller. For adding a new record, it is necessary to add an HTML form that will be used for adding a new record. This form should have the id "formAddNewRow" and should contain the input elements that the user needs to populate. An example of the form is shown in the listing below:
<form id="formAddNewRow" action="#" title="Add new company">
<label for="name">Name</label><input type="text"
name="name" id="name" class="required" rel="0" />
<br />
<label for="name">Address</label><input type="text"
name="address" id="address" rel="1" />
<br />
<label for="name">Postcode</label><input
type="text" name="postcode" id="postcode"/>
<br />
<label for="name">Town</label><input
type="text" name="town" id="town" rel="2" />
<br />
<label for="name">Country</label>
<select name="country" id="country">
<option value="1">Serbia</option>
<option value="2">France</option>
<option value="3">Italy</option>
</select>
<br />
</form>
When the DataTables Editable plug-in detects the Adding new record form, the "Add" button will be auto-generated. When a user presses the "Add" button, the DataTables Editable plug-in opens a form in the new dialog window where the user can enter information about the new record (the dialog is shown below).
This form cannot be auto-generated because I assume that in each add functionality, you will need some custom form with various elements such as textboxes, calendars, etc. Therefore, I assume that it will be easier that you add a plain HTML form that suits you best and style it, rather than use some auto-generated functionality. In this form, it is important to add rel attributes to the input elements that should be copied to the table when a record is added. The rel attributes are used by the DataTable Editable plug-in to map values of the new record with the columns in the table. In the example given above, the values that will be entered in the name, address, and town inputs will be mapped to the columns 0, 1, and 2 of the table - rel attributes are used for this mapping.
As it can be seen, OK and Cancel buttons do not need to be added in the form - the DataTables Editable plug-in adds them automatically as the last elements in the form. The form is automatically validated on the client-side using a jQuery validation plug-in. Therefore, you can add the "required", "email", "date", and other CSS classes to automatically implement client-side validation. In the above example, name is marked as required field and a client-side error message will be shown if this field is not populated. You can see what validation rules can be used on the jQuery validation plug-in site. When the DataTables Editable plug-in detects the "Add new record" form, it will enable the user to add a new record via that form and post elements found in the form to the server-side. When the user presses the "OK" button, an AJAX request is sent to the server and if everything is fine, the dialog is closed and a new row is added to the table. An example of the AJAX request that is sent from the form displayed above is shown on the following figure:
The AJAX call sends all the values of the input elements in the form and expects to get the ID of the new row back. Once the ID is returned, the new row is added, populated with the values from the form, and the returned ID of the record is set as an ID attribute of the new row.
DataTables Editable handles common operations such as opening a dialog, posting a request to the server, closing a dialog when Cancel is pressed, and adding a row in the table if an operation is successful. The only thing that needs to be done is creating a plain HTML form as a template for adding a new record, and a server-side action that accepts information about the new record.
The Controller action that accepts the data entered in the form is shown below:
public class CompanyController : Controller
{
public int AddData(string name, string address, string town, int country)
{
var companies = DataRepository.GetCompanies();
if (companies.Any(c => c.Name.ToLower().Equals(name.ToLower())))
{
Response.Write("Company with the name '" + name + "' already exists");
Response.StatusCode = 404;
Response.End();
return -1;
}
var company = new Company();
company.Name = name;
company.Address = address;
company.Town = town;
companies.Add(company);
return company.ID;
}
}
The signature of the method depends on the form parameters - for each parameter that is posted to the server, one argument in the method should be added. As the name, address, town, and country are posted from the client, these parameters are added in the method call. The Action method returns an integer value that represents the ID of the new record. If any error occurs (such as duplicate name constraint violation as shown in the example), an error message should be returned as a response text. Also, the status code of the response should be set to some HTTP error status. The actual value is irrelevant however, it is necessary to return some of the status codes in the 4xx or 5xx family to notify the DataTables Editable plug-in that the error occurred while trying to add a record. In this case, I used a 404 error message but the actual code is irrelevant - the only thing that is needed is that the plug-in detects that the error occurred and that it shows the response text to the user.
Configuration in the Server-Side Processing Mode
The DataTables plug-in can use either a row in the table as a source of data or it can be configured to use a JSON source from the server-side page. In the server-side mode, only data that should be shown on the current page is returned from the server and displayed in the table. The standard DataTables functionalities such as filtering, ordering, and pagination just forward the request to the server-side where the information is processed and returned back to the DataTables plug-in. This mode requires some server-side development but can significantly increase the performance. The DataTables Editable plug-in can detect whether the DataTables plug-in is used in server-side mode and support AJAX based functionalities. In this section, I will show you what modifications should be done in the DataTables Editable plug-in to work in this mode.
Model
In the server-side mode, the model is not changed - the same company class is used as in the previous example.
View
I have created a different view page that renders the output to match the DataTables AJAX mode. The view is shown below:
@{
Layout = "~/Views/Company/JQueryDataTableEditableLayout.cshtml";
}
@section head{
<script language="javascript" type="text/javascript">
$(document).ready(function () {
$('#myDataTable').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": 'TableData',
"aoColumns": [
{ "sName": "ID",
"bSearchable": false,
"bSortable": false,
"bVisible": false
},
{ "sName": "COMPANY_NAME" },
{ "sName": "ADDRESS" },
{ "sName": "TOWN" }
]
}).makeEditable();
});
</script>
}
<div id="demo">
<h2>Ajax example</h2>
<table id="myDataTable" class="display">
<thead>
<tr>
<th>ID</th>
<th>Company name</th>
<th>Address</th>
<th>Town</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
In the DataTables call in JavaScript are added the bServerside and sAjaxSource parameters. Also, columns are explicitly defined where you can see that the ID of the column is added as a hidden column. In the AJAX mode, you cannot easily put the ID of the record as an id attribute of the <TR> tag that surrounds a Company. Therefore, in the AJAX mode, the ID of each record that will be edited or deleted must be placed in the first hidden column of the table. The table body is empty because it is not generated on the server. Each time data is required, the DataTables plug-in calls the sAjaxSource page to get the JSON array that will be dynamically injected into the table body on the client-side. The only difference that should be done is in the "Add new row" form. As we have the first column to be the ID of the company, we need to put a matching input element with rel="0" in the form for adding a new row. The most convenient thing to do is to add this element as a hidden input without a name (so it will not be sent to the server), with some dummy value. This element is required so adding a new row in the table would not break due to the fact that the number of inputs in the form and columns in the table do not match. The value of this hidden field is irrelevant as the ID will be taken from the server and set in the table as an ID when a row is added. An example of the "Add new row" form is shown below:
<form id="formAddNewRow" action="#" title="Add new company">
<input type="hidden" id="id" name="id" value="-1" rel="0" />
<label for="name">Name</label>
<input type="text" name="name" id="name" class="required" rel="1" />
<br />
<label for="name">Address</label>
<input type="text" name="address" id="address" rel="2" />
<br />
<label for="name">Postcode</label>
<input type="text" name="postcode" id="postcode"/>
<br />
<label for="name">Town</label>
<input type="text" name="town"
id="town" rel="3" />
<br />
<label for="name">Country</label>
<select name="country" id="country">
<option value="1">Serbia</option>
<option value="2">France</option>
<option value="3">Italy</option>
</select>
<br />
</form>
Controller
In my previous article, I explained how you can implement a controller to work with DataTables in server-side mode. In short, two major differences are:
- Controller action that is executed when the view page is requested does nothing. No model is associated to the view as data binding is not done on the server-side.
- An additional action that will be referenced via
sAjaxSource must be implemented where all the processing is done on the server-side.
Integration of the DataTables plug-in with server-side code is not covered here, but you can find how this can be implemented in the article Integrating the jQuery DataTables plug-in into an ASP.NET MVC application. In this article, you can find how to replace client-side pagination, filtering, and ordering functionalities used in this article, with server-side actions, in order to improve the performance of DataTables.
Full Customization
In the examples above, I have shown a few out of the box functionalities of the DataTable Editable plug-in that can be used without any change. However, similar to the original DataTables plug-in, the DataTables Editable plug-in allows you to configure properties of the plug-in and customize it. In this section, I will explain how this plug-in can be customized.
Setting AJAX URLs
The first thing you might want to change are URLs that will be called to update, delete, or add data. By default, if the URL of the page where the table is rendered is /Company/Index, URLs for data management operation will be /Company/UpdateData, /Company/AddData, and /Company/DeleteData. This is very convenient for ASP.NET MVC applications because these actions can be placed inside the same controller. If you have a different controller or set of views, e.g., /Employee/List or /Manager/Details, where the editable data table is placed, you will just add UpdateData, DeleteData, and AddData into the appropriate controllers and each page will call its data management action. However, you are able to completely customize data management URLs and put any URL you want. The example below shows how you can configure the DataTables Editable table to use PHP pages instead of ASP.NET MVC pages. You can put any value instead of these (other MVC pages, ASPX pages, etc.).
$('#myDataTable').dataTable().makeEditable({
sUpdateURL: "/Home/UpdateData.php",
sAddURL: "/Home/AddData.php",
sDeleteURL: "/Home/DeleteData.php"
});
Configuring DOM
You saw that lot of elements such as buttons are auto-generated by the plug-in. The only thing you need to do is to define an element with class "add_delete_toolbar" that will be the placeholder for Add and Delete buttons. If you want full control over the content, you can put these buttons directly in the view page. If DataTables Editable finds that buttons already exists, new ones will not be generated and event handlers will be attached to the existing ones. The only thing you need to do is to put the expected IDs into the HTML elements you want to use so DataTables Editable can find them. The default IDs of the elements are:
formAddNewRow - ID of the form that will be shown in the popup dialog when a new row is addedbtnAddNewRow - button that opens the dialog for adding a new rowbtnAddNewRowOk - confirmation button in the Add New Row dialogbtnAddNewRowCancel - cancel button in the Add New Row dialogbtnDeleteRow - button for deleting the selected row
Note that these elements do not need to be <button> HTML elements - you can place anything you want, e.g., <a>, <span>, <input>, <img>, etc. The only requirement is that these elements have expected IDs. If you do not like these IDs, you can change them too. This is suitable if you have two different tables you enhanced with the DataTables Editable plug-in on the same page and you do not want to mix their control buttons. An example configuration of the DataTables Editable plug-in with the definition of IDs of the control buttons is shown below:
$('#myDataTable').dataTable().makeEditable({
sAddNewRowFormId: "formAddNewCompany",
sAddNewRowButtonId: "btnAddNewCompany",
sAddNewRowOkButtonId: "btnAddNewCompanyOk",
sAddNewRowCancelButtonId: "btnAddNewCompanyCancel",
sDeleteRowButtonId: "btnDeleteCompany",
});
To use this configuration, you will need to place elements with exactly same IDs and position them into the page wherever you like. If you don't want a placeholder for adding Add and Delete buttons to be a div with class add_delete_toolbar, you can change this too. The configuration I frequently use to inject the buttons in the table header on the right side of "Show XXX entries per page" is shown in the example below:
$('#myDataTable').dataTable().makeEditable({
'sAddDeleteToolbarSelector': '.dataTables_length'
});
The DataTables plug-in places "Show XXX entries per page" into the div with class "datatable_length". If I put this class as a selector for the toolbar, the DataTables Editable plug-in will inject Add and Delete buttons in that div.
Customizing the Error Messages
If you don't like the standard browser's message box that is shown when an error occurs, you can change this behaviour. In the DataTables Editable initialization, you can pass your custom error function. This function should accept two parameter messages that will be shown and the action that caused an error. An example of using a custom show message function is shown in the example below:
$('#myDataTable').dataTable().makeEditable({
fnShowError: function (message, action) {
switch (action) {
case "update":
jAlert(message, "Update failed");
break;
case "delete":
jAlert(message, "Delete failed");
break;
case "add":
$("#lblAddError").html(message);
$("#lblAddError").show();
break;
}
}
});
In this example, when an error occurs when adding a new record, a message is placed in the error label with an ID "lblAddError" (the assumption is that this label is placed in the form that will be shown in the dialog and that it is initially hidden). For update and delete, error messages are used in a custom jAlert plug-in that shows a "fancy" message box instead of the standard one. You can use any other plug-in you want instead of this one. An example of implementation of custom messages can be found on this live demo site.
Configure Custom Editors for the Columns
Editing cells using textboxes is default behaviour for the Jeditable but this plug-in enables you to use different editors for each column. As an example, in some cases, you will want to use a TextArea or select list for inline editing instead of a textbox.
If you pass aoColumns parameter to the datatable's initialization function, you will be able to configure the editors for each column in the table. The parameter aoColumns represents an array of objects containing the properties of the inline editor. An example of the implementation of custom editors can be found in this live demo site. Configuration of the custom column editors is shown in the script below:
$('#myDataTable').dataTable().makeEditable({
"aoColumns": [
{
},
null,
{
indicator: 'Saving...',
tooltip: 'Click to select town',
loadtext: 'loading...',
type: 'select',
onblur: 'submit',
data: "{'London':'London','Liverpool':'Liverpool','Portsmouth':
'Portsmouth','Edinburgh':'Edinburgh', 'Blackburn':'Blackburn',
'Kent':'Kent','Essex':'Essex','Oxon':'Oxon','Lothian':'Lothian',
'West Sussex':'West Sussex','Lanarkshire':'Lanarkshire',
'Birmingham':'Birmingham','East Sussex':'East Sussex','Surrey':'Surrey'}"
}
]
});
The first object in the aoColumns array definition is an empty object {}. If you pass an empty object as a configuration parameter for some column, the default editor will be used. The second object in the array is a null value. This value makes the column read-only, i.e., the editable plug-in will not be applied on the cells in the second column. This is useful if you have HTML links in some cells and you do not want to allow the user to edit them.
The third element is the most interesting one. Here is placed a configuration object for the Jeditable editor that will be applied on the cells in the third column. The editor that will be used on the cells in the third column will be a select list (type: 'select') with the list elements defined in the data property. The inline select list that will be shown when the user click on the cells in the third column is shown in the following figure:
The data property contains a set of value:label pairs that will be used to build a list. A label will be shown in the list and the value will be used to update the cell content and it will be sent to the server-side. Submitting the selected value happens when the user clicks on any other cell causing the onblur event. The configuration is set so that the onblur selected value should be submitted to the server (onblur:'submit'). If you do not want this behaviour, you can remove the onblur:'submit' option and place the submit:'Ok' option in the configuration. This will add a submit button with the label 'Ok' to the left of the select list and the value will be submitted to the server when the user presses this button. You can even use a server-side page as a data source for the list if you put the loadurl parameter instead of the data parameter. This configuration forces the loadurl parameter to read values for the select list from the URL instead of the local data array.
Other parameters in the column configuration set the tooltip and text that will be shown while the editor is processing results using the AJAX call. The configuration parameters that can be used for the individual editor setup can be found on the Jeditable site, therefore it might be good to take a look at this site first if you are going to configure the individual editors per column.
Jeditable is a very powerful plugin that has a lot of plugins for custom input types so you can use date/time pickers, masked inputs, AJAX uploads, or even easily create your own editor. You can see the various input types on the Jeditable custom input demo site.
Summary
This article shows you how to create a datatable that has integrated add, edit, and delete functionalities using the jQuery Datatables Editable plug-in. This plug-in enables you to focus just on the server-side functionalities that handle data management requests and implement only code that is specific for your application. The complete example can be downloaded from above.
The plug-in with documentation is hosted here so you can take the plug-in or example of usage and include it in your project.
History
- 6th March, 2011: Initial version
