Click here to Skip to main content
15,559,940 members
Articles / Web Development / ASP.NET
Posted 20 May 2013


31 bookmarked

CRUD tables in WebMatrix using jTable jQuery plugin

Rate me:
Please Sign up or sign in to vote.
4.87/5 (12 votes)
23 May 2013CPOL4 min read
A working example of a jTable implementation in WebMatrix 3


I heard for the first time of the existence of jTable from this excellent article (AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in) that presents its remarkable potential in conjunction with ASP.NET MVC 3.

jTable is a jQuery plugin that can be used to create AJAX based CRUD tables without much effort. Its API makes available methods to create, edit and delete records from the data source and, at the same time, to update the displayed page with pleasant graphical animations. Moreover, jTable is platform independent, working on all common browsers, and it is not dependent on any server-side technology.

Good starting point for the discovery of its use are the mentioned article, and the jTable site, that includes many examples for ASP.NET MVC, Web Forms and PHP. Nevertheless, there is no tutorial for the ASP.NET Web Pages environment. 

In this article, I present a working example of a jTable implementation in WebMatrix 3. I am not trying to replace the tutorials that I have previously quoted, but I am simply going to show how some tasks could be accomplished in WebMatrix, leaving to you to explore and test the whole potential of the jTable library.

The example uses a simple “Orders” Sql Ce database to create a table with paging and sorting capabilities and a child view to the details of any single master row. 

Image 1

Using the code  

I have built it starting from the Empty Site template. Once created the new site, the first step is to add the jTable library: you can accomplish this task through the NuGet Gallery, that installs the jTable plugin with the needed dependencies (jQuery and jQuery UI), or downloading from the jTable site the zipped file and copying it into the root directory of your site.

I have adopted the second solution, because I used CDN for jQuery and jQuery UI.

Now a quick look at the files that make up the example.

The Default.cshtml page includes no server side code, but only HTML and JavaScript; its only content is the script that defines the jTable instance and a div tag as container.    

<!-- jTableOrders r1.01 - 23.05.2013 -->

