In this post we will see How we can Bind or Populate ASP.NET MVC Dropdownlist from Database (MS SQL Database).
MVC Dropdownlist will be populated using MVC Model class Countries.cs and @Html.DropDownListFor html design.
MS SQL Database table :
Create a Model class named Countries.cs in your MVC Project’s model folder and edit it as below :
Countries.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace myapp.Models
{
public class Countries
{
public List<SelectListItem> countries { get; set; }
public string country { get; set; }
public int id { get; set; }
}
}
Now create a MVC Controller named HomeController.cs and edit it as below:
HomeContoller.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using myapp.Models;
namespace myapp.Controllers
{
public class HomeController : Controller
{
public static string connectionString = @"Data Source=192.168.0.192;Initial Catalog=ParallelCodes;User ID=sa;Password=1234";
public static Countries cMain;
public ActionResult Index()
{
Countries c = new Countries();
cMain = new Countries();
c.countries = cMain.countries = FillList();
ViewBag.LblCountry = "";
return View(c);
}
[HttpPost]
public ActionResult Index(Countries country)
{
var g = cMain.countries;
var selectedCountry = g.Find(p => p.Value == country.id.ToString());
country.countries = FillList();
ViewBag.LblCountry = "You selected "+ selectedCountry.Text.ToString();
return View(country);
}
public List<SelectListItem> FillList()
{
var list = new List<SelectListItem>();
try
{
using (SqlConnection c = new SqlConnection(connectionString))
{
c.Open();
SqlCommand cmd = new SqlCommand("select * from tblCountries", c);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
while (reader.Read())
{
list.Add(new SelectListItem { Text = reader["Country"].ToString(), Value = reader["Id"].ToString() });
}
}
else
{
list.Add(new SelectListItem { Text = "No records found", Value = "0" });
}
c.Close();
}
}
catch (Exception ex)
{
list.Add(new SelectListItem { Text = ex.Message.ToString(), Value = "0" });
}
return list;
}
}
}
The MVC Controller contist of two methods HttpPost and HttpGet (Index method) with ActionResult as return object.
HttpGet method will help us to get the initial page object with return type as “Countries” object. Here a List<SelectListItem> from class System.Web.Mvc.SelectListItem is filled and same is used on design page to show the MVC dropdown list to our users.
HttpPost method will post a Countries object with select country from dropdownlist info to the server. This method too will return Countries object.
Create a MVC View from the Index (HttpGet) method by right clicking on the method name > Add View and edit it as following:
Index.cshtml:
@model myapp.Models.Countries
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<title>Index</title>
</head>
<body>
<div style="padding:2px;">
<h2>MVC Dropdownlist from SQL Database</h2>
<hr />
<div style="padding:10px;">
@using (Html.BeginForm())
{
<span>Country:</span> @Html.DropDownListFor(m => m.id, Model.countries, "--Please select a country--", new { @class = "btn btn-default" })
<br />
<br />
<button class="btn btn-primary">Submit</button>
<br /><br />
@ViewBag.LblCountry
}
</div>
</div>
</body>
</html>
Download Source code for MVC dropdownlist from SQL Database.
Thank You. Have a great day 🙂