Sunday, August 28, 2011

Menu Control From Database 

CREATE TABLE [dbo].[SAMPLECATEGORIES]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[parentid] [int] NULL,
[title] [nvarchar] (255) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NOT NULL
)

-------------------------------------------------------------------------------------------

SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @ptrval binary(16)

BEGIN TRANSACTION
ALTER TABLE [dbo].[SAMPLECATEGORIES] DROP CONSTRAINT [FK_SAMPLECATEGORIES_SAMPLECATEGORIES]
SET IDENTITY_INSERT [dbo].[SAMPLECATEGORIES] ON
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (1, NULL, N'Category 1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (2, NULL, N'Category 2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (3, NULL, N'Category 3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (4, 1, N'Category 1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (5, 1, N'Category 1.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (6, 2, N'Category 2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (7, 2, N'Category 2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (8, 2, N'Category 2.3')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (9, 7, N'Category 2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (10, 7, N'Category 2.2.2')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (11, 10, N'Category 2.2.2.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (12, 6, N'Category 2.1.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (13, 3, N'Category 3.1')
INSERT INTO [dbo].[SAMPLECATEGORIES] ([id], [parentid], [title]) VALUES (14, 7, N'Category 2.2.3')
SET IDENTITY_INSERT [dbo].[SAMPLECATEGORIES] OFF
ALTER TABLE [dbo].[SAMPLECATEGORIES] ADD CONSTRAINT [FK_SAMPLECATEGORIES_SAMPLECATEGORIES] FOREIGN KEY ([parentid]) REFERENCES [dbo].[SAMPLECATEGORIES] ([id])
COMMIT TRANSACTION


------------------------------------------------------------------------------------------------------

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
      ,[parentid]
      ,[title]
  FROM [master].[dbo].[SAMPLECATEGORIES]



Menu3.aspx
------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Menu3.aspx.cs" Inherits="Menu3" %>
<!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>
</head>
<body>
    <form id="form1" runat="server">
     <div>
         <asp:TreeView  
                ID="TreeView1"
                ExpandDepth="0" 
                PopulateNodesFromClient="true" 
                ShowLines="true" 
                ShowExpandCollapse="true" 
                runat="server"
                OnTreeNodePopulate="TreeView1_TreeNodePopulate"  />
    </div>
    </form>
</body>
</html>
Menu3.aspx.cs
---------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public partial class Menu3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            PopulateRootLevel();
    }
    private void PopulateRootLevel()
    {
        SqlConnection objConn = new SqlConnection(@"Data Source=truefaster\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
        SqlCommand objCommand = new SqlCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID IS NULL", objConn);
        SqlDataAdapter da = new SqlDataAdapter(objCommand);
        DataTable dt = new DataTable();
        da.Fill(dt);
        PopulateNodes(dt, TreeView1.Nodes);
    }
    private void PopulateSubLevel(int parentid, TreeNode parentNode)
    {
        SqlConnection objConn = new SqlConnection(@"Data Source=truefaster\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
        SqlCommand objCommand = new SqlCommand(@"select id,title,(select count(*) FROM SampleCategories WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID=@parentID", objConn);
        objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid;
        SqlDataAdapter da = new SqlDataAdapter(objCommand);
        DataTable dt = new DataTable();
        da.Fill(dt);
        PopulateNodes(dt, parentNode.ChildNodes);
    }
    protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
    {
        PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
    }
    private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
    {
        foreach (DataRow dr in dt.Rows)
        {
            TreeNode tn = new TreeNode();
            tn.Text = dr["title"].ToString();
            tn.Value = dr["id"].ToString();
            nodes.Add(tn);
            //If node has child nodes, then enable on-demand populating
            tn.PopulateOnDemand = ((int)(dr["childnodecount"]) > 0);
        }
    }
}


output:

Menu From Database  









Posted by
simple money technique


at
3:02 AM





















Labels:
,





















0
comments:
        












Post a Comment