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 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”.
- Select Empty
- Select MVC checkbox
- 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!
Kisuke JopPosted Jun 10, 2020, 10:04 PM
@Html.DropDownListFor(model => model.SkillID, ViewBag.SkillListItem as SelectList, new { @class = "form-control" }) ... Will only select the first item in the dropdownlist and not what was stored in the db when editing.
Mayooran NavamanyPosted Apr 25, 2020, 10:48 PM
Its Useful thank you for sharing
Sourav Kumar DasPosted Dec 30, 2019, 3:07 AM
Nice and informative article Sir. Thanks for sharing.
Dont Compar To OthersPosted Dec 29, 2019, 11:52 PM
Var lstskill = (from a in db.tblSkills orderby a.Title select a).ToList(); --my suggestion : Linq Method var listSkills = db.tblSkills.OrderByDescending(a=>a.Title).select(a => new SelectListItem { value = a.SkillID.Tostring(), text = Title }).ToList(); linq to sql -->var listSkills = (from fa in db.tblSkills orderby fa.Title select new SelectListItem { Text = fa.Title, Value = fa.SkillID.ToString() }).ToList();
Ankur MistryPosted Dec 28, 2019, 7:14 PM
Useful stuff thank you for sharing