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

After reading this article you will get a clear ideas 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 generate 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
  1. USE [MbkTest]  
  2. GO  
  3. /****** Object:  Table [dbo].[tblSkills]    Script Date: 17-Dec-19 12:09:36 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. SET ANSI_PADDING ON  
  9. GO  
  10. CREATE TABLE [dbo].[tblSkills](  
  11.     [SkillID] [int] IDENTITY(1,1) NOT NULL,  
  12.     [Title] [varchar](50) NULL,  
  13. PRIMARY KEY CLUSTERED   
  14. (  
  15.     [SkillID] ASC  
  16. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  17. ON [PRIMARY]  
  18.   
  19. GO  
  20. SET ANSI_PADDING OFF  
  21. GO  
tblEmployeesTables Structures
  1. USE [MbkTest]  
  2. GO  
  3.   
  4. /****** Object:  Table [dbo].[tblEmployees]    Script Date: 17-Dec-19 12:06:17 PM ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9. SET ANSI_PADDING ON  
  10. GO  
  11. CREATE TABLE [dbo].[tblEmployees](  
  12.     [EmployeeID] [int] IDENTITY(1,1) NOT NULL,  
  13.     [PhoneNumber] [varchar](50) NULL,  
  14.     [SkillID] [intNULL,  
  15.     [YearsExperience] [intNULL,  
  16.     [EmployeeName] [nvarchar](50) NULL,  
  17. PRIMARY KEY CLUSTERED   
  18. (  
  19.     [EmployeeID] ASC  
  20. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  21. ON [PRIMARY]  
  22.   
  23. GO  
  24. SET ANSI_PADDING OFF  
  25. GO  
Create Project named “DropdownListValue”
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Set Default Value To Dropdown List From Database In ASP.NET MVC 
  1. Select Empty
  2. Select MVC checkbox
  3. Click OK
Set Default Value To Dropdown List From Database In ASP.NET MVC 
 
Default view of Solution Explorer
 
Add New Controller called “EmployeeController”
 
Right click on Controller Select ADD --> CONTROLLER
 
Set Default Value To Dropdown List From Database In ASP.NET MVC 
 
Give controller name in the following screenshot.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC 
 
Double click on EmployeeController.cs file to see default code. Keep it only 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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace DropdownListValue.Controllers  
  8. {  
  9.     public class EmployeeController : Controller  
  10.     {      
  11.         
  12.         // GET: Employee/Edit/5  
  13.         public ActionResult Edit(int id)  
  14.         {  
  15.             return View();  
  16.         }  
  17.   
  18.         // POST: Employee/Edit/5  
  19.         [HttpPost]  
  20.         public ActionResult Edit(int id, FormCollection collection)  
  21.         {  
  22.             try  
  23.             {  
  24.                 // TODO: Add update logic here  
  25.                 return RedirectToAction("Index");  
  26.             }  
  27.             catch  
  28.             {  
  29.                 return View();  
  30.             }  
  31.         }        
  32.     }  
  33. }  
Now we are going connect to the database by adding LINQ TO SQL Classes in project,
 
Right click on Project title select ADD--> NEW ITEM ---->Data (Leftside)-->Linq To Sql Classes
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Now switch to SERVER EXPLORER to connect with database. You can activate SERVER EXPLORER by pressing CTRL + ALT + S.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
After selecting proper selection, click TEST CONNECTION to confirm your connection.
 
As your connection was established successfully Server explorer will display your database tables.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Double click on EMPOYEEDATACLASSES.DBML file, drag and drop the tblEmployees and tblSkills table on dbml canvas.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
As in the above screen shot we had completed task to add tables to Linq To SQL dataclass.
 
Now switch back to EMPLOYEECONTROLLER.CS file to do following coding.
 
Coding for EDIT actionmethod and generating SelectList items for dropdownlist.
 
EMPLOYEECONTROLLER.CS code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace DropdownListValue.Controllers  
  8. {  
  9.     public class EmployeeController : Controller  
  10.     {  
  11.         // GET: Employee/Edit/5  
  12. public ActionResult Edit(int id)  
  13.         {  
  14.             //Linq to SQL  class Instance created  
  15.             EmployeeDataClassesDataContext db = new EmployeeDataClassesDataContext();  
  16.   
  17.             //Fetching Skills Records in LIST Collection format.  
  18.             var lstskill = (from a in db.tblSkills orderby a.Title select a).ToList();  
  19.   
  20.             //Creating ViewBag named SkillListItem to used in VIEW.  
  21.             ViewBag.SkillListItem = ToSelectList(lstskill);  
  22.   
  23.             //Fetching specific Employee Record.  
  24.             var EmployeeDetail = (from a in db.tblEmployees  
  25.                                     where a.EmployeeID == id  
  26.                                 select a).FirstOrDefault();  
  27.   
  28.             //Sending Employees list to View.  
  29.             return View(EmployeeDetail);  
  30.         }  
  31.   
  32.         // POST: Employee/Edit/5  
  33.         [HttpPost]  
  34.         public ActionResult Edit(int id, FormCollection collection)  
  35.         {  
  36.             try  
  37.             {  
  38.                 // TODO: Add update logic here  
  39.   
  40.                 return RedirectToAction("Index");  
  41.             }  
  42.             catch  
  43.             {  
  44.                 return View();  
  45.             }  
  46.         }  
  47.   
  48.         //NonAction method can not used as like ActionMethod.   
  49.         //This is used for processing and functionalities purpose.  
  50.         [NonAction]  
  51.         public SelectList ToSelectList(List<tblSkill> lstskill)  
  52.         {  
  53.             List<SelectListItem> list = new List<SelectListItem>();  
  54.   
  55.             foreach (tblSkill item in lstskill)  
  56.             {  
  57.                 list.Add(new SelectListItem()  
  58.                 {  
  59.                     Text = item.Title,  
  60.                     Value = Convert.ToString(item.SkillID)  
  61.                 });  
  62.             }  
  63.   
  64.             return new SelectList(list, "Value""Text");  
  65.         }  
  66.     }  
  67. }  
Now right click on EDIT HttpGet which is the first method; the second EDIt method is HttpPost
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Select ADD VIEW.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
After clicking on ADD button
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
You can see 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.
 
You 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 Datas
 
Set Default Value To Dropdown List From Database In ASP.NET MVC 
 
Default generated code of EDIT.CSHTML
  1. @model DropdownListValue.tblEmployee  
  2.   
  3. @{  
  4.     ViewBag.Title = "Edit";  
  5. }  
  6.   
  7. <h2>Edit</h2>  
  8.   
  9. @using (Html.BeginForm())  
  10. {  
  11.     @Html.AntiForgeryToken()  
  12.   
  13.     <div class="form-horizontal">  
  14.         <h4>tblEmployee</h4>  
  15.         <hr />  
  16.         @Html.ValidationSummary(true""new { @class = "text-danger" })  
  17.         @Html.HiddenFor(model => model.EmployeeID)  
  18.   
  19.         <div class="form-group">  
  20.             @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })  
  21.             <div class="col-md-10">  
  22.                 @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })  
  23.                 @Html.ValidationMessageFor(model => model.EmployeeName, ""new { @class = "text-danger" })  
  24.             </div>  
  25.         </div>  
  26.         <div class="form-group">  
  27.             @Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })  
  28.             <div class="col-md-10">  
  29.                 @Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })  
  30.                 @Html.ValidationMessageFor(model => model.PhoneNumber, ""new { @class = "text-danger" })  
  31.             </div>  
  32.         </div>  
  33.   
  34.         <div class="form-group">  
  35.             @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })  
  36.             <div class="col-md-10">  
  37.                 @Html.EditorFor(model => model.SkillID, new { htmlAttributes = new { @class = "form-control" } })  
  38.                 @Html.ValidationMessageFor(model => model.SkillID, ""new { @class = "text-danger" })  
  39.             </div>  
  40.         </div>  
  41.   
  42.         <div class="form-group">  
  43.             @Html.LabelFor(model => model.YearsExperience, htmlAttributes: new { @class = "control-label col-md-2" })  
  44.             <div class="col-md-10">  
  45.                 @Html.EditorFor(model => model.YearsExperience, new { htmlAttributes = new { @class = "form-control" } })  
  46.                 @Html.ValidationMessageFor(model => model.YearsExperience, ""new { @class = "text-danger" })  
  47.             </div>  
  48.         </div>  
  49.         <div class="form-group">  
  50.             <div class="col-md-offset-2 col-md-10">  
  51.                 <input type="submit" value="Save" class="btn btn-default" />  
  52.             </div>  
  53.         </div>  
  54.     </div>  
  55. }  
  56.   
  57. <div>  
  58.     @Html.ActionLink("Back to List""Index")  
  59. </div>  
  60.   
  61. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  62. <script src="~/Scripts/jquery.validate.min.js"></script>  
  63. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>  
At Present EDIT.CSHTML Output Will Look Like this,
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
In the above screenshot you can see skillid is 2.
 
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
SkillID = 2 and Skill Value = C#
 
Now we are implementing DROPDOWN LIST of SKILLS.
 
REMOVE the following Code,
  1. <div class="form-group">  
  2.          @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })  
  3.          <div class="col-md-10">  
  4.              @Html.EditorFor(model => model.SkillID, new { htmlAttributes = new { @class = "form-control" } })  
  5.              @Html.ValidationMessageFor(model => model.SkillID, ""new { @class = "text-danger" })  
  6.          </div>  
  7.      </div>  
UPDATE the following CODE,
  1. <div class="form-group">  
  2.           @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })  
  3.           <div class="col-md-10">  
  4.               @*ViewBag.SkillListItem is holding all the Skill values*@  
  5.               @Html.DropDownListFor(model => model.SkillID, ViewBag.SkillListItem as SelectList, new { @class = "form-control" })  
  6.               @Html.ValidationMessageFor(model => model.SkillID, ""new { @class = "text-danger" })  
  7.           </div>  
  8.  </div>  
Set Default Value To Dropdown List From Database In ASP.NET MVC
 

Full Code

 
EDIT.CSHTML Code
  1. @model DropdownListValue.tblEmployee  
  2.   
  3. @{  
  4.     ViewBag.Title = "Edit";  
  5. }  
  6. <h2>Edit</h2>  
  7.   
  8. @using (Html.BeginForm())  
  9. {  
  10.     @Html.AntiForgeryToken()  
  11.       
  12.     <div class="form-horizontal">  
  13.         <h4>tblEmployee</h4>  
  14.         <hr />  
  15.         @Html.ValidationSummary(true""new { @class = "text-danger" })  
  16.         @Html.HiddenFor(model => model.EmployeeID)  
  17.   
  18.         <div class="form-group">  
  19.             @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })  
  20.             <div class="col-md-10">  
  21.                 @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })  
  22.                 @Html.ValidationMessageFor(model => model.EmployeeName, ""new { @class = "text-danger" })  
  23.             </div>  
  24.         </div>  
  25.   
  26.         <div class="form-group">  
  27.             @Html.LabelFor(model => model.PhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })  
  28.             <div class="col-md-10">  
  29.                 @Html.EditorFor(model => model.PhoneNumber, new { htmlAttributes = new { @class = "form-control" } })  
  30.                 @Html.ValidationMessageFor(model => model.PhoneNumber, ""new { @class = "text-danger" })  
  31.             </div>  
  32.         </div>  
  33.   
  34.         <div class="form-group">  
  35.             @Html.LabelFor(model => model.SkillID, htmlAttributes: new { @class = "control-label col-md-2" })  
  36.             <div class="col-md-10">  
  37.                 @*ViewBag.SkillListItem is holding all the Skill values*@  
  38.                 @Html.DropDownListFor(model => model.SkillID, ViewBag.SkillListItem as SelectList, new { @class = "form-control" })  
  39.                 @Html.ValidationMessageFor(model => model.SkillID, ""new { @class = "text-danger" })  
  40.             </div>  
  41.         </div>  
  42.   
  43.         <div class="form-group">  
  44.             @Html.LabelFor(model => model.YearsExperience, htmlAttributes: new { @class = "control-label col-md-2" })  
  45.             <div class="col-md-10">  
  46.                 @Html.EditorFor(model => model.YearsExperience, new { htmlAttributes = new { @class = "form-control" } })  
  47.                 @Html.ValidationMessageFor(model => model.YearsExperience, ""new { @class = "text-danger" })  
  48.             </div>  
  49.         </div>  
  50.   
  51.         
  52.   
  53.         <div class="form-group">  
  54.             <div class="col-md-offset-2 col-md-10">  
  55.                 <input type="submit" value="Save" class="btn btn-default" />  
  56.             </div>  
  57.         </div>  
  58.     </div>  
  59. }  
  60.   
  61. <div>  
  62.     @Html.ActionLink("Back to List""Index")  
  63. </div>  
  64.   
  65. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
  66. <script src="~/Scripts/jquery.validate.min.js"></script>  
  67. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>  
EMPLOYEESCONTROLLER.CS Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace DropdownListValue.Controllers  
  8. {  
  9.     public class EmployeeController : Controller  
  10.     {      
  11.         // GET: Employee/Edit/5  
  12.         public ActionResult Edit(int id)  
  13.         {  
  14.             //Linq to SQL  class Instance created  
  15.             EmployeeDataClassesDataContext db = new EmployeeDataClassesDataContext();  
  16.   
  17.             //Fetching Skills Records in LIST Collection format.  
  18.             var lstskill = (from a in db.tblSkills orderby a.Title select a).ToList();  
  19.   
  20.             //Creating ViewBag named SkillListItem to used in VIEW.  
  21.             ViewBag.SkillListItem = ToSelectList(lstskill);  
  22.   
  23.             //Fetching specific Employee Record.  
  24.             var EmployeeDetail = (from a in db.tblEmployees  
  25.                                     where a.EmployeeID == id  
  26.                                 select a).FirstOrDefault();  
  27.   
  28.             //Sending Employees list to View.  
  29.             return View(EmployeeDetail);  
  30.         }  
  31.   
  32.         // POST: Employee/Edit/5  
  33.         [HttpPost]  
  34.         public ActionResult Edit(int id, FormCollection collection)  
  35.         {  
  36.             try  
  37.             {  
  38.                 // TODO: Add update logic here  
  39.   
  40.                 return RedirectToAction("Index");  
  41.             }  
  42.             catch  
  43.             {  
  44.                 return View();  
  45.             }  
  46.         }  
  47.   
  48.         //NonAction method can not used as like ActionMethod.   
  49.         //This is used for processing and functionalities purpose.  
  50.         [NonAction]  
  51.         public SelectList ToSelectList(List<tblSkill> lstskill)  
  52.         {  
  53.             List<SelectListItem> list = new List<SelectListItem>();  
  54.   
  55.             foreach (tblSkill item in lstskill)  
  56.             {  
  57.                 list.Add(new SelectListItem()  
  58.                 {  
  59.                     Text = item.Title,  
  60.                     Value = Convert.ToString(item.SkillID)  
  61.                 });  
  62.             }  
  63.   
  64.             return new SelectList(list, "Value""Text");  
  65.         }  
  66.     }  
  67. }  
Set Default Value To Dropdown List From Database In ASP.NET MVC
 
Thank You..
 
Happy Coding…