ASP DOT NET

Tuesday, August 21, 2018

Q. How to bind treeview with database using asp.net C#?

Step 1.Create two table named VehicleTypes and VehicleSubTypes and insert some record?

CREATE TABLE [dbo].[VehicleTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
 CONSTRAINT [PK_VehicleTypes] 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]


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

CREATE TABLE [dbo].[VehicleSubTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[VehicleTypeId] [int] NULL,
 CONSTRAINT [PK_VehicleSubTypes] 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]

ALTER TABLE [dbo].[VehicleSubTypes]  WITH CHECK ADD  CONSTRAINT [FK_VehicleSubTypes_VehicleSubTypes] FOREIGN KEY([VehicleTypeId])
REFERENCES [dbo].[VehicleTypes] ([ID])
GO
ALTER TABLE [dbo].[VehicleSubTypes] CHECK CONSTRAINT [FK_VehicleSubTypes_VehicleSubTypes]

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

INSERT [dbo].[VehicleTypes] ([ID], [Name]) VALUES (1, N'Cars')
INSERT [dbo].[VehicleTypes] ([ID], [Name]) VALUES (2, N'Bikes')
INSERT [dbo].[VehicleTypes] ([ID], [Name]) VALUES (3, N'Heavy Vehicle')
-----------------------------------------------------------------

INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (1, N'Alto', 1)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (2, N'WagnoR', 1)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (3, N'Scorpio', 1)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (4, N'Duster', 1)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (5, N'Avenger', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (6, N'Dicover', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (7, N'Unicorn', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (8, N'Karizma', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (9, N'Hero', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (10, N'Palatina', 2)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (11, N'Maruti Suzuki', 1)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (12, N'Truck', 3)
INSERT [dbo].[VehicleSubTypes] ([ID], [Name], [VehicleTypeId]) VALUES (13, N'Luxery Buses', 3)
SET IDENTITY_INSERT [dbo].[VehicleSubTypes] OFF
SET IDENTITY_INSERT [dbo].[VehicleTypes] ON


Step 2. Now open visual studio and create aspx page.

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 

    <style type="text/css">
        .nodeLevel1
        {
            font:40px Arial,Sans-Serif;
        }
        .nodeLevel2
        {
            font:20px Arial,Sans-Serif;
        }
        .nodeLevel3
        {
            font:10px Arial,Sans-Serif;
        }     
       
        .treeNode
        {
            transition: all .3s;
            padding: 12px 5px;
            text-align: left;
            /*width: 100%;*/
            margin: 0;
            min-width: 250px !important;
            border: 2px solid #8e44ad;
            text-decoration:none !important;
            color:black;
            /*color:blue;
            font:14px Arial, Sans-Serif;*/
        }
     .rootNode
        {
            font-size:18px;
            /*width:100%;*/
            border-bottom:Solid 1px black;
            color:#337ab7;
        }
     .leafNode {
            border: Dotted 2px black;
            padding: 10px;
            background-color: #eeeeee;
            font-weight: bold;
        }
     .selectNode
     {
        background-color:white;
        border:Dotted 2px black;
        font-weight:bold;
        color:#fff;
     }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <h3>Vehicle Details</h3>
        <hr />
        <asp:TreeView ID="TreeView1" runat="server" ImageSet="XPFileExplorer" NodeIndent="15"  NodeStyle-CssClass="treeNode"
    RootNodeStyle-CssClass="rootNode" ExpandDepth="1"  Font-Size="Small" HoverNodeStyle-CssClass="nodeLevel2"
    LeafNodeStyle-CssClass="leafNode" SelectedNodeStyle-CssClass="selectNode">

            <LevelStyles>
                <asp:TreeNodeStyle CssClass="nodeLevel1" />
                <asp:TreeNodeStyle CssClass="nodeLevel2" />
                <asp:TreeNodeStyle CssClass="nodeLevel3" />
                <asp:TreeNodeStyle CssClass="AspNet-TreeView-Collapse" />
                <asp:TreeNodeStyle CssClass="AspNet-TreeView-Expand" />
            </LevelStyles>

            <HoverNodeStyle Font-Underline="True" ForeColor="#6666AA" />
            <NodeStyle Font-Names="Tahoma" Font-Size="8pt" ForeColor="Black" HorizontalPadding="2px"
                NodeSpacing="0px" VerticalPadding="2px"></NodeStyle>
            <ParentNodeStyle Font-Bold="False" />
            <SelectedNodeStyle BackColor="#B5B5B5" Font-Underline="False" HorizontalPadding="0px" VerticalPadding="0px" />
        </asp:TreeView>
    </form>
</body>
</html>


step 3. now write c# code into code behind 

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.Configuration;
public partial class TreeView : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            DataTable dt = this.GetData("SELECT Id, Name FROM VehicleTypes");
            this.PopulateTreeView(dt, 0, null);
        }
    }
    private void PopulateTreeView(DataTable dtParent, int parentId, TreeNode treeNode)
    {
        foreach (DataRow row in dtParent.Rows)
        {
            TreeNode child = new TreeNode
            {
                Text = row["Name"].ToString(),
                Value = row["Id"].ToString()
            };
            if (parentId == 0)
            {
                TreeView1.Nodes.Add(child);
                DataTable dtChild = this.GetData("SELECT Id, Name FROM VehicleSubTypes WHERE VehicleTypeId = " + child.Value);
                PopulateTreeView(dtChild, int.Parse(child.Value), child);
            }
            else
            {
                treeNode.ChildNodes.Add(child);
            }
        }
    }
    private DataTable GetData(string query)
    {
        DataTable dt = new DataTable();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }
            return dt;
        }
    }
} 


OutPut.









at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to to select duplicate rows from sql server?

 SELECT * FROM Recruitment WHERE Email IN (SELECT Email FROM Recruitment GROUP BY Email HAVING COUNT(*) > 1); WITH CTE AS (     SELECT   ...