Thursday 10 May 2012

Cascading DropDownList in ASP.NET using database



Description :


        Asp.Net Dropdownlist selection on client side using Web-service, Ajax call and database.



Database :

CREATE TABLE [dbo].[Country](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NULL,   
      [ParentId] [int] NULL, 
 CONSTRAINT [PK_Products] 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]

GO
SET IDENTITY_INSERT [dbo].[Country] ON
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (1, N'India', NULL)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (2, N'AndhraPradesh', 1)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (3, N'Karnataka', 1)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (4, N'Tamilnadu', 1)

INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (5, N'USA', NULL)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (6, N'California', 5)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (7, N'Washington', 5)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (7, N'Florida', 5)


INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (8, N'Oman', NULL)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (9, N'Zufar', 8)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (10, N'Masqat', 8)
INSERT [dbo].[Country] ([Id], [Name], [ParentId]) VALUES (10, N'al-Batina', 8)

SET IDENTITY_INSERT [dbo].[Country] OFF


In .aspx Page:-

<head runat="server">
    <script src="../Javascript/jquery-1.6.4.min.js" type="text/javascript"></script>
    <title></title>
    <script type="text/javascript">
        var pageUrl = '<%=ResolveUrl("~/Cascadingddl.aspx")%>';
        function ProjectChanged() {
            $.ajax({
                type: "POST",
                url: pageUrl + '/GetCountrySummary',
                data: '{Id: ' + $('#<%=ddlCountry.ClientID%>').val() + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: CountryChangedCallBack
            });
        }
        function CountryChangedCallBack (response) {
            var CountryList = response.d;
            $("#<%=ddlState.ClientID%>").empty();
            $('#<%= ddlState.ClientID%>').append(new Option('--Select--', '0'));
            $.each(CountryList, function (index) {
                var Country = CountryList [index];
                $('#<%= ddlState.ClientID%>').append(new Option(Country.name, Country.id));
            });
        }

        function StateChanged() {
            debugger;
            var CountryName = $('#<%= ddlState.ClientID %> option:selected').text();
            alert("Country Name : " + CountryName + " and Id :" + $('#<%= ddlState.ClientID%>').val());

        }

    </script>
</head>
<body>
    <form id="form1" runat="server">
    <table>
        <tr>
            <td>
                Select Country:
            </td>
            <td>
                <asp:DropDownList ID=" ddlCountry " onchange="CountryChanged();" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Select Product :
            </td>
            <td>
                <asp:DropDownList ID=" ddlState " runat="server" onchange=" StateChanged ();" />
            </td>
        </tr>
    </table>
    </form>
</body>

In .Cs Page:-

String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            String strQuery = "Select Id,Name from Country Where Id is Null";
            // Fill The Country Dropdownlist in Form_Load
        }
    } 


    [System.Web.Services.WebMethod]

    public static System.Collections.ArrayList GetCountrySummary(int Id)
    {
        System.Collections.ArrayList list = new System.Collections.ArrayList();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        String strQuery = "Select Id,Name from Country Where Id="+Id;
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strConnString))
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = strQuery;
                cmd.Connection = con;
                con.Open();
                System.Data.SqlClient.SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    var Country = new
                    {
                        Cid = sdr["id"],
                        Cname = sdr["Name"]                        
                    };
                    list.Add(Country);
                }
                con.Close();
            }
        }
        return list;
    }




No comments:

Post a Comment