<!DOCTYPE html>
<html lang="en">
        <meta charset="utf-8" />
        <title>Just another jTable example</title>
        <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
        <link href="" 
                rel="stylesheet" type="text/css" />
        <link href="" rel="stylesheet" 
                type="text/css" /> 
        <script src="" type="text/javascript"></script>
        <script src="" type="text/javascript"></script>
        <script src="" 

        <script type="text/javascript">
            $(document).ready(function () {
                    title: 'Orders list',
                    paging: true, //Enable paging
                    pageSize: 10, //Set page size (default: 10)
                    sorting: true, //Enable sorting
                    defaultSorting: 'Order Date DESC', //Set default sorting
                    actions: {
                        listAction: '/Actions/OrdersList',
                        createAction: '/Actions/CreateOrder',
                        updateAction: '/Actions/UpdateOrder',
                        deleteAction: '/Actions/DeleteOrder'
                    fields: {
                        'Order Id': {
                            key: true,
                            list: false
                        //CHILD TABLE DEFINITION FOR "DETAILS"
                        'Details': {
                            title: '',
                            width: '5%',
                            sorting: false,
                            edit: false,
                            create: false,
                            display: function (OrderData) {
                                //Create an image that will be used to open child table
                                var $img = $('<img src="" ' + 
                                    'title="Edit order details" />');
                                //Open child table when user clicks the image
                                $ () {
                                                title: OrderData.record['Ship Name'] + 
                                                        ' - Order Details',
                                                actions: {
                                                        + OrderData.record['Order Id'],
                                                    deleteAction: '/Actions/ChildTable/DeleteDetail',
                                                    updateAction: '/Actions/ChildTable/UpdateDetail',
                                                    createAction: '/Actions/ChildTable/CreateDetail'
                                                fields: {
                                                    'Order Id': {
                                                        type: 'hidden',
                                                        defaultValue: OrderData.record['Order Id']
                                                    'Detail Id': {
                                                        key: true,
                                                        create: false,
                                                        edit: false,
                                                        list: false
                                                    'Product Name': {
                                                        title: 'Product',
                                                        width: '50%'
                                                    'Unit Price': {
                                                        title: 'Unit Price',
                                                        width: '25%'
                                                    'Quantity': {
                                                        title: 'Quantity',
                                                        width: '25%'
                                            }, function (data) { 
                                //Return image to show on the order row
                                return $img;
                        'Ship Name': {
                            title: 'Firm',
                            width: '40%'
                        'Ship Country': {
                            title: 'Country',
                            width: '20%'
                        'Order Date': {
                            title: 'Order',
                            width: '20%',
                            type: 'date'
                        'Shipped': {
                            title: 'Shipped',
                            width: '20%',
                            type: 'checkbox',
                            values: { 'false': 'False', 'true': 'True' }

        <div id="OrdersTableContainer"></div>

The part that differs depending on the development model is the server-side code that accomplishes the actions required by the jTable instance:

  • list records, 
  • add a record,
  • delete a record,
  • update a record. 

In this example, there are four .cshtml files for the master table in the /Actions folder and four .cshtml files for the child table in the /Actions/ChildTable folder.

Any file receives query string parameters from jTable, executes an action on the database table and returns a result with the Response.Write() method.  

Points of Interest  

I am going to look more thoroughly at one of their, the most complex one, which is called for listing orders with sorting and paging: OrdersList.cshtml. 

    public static string SqlQuery(string order)
        var orderPart = "";
            case "Ship Name DESC":
                orderPart = "[Ship Name] DESC";
            case "Ship Country ASC":
                orderPart = "[Ship Country] ASC";
            case "Ship Country DESC":
                orderPart = "[Ship Country] DESC";
            case "Order Date ASC":
                orderPart = "[Order Date] ASC";
            case "Order Date DESC":
                orderPart = "[Order Date] DESC";
            case "Shipped ASC":
                orderPart = "[Shipped] ASC";
            case "Shipped DESC":
                orderPart = "[Shipped] DESC";
                orderPart = "[Ship Name] ASC";
        return "SELECT * FROM Orders ORDER BY " + orderPart + 
            " OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY";
        var db = Database.Open("Orders");
        var recCount = db.QueryValue("SELECT COUNT(*) FROM Orders");
        var orders = db.Query(SqlQuery(Request["jtSorting"]),
                Request["jtStartIndex"], Request["jtPageSize"]).ToList();
        var json = Json.Encode(new{Result = "OK", Records = orders, TotalRecordCount = recCount});
    catch (Exception ex)
        var json = Json.Encode(new{Result = "ERROR", Message = ex.Message});

In case of sorting and paging, jTable sends to the action file three parameters: 

  • jtSorting: a string representing requested sorting. It is built from sorting field name plus sorting direction. For instance, it can be 'Name ASC', 'BirtDate DESC', 'Age ASC'... etc.;
  • jtStartIndex: the start index of records for current page;
  • jtPageSize: count of maximum expected records. 

Besides the records required, the action file must return the total count of records in the table.

To accomplish this task, my file uses a function to build the SQL query adding the right ORDER BY clause and extracting the requested range of records with the OFFSET-FETCH clause.

This Mike Brind’s article (Web Pages - Efficient Paging Without The WebGrid) explains well the use of the OFFSET-FETCH clause in SQL CE. Pay attention that this works only with SQL Ce or with the new SQL Server 2012; with older versions of SQL Server you must use other solutions as ROW_NUMBER() and TOP.

To return the total number of records, the action file executes two queries: first, it stores the records number in recCount and then it extracts the required records. Finally, it creates an JSON object with Json.Encode() and returns it to jTable.  


As I have previously stated, this article is not intended as a substitute of the jTable documentation: use it as a support looking at the tutorials for the other platforms and trying to figure out as they can be adapted to the WebMatrix environment.


23.05.2013 - jTableOrders r1.01

  • Added options pageSize and defaultSorting to the jTable instance. 

20.05.2013 - jTableOrders r1.00

  • First release.  


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Chief Technology Officer Federfarma Pavia
Italy Italy
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionNice Job Pin
Member 1074591723-Oct-15 17:14
Member 1074591723-Oct-15 17:14 
Member 1059208210-Oct-14 22:10
Member 1059208210-Oct-14 22:10 
QuestionOkay - How exactly do I run this WebMatrix app/page? Pin
Todd Dickinson9-Sep-14 9:32
Todd Dickinson9-Sep-14 9:32 
AnswerRe: Okay - How exactly do I run this WebMatrix app/page? Pin
Gianmaria Gregori10-Sep-14 1:30
professionalGianmaria Gregori10-Sep-14 1:30 
GeneralMy vote of 5 Pin
Halil ibrahim Kalkan24-May-13 4:10
Halil ibrahim Kalkan24-May-13 4:10 
GeneralRe: My vote of 5 Pin
Gianmaria Gregori24-May-13 4:22
professionalGianmaria Gregori24-May-13 4:22 
GeneralMy vote of 5 Pin
fredatcodeproject24-May-13 2:41
professionalfredatcodeproject24-May-13 2:41 
GeneralRe: My vote of 5 Pin
Gianmaria Gregori24-May-13 3:40
professionalGianmaria Gregori24-May-13 3:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.