THE POWER OF POSITIVE THINKING: CASCADING DROPDOWNLIST USING ASP.NET CORE MVC

Let’s implement a feature in your ASP.NET Core MVC Employee Management System where selecting a RoleId from a dropdown list dynamically populates another dropdown with the corresponding UserId and UserName values from a SQL Server database. This is a common scenario for cascading dropdowns, often used in forms (e.g., assigning tasks to users based on their roles). I’ll provide a step-by-step explanation with source code, using ADO.NET for SQL Server access, proper naming conventions, coding standards, and comments. We’ll assume this fits into your existing system with TblRole and TblUser tables.

What and Why

What

Cascading Dropdowns: When a RoleId is selected in the first dropdown, an AJAX call retrieves users (UserId and UserName) with that role, populating a second dropdown.
Tools: ASP.NET Core MVC, ADO.NET, SQL Server, jQuery for client-side interaction.

Why

User Experience: Allows admins to filter users by role before assigning tasks, reducing clutter.
Efficiency: Dynamically loads relevant data without page reloads.
Use Case: In your system, an admin might select "Manager" (RoleId=2) to see only managers in the user dropdown for task assignment.

Step 1: Database Setup

Assumption

Tables TblRole and TblUser exist from your previous setup:

sql

CREATE TABLE TblRole (
    RoleId INT PRIMARY KEY IDENTITY(1,1),
    RoleName NVARCHAR(50) NOT NULL
);

CREATE TABLE TblUser (
    UserId INT PRIMARY KEY IDENTITY(1,1),
    UserName NVARCHAR(50) NOT NULL,
    UserPassword NVARCHAR(50) NOT NULL,
    RoleId INT FOREIGN KEY REFERENCES TblRole(RoleId),
    IsActive BIT NOT NULL DEFAULT 1
);

-- Sample Data
INSERT INTO TblRole (RoleName) VALUES ('Admin'), ('Manager'), ('Employee');
INSERT INTO TblUser (UserName, UserPassword, RoleId, IsActive) 
VALUES ('admin1', 'pass123', 1, 1), ('mgr1', 'pass123', 2, 1), ('emp1', 'pass123', 3, 1);

Why

TblRole defines roles; TblUser links users to roles via RoleId.

Step 2: Update Models

What

Ensure models reflect the database schema.

How

In EmployeeManagement/Models/:

TblRole.cs:

csharp

// Represents a role in the system
public class TblRole
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }
}

TblUser.cs:

csharp

// Represents a user in the system
public class TblUser
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string UserPassword { get; set; }
    public int RoleId { get; set; }
    public bool IsActive { get; set; }
    public TblRole Role { get; set; } // Navigation property (optional)
}

TaskViewModel.cs (new, for the form):

csharp

// View model for task assignment form
public class TaskViewModel
{
    public int RoleId { get; set; }
    public int UserId { get; set; }
    public string TaskName { get; set; }
    public List<TblRole> Roles { get; set; } = new List<TblRole>();
    public List<TblUser> Users { get; set; } = new List<TblUser>();
}

Why

Models map to database tables; TaskViewModel aggregates data for the view.

Step 3: Update Repository Layer

What

Add methods to fetch roles and users by RoleId.

How

In EmployeeManagement/Repositories/:

IUserRepository.cs (new):

csharp

public interface IUserRepository
{
    List<TblRole> GetAllRoles();
    List<TblUser> GetUsersByRoleId(int roleId);
}

UserRepository.cs (new):

csharp

using System.Data.SqlClient;

public class UserRepository : IUserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
    }

    // Retrieves all roles from TblRole
    public List<TblRole> GetAllRoles()
    {
        var roles = new List<TblRole>();
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            var sql = "SELECT RoleId, RoleName FROM TblRole";
            using (var command = new SqlCommand(sql, connection))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        roles.Add(new TblRole
                        {
                            RoleId = reader.GetInt32(0),
                            RoleName = reader.GetString(1)
                        });
                    }
                }
            }
        }
        return roles;
    }

    // Retrieves users by RoleId from TblUser
    public List<TblUser> GetUsersByRoleId(int roleId)
    {
        var users = new List<TblUser>();
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            var sql = "SELECT UserId, UserName FROM TblUser WHERE RoleId = @RoleId AND IsActive = 1";
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@RoleId", roleId);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(new TblUser
                        {
                            UserId = reader.GetInt32(0),
                            UserName = reader.GetString(1)
                        });
                    }
                }
            }
        }
        return users;
    }
}

Why

Repository abstracts data access, keeping SQL logic separate from the controller.

Step 4: Update Service Layer

What

Add service methods to support the dropdowns.

How

In EmployeeManagement/Services/:

IUserService.cs (update):

csharp

public interface IUserService
{
    List<TblRole> GetAllRoles();
    List<TblUser> GetUsersByRoleId(int roleId);
}

UserService.cs (update):

csharp

public class UserService : IUserService
{
    private readonly IUserRepository _userRepository;

    public UserService(IUserRepository userRepository)
    {
        _userRepository = userRepository ?? throw new ArgumentNullException(nameof(userRepository));
    }

