APS .NET MVC Cascading DropdownList Using LINQ

Cascading Dropdown Lists Using LINQ 

Let us start with an example Country, State, City.. 

Step 1 : Go to VisualStudio2010>>NewProject >> Give name as 'Cascading using Linq' 

Step 2 :Creating Database and tables 

First we have to change the connection string in the WebConfig for that add following changes 

<connectionstrings>

    <add name="Leedhar_CampusIndya" connectionstring="Data Source=LEEDHAR1-PC\SQLEXPRESS;Database=Leedhar_Cascading;uid=asd;pwd=asd;Trusted_Connection=False;Persist Security Info=True" providername="System.Data.SqlClient"></add>

  </connectionstrings>


Next for creating the tables for Country State City 

Goto>> Models>>Add>> Class>> Give name as 'Leedhar_Cascading.cs' 

Add following code for creating the tables 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;


namespace Cascading_using_Linq.Models
{
    public class Leedhar_Cascading:DbContext
    {
        public DbSet Country { get; set; }
        public DbSet State { get; set; }
        public DbSet City { get; set; }
    }
}

Then create class for Country, State, City in the Models 

In the Country.cs add following codes for creating the fields for the table Country 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
namespace Cascading_using_Linq.Models
{
   public class Country
    {

        [Key]
        public virtual int CountryCode { get; set; }
        public virtual int CountryNumber { get; set; }
        [Required]
        public virtual String CountryName { get; set; }
        public virtual ICollection State { get; set; }
    }
}




In the State add following codes 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
namespace Cascading_using_Linq.Models
{
   public class State
    {
        [Key]
        public virtual int StateCode { get; set; }
        [Required]
        public virtual String StateName { get; set; }
        public virtual ICollection City { get; set; }
    }
}





Next add following codes in the City.cs for adding the fields in the table City 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace Cascading_using_Linq.Models
{
   public class City
    {
        [Key]
        public virtual int CityCode { get; set; }
        [Required]
        public virtual String CityName { get; set; }
       
    }
}


Next for searching and sorting the Country,State, City from the database we have to add an 'ExtensionMethods.cs' in the Models 

Next add following codes to the class 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Linq.Expressions;
using System.Data.Linq.SqlClient;

namespace Cascading_using_Linq.Models
{
    public static class ExtensionMethods
    {
 
        public static IQueryable OrderBy(this IQueryable source, string propertyName, bool asc)
        {
            var type = typeof(T);
            string methodName = asc ? "OrderBy" : "OrderByDescending";
            var property = type.GetProperty(propertyName);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExp = Expression.Lambda(propertyAccess, parameter);
            MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
            return source.Provider.CreateQuery(resultExp);
        }

        public static IQueryable Like(this IQueryable source, string propertyName, string keyword)
        {
            var type = typeof(T);
            var property = type.GetProperty(propertyName);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var constant = Expression.Constant("%" + keyword + "%");
            MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Like", new Type[] { typeof(string), typeof(string) }), propertyAccess, constant);
            Expression<>> lambda = Expression.Lambda<>>(methodExp, parameter);
            return source.Where(lambda);
        }
    }
}



Step 3 :Creating View Pages 

Goto>>Controller >>Add>>Controller>> Give name as 'AdminController' 

In the 'AdminController.cs' right click on index>> Add view>>give name as 'Country.cshtml' give the master page . Click Add button 

Next in the masterpage we have to give the flexigrid.css and flexigrid.js for displaying the country . 

Next in the Country.cshtml page add following codes for Add Delete Edit and View the Country 


@{
    ViewBag.Title = "Country";
    Layout = "~/Views/Shared/_Layout.cshtml";
}


<link href="../../Content/themes/dev/jquery-ui-1.8.21.custom.css" rel="stylesheet" type="text/css">


<div style="padding-left:150px; padding-top:50px; padding-bottom:50px;">
<table id="flex2" style="display:none">
</table>
</div>
<script type="text/javascript">
    $("#flex2").flexigrid({
        url: '/admin/Country/',
        dataType: 'json',
        colModel: [
		{ display: 'Country Code', name: 'CountryCode', width: 200, sortable: true, align: 'center', hide: true },
		{ display: 'Country Number', name: 'CountryNumber', width: 250, sortable: true, align: 'center' },
		{ display: 'Country Name', name: 'CountryName', width: 250, sortable: true, align: 'center' },
		],
        buttons: [
		{ name: 'Add', bclass: 'add', onpress: test },
		{ name: 'Edit', bclass: 'edit', onpress: test },
		{ name: 'Delete', bclass: 'delete', onpress: test },
		{ separator: true }
		],
        searchitems: [
		{ display: 'Country Name', name: 'CountryName' },
		],
        sortname: "CountryName",
        sortorder: "asc",
        usepager: true,
        title: 'Country',
        useRp: true,
        rp: 15,
        showTableToggleBtn: true,
        width: 700,
        onSubmit: addFormData,
        height: 200
    });


    //This function adds paramaters to the post of flexigrid. You can add a verification as well by return to false if you don't want flexigrid to submit			
    function addFormData() {
        //passing a form object to serializeArray will get the valid data from all the objects, but, if the you pass a non-form object, you have to specify the input elements that the data will come from
        var dt = $('#sform').serializeArray();
        $("#flex2").flexOptions({ params: dt });
        return true;
    }


    $('#sform').submit(function () {
        alert("Hello World");
        $('#flex2').flexOptions({ newp: 1 }).flexReload();
        return false;
    });


    function test(com, grid) {
        if (com == 'Delete') {


            $('#fntype').val('Delete');
            $('#CountryCode').val($('.trSelected td:eq(0)').text());
            $('#CountryNumber').val('');
            $('#CountryName').val('');


            $('.trSelected', grid).each(function () {
                var id = $(this).attr('id');
                id = id.substring(id.lastIndexOf('row') + 3);
                addFormData();$('#flex2').flexOptions({ url: '/admin/Country/'}).flexReload();
            });


            clearAll();


        } else if (com == 'Add') {


            $("#sform").dialog({
                autoOpen: false,
                show: "blind",
            });
            $("#sform").dialog("open");


            $('#fntype').val('Add');
            $('#CountryCode').val('');
            $('#CountryNumber').val('');
            $('#CountryName').val('');


        } else if (com == 'Edit') {


            $('.trSelected', grid).each(function () {
            
            $("#sform").dialog({
                autoOpen: false,
                show: "blind",
                width:400,
                height:300
            });
            $("#sform").dialog("open");




            $('#fntype').val('Edit');
            $('#CountryCode').val($('.trSelected td:eq(0)').text());
            $('#CountryNumber').val($('.trSelected td:eq(1)').text());
            $('#CountryName').val($('.trSelected td:eq(2)').text());


            
                        });








        }
    }


    function clearAll()
    {
            $('#fntype').val('');
            $('#CountryCode').val('');
            $('#CountryNumber').val('');
            $('#CountryName').val('');


    }
</script>


<div style="display:none">
<form id="sform">
<input type="hidden" id="fntype" name="fntype">
<input type="hidden" id="CountryCode" name="CountryCode">
<label style="width:15">Number </label>: <input id="CountryNumber" name="CountryNumber"><br>
<label style="width:15">Country Name </label>: <input id="CountryName" name="CountryName">
<input type="button" value="Submit" onclick="addFormData();$('#flex2').flexOptions({ url: '/admin/Country/'}).flexReload();clearAll();$('#sform').dialog('close');">
</form>
</div>


<script src="../../Scripts/Leedhar.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.validate.unobtrusive.min.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.unobtrusive-ajax.min.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-ui-1.8.11.min.js" type="text/javascript"></script>
<script src="../../Scripts/utils.js" type="text/javascript"></script>
Step 4 :Admin Controller 

Again come back to AdminController 

> Add following codes for Add, Edit, Delete and View the Country 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Linq;
using System.Web.Script.Serialization;
using System.Configuration;
using System.IO;
using Cascading_using_Linq.Models;
namespace Cascading_using_Linq.Controllers
{
    public class AdminController : Controller
    {

        Leedhar_Cascading _DB = new Leedhar_Cascading();
        public ActionResult Index()
        {
            return View();
        }

#region Country
        [HttpGet]
        public ActionResult Country(int id = 0)
        {
            return View();
        }

        [HttpPost]
        public ActionResult Country()
        {

            int page = int.Parse(Request.Form["page"]);
            int rp = int.Parse(Request.Form["rp"]);
            string qtype = Request.Form["qtype"].ToString();
            string query = Request.Form["query"].ToString();
            string sortname = Request.Form["sortname"].ToString();
            string sortorder = Request.Form["sortorder"].ToString();


            switch (Request.Form["fntype"])
            {
                case "Add":
                    if (Request.Form["CountryNumber"] != "" && Request.Form["CountryNumber"] != null)
                    {
                        int CountryNumber = Convert.ToInt32(Request.Form["CountryNumber"]);
                        _DB.Country.Add(new Country() { CountryName = Request.Form["CountryName"], CountryNumber = CountryNumber });
                        _DB.SaveChanges();
                    }
                    break;
                case "Edit":
                    if (Request.Form["CountryNumber"] != "" && Request.Form["CountryNumber"] != null)
                    {
                        int CountryCC = Convert.ToInt32(Request.Form["CountryCode"]);
                        var x = _DB.Country.Single(r => r.CountryCode == CountryCC);
                        x.CountryName = Request.Form["CountryName"];
                        x.CountryNumber = Convert.ToInt32(Request.Form["CountryNumber"]);
                        _DB.SaveChanges();
                    }
                    break;
                case "Delete":
                    if (Request.Form["CountryCode"] != "" && Request.Form["CountryCode"] != null)
                    {
                        int CountryCode = Convert.ToInt32(Request.Form["CountryCode"]);
                        _DB.Country.Remove(_DB.Country.Single(r => r.CountryCode == CountryCode));
                        _DB.SaveChanges();
                    }
                    break;
            }

            var DBList = from c in _DB.Country select c;

            if (!string.IsNullOrEmpty(sortname) && !string.IsNullOrEmpty(sortorder))
            {
                DBList = DBList.OrderBy(sortname, (sortorder == "asc"));
            }

            if (!string.IsNullOrEmpty(qtype) && !string.IsNullOrEmpty(query))
            {
                DBList = DBList.Like(qtype, query);
            }

            DBList = DBList.Skip((page - 1) * rp).Take(rp);


            var flexgrid = new
            {
                page = page,
                total = _DB.Country.Count(),
                rows = DBList
                .Select(x => new
                {
                    id = x.CountryName,
                    cell = new { x.CountryCode, x.CountryNumber, x.CountryName }
                }
                )

            };


            return Json(flexgrid, JsonRequestBehavior.AllowGet);
        }

        #endregion Country
}}

Let us Debug to get the result.. 



Step 5:Add States 

Create a State.cshtmlpage in the Admin folder within the views 

Add following codes add view delete edit state within a particular country 




Again come back to AdminController add following codes for binding the country and adding state within the country 

  public ActionResult JsonCountry()
        {
            var result = _DB.Country
                    .Select(x => new { x.CountryName, x.CountryCode });
            return Json(result, JsonRequestBehavior.AllowGet);
        }
#region State


        [HttpGet]
        public ActionResult State()
        {
            return View();
        }


        [HttpPost]
        public ActionResult State(int id = 0)
        {

            int page = int.Parse(Request.Form["page"]);
            int rp = int.Parse(Request.Form["rp"]);
            string qtype = Request.Form["qtype"].ToString();
            string query = Request.Form["query"].ToString();
            string sortname = Request.Form["sortname"].ToString();
            string sortorder = Request.Form["sortorder"].ToString();


            int CountryCode = 100000;
            switch (Request.Form["fntype"])
            {
                case "Add":
                    if (Request.Form["CountryCode"] != "" && Request.Form["CountryCode"] != null)
                    {
                        CountryCode = Convert.ToInt32(Request.Form["CountryCode"]);
                        _DB.Country.Single(r => r.CountryCode == CountryCode).State.Add(new State() { StateName = Request.Form["StateName"] });
                        _DB.SaveChanges();
                    }
                    break;
                case "Edit":
                    if (Request.Form["StateName"] != "" && Request.Form["StateName"] != null)
                    {
                        int StateCC = Convert.ToInt32(Request.Form["StateCode"]);
                        var x = _DB.State.Single(r => r.StateCode == StateCC);
                        x.StateName = Request.Form["StateName"];
                        _DB.SaveChanges();
                    }
                    break;
                case "Delete":
                    if (Request.Form["StateCode"] != "" && Request.Form["StateCode"] != null)
                    {
                        int StateCode = Convert.ToInt32(Request.Form["StateCode"]);
                        _DB.State.Remove(_DB.State.Single(r => r.StateCode == StateCode));
                        _DB.SaveChanges();
                    }
                    break;
            }

            if (Request.Form["CountryCode"] == "All")
            {
                CountryCode = 1000000;
            }
            else
            {
                if (Request.Form["CountryCode"] != "" && Request.Form["CountryCode"] != null)
                {
                    CountryCode = int.Parse(Request.Form["CountryCode"]);
                }
            }
            var DBList = from x in _DB.Country
                         from c in x.State
                         where x.CountryCode == CountryCode || CountryCode == 1000000
                         select c;


            if (!string.IsNullOrEmpty(sortname) && !string.IsNullOrEmpty(sortorder))
            {
                DBList = DBList.OrderBy(sortname, (sortorder == "asc"));
            }

            if (!string.IsNullOrEmpty(qtype) && !string.IsNullOrEmpty(query))
            {
                DBList = DBList.Like(qtype, query);
            }

            DBList = DBList.Skip((page - 1) * rp).Take(rp);


            var flexgrid = new
            {
                page = page,
                total = _DB.State.Count(),
                rows = DBList
                .Select(x => new
                {
                    id = x.StateName,
                    cell = new { x.StateCode, x.StateName }
                }
                )

            };


            return Json(flexgrid, JsonRequestBehavior.AllowGet);
        }

        #endregion State



Again let us debug to add the state within the country 



Step 6:Add City 

Create a City.cshtml page in the Admin folder with in the views 

Add following codes for display the country ,state and add, delete, edit the city within the state 





In the AdminController add following codes for binding the country, binding the state within the country and to add , view, edit, delete the city 

    public ActionResult JsonState()
        {

            int CountryCode = 100000;
            if (Request.QueryString["CountryCode"] == "All")
            {
                CountryCode = 1000000;
            }
            else
            {
                if (Request.QueryString["CountryCode"] != "" && Request.QueryString["CountryCode"] != null)
                {
                    CountryCode = int.Parse(Request.QueryString["CountryCode"]);
                }
            }
            var DBList = from x in _DB.Country
                         from c in x.State
                         where x.CountryCode == CountryCode || CountryCode == 1000000
                         select c;

            var result = DBList
                    .Select(x => new { x.StateName, x.StateCode });
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult JsonCity()
        {

            int StateCode = 100000;
            if (Request.QueryString["StateCode"] == "All")
            {
                StateCode = 1000000;
            }
            else
            {
                if (Request.QueryString["StateCode"] != "" && Request.QueryString["StateCode"] != null)
                {
                    StateCode = int.Parse(Request.QueryString["StateCode"]);
                }
            }
            var DBList = from x in _DB.State
                         from c in x.City
                         where x.StateCode == StateCode || StateCode == 1000000
                         select c;

            var result = DBList
                    .Select(x => new { x.CityName, x.CityCode });
            return Json(result, JsonRequestBehavior.AllowGet);
        }
#region City
        [HttpGet]
        public ActionResult City(int id = 0)
        {
            return View();
        }


        [HttpPost]
        public ActionResult City()
        {

            int page = int.Parse(Request.Form["page"]);
            int rp = int.Parse(Request.Form["rp"]);
            string qtype = Request.Form["qtype"].ToString();
            string query = Request.Form["query"].ToString();
            string sortname = Request.Form["sortname"].ToString();
            string sortorder = Request.Form["sortorder"].ToString();


            int StateCode = 100000;
            switch (Request.Form["fntype"])
            {
                case "Add":
                    if (Request.Form["StateCode"] != "" && Request.Form["StateCode"] != null && Request.Form["StateCode"] != "All")
                    {
                        StateCode = Convert.ToInt32(Request.Form["StateCode"]);
                        _DB.State.Single(r => r.StateCode == StateCode).City.Add(new City() { CityName = Request.Form["CityName"] });
                        _DB.SaveChanges();
                    }
                    break;
                case "Edit":
                    if (Request.Form["CityCode"] != "" && Request.Form["CityCode"] != null)
                    {
                        int CityCC = Convert.ToInt32(Request.Form["CityCode"]);
                        var x = _DB.City.Single(r => r.CityCode == CityCC);
                        x.CityName = Request.Form["CityName"];
                        _DB.SaveChanges();
                    }
                    break;
                case "Delete":
                    if (Request.Form["CityCode"] != "" && Request.Form["CityCode"] != null)
                    {
                        int CityCode = Convert.ToInt32(Request.Form["CityCode"]);
                        _DB.City.Remove(_DB.City.Single(r => r.CityCode == CityCode));
                        _DB.SaveChanges();
                    }
                    break;
            }

            if (Request.Form["StateCode"] == "All")
            {
                StateCode = 1000000;
            }
            else
            {
                if (Request.Form["StateCode"] != "" && Request.Form["StateCode"] != null)
                {
                    StateCode = int.Parse(Request.Form["StateCode"]);
                }
            }
            var DBList = from x in _DB.State
                         from c in x.City
                         where x.StateCode == StateCode || StateCode == 1000000
                         select c;


            if (!string.IsNullOrEmpty(sortname) && !string.IsNullOrEmpty(sortorder))
            {
                DBList = DBList.OrderBy(sortname, (sortorder == "asc"));
            }

            if (!string.IsNullOrEmpty(qtype) && !string.IsNullOrEmpty(query))
            {
                DBList = DBList.Like(qtype, query);
            }

            DBList = DBList.Skip((page - 1) * rp).Take(rp);


            var flexgrid = new
            {
                page = page,
                total = _DB.City.Count(),
                rows = DBList
                .Select(x => new
                {
                    id = x.CityName,
                    cell = new { x.CityCode, x.CityName }
                }
                )

            };


            return Json(flexgrid, JsonRequestBehavior.AllowGet);
        }

        #endregion City

      



Step 7:Debug to get the final result