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 🙂
thank you for your help, I have successfully displayed the data. But I have a problem because the first data or [0] does not appear. The data displayed starts from the second or [1]. Can you help me?
Because i was reading data from DataReader. Use this:
//—————————————————————————–
c.Open();
SqlCommand cmd = new SqlCommand(“select * from tblCountries”, c);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
if(dt!=null && dt.Rows.Count>0)
{
foreach(DataRow row in dt.Rows)
{
list.Add(new SelectListItem { Text = row[“Country”].ToString(), Value = row[“Id”].ToString() });
}
}
else
{
list.Add(new SelectListItem { Text = “No records found”, Value = “0” });
}
//—————————————————————————–
Thank You.
Why we use this public static Item cMain; this
Perfect! I have looked everywhere for this and tried so many versions but this was the simplest easy to follow. Thank you!
Hi….I’m using VS22. I’m getting an error on “Country: @Html.DropDownListFor(m => m.id , Model.countries, “–Please select a country–“, new { @class = “btn btn-default” }) ”
error CS1977: Cannot use a lambda expression as an argument to a dynamically dispatched operation. Any ideas? Thanks
Disregard previous question. Figured it out. @model vs @Model.
On a separate note, do you have a tutorial taking this tutorial to the next level retrieving data from database based on the selection? Thanks