    public List<TblRole> GetAllRoles()
    {
        return _userRepository.GetAllRoles();
    }

    public List<TblUser> GetUsersByRoleId(int roleId)
    {
        return _userRepository.GetUsersByRoleId(roleId);
    }
}

Why

Service layer provides a clean interface for the controller, enforcing business rules if needed.

Step 5: Configure Dependency Injection

How

In EmployeeManagement/Program.cs:

csharp

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllersWithViews();

// Register dependencies
builder.Services.AddScoped<IUserRepository>(sp => 
    new UserRepository(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IUserService, UserService>();

var app = builder.Build();
app.UseStaticFiles();
app.UseRouting();
app.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
app.Run();

appsettings.json:

json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YourServer;Database=EmployeeDB;Trusted_Connection=True;"
  }
}

Why

Ensures repositories and services are available via dependency injection.

Step 6: Implement Controller

What

Create a TaskController with actions for the form and AJAX call.

How

In EmployeeManagement/Controllers/TaskController.cs:

csharp

using Microsoft.AspNetCore.Mvc;
using EmployeeManagement.Services;
using EmployeeManagement.Models;

public class TaskController : Controller
{
    private readonly IUserService _userService;

    public TaskController(IUserService userService)
    {
        _userService = userService;
    }

    // Displays the task assignment form
    public IActionResult AssignTask()
    {
        var viewModel = new TaskViewModel
        {
            Roles = _userService.GetAllRoles(),
            Users = new List<TblUser>() // Initially empty
        };
        return View(viewModel);
    }

    // AJAX endpoint to get users by RoleId
    [HttpGet]
    public IActionResult GetUsersByRoleId(int roleId)
    {
        var users = _userService.GetUsersByRoleId(roleId);
        return Json(users.Select(u => new { u.UserId, u.UserName }));
    }
}

Why

AssignTask: Loads the initial form with roles.
GetUsersByRoleId: Returns JSON data for the second dropdown.

Step 7: Create the View

What

A form with cascading dropdowns using jQuery AJAX.

How

In EmployeeManagement/Views/Task/AssignTask.cshtml:

html

@model EmployeeManagement.Models.TaskViewModel

@{
    ViewData["Title"] = "Assign Task";
}

<div class="container mt-4">
    <h2>Assign Task</h2>
    <form asp-action="AssignTask" method="post">
        <div class="form-group mb-3">
            <label for="RoleId" class="form-label">Select Role</label>
            <select asp-for="RoleId" class="form-control" id="roleDropdown">
                <option value="">-- Select Role --</option>
                @foreach (var role in Model.Roles)
                {
                    <option value="@role.RoleId">@role.RoleName</option>
                }
            </select>
        </div>
        <div class="form-group mb-3">
            <label for="UserId" class="form-label">Select User</label>
            <select asp-for="UserId" class="form-control" id="userDropdown">
                <option value="">-- Select User --</option>
            </select>
        </div>
        <div class="form-group mb-3">
            <label asp-for="TaskName" class="form-label">Task Name</label>
            <input asp-for="TaskName" class="form-control" />
        </div>
        <button type="submit" class="btn btn-primary">Assign</button>
    </form>
</div>

@section Scripts {
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#roleDropdown').change(function () {
                var roleId = $(this).val();
                if (roleId) {
                    $.ajax({
                        url: '@Url.Action("GetUsersByRoleId", "Task")',
                        type: 'GET',
                        data: { roleId: roleId },
                        success: function (data) {
                            var userDropdown = $('#userDropdown');
                            userDropdown.empty();
                            userDropdown.append('<option value="">-- Select User --</option>');
                            $.each(data, function (index, user) {
                                userDropdown.append(
                                    $('<option></option>').val(user.userId).text(user.userName)
                                );
                            });
                        },
                        error: function () {
                            alert('Error loading users.');
                        }
                    });
                } else {
                    $('#userDropdown').empty().append('<option value="">-- Select User --</option>');
                }
            });
        });
    </script>
}

Why

Role Dropdown: Populated on page load with all roles.
User Dropdown: Dynamically updated via AJAX when a role is selected.
jQuery: Handles client-side interaction for a smooth UX.

Step 8: Test in Visual Studio 2022

How

Build solution (Ctrl+Shift+B).
Set EmployeeManagement as startup project → F5.
Navigate to /Task/AssignTask.
Select a role (e.g., "Manager") → verify the user dropdown updates with relevant users (e.g., "mgr1").

Sample Output

Role Dropdown: "Admin", "Manager", "Employee".
Select "Manager" → User Dropdown: "mgr1".

Key Points

SQL: Simple queries fetch roles and users by RoleId.
AJAX: Ensures dynamic updates without page reloads.
Standards: PascalCase for classes (TaskViewModel), camelCase for variables (roleId), clear comments.

Enhancements

Add validation (e.g., required fields).
Handle POST in AssignTask to save the task (integrate with previous TaskRepository).

THE POWER OF POSITIVE THINKING

Pages

Saturday, March 8, 2025

CASCADING DROPDOWNLIST USING ASP.NET CORE MVC

No comments:

Post a Comment

About Me

Blog Archive