Introduction
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.
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.
<!---->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Just another jTable example</title>
<link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
<link href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css"
rel="stylesheet" type="text/css" />
<link href="http://www.codeproject.com/jtable.2.3.0/themes/metro/blue/jtable.min.css" rel="stylesheet"
type="text/css" />
<script src="http://code.jquery.com/jquery-1.9.1.js" type="text/javascript"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js" type="text/javascript"></script>
<script src="http://www.codeproject.com/jtable.2.3.0/jquery.jtable.min.js"
type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$( title: paging: true, //Enable paging
pageSize: 10, //Set page size (default: 10)
sorting: true, //Enable sorting
defaultSorting: actions: {
listAction: createAction: updateAction: deleteAction: },
fields: {
key: true,
list: false
},
//CHILD TABLE DEFINITION FOR "DETAILS"
title: width: sorting: false,
edit: false,
create: false,
display: function (OrderData) {
//Create an image that will be used to open child table
var $img = $( //Open child table when user clicks the image
$img.click(function () {
$( $img.closest( {
title: OrderData.record[ actions: {
listAction:
+ OrderData.record[ deleteAction: updateAction: createAction: },
fields: {
type: defaultValue: OrderData.record[ },
key: true,
create: false,
edit: false,
list: false
},
title: width: },
title: width: },
title: width: }
}
}, function (data) {
data.childTable.jtable( });
});
//Return image to show on the order row
return $img;
}
},
title: width: },
title: width: },
title: width: type: },
title: width: type: values: { }
}
});
$( });
</script>
</head>
<body>
<div id="OrdersTableContainer"></div>
</body>
</html>
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.
@functions{
public static string SqlQuery(string order)
{
var orderPart = "";
switch(order)
{
case "Ship Name DESC":
orderPart = "[Ship Name] DESC";
break;
case "Ship Country ASC":
orderPart = "[Ship Country] ASC";
break;
case "Ship Country DESC":
orderPart = "[Ship Country] DESC";
break;
case "Order Date ASC":
orderPart = "[Order Date] ASC";
break;
case "Order Date DESC":
orderPart = "[Order Date] DESC";
break;
case "Shipped ASC":
orderPart = "[Shipped] ASC";
break;
case "Shipped DESC":
orderPart = "[Shipped] DESC";
break;
default:
orderPart = "[Ship Name] ASC";
break;
}
return "SELECT * FROM Orders ORDER BY " + orderPart +
" OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY";
}
}
@{
try
{
System.Threading.Thread.Sleep(200);
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});
Response.Write(json);
}
catch (Exception ex)
{
var json = Json.Encode(new{Result = "ERROR", Message = ex.Message});
Response.Write(json);
}
}
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.
Conclusion
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.
History
23.05.2013 - jTableOrders r1.01
- Added options pageSize and defaultSorting to the jTable instance.
20.05.2013 - jTableOrders r1.00
