Nested JTemplate with DataSet Hierarchical Data Binding

This example shows how to display Master-Detail data using JTemplates.
It fetches all the master-detail related data from DB, at one shot, and saves it in DataSet with the relation defined. Later on, to get the nested lists for the nested JTemplate, it runs a filter on the DataTable to get relevant records using DataTable.Select(expression). It’s DB friendly and easy to implement. I used adhoc queries/SQLs in my example, which ofcourse, may be replaced with storedProcs in live scenario.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="NestedJTemplate2.aspx.cs" Inherits="NestedJTemplate2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <script src="Scripts/jtemplates.js" type="text/javascript"></script>
    <script src="Scripts/jquery.blockUI.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.blockUI({ message: '<h1> Processing...</h1>' });
            $.ajax({
                type: "POST",
                url: "NestedJTemplate2.aspx/getSalesOrderHeader",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (data) {
                    $.unblockUI();
                    $('#placeholder').setTemplateURL('JTemplates/ForEachTemplate2.htm');
                    $('#placeholder').processTemplate(data.d);
                },
                error: function (textStatus) {
                    $.unblockUI();
                    alert(textStatus);
                }
            });


        });
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <div id="placeholder" style="clear: both;">&nbsp;</div>
    </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.Web.Services;

public partial class NestedJTemplate2 : System.Web.UI.Page
{
    public class SalesOrderHeader
    {
        public int SalesOrderID { get; set; }
        public string SalesOrderNumber { get; set; }
        public string PurchaseOrderNumber { get; set; }
        public List<SalesOrderDetail> SalesOrderDetails { get; set; }
    }

    public class SalesOrderDetail
    {
        public int SalesOrderID { get; set; }
        public string CarrierTrackingNumber { get; set; }
        public string AccountNumber { get; set; }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        
    }

    private static DataSet GetData()
    {
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr2"].ConnectionString;
            SqlDataAdapter cmd1 = new SqlDataAdapter("SELECT * FROM [AdventureWorks].[Sales].[SalesOrderHeader] where SalesOrderID < 43670;", conn);
            cmd1.Fill(ds, "SalesOrderHeader");
            SqlDataAdapter cmd2 = new SqlDataAdapter("SELECT * FROM [AdventureWorks].[Sales].[SalesOrderDetail] d, [AdventureWorks].[Sales].[SalesOrderHeader] a where d.SalesOrderID = a.SalesOrderID and a.SalesOrderID < 43670;", conn);
            cmd2.Fill(ds, "SalesOrderDetail");
        }
        ds.Relations.Add("myrelation", ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"], ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
        return ds;
    }

    

    [WebMethod]
    public static List<SalesOrderHeader> getSalesOrderHeader()
    {
        DataSet ds = GetData();
        DataTable dt = ds.Tables["SalesOrderHeader"];
        DataTable dt2 = ds.Tables["SalesOrderDetail"];

        List<SalesOrderHeader> SalesOrderSHeader = new List<SalesOrderHeader>();
        foreach (DataRow dr in dt.Rows)
        {
            List<SalesOrderDetail> SalesOrderSDetail = new List<SalesOrderDetail>();
            SalesOrderHeader _SalesOrderHeader = new SalesOrderHeader();
            _SalesOrderHeader.SalesOrderID = int.Parse(dr["SalesOrderID"].ToString());
            _SalesOrderHeader.SalesOrderNumber = dr["SalesOrderNumber"].ToString();
            _SalesOrderHeader.PurchaseOrderNumber = dr["PurchaseOrderNumber"].ToString();

            //Filter the Datatable to contain records having SalesOrderID equal to master table's SalesOrderID
            DataRow[] dt3 = dt2.Select("SalesOrderID = " + _SalesOrderHeader.SalesOrderID);
            for (int i = 0; i < dt3.Length; i++)
            {
                SalesOrderDetail _SalesOrderDetail = new SalesOrderDetail();
                _SalesOrderDetail.SalesOrderID = int.Parse(dt3[i]["SalesOrderID"].ToString());
                _SalesOrderDetail.CarrierTrackingNumber = dt3[i]["ProductID"].ToString();
                _SalesOrderDetail.AccountNumber = dt3[i]["PurchaseOrderNumber"].ToString();
                SalesOrderSDetail.Add(_SalesOrderDetail);
            }
            _SalesOrderHeader.SalesOrderDetails = SalesOrderSDetail;
            SalesOrderSHeader.Add(_SalesOrderHeader);
        }
        return SalesOrderSHeader;
    }

    
}

{#foreach $T as currentOrder}
            
<h1> SalesOrderID: {$T.currentOrder.SalesOrderID} </h1>
<li> SalesOrderNumber: {$T.currentOrder.SalesOrderNumber} </li>
<li> PurchaseOrderNumber: {$T.currentOrder.PurchaseOrderNumber} </li>
                        
<h4>Items</h4>            
<ul>
    {#foreach $T.currentOrder.SalesOrderDetails as _SalesOrderDetails}
        <li>{$T._SalesOrderDetails.CarrierTrackingNumber} </li>
        <li>{$T._SalesOrderDetails.AccountNumber} </li>
    {#/for}
</ul>            
            
{#/for}

Advertisements
This entry was posted in JQuery. Bookmark the permalink.

One Response to Nested JTemplate with DataSet Hierarchical Data Binding

  1. nabinpadhi says:

    Could you please share the live example link or project ?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s