Friday, 25 October 2013

JSON: Create Cascading DropDownList from Database using JQuery in MVC 4 Razor

Hi friends,in this article I will explain about JSON: Create Cascading DropDownList from Database using JQuery and Ajax in MVC 4 Razor.
In previous articles I already explained about How to Bind Data to DropDownList from Database using Stored Procedure in MVC 4 Razor,How to bind DropDownList from database in C# MVC 4 razor and How to Create Cascading DropDownList in MVC 4 Razor and JQuery.
Create the Get_Country Stored procedure:
CREATE PROCEDURE [dbo].[Get_Country]
AS
BEGIN
      SET NOCOUNT ON;
    SELECT * FROM country order by ID Asc
END


Write the below code in Account.cs model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MvcCRUD.Models
{
    public class Account
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        public DataSet Bind2DDL()
        {
            StudentContext db = new StudentContext();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);
            SqlCommand cmd = new SqlCommand("Get_Country", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        public DataSet BindStates(string country)
        {
            StudentContext db = new StudentContext();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);
            SqlCommand cmd = new SqlCommand("Select * from States where Country_Name='" + country + "'  order by id asc", con);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
    }
}

Create Controller as DropDownList Controller and write the below code in it.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcCRUD.Models;
using System.Data;
using System.Data.SqlClient;

namespace MvcCRUD.Controllers
{
    public class DropDownListController : Controller
    {
        public ActionResult Bind2Ddl()
        {
            Account obj = new Account();
            DataSet ds = obj.Bind2DDL();
            List<SelectListItem> country = new List<SelectListItem>();
            country.Add(new SelectListItem { Text = "Select Country", Value = "Select" });       
            foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
            {
                country.Add(new SelectListItem { Text = @dr["NAME"].ToString(), Value = @dr["NAME"].ToString() });             
            }
            ViewBag.country = country;      
            return View();
        }
        public JsonResult GetStates(string country)
        {
            Account obj = new Account();
            DataSet ds = obj.BindStates(country);
            List<string> states = new List<string>();
            states.Add("Select State");
            foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
            {               
               states.Add(@dr["State_name"].ToString ());
            }
            return Json(states);
        }
   
    }
}

Create View Bind2Ddl.cshtml and write the below code in it.
@model MvcCRUD.Models.Account
@{
    ViewBag.Title = "Bind Data to DropDownList from Database using Stored Procedure in MVC 4 Razor";
}
<h3 style='color:Green'>Cascading DropDownList from Database using JQuery in MVC 4 Razor</h3><br/>
country
@Html.DropDownList("country")
State
<select id="State" name="State"></select>

<input type ="submit" value="Submit" id="btn" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.js" type ="text/javascript" ></script>
<script type="text/javascript">
    $(document).ready(function () {
        $("#btn").click(function () {
            alert("You have Selected Country as " + $("#country").val() + " and State as " + $("#State").val());
        });
        $("#State").prop("disabled", true);
        $("#country").change(function () {
            if ($("#country").val() != "Select Country" && $("#country").val() != null) {
                var options = {};
                options.url = "/DropDownList/GetStates";
                options.type = "POST";
                options.data = JSON.stringify({ country: $("#country").val() });
                options.dataType = "json";
                options.contentType = "application/json";
                options.success = function (States) {
                    $("#State").empty();
                    for (var i = 0; i < States.length; i++) {
                        $("#State").append("<option>" + States[i] + "</option>");
                    }
                    $("#State").prop("disabled", false);
                };
                options.error = function () { alert("Error retrieving States!"); };
                $.ajax(options);
            }
            else {
                $("#State").empty();
                $("#State").prop("disabled", true);
            }
        });
    });
</script>


The output of the above code as shown in the below figures.
The Country DropDownList will be bind as sown in the below figure.

The State DropDownList will be bind as sown in the below figure.

When we click on Submit button as shown in the below figure.
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles."
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog for more articles."

2 comments:

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.