Step 1. Create Table for State.
CREATE TABLE [dbo].[States](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[StateName] [varchar](50) NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Step 2. Insert some record for State Table.
SET IDENTITY_INSERT [dbo].[States] ON
INSERT [dbo].[States] ( [StateName]) VALUES ( N'Odisha')
INSERT [dbo].[States] ( [StateName]) VALUES ( N'West Bengal')
INSERT [dbo].[States] ( [StateName]) VALUES ( N'Bihar')
INSERT [dbo].[States] ( [StateName]) VALUES ( N'Jharkhand')
INSERT [dbo].[States] ( [StateName]) VALUES ( N'MP')
SET IDENTITY_INSERT [dbo].[States] OFF
Step 3. Now Create table For District.
CREATE TABLE [dbo].[Districts](
[DistrictId] [int] IDENTITY(1,1) NOT NULL,
[StateId] [int] NOT NULL,
[DistrictName] [varchar](50) NULL,
CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED
(
[DistrictId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Districts] WITH CHECK ADD CONSTRAINT [FK_Districts_States1] FOREIGN KEY([StateId])
REFERENCES [dbo].[States] ([StateId])
GO
ALTER TABLE [dbo].[Districts] CHECK CONSTRAINT [FK_Districts_States1]
Step 4. Insert some record for Distrct Table.
SET IDENTITY_INSERT [dbo].[Districts] ON
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 1, N'Mayurbhanj')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 1, N'Keonjhar')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 1, N'Khorda')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 1, N'Balasore')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 2, N'Hoogly')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 2, N'Howrah')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 2, N'Jalpaiguri')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 3, N'Aurangabad')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 3, N'Patna')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 4, N'Bokaro')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 5, N'Bhopal')
INSERT [dbo].[Districts] ( [StateId], [DistrictName]) VALUES ( 5, N'Itarsi')
SET IDENTITY_INSERT [dbo].[Districts] OFF
Step 5. Now writing this Query for Dropdown List.
SELECT '- '+DistrictName AS TextField,DistrictId AS ValueField,StateId FROM DBO.Districts
UNION SELECT UPPER(StateName),0,StateId FROM dbo.States ORDER BY StateId,DistrictId
Step 6. Now open your aspx. page in visual studio.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="drpDistricts" runat="server"></asp:DropDownList>
</div>
</form>
</body>
</html>
Step 6. Write here your Source Code in C#.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default4 : System.Web.UI.Page
{
string connetionString = "Data Source=.;Initial Catalog=rupa;User ID=sa;Password=123";
protected void Page_Load(object sender, EventArgs e)
{
string sql = "SELECT '- '+DistrictName AS TextField,DistrictId AS ValueField,StateId FROM DBO.Districts UNION SELECT UPPER(StateName),0,StateId FROM dbo.States ORDER BY StateId,DistrictId";
drpDistricts.DataTextField = "TextField";
drpDistricts.DataValueField = "ValueField";
drpDistricts.DataSource = getData(sql);
drpDistricts.DataBind();
foreach (ListItem item in drpDistricts.Items)
{
if (item.Value.ToString() == "0")
{
item.Attributes.Add("Style", "color:red");
item.Attributes.Add("Disabled", "true");
}
}
}
private DataTable getData(string sql)
{
SqlDataAdapter adapter = new SqlDataAdapter();
DataTable dTable = new DataTable();
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(dTable);
adapter.Dispose();
command.Dispose();
connection.Close();
return dTable;
}
}
Out will be display Like Bellow.
No comments:
Post a Comment