Mvc4 How to bind Dropdown List using Sql Database

How to bind Dropdown List in mvc4 using Sql Database

Introduction: This tutorial Explain How to bind dropdownlist in mvc4 web application. here we can bind the dropdown List using the sql server database.

Tools : Visual studio 2012 & Sql server database

Step 1: First you can create new mvc4 application and add new database as following

Step 2: Now you can add model class as following.

Models >> add >> class

Step 3 : Now you can add code in model class DAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
namespace MvcApplication1.Models
{
    public class DAL
    {
        public int id { get; set; }
        [Required]
        public string Name { get; set; }
        public string City { get; set; }
        public string Education { get; set; }

        public SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;\Database1.mdf;Integrated Security=True");

public DataSet Binddl(DAL da)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter("select Name from tbl_user",con);
            adp.Fill(ds);
            return ds;

        }

Step 4: Now add code in controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcApplication1.Models;
using System.Data;
using System.Data.SqlClient;
namespace MvcApplication1.Controllers
{
    public class ABCController : Controller
    {
        //
        // GET: /ABC/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ddl(DAL da)
        {
            DataSet ds = da.Binddl(da);
            ViewBag.fname = ds.Tables[0];
            List<SelectListItem> lst = new List<SelectListItem>();
            foreach (System.Data.DataRow dr in ViewBag.fname.Rows)
            {
                lst.Add(new SelectListItem { Text = @dr["Name"].ToString(), Value = @dr["Name"].ToString() });
            }
            ViewBag.Name = lst;
            return View();
        }

          }

    }

Step 5: Now you can View as following in ddl.cshtml

@model MvcApplication1.Models.DAL

@{
ViewBag.Title = "ddl";
}

<h2>ddl</h2>

@Html.DropDownList("Name")

Now you can run Project And see the Output as following.