Search This Blog

2009-12-23

Binding ASP .NET Treeview with LINQ to Stored Procedure

//--------------------------Table Structure---------------------------
USE [WMMM]
GO
/****** Object: Table [dbo].[MenuMaster] Script Date: 12/23/2009 16:41:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MenuMaster](
[ID] [bigint] NOT NULL,
[Name] [varchar](50) NULL,
[ParentID] [bigint] NULL,
CONSTRAINT [PK_MenuMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

//-------------------------Stored Proc To Retive Data----------------------
USE [WMMM]
GO
/****** Object: StoredProcedure [dbo].[spMenuMasterSelect] Script Date: 12/23/2009 16:43:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spMenuMasterSelect]
AS
BEGIN
SET NOCOUNT ON;
SELECT ID,Name,ParentID from dbo.MenuMaster
END

GO
SET ANSI_PADDING OFF
//----------------------Code To Create Treeview---------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Web.UI.WebControls;
using DAL;

/// <summary>
/// Summary description for MenuBLL
/// </summary>
///

public class MenuBLL : IDisposable
{
WMMMDataContext db;
//list of all nodes
SortedList<int, MenuMaster> myMenus = new SortedList<int, MenuMaster>();
//list of all created nodes
SortedList<int, TreeNode> myCreatedNodes = new SortedList<int, TreeNode>();

public MenuBLL()
{
//
// TODO: Add constructor logic here
//
}
public void CreateMenu(MenuBO objMenuBo)
{
TreeView TreeView1 = objMenuBo.LeftTreeView;
TreeView1.Nodes.Clear();
db = new WMMMDataContext();
var found = from o in db.spMenuMasterSelect()
select new { o.ID, o.Name, o.ParentID };
MenuMaster mnuMaster = null;
foreach (var result in found)
{
mnuMaster = new MenuMaster();
mnuMaster.MenuID = Convert.ToInt32(result.ID);
mnuMaster.MenuName = result.Name;
mnuMaster.MenuParentId = Convert.ToInt32(result.ParentID);
myMenus.Add(mnuMaster.MenuID, mnuMaster);

}
TreeNode aNode = null;
foreach (int akey in myMenus.Keys)
{
string code = myMenus[akey].MenuID.ToString();
aNode = new TreeNode(myMenus[akey].MenuName, code);
CreateNode(aNode, TreeView1);
}
}
public void CreateNode(TreeNode aNode, TreeView TreeView1)
{
//This list stores all the nodes id from the current node to the ultimate parent
List<int> myPath = new List<int>();
if (!myCreatedNodes.ContainsValue(aNode))//if the node was not alreazdy created
{
int nodeId = 1001;
nodeId = Convert.ToInt32(aNode.Value);
//Building the current node path untill the ultimate parent
myPath.Add(nodeId);
while (nodeId != 0)
{
if (nodeId != 0)
{
nodeId = myMenus[nodeId].MenuParentId;
myPath.Add(nodeId);
}
}
}
//descending from Ultimate parent until the node
//if the current node does not exists we create it and add it to created nodes collection.
//if it has not a parent we add it to the treeview
//if it has a parent,the parent was already created because we come from it, so we add the current node to the parent.
TreeNode nodeToAdd = null, ParentNodeTofind = null;
for (int j = myPath.Count - 1; j > -1; j--)
{
if (myPath[j] != 0)
{
//checking for each path if the nodes was already created
if (!myCreatedNodes.Keys.Contains(myMenus[myPath[j]].MenuID))
{
//creating the node and adding it to the created nodes collection.
nodeToAdd = new TreeNode(myMenus[myPath[j]].MenuName, myMenus[myPath[j]].MenuID.ToString());
nodeToAdd.NavigateUrl = "../Nodes/Dashboard.aspx?NodeID=" + myMenus[myPath[j]].MenuID.ToString();
myCreatedNodes.Add(myMenus[myPath[j]].MenuID, nodeToAdd);
int parentId = myMenus[myPath[j]].MenuParentId;
//checking if the node has a parent
if (parentId == 0)//this node has no parent we add it to the tree view
{
TreeView1.Nodes.Add(nodeToAdd);
}

else//this node has a parent
{
//rerieving parent node (sure to find it)
ParentNodeTofind = myCreatedNodes[myMenus[myPath[j]].MenuParentId];
//we add the node to its parent childNodes
ParentNodeTofind.ChildNodes.Add(nodeToAdd);
}
}
}
}
}

#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

#endregion
private bool disposedValue = false;// To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
}
// TODO: free managed resources when explicitly called
//if (dbCommand != null)
//{
// dbCommand.Dispose();
// //GC.SuppressFinalize(dbCommand);
//}

}
// TODO: free shared unmanaged resources
this.disposedValue = true;
}
}
public class MenuMaster
{

private string _MenuName;
private int _MenuID;
private int _MenuParentId;


public string MenuName
{
get { return _MenuName; }

set { _MenuName = value; }
}
public int MenuID
{
get { return _MenuID; }
set { _MenuID = value; }
}
public int MenuParentId
{
get { return _MenuParentId; }
set { _MenuParentId = value; }
}
}


//--------------------------Tree View in ASPX Page---------------
<asp:TreeView ID="tvLeftMenu" runat="server" CssClass="input-value" ShowLines="True"
BackColor="Transparent" BorderColor="DarkGray" Font-Bold="True" Font-Size="Small"
ForeColor="Black" ExpandDepth="13" ShowExpandCollapse="false" Width="100px" Font-Names="Times New Roman">
</asp:TreeView>

//-----------------------SQL Table Data-----------------

//------------------------Output Of the Treeview-------------------

//------------------------For LINQ To Stored Proc Visit the following URL
Introduction to Linq To Stored Procedure

No comments: