Filling a dropdown list in an ASP.NET MVC view from a SQL Server table is a fundamental pattern every .NET web developer encounters. The approach splits cleanly into a model, a data access layer, a controller, and a view—letting a stored procedure hand data back through a dedicated class rather than scattering SQL throughout the controller.
The example below uses an Employee table, a Get_Employee stored procedure, an EmployeeModel class to shape the data, an EmployeeDBHandle class to manage the database conversation, and a controller action that feeds everything to the view. It reflects the standard ASP.NET MVC 5 layout and works as a reliable template for any dropdown backed by a lookup table.
The Database Foundation
Before any C# code runs, you need a table and a stored procedure. The Employee_Master table holds EmpId, FirstName, Department, Designation, and CompanyName. The Get_Employee procedure returns the full set via a simple SELECT. Keeping retrieval logic in a stored procedure means the controller never touches column names or join logic—if the schema changes, you update one place.
If the dropdown feeds a second dropdown (cascading selects), parameterize the procedure here. Filter by the parent dropdown's selection and pass that parameter through the controller.
The Model and Data Access Layer
EmployeeModel is a plain class with only the fields the view needs. You do not need to mirror every database column. A slim model keeps the view binding clean and avoids dragging unused data into the page lifecycle.
EmployeeDBHandle wraps the SQL Server interaction. It opens a SqlConnection using the Conn connection string from Web.config, executes the stored procedure via SqlDataAdapter, fills a DataTable, then maps each row into an EmployeeModel instance. The class exposes a single GetEmployee method returning List<EmployeeModel>. That list is the only thing the controller knows about.
The Controller and the View
The controller action is deliberately simple. Instantiate EmployeeDBHandle, call GetEmployee, clear ModelState, and return the view with the populated list. The view then renders Html.DropDownList, binding to a SelectList built from the model.
The single most common failure point**: a missing or misnamed connection string. If you see an error referencing Conn, the Web.config entry under <connectionStrings> is either absent or uses a different name than your code expects.
Why It Matters
This layered approach trades a few extra files for long-term flexibility. The view knows nothing about SQL. The controller knows nothing about column names. The data access class knows nothing about HTTP. Swap any layer—the database, the ORM, the frontend—and the others keep working.
For modern projects, the obvious upgrade is replacing SqlDataAdapter + DataTable with Dapper or Entity Framework. Both eliminate the manual DataRow mapping and cut the data access class to a handful of lines. If you are starting fresh, use Dapper. If you are maintaining existing code written in this pattern, this layout is exactly what you will find and exactly how to extend it without a rewrite.
Who Should Care
| Scenario | Use This Pattern? |
|---|---|
| New .NET 6/8 project | No—use ASP.NET Core with DI and Dapper/EF Core |
| Maintaining legacy .NET Framework 4.x code | Yes—understand it, extend it, plan migration |
| Simple internal tool, low traffic | Yes—fast to build, easy to debug |
| High-scale production app | Consider Dapper or EF now; this pattern scales poorly |
| Learning MVC fundamentals | Yes—clarifies the layer separation that modern stacks abstract away |
What to Watch Next
If you are writing this pattern today, swap the DataTable for Dapper on day one. The mental model stays the same; the boilerplate disappears. Keep the stored procedure, keep the layer separation, and you have a maintainable dropdown without the noise.
Quick checklist before you ship:
- Connection string name in Web.config matches the code exactly.
- Stored procedure returns rows when tested directly in SSMS.
- Model class contains only the fields the view uses.
- Controller does not contain any SQL—only a call to the data access method.
Key takeaways
- Keep the model class scoped to what the view actually uses.
- Wrap SQL Server access in a dedicated data access class, not in the controller.
- Use a stored procedure so the controller never deals with column names.
- The Conn connection string in Web.config must match the name your code reads.
- Modern projects should swap the DataTable for Dapper or EF to cut boilerplate.
Frequently asked questions
Why is my dropdown empty in the view?
The most common cause is a missing or misnamed connection string in Web.config, so GetEmployee returns an empty list. Check the Conn entry and verify the stored procedure returns rows when run directly in SQL Server.
Can I bind the dropdown to a ViewBag SelectList?
Yes. Build the SelectList in the controller from the list returned by the data access class, pass it through ViewBag, and consume it with Html.DropDownList in the view.
Do I need a stored procedure for a simple dropdown?
No, but it keeps database logic out of C# and makes it easier to change the query later without recompiling. For simple lookups, a direct SELECT via Dapper is fine.
How do I cascade one dropdown from another?
Add a parameter to the stored procedure, pass the selected value from the first dropdown through to the controller, and refresh the second dropdown either via a partial view or a JavaScript call to a child action.