Set Default Value To Dropdown List From Database In ASP.NET MVC

After reading this article you will get a clear idea of how to fill the default value from the database's table.

In this article you will learn the following things,

• How to create an Asp.Net MVC empty project
• How to use Linq to SQL?
• How to set DropDown list value from Database

Steps Summary

We have implemented the task with the following steps

• Two (2) Tables Created
• tblEmployee
• tblSkills
• Net MVC Empty Project Created.
• Add Linq to SQL Class item & through Server Explorer add two tables
• Add Controller
• Update EDIT action method
• Using Scaffolding generates EDIT.CSHTML view and system will create default _Layout. cshtml.
• Modify view EDIT.CSHTML for DropDownList HTML helper
• Execute

Step-by-step implementation
 

Tables Used

In this walk-through article, I used two tables.

• tblSkills
• tblEmployees

tblSkills Tables Structures

USE [MbkTest]
GO

/****** Object:  Table [dbo].[tblSkills]    Script Date: 17-Dec-19 12:09:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblSkills](
    [SkillID] [int] IDENTITY(1,1) NOT NULL,
      NULL,
    PRIMARY KEY CLUSTERED
    (
        [SkillID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

tblEmployeesTables Structures

USE [MbkTest]
GO

/****** Object:  Table [dbo].[tblEmployees]    Script Date: 17-Dec-19 12:06:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblEmployees](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      NULL,
    [SkillID] [int] NULL,
    [YearsExperience] [int] NULL,
      NULL,
    PRIMARY KEY CLUSTERED
    (
        [EmployeeID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Create a Project named “DropdownListValue”.

1. Select Empty
2. Select MVC checkbox
3. Click OK

Default view of Solution Explorer.

Add a New Controller called “EmployeeController”.

Right-click on Controller Select ADD --> CONTROLLER.

Give the controller name in the following screenshot.

Double-click on the EmployeeController.cs file to see the default code. Keep only the default EDIT ActionResult method and remove the rest of the code from your file if you don't need it.

Default Code of EmployeeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace DropdownListValue.Controllers
{
    public class EmployeeController : Controller
    {
        // GET: Employee/Edit/5
        public ActionResult Edit(int id)
        {
            return View();
        }

        // POST: Employee/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

Now we are going to connect to the database by adding LINQ TO SQL Classes in the project,

Right-click on the Project title and select ADD--> NEW ITEM ---->Data (Leftside)-->Linq To SQL Classes.

Now switch to SERVER EXPLORER to connect with the database. You can activate SERVER EXPLORER by pressing CTRL + ALT + S.

After selecting the proper selection, click TEST CONNECTION to confirm your connection.

As your connection was established successfully Server Explorer will display your database tables.

Double-click on EMPOYEEDATACLASSES.DBML file, drag and drop the tblEmployees and tblSkills table on dbml canvas.

As in the above screenshot, we had completed the task of adding tables to the Linq To SQL data class.

Now switch back to EMPLOYEECONTROLLER.CS file to do the following coding.

Coding for EDIT action and generating SelectList items for the dropdown list.

EMPLOYEECONTROLLER.CS code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace DropdownListValue.Controllers
{
    public class EmployeeController : Controller
    {
        // GET: Employee/Edit/5
        public ActionResult Edit(int id)
        {
            // Linq to SQL class Instance created
            EmployeeDataClassesDataContext db = new EmployeeDataClassesDataContext();

            // Fetching Skills Records in LIST Collection format.
            var lstskill = (from a in db.tblSkills orderby a.Title select a).ToList();

            // Creating ViewBag named SkillListItem to used in VIEW.
            ViewBag.SkillListItem = ToSelectList(lstskill);

            // Fetching specific Employee Record.
            var EmployeeDetail = (from a in db.tblEmployees
                                  where a.EmployeeID == id
                                  select a).FirstOrDefault();

            // Sending Employees list to View.
            return View(EmployeeDetail);
        }

        // POST: Employee/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // NonAction method can not used as like ActionMethod.
        // This is used for processing and functionalities purpose.
        [NonAction]
        public SelectList ToSelectList(List<tblSkill> lstskill)
        {
            List<SelectListItem> list = new List<SelectListItem>();

            foreach (tblSkill item in lstskill)
            {
                list.Add(new SelectListItem()
                {
                    Text = item.Title,
                    Value = Convert.ToString(item.SkillID)
                });
            }

            return new SelectList(list, "Value", "Text");
        }
    }
}

Now right-click on EDIT HttpGet which is the first method; the second EDIt method is HttpPost

Select ADD VIEW.

After clicking on the ADD button.

You can see this in the above screenshot.

• Views--->Employee--->Edit. cshtml file was created.
• Views--->Shared--->_Layout. cshtml file was created.

Now run the project by pressing F5 to see output.

Your URL will display like this,

http://localhost:57207/Employee/Edit

Change the URL to http://localhost:57207/Employee/Edit/1 because Employee ID 1 and Employee Name is Suhana Kalla.

Sample Employees Data

Default generated code of EDIT.CSHTML

@model DropdownListValue.tblEmployee

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>tblEmployee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.EmployeeID)

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeName, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.PhoneNumber, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.SkillID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.SkillID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.YearsExperience, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.YearsExperience, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.YearsExperience, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

At Present EDIT.CSHTML Output Will Look Like this,

In the above screenshot you can see skilled is 2.

SkillID = 2 and Skill Value = C#

Now we are implementing a DROPDOWN LIST of SKILLS.

REMOVE the following Code,

<div class="form-group">
    @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.EditorFor(model => model.SkillID, new { htmlAttributes = new { @class = "form-control" } })
        @Html.ValidationMessageFor(model => model.SkillID, "", new { @class = "text-danger" })
    </div>
</div>

UPDATE the following CODE,

<div class="form-group">
    @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @* ViewBag.SkillListItem is holding all the Skill values *@
        @Html.DropDownListFor(model => model.SkillID, ViewBag.SkillListItem as SelectList, new { @class = "form-control" })
        @Html.ValidationMessageFor(model => model.SkillID, "", new { @class = "text-danger" })
    </div>
</div>

Full Code
 

EDIT.CSHTML Code

@model DropdownListValue.tblEmployee

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
       
    <div class="form-horizontal">
        <h4>tblEmployee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.EmployeeID)

        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.PhoneNumber, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @* ViewBag.SkillListItem is holding all the Skill values *@
                @Html.DropDownListFor(model => model.SkillID, ViewBag.SkillListItem as SelectList, new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.SkillID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.YearsExperience, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.YearsExperience, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.YearsExperience, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

EMPLOYEESCONTROLLER.CS Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace DropdownListValue.Controllers
{
    public class EmployeeController : Controller
    {
        // GET: Employee/Edit/5
        public ActionResult Edit(int id)
        {
            // Linq to SQL class Instance created
            EmployeeDataClassesDataContext db = new EmployeeDataClassesDataContext();

            // Fetching Skills Records in LIST Collection format.
            var lstskill = (from a in db.tblSkills orderby a.Title select a).ToList();

            // Creating ViewBag named SkillListItem to used in VIEW.
            ViewBag.SkillListItem = ToSelectList(lstskill);

            // Fetching specific Employee Record.
            var EmployeeDetail = (from a in db.tblEmployees
                                  where a.EmployeeID == id
                                  select a).FirstOrDefault();

            // Sending Employees list to View.
            return View(EmployeeDetail);
        }

        // POST: Employee/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // NonAction method can not used as like ActionMethod.
        // This is used for processing and functionalities purpose.
        [NonAction]
        public SelectList ToSelectList(List<tblSkill> lstskill)
        {
            List<SelectListItem> list = new List<SelectListItem>();

            foreach (tblSkill item in lstskill)
            {
                list.Add(new SelectListItem()
                {
                    Text = item.Title,
                    Value = Convert.ToString(item.SkillID)
                });
            }

            return new SelectList(list, "Value", "Text");
        }
    }
}

Thank You.

Happy Coding!