How To Fetch SQL Server Data Into Dropdown in ASP.NET MVC

📌 Introduction

In real-world applications, we often need to show data in a dropdown list (like selecting Country, State, Department, etc.).

Instead of hardcoding values, we fetch them from a SQL Server database dynamically.

👉 In this blog, you will learn:

• What is a dropdown in MVC

• Why do we use database data in a dropdown

• Step-by-step implementation

• Full working example (CRUD-ready concept)

🤔 Why Fetch Data from Database?

Imagine you are building:

• Employee Management System → Department list

• School System → Class list

• E-commerce → Category list

If you hardcode values:

• ❌ Not flexible

• ❌ Hard to update

If you use a database

• ✅ Dynamic

• ✅ Easy to update

• ✅ Real-time data

🧠 Concept Flow (Very Important)

SQL Server → Model → Controller → View (Dropdown)

🏗 Step 1: Create Database Table

CREATE TABLE Department (
    DeptId INT PRIMARY KEY IDENTITY,
    DeptName NVARCHAR(100)
);

Insert Sample Data

INSERT INTO Department (DeptName) VALUES ('HR');
INSERT INTO Department (DeptName) VALUES ('IT');
INSERT INTO Department (DeptName) VALUES ('Sales');

🧩 Step 2: Create Model Class

public class Department
{
    public int DeptId { get; set; }
    public string DeptName { get; set; }
}

🔌 Step 3: Database Connection (ADO.NET)

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

🎯 Step 4: Fetch Data in Controller

public class EmployeeController : Controller
{
    string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    public ActionResult Create()
    {
        List<Department> deptList = new List<Department>();

        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Department", con);
            con.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                deptList.Add(new Department
                {
                    DeptId = Convert.ToInt32(dr["DeptId"]),
                    DeptName = dr["DeptName"].ToString()
                });
            }
        }

        ViewBag.Departments = deptList;

        return View();
    }
}

🎨 Step 5: Create Dropdown in View

@{
    var deptList = ViewBag.Departments as List<YourNamespace.Models.Department>;
}

<select name="DeptId" class="form-control">
    <option value="">-- Select Department --</option>

    @foreach (var item in deptList)
    {
        <option value="@item.DeptId">@item.DeptName</option>
    }
</select>

What is this?

⚡ Better Way (Using SelectList)

👉 Cleaner and professional method

Controller

ViewBag.DepartmentList = new SelectList(deptList, "DeptId", "DeptName");

View

@Html.DropDownList("DeptId", ViewBag.DepartmentList as SelectList, "-- Select Department --", new { @class = "form-control" })

What is this?

🔥 Advanced Version (Using Stored Procedure)

SQL

CREATE PROCEDURE GetDepartments
AS
BEGIN
    SELECT * FROM Department
END

Controller

SqlCommand cmd = new SqlCommand("GetDepartments", con);
cmd.CommandType = CommandType.StoredProcedure;

🧪 Output

✔ Dropdown will show:

• HR

• IT

• Sales

⚠️ Common Mistakes

• ❌ Forgetting con.Open()

• ❌ Wrong column name

• ❌ Null ViewBag

• ❌ Not casting SelectList properly

💡 Pro Tips (Important 🔥)

• Use Entity Framework instead of ADO.NET for cleaner code

• Use ViewModel instead of ViewBag (best practice)

• Use AJAX for dependent dropdown (State → City)

🎯 Real-Life Example

When user creates employee:

Name: Abhay

Department: [IT ▼]

👉 Department list comes from database dynamically

🏁 Summary

Fetching dropdown data from a SQL Server database in ASP.NET MVC enables dynamic, maintainable, and real-time user interfaces. By following a structured flow from database to view, developers can efficiently populate dropdowns, avoid hardcoding, and build scalable applications with cleaner and more professional approaches like SelectList and stored procedures.