Populate DropDownList from Database using Entity Framework and Linq query in ASP.Net MVC

Last Reply 2 months ago By dharmendr

Posted 2 months ago

hello,

i want to pass LinQ query below instead of in line select statement

var cat = (from ct in db.categories
           select cat);
model.FirstLevel = PopulateDropDown("SELECT CountryId, CountryName FROM Countries", "CountryName", "CountryId");
return View(model);

 

        private static List<SelectListItem> PopulateDropDown(string query, string textColumn, string valueColumn)
        {
            List<SelectListItem> items = new List<SelectListItem>();
            string constr = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            items.Add(new SelectListItem
                            {
                                Text = sdr[textColumn].ToString(),
                                Value = sdr[valueColumn].ToString()
                            });
                        }
                    }
                    con.Close();
                }
            }

            return items;
        }

 

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi nauna,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Model

public class DetailModel
{
    public int Id { get; set; }
    public List<SelectListItem> Country { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        DetailModel model = new DetailModel();
        NorthwindEntities entities = new NorthwindEntities();
        model.Country = (from country in entities.Employees
                            select new SelectListItem()
                            {
                                Text = country.Country,
                                Value = country.Country
                            }).Distinct().ToList();
        return View(model);
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<_Populate_DropDownList_Linq.Models.DetailModel>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>
</head>
<body>
    <div><%:Html.DropDownListFor(m => m.Country, Model.Country, "Select", new { style = "width:150px;" })%></div>
</body>
</html>

Sceenshot