So let’s start with an example for demonstration purposes, but before that, we’ll create a database and then create a table and insert some dummy values and write a stored procedure to get employee data for the bind dropdown.
Level 1: Create a database in SQL Server.
level2: Create a table with the name “master_worker“.
Insert some dummy records into the table.
Write a stored procedure.
Now, open your visual studio and go to file >> New >> select project.
After you select the project, a new pop-up window will open in which you must select “Internet” from the left panel and select “Asp.Net web application” and then name your web application, select application path and click OK As I have shown below.
Now again you can see another popup that will appear on your screen where you have to select the template as “blank” Check the MVC for Folders and Core References checkbox, and then click OK button.
Now, after completing all the above steps, the structure of your project will look like I have shown below.
Now, you need to add model and controller to your web application and for that, you need to right click on “models“folder and select”add” >> “status“.
You can see one popup that will appear on your screen to add model class in your application, here you need to select “status” and then give the name of your model class and just click “add” button. Here I will name my model as “EmployeeModel.cs”. And I will also add another model class with the name “EmployeeDBHandle.csAs I show below.
Now, just like the model, you need to add a controller class to your web application, and for that, you need to right-click on the “of the cattle” folder and select add >> Beef.
Then after you can see one popup will appear on your screen where you have to select “MVC 5 controller with read/write operations” and click the Add button.
Now, add the controller name and simply add a controller to your application
Note: The controller name must end with ig controller EmployeeController, StudentController, ProductController, etc.
Now you need to write the following code in Model Class “EmployeeModel.cs“and”EmployeeDBHandle.cs“.
EmployeeModel.cs
using System;
using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Web; namespace Codingvila.Models { public class EmployeeModel { public List<EmployeeModel> EmployeeInformation { get; set; } public int EmpId { get; set; } public string FirstName { get; set; } public string Department { get; set; } public string Designation { get; set; } public string CompanyName { get; set; } } }
EmployeeDBHandle.cs
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; namespace Codingvila.Models { public class EmployeeDBHandle { private SqlConnection con; private void connection() { string constring = ConfigurationManager.ConnectionStrings["Conn"].ToString(); con = new SqlConnection(constring); } // ********** VIEW EMPLOYEE DETAILS ******************** public List<EmployeeModel> GetEmployee() { connection(); List<EmployeeModel> EmployeeInformation = new List<EmployeeModel>(); SqlCommand cmd = new SqlCommand("EXEC Get_Employee", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter sd = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); con.Open(); sd.Fill(dt); con.Close(); foreach (DataRow dr in dt.Rows) { EmployeeInformation.Add( new EmployeeModel { EmpId = Convert.ToInt32(dr["EmpId"]), FirstName = Convert.ToString(dr["FirstName"]), Department = Convert.ToString(dr["Department"]), Designation = Convert.ToString(dr["Designation"]), CompanyName = Convert.ToString(dr["CompanyName"]) }); } return EmployeeInformation; } } }
Now we need a database connection so you just follow a few steps as I show below.
Now, you need to add a connection string in Web.config file to get the database connection.
Web.Config
<connectionStrings> <add name="Conn" connectionString="Data Source=DESKTOP-P1PHIU6SQLEXPRESS;Initial Catalog=DB_Codingvila;Integrated Security=True"/> </connectionStrings>
Then, after you need to write the following code in your EmployeeController class.
employee auditor
using Codingvila.Models; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; namespace Codingvila.Controllers { public class EmployeeController : Controller { // // GET: /Employee/ public ActionResult Index() { EmployeeDBHandle dbhandle = new EmployeeDBHandle(); ModelState.Clear(); return View(dbhandle.GetEmployee()); } } }
Now, you need to add a View and for that, you need to right-click on the controller’s action method “index()” and select “Add a view“.
Now, add the display name, select blank Template and select the model name and just click on add button to add a blank view to your application. If you look, you can see in your project directory that the display name is appended with the file extension “.cshtml“.
Now write the following code for your view.
Index.cshtml
@model Codingvila.Models.EmployeeModel @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Employee Details</title> </head> <body> <div> <h1>Employee Detail</h1> <br> Employee Name :@Html.DropDownListFor(m => m.EmpId, new SelectList(Model.EmployeeInformation, "EmpId", "FirstName"), "") </div> </body> </html>
Now finally, you need to make some changes in RouteConfig.cs A file where you need to define your controller name, default action method and ID as the optional parameter to route pages and to set your URL pattern.
Finally, your application is ready and while you run your application you can see the output as I have shown below.
Note: Your URL pattern must be something like (http://yourdomain.com/name of controller/name of action method) ig http://codingvila.com/articles/Index/, where articles is the name of my controller and Index, is my modus operandi.
Summary
This article explains how to create an MVC application and how to bind a dropdown list in ASP.NET MVC from a database using a stored procedure.
