Cascading Dropdown in asp.net MVC core using ADO.NET

Question

I am doing cascading drop down from Country to States in asp.net MVC core using ADO.NET using the stored procedure. Is it the right approach to use ADO.NET in ASP.NET MVC Core. and my question is I have issues while selecting the country list from the drop-down. States are not coming to the drop down as CountryId is not passing into the Controller method getStateByCountry.Suggestions would be highly appreciated.

 

**View Model** :

public class Country

{

public int CountryId { get; set; }

public string CountryName { get; set; }

public virtual int StateId { get; set; }

public virtual string StateName { get; set; }

public virtual List<State> states { get; set; }

}

**Controller:**

public class CascadeController : Controller

{

DataBaseSetting db = new DataBaseSetting();

public IActionResult Index()

{

return View();

}

public IActionResult Create()

{

try

{

List<Country> list_country = new List<Country>();

List<SelectListItem> selectListItems = new List<SelectListItem>();

list_country = db.GetCountry();

ViewBag.Country = list_country;

return View();

}

catch (Exception ex)

{

throw ex;

}

}

public JsonResult getStateByCountry(int ID)

{

List<State> Liststates = new List<State>();

Liststates = db.GetStatesByCountry(ID);

Liststates.Insert(0, new State { StateId = 0, StateName = "Select State" });

return Json(new SelectList("StateId", "StateName"));

}

}

**Ado Code:**

public class DataBaseSetting

{

string ConnectionString = "Data Source=ABC-17;Initial Catalog=*****User ID=sa; Password=******";

public List<Country> GetCountry()

{

List<Country> list_Country = new List<Country>();

using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))

{

using (SqlCommand sqlCommand = new SqlCommand("spGetCountry", sqlConnection))

{

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlConnection.Open();

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

while (sqlDataReader.Read())

{

Country country = new Country()

{

CountryId = (int)sqlDataReader["CountryId"],

CountryName = sqlDataReader["CountryName"].ToString()

};

list_Country.Add(country);

}

}

return list_Country;

}

}

public List<State> GetStatesByCountry(int CountryId)

{

List<State> ListState = new List<State>();

using (SqlConnection sqlCOnnection = new SqlConnection(ConnectionString))

{

using (SqlCommand sqlCommand = new SqlCommand("GetStateByCountry", sqlCOnnection))

{

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.AddWithValue("@CountryId", CountryId);

sqlCOnnection.Open();

SqlDataReader dataReader = sqlCommand.ExecuteReader();

while (dataReader.Read())

{

State state = new State()

{

StateId = (int)dataReader["StateId"],

StateName = dataReader["StateName"].ToString(),

};

ListState.Add(state);

}

}

}

return ListState;

}

}

**Create View:**

@model CascadingDropDown.Models.Country

@{

ViewData["Title"] = "Create";

}

<div class="row">

<div class="form-group col-md-4">

<label asp-for="CountryName" class="control-label"></label>

<select asp-for="CountryId" class="form-control" asp-items="@(new SelectList(ViewBag.Country,"CountryId","CountryName"))">

<option>Select Country</option>

</select>

</div>

<div class="form-group col-md-4">

<label asp-for="StateName" class="control-label"></label>

<select asp-for="StateId" class="form-control" asp-items="@(new SelectList(string.Empty,"StateId","StateName"))">

<option>Select Country</option>

</select>

</div>

<div class="pull-right">

<input type="submit" value="Register" class="btn btn-primary" />

</div>

</div>}

<script src="~/lib/jquery/dist/jquery.js"></script>

<script>

$(function () {

$("#CountryId").change(function () {

var url = '@Url.Content("~/")' + 'Cascade/getStateByCountry';

alert(url);

var ddlsource = "#CountryId";

$.getJSON(url, { StateId: $(ddlsource).val() }, function (data) {

var items = '';

$("#StateId").empty();

$.each(data, function (i, row) {

items += "<option value='" + row.value + "'>" + row.text + "</option>";

alert(ddlsource.value)

});

$("#StateId").html(items);

});

});

});

</script>