Using jQuery tree for database data

Hi,

Actually this blog post is to answer a really nice guy from a forum (.Net) I run in Hebrew, but I thought it will be really a really nice blog post in general.

I get a lot of questions regarding the concept of using jQuery (in general) with .net and database data.

I actually blame Microsoft for having the tutorials in their website teaching only SqlDataSource which I never used and recommend that you don’t use too.

So, the majority of the questions I get are not at all about code, it’s about concept, how to communicate with data, how to retrieve data and more.

Ok, so this tutorial is all about jQuery, LINQ to Sql, and the jQuery tree.

The only difference is that I’m not going to display files in folders but pages from a Sql table, every page has a “ParentID” fields, so it will be very easy to understand.

This tutorial is also to clarify you can use the tree for any relational database or file data.

So,

Lets Start.

First, lets go and download the 2 packages.
jQuery
jQuery File Tree

We will open up a new website in visual studio.
Our files should look like this:

What do we have here?
Well, we have all the css from the jQuery file tree library, we have the Images also from this library, we have the js files from: jQuery, jQuery file tree and another js file which we created for our custom functions.

Alright, now let’s create our page:

We have all the script file and css files in our page, also, we wrote a div called “myFirstTree” in our page, this div will hold our tree.

What else do we need?

Well, like I said, we are displaying database data here, so lets create a very simple database with one table, also we will create a LINQ mapping file for it.
B.T.W, you can use any kind of data access, you can use nHibernate, or whatever you may find comfortable for your use.
I use linq for this example, don’t ask why, just felt like it

So, we have these fields:
pageID – Identity field
pageName
pageParentID

let’s create our data, just randomly fill our table with some data, and then we can go back and retrieve it from the client side using some jQuery.

So, we have our data, I don’t think i need to further explain this no?

Now, lets write some js code to retrieve the data from the server for us.

Our js code should look like this:

   1: $(document).ready(function() {

   2:     $('#myFirstTree').fileTree(

   3:     {

   4:         root: '0',

   5:         script: 'jQueryTreeHandler.ashx',

   6:         multiFolder: true,

   7:         loadMessage: 'Tree loading...'

   8:     }, function(file) {

   9:         alert(file);

  10:     });

  11: });

Let’s explain this code a bit shall we?

first, we chose our pre crated div “myFirstTree” and then we initialized the fileTree object with these parameters:

root: the first parameter that will be passed to our Handler

script: our path to the handler

multiFolder: whether our tree will be multi folder or not, when this is false, clicking each folder will collapse all the other folders in the tree.

loadingMessage: this message will be displayed when data is being retrieved from the server.

fileFunction: this function will be fired when a file from the tree is selected.

All pretty straight forward up until now, lets go on and create our handler:

the parameter we know is passed in is called “dir” so that is what we will use here.

So, this is what your handler code should look like:

   1: <%@ WebHandler Language="C#" Class="jQueryTreeHandler" %>

   2:

   3: using System;

   4: using System.Web;

   5: using System.Linq;

   6:

   7: public class jQueryTreeHandler : IHttpHandler {

   8:

   9:     public void ProcessRequest (HttpContext context) {

  10:         context.Response.ContentType = "text/plain";

  11:         int parentID = Convert.ToInt32(context.Request.Form["dir"]);

  12:

  13:         string list = "";

  14:         jQueryTreeDataContext db = new jQueryTreeDataContext();

  15:         var pages = from p in db.Pages

  16:                     where p.pageParentID == parentID

  17:                     select p;

  18:

  19:         foreach (Page item in pages)

  20:         {

  21:             list += String.Format("<li class=\"directory collapsed\"><a href=\"#\" rel=\"{0}\">{1}</a></li>", item.pageID.ToString(), item.pageName);

  22:         }

  23:

  24:         context.Response.Write(String.Format("<ul class=\"jqueryFileTree\" style=\"display: none;\">{0}</ul>", list));

  25:     }

  26:

  27:     public bool IsReusable {

  28:         get {

  29:             return false;

  30:         }

  31:     }

  32:

  33: }

Comment: I didn't take care of a situation where a row has no children, it wasn’t necessary for my example. but in real life code you should, and if a row does not have any children you shouldn’t display a folder but a file instead.

and this is how our code looks like in the final result (Html output)

Comment: I had to tweak the jQuery file tree code in order for it to work with database data, normally it has a regullar expression validating the "rel" attribute actually contains data with "/", that needed to be changes.

I uploaded the file to here for you, so download away...
jQuery file tree tweaked (248)