Procedure
ALTER PROCEDURE dbo.p
AS
begin
select m.eid,m.name,m.mgr,(select count(*) from emp where emp.mgr=m.eid) ccount from emp m left join emp t on m.mgr=t.eid
end
design code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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 language="javascript" type="text/javascript">
function OnTreeClick(evt) {
var src = window.event != window.undefined ? window.event.srcElement : evt.target;
var isChkBoxClick = (src.tagName.toLowerCase() == "input" && src.type == "checkbox");
if (isChkBoxClick) {
//alert(src.value);
var parentTable = GetParentByTagName("table", src);
var nxtSibling = parentTable.nextSibling;
//check if nxt sibling is not null & is an element node
if (nxtSibling && nxtSibling.nodeType == 1) {
if (nxtSibling.tagName.toLowerCase() == "div")
//if node has children
{
//check or uncheck children at all levels
CheckUncheckChildren(parentTable.nextSibling, src.checked);
}
}
//check or uncheck parents at all levels
CheckUncheckParents(src, src.checked);
}
}
function CheckUncheckChildren(childContainer, check) {
var childChkBoxes = childContainer.getElementsByTagName("input");
var childChkBoxCount = childChkBoxes.length;
for (var i = 0; i < childChkBoxCount; i++) {
childChkBoxes[i].checked = check;
}
}
function CheckUncheckParents(srcChild, check) {
var parentDiv = GetParentByTagName("div", srcChild);
var parentNodeTable = parentDiv.previousSibling;
if (parentNodeTable) {
var checkUncheckSwitch;
if (check) //checkbox checked
{
var isAllSiblingsChecked = AreAllSiblingsChecked(srcChild);
if (isAllSiblingsChecked)
checkUncheckSwitch = true;
else
return; //do not need to check parent if any(one or more) child not checked
}
else //checkbox unchecked
{
checkUncheckSwitch = false;
}
var inpElemsInParentTable = parentNodeTable.getElementsByTagName("input");
if (inpElemsInParentTable.length > 0) {
var parentNodeChkBox = inpElemsInParentTable[0];
parentNodeChkBox.checked = checkUncheckSwitch; //do the same recursively
CheckUncheckParents(parentNodeChkBox, checkUncheckSwitch);
}
}
}
function AreAllSiblingsChecked(chkBox) {
var parentDiv = GetParentByTagName("div", chkBox);
var childCount = parentDiv.childNodes.length;
for (var i = 0; i < childCount; i++) {
if (parentDiv.childNodes[i].nodeType == 1) {
//check if the child node is an element node
if (parentDiv.childNodes[i].tagName.toLowerCase() == "table") {
var prevChkBox = parentDiv.childNodes[i].getElementsByTagName("input")[0];
//if any of sibling nodes are not checked,
return false
if (!prevChkBox.checked) {
return false;
}
}
}
}
return true;
}
//utility function to get the container of an element by tagname
function GetParentByTagName(parentTagName, childElementObj) {
var parent = childElementObj.parentNode;
while (parent.tagName.toLowerCase() != parentTagName.toLowerCase()) {
parent = parent.parentNode;
}
return parent;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="sm" runat="server"></asp:ScriptManager>
<asp:UpdatePanel ID="up" runat="server">
<ContentTemplate>
<div>
<table align="center">
<tr><td>EID</td><td><asp:TextBox ID="tb" runat="server"></asp:TextBox></td></tr>
<tr><td>NAME</td><td><asp:TextBox ID="tb1" runat="server"></asp:TextBox></td></tr>
<tr><td>MGR</td><td><asp:DropDownList ID="ddl" runat="server"></asp:DropDownList></td></tr>
<tr><td> </td><td><asp:Button ID="btn" runat="server" Text="Save" Width="80"
onclick="btn_Click"/></td></tr>
<tr><td> </td><td><asp:TreeView ID="tv" runat="server" ShowCheckBoxes="All"
ontreenodepopulate="tv_TreeNodePopulate"></asp:TreeView></td></tr>
</table>
</div>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>
c# code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
#region
DataClassesDataContext o = new DataClassesDataContext();
#endregion
protected void Page_Load(object sender, EventArgs e)
{
tv.Attributes.Add("onclick", "OnTreeClick(event)");
if (!IsPostBack)
{
fill();
filltv();
tv.CollapseAll();
}
}
void fill()
{
var m = (from x in o.EMPs select new {x.EID,x.NAME}).ToList();
ddl.DataSource = m;
ddl.DataTextField = "NAME";
ddl.DataValueField = "EID";
ddl.DataBind();
}
void filltv()
{
tv.Nodes.Clear();
var m=(from x in o.p() where x.mgr==0 select new {x.eid,x.name,x.ccount}).ToList();
for (int i = 0; i < m.Count; i++)
{
TreeNode tn = new TreeNode();
tn.Text = m[i].name;
tn.Value = m[i].eid.ToString();
tv.Nodes.Add(tn);
tn.PopulateOnDemand = false;
if (m[i].ccount > 0)
tn.PopulateOnDemand = true;
}
}
protected void btn_Click(object sender, EventArgs e)
{
EMP p = new EMP();
p.EID = Convert.ToInt32(tb.Text);
p.NAME = tb1.Text;
p.MGR = Convert.ToInt32(ddl.SelectedValue);
o.EMPs.InsertOnSubmit(p);
o.SubmitChanges();
ScriptManager.RegisterClientScriptBlock(this, GetType(), "x", "alert('One record saved.')", true);
fill();
filltv();
}
protected void tv_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
var m = (from x in o.p() where x.mgr == Convert.ToInt32(e.Node.Value) select new {x.eid,x.name,x.ccount }).ToList();
for(int i=0;i<m.Count;i++)
{
TreeNode tn = new TreeNode();
tn.Value = m[i].eid.ToString();
tn.Text = m[i].name;
e.Node.ChildNodes.Add(tn);
tn.PopulateOnDemand = false;
if (m[i].ccount > 0)
tn.PopulateOnDemand = true;
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
