Project Introduction

The Traffic Management System is designed to monitor and manage traffic flow, incidents, and signals in urban areas. Built using ASP.NET and SQL Server, this application provides a comprehensive platform for collecting and analyzing traffic data, reporting incidents, and managing traffic signals. The system aims to enhance road safety, improve traffic efficiency, and provide real-time information to users, including traffic alerts and performance metrics.

Project Objectives

  • To create a secure user authentication system for managing user accounts and roles.
  • To enable the collection and management of traffic data, including vehicle counts and average speeds.
  • To track and report incidents, including their severity and location.
  • To manage traffic signals and their statuses to ensure efficient traffic flow.
  • To analyze performance metrics related to traffic conditions and incidents.
  • To provide route information, including distances and estimated travel times.
  • To implement an alert system to notify users of important traffic updates and incidents.
  • To generate reports on traffic conditions, incidents, and system performance.
  • To collect user feedback to continuously improve the system's functionality and user experience.

Project Modules

  1. User Management Module: Handles user registration, login, and role management.
  2. Traffic Data Management Module: Collects and manages traffic data from various locations.
  3. Incident Management Module: Tracks and reports traffic incidents and their details.
  4. Signal Management Module: Manages traffic signals and their operational statuses.
  5. Performance Metrics Module: Analyzes and reports on traffic performance metrics.
  6. Route Management Module: Provides information on routes, distances, and estimated travel times.
  7. Alert Management Module: Sends alerts to users regarding traffic updates and incidents.
  8. Report Generation Module: Generates reports on traffic conditions and system performance.
  9. Feedback Module: Collects user feedback to enhance the system.

SQL Server Database Tables


-- Create Users Table
CREATE TABLE Users (
    UserId INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL UNIQUE,
    PasswordHash NVARCHAR(256) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    RoleId INT,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (RoleId) REFERENCES Roles(RoleId)
);
-- Create Roles Table
CREATE TABLE Roles (
    RoleId INT PRIMARY KEY IDENTITY(1,1),
    RoleName NVARCHAR(50) NOT NULL UNIQUE
);
-- Create TrafficData Table
CREATE TABLE TrafficData (
    TrafficDataId INT PRIMARY KEY IDENTITY(1,1),
    Location NVARCHAR(100) NOT NULL,
    Timestamp DATETIME NOT NULL,
    VehicleCount INT NOT NULL,
    AverageSpeed DECIMAL(5, 2) NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create Incidents Table
CREATE TABLE Incidents (
    IncidentId INT PRIMARY KEY IDENTITY(1,1),
    Description NVARCHAR(MAX) NOT NULL,
    Location NVARCHAR(100) NOT NULL,
    Severity NVARCHAR(50) NOT NULL, -- e.g., Minor, Major, Critical
    Timestamp DATETIME NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create Signals Table
CREATE TABLE Signals (
    SignalId INT PRIMARY KEY IDENTITY(1,1),
    Location NVARCHAR(100) NOT NULL,
    SignalType NVARCHAR(50) NOT NULL, -- e.g., Traffic Light, Stop Sign
    Status NVARCHAR(50) NOT NULL, -- e.g., Active, Inactive
    CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create PerformanceMetrics Table
CREATE TABLE PerformanceMetrics (
    MetricId INT PRIMARY KEY IDENTITY(1,1),
    MetricName NVARCHAR(100) NOT NULL,
    Value DECIMAL(18, 2) NOT NULL,
    Timestamp DATETIME NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create Routes Table
CREATE TABLE Routes (
    RouteId INT PRIMARY KEY IDENTITY(1,1),
    StartLocation NVARCHAR(100) NOT NULL,
    EndLocation NVARCHAR(100) NOT NULL,
    Distance DECIMAL(10, 2) NOT NULL,
    EstimatedTime DECIMAL(10, 2) NOT NULL, -- in minutes
    CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create Alerts Table
CREATE TABLE Alerts (
    AlertId INT PRIMARY KEY IDENTITY(1,1),
    UserId INT,
    Message NVARCHAR(256) NOT NULL,
    IsRead BIT DEFAULT 0,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (User Id) REFERENCES Users(UserId)
);
-- Create Reports Table
CREATE TABLE Reports (
    ReportId INT PRIMARY KEY IDENTITY(1,1),
    UserId INT,
    ReportDate DATETIME DEFAULT GETDATE(),
    Description NVARCHAR(MAX),
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (User Id) REFERENCES Users(UserId)
);

Explanation of the Tables

Users: Stores user information, including username, password hash, email, and role.

Roles: Defines user roles (e.g., Admin, Traffic Officer).

TrafficData: Contains traffic data records, including location, timestamp, vehicle count, and average speed.

Incidents: Stores information about traffic incidents, including description, location, severity, and timestamp.

Signals: Manages traffic signals, including location, type, and status.

PerformanceMetrics: Stores performance metrics related to traffic management, including metric name and value.

Routes: Contains information about routes, including start and end locations, distance, and estimated travel time.

Alerts: Manages alerts for users, such as notifications about incidents or traffic conditions.

Reports: Stores reports submitted by users, including description and associated user.

To create models and repositories using ADO.NET for your Traffic Management System based on the provided SQL Server queries

We will follow these steps:

Create Models

Define C# classes that represent the database tables.


Models/User.cs
public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string PasswordHash { get; set; }
    public string Email { get; set; }
    public int RoleId { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Role.cs
public class Role
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }
}
Models/TrafficData.cs
public class TrafficData
{
    public int TrafficDataId { get; set; }
    public string Location { get; set; }
    public DateTime Timestamp { get; set; }
    public int VehicleCount { get; set; }
    public decimal AverageSpeed { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Incident.cs
public class Incident
{
    public int IncidentId { get; set; }
    public string Description { get; set; }
    public string Location { get; set; }
    public string Severity { get; set; }
    public DateTime Timestamp { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Signal.cs
public class Signal
{
    public int SignalId { get; set; }
    public string Location { get; set; }
    public string SignalType { get; set; }
    public string Status { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/PerformanceMetric.cs
public class PerformanceMetric
{
    public int MetricId { get; set; }
    public string MetricName { get; set; }
    public decimal Value { get; set; }
    public DateTime Timestamp { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Route.cs
public class Route
{
    public int RouteId { get; set; }
    public string StartLocation { get; set; }
    public string EndLocation { get; set; }
    public decimal Distance { get; set; }
    public decimal EstimatedTime { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Alert.cs
public class Alert
{
    public int AlertId { get; set; }
    public int UserId { get; set; }
    public string Message { get; set; }
    public bool IsRead { get; set; }
    public DateTime CreatedAt { get; set; }
}
Models/Report.cs
public class Report
{
    public int ReportId { get; set; }
    public int UserId { get; set; }
    public DateTime ReportDate { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }
}

Create Repositories

Now, let's create repository classes for each model using ADO.NET.

Repositories/UserRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class UserRepository
{
    private readonly string _connectionString;
    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddUser (User user)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Users (Username, PasswordHash, Email, RoleId) VALUES (@Username, @PasswordHash, @Email, @RoleId)", connection);
            command.Parameters.AddWithValue("@Username", user.Username);
            command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
            command.Parameters.AddWithValue("@Email", user.Email);
            command.Parameters.AddWithValue("@RoleId", user.RoleId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public User GetUser ById(int userId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Users WHERE UserId = @User Id", connection);
            command.Parameters.AddWithValue("@User Id", userId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new User
                    {
                        UserId = (int)reader["User Id"],
                        Username = reader["Username"].ToString(),
                        PasswordHash = reader["PasswordHash"].ToString(),
                        Email = reader["Email"].ToString(),
                        RoleId = (int)reader["RoleId"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<User> GetAllUsers()
    {
        var users = new List<User>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Users", connection);
            connection.Open();
 using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    users.Add(new User
                    {
                        UserId = (int)reader["User Id"],
                        Username = reader["Username"].ToString(),
                        PasswordHash = reader["PasswordHash"].ToString(),
                        Email = reader["Email"].ToString(),
                        RoleId = (int)reader["RoleId"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return users;
    }
    public void UpdateUser (User user)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Users SET Username = @Username, PasswordHash = @PasswordHash, Email = @Email, RoleId = @RoleId WHERE UserId = @User Id", connection);
            command.Parameters.AddWithValue("@Username", user.Username);
            command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
            command.Parameters.AddWithValue("@Email", user.Email);
            command.Parameters.AddWithValue("@RoleId", user.RoleId);
            command.Parameters.AddWithValue("@User Id", user.UserId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteUser (int userId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Users WHERE UserId = @User Id", connection);
            command.Parameters.AddWithValue("@User Id", userId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/RoleRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class RoleRepository
{
    private readonly string _connectionString;
    public RoleRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddRole(Role role)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Roles (RoleName) VALUES (@RoleName)", connection);
            command.Parameters.AddWithValue("@RoleName", role.RoleName);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public Role GetRoleById(int roleId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Roles WHERE RoleId = @RoleId", connection);
            command.Parameters.AddWithValue("@RoleId", roleId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Role
                    {
                        RoleId = (int)reader["RoleId"],
                        RoleName = reader["RoleName"].ToString()
                    };
                }
            }
        }
        return null;
    }
    public List<Role> GetAllRoles()
    {
        var roles = new List<Role>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Roles", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    roles.Add(new Role
                    {
                        RoleId = (int)reader["RoleId"],
                        RoleName = reader["RoleName"].ToString()
                    });
                }
            }
        }
        return roles;
    }
    public void UpdateRole(Role role)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Roles SET RoleName = @RoleName WHERE RoleId = @RoleId", connection);
            command.Parameters.AddWithValue("@RoleName", role.RoleName);
            command.Parameters.AddWithValue("@RoleId", role.RoleId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteRole(int roleId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Roles WHERE RoleId = @RoleId", connection);
            command.Parameters.AddWithValue("@RoleId", roleId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/TrafficDataRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class TrafficDataRepository
{
    private readonly string _connectionString;
    public TrafficDataRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddTrafficData(TrafficData trafficData)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO TrafficData (Location, Timestamp, VehicleCount, AverageSpeed) VALUES (@Location, @Timestamp , @VehicleCount, @AverageSpeed)", connection);
            command.Parameters.AddWithValue("@Location", trafficData.Location);
            command.Parameters.AddWithValue("@Timestamp", trafficData.Timestamp);
            command.Parameters.AddWithValue("@VehicleCount", trafficData.VehicleCount);
            command.Parameters.AddWithValue("@AverageSpeed", trafficData.AverageSpeed);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public TrafficData GetTrafficDataById(int trafficDataId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM TrafficData WHERE TrafficDataId = @TrafficDataId", connection);
            command.Parameters.AddWithValue("@TrafficDataId", trafficDataId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new TrafficData
                    {
                        TrafficDataId = (int)reader["TrafficDataId"],
                        Location = reader["Location"].ToString(),
                        Timestamp = (DateTime)reader["Timestamp"],
                        VehicleCount = (int)reader["VehicleCount"],
                        AverageSpeed = (decimal)reader["AverageSpeed"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<TrafficData> GetAllTrafficData()
    {
        var trafficDataList = new List<TrafficData>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM TrafficData", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    trafficDataList.Add(new TrafficData
                    {
                        TrafficDataId = (int)reader["TrafficDataId"],
                        Location = reader["Location"].ToString(),
                        Timestamp = (DateTime)reader["Timestamp"],
                        VehicleCount = (int)reader["VehicleCount"],
                        AverageSpeed = (decimal)reader["AverageSpeed"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return trafficDataList;
    }
    public void UpdateTrafficData(TrafficData trafficData)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE TrafficData SET Location = @Location, Timestamp = @Timestamp, VehicleCount = @VehicleCount, AverageSpeed = @AverageSpeed WHERE TrafficDataId = @TrafficDataId", connection);
            command.Parameters.AddWithValue("@Location", trafficData.Location);
            command.Parameters.AddWithValue("@Timestamp", trafficData.Timestamp);
            command.Parameters.AddWithValue("@VehicleCount", trafficData.VehicleCount);
            command.Parameters.AddWithValue("@AverageSpeed", trafficData.AverageSpeed);
            command.Parameters.AddWithValue("@TrafficDataId", trafficData.TrafficDataId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteTrafficData(int trafficDataId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM TrafficData WHERE TrafficDataId = @TrafficDataId", connection);
            command.Parameters.AddWithValue("@TrafficDataId", trafficDataId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/IncidentRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class IncidentRepository
{
    private readonly string _connectionString;
    public IncidentRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddIncident(Incident incident)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Incidents (Description, Location, Severity, Timestamp) VALUES (@Description, @Location, @Severity, @Timestamp)", connection);
            command.Parameters.AddWithValue("@Description", incident.Description);
            command.Parameters.AddWithValue("@Location", incident.Location);
            command.Parameters.AddWithValue("@Severity", incident.Severity);
            command.Parameters.AddWithValue("@Timestamp", incident.Timestamp);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public Incident GetIncidentById(int incidentId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Incidents WHERE IncidentId = @IncidentId", connection);
            command.Parameters.AddWithValue("@IncidentId", incidentId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Incident
                    {
                        IncidentId = (int)reader["IncidentId"],
                        Description = reader["Description"].ToString(),
                        Location = reader["Location"].ToString(),
                        Severity = reader["Severity"].ToString(),
                        Timestamp = (DateTime)reader["Timestamp"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<Incident> GetAllIncidents()
    {
        var incidents = new List<Incident>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Incidents", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    incidents.Add(new Incident
                    {
                        IncidentId = (int)reader["IncidentId"],
                        Description = reader["Description"].ToString(),
                        Location = reader["Location"].ToString(),
                        Severity = reader["Severity"].ToString(),
                        Timestamp = (DateTime)reader["Timestamp"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return incidents;
    }
    public void UpdateIncident(Incident incident)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Incidents SET Description = @Description, Location = @Location, Severity = @Severity, Timestamp = @Timestamp WHERE IncidentId = @IncidentId", connection);
            command.Parameters.AddWithValue("@Description", incident.Description);
            command.Parameters.AddWithValue("@Location", incident.Location);
            command.Parameters.AddWithValue("@Severity", incident.Severity);
            command.Parameters.AddWithValue("@Timestamp", incident.Timestamp);
            command.Parameters.AddWithValue("@IncidentId", incident.IncidentId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteIncident(int incidentId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Incidents WHERE IncidentId = @IncidentId", connection);
            command.Parameters.AddWithValue("@IncidentId", incidentId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/SignalRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class SignalRepository
{
    private readonly string _connectionString;
    public SignalRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddSignal(Signal signal)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Signals (Location, SignalType, Status) VALUES (@Location, @SignalType, @Status)", connection);
            command.Parameters.AddWithValue("@Location", signal.Location);
            command.Parameters.AddWithValue("@SignalType", signal.SignalType);
            command.Parameters.AddWithValue("@Status", signal.Status);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public Signal GetSignalById(int signalId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Signals WHERE SignalId = @SignalId", connection);
            command.Parameters.AddWithValue("@SignalId", signalId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Signal
                    {
                        SignalId = (int)reader["SignalId"],
                        Location = reader["Location"].ToString(),
                        SignalType = reader["SignalType"].ToString(),
                        Status = reader["Status"].ToString(),
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<Signal> GetAllSignals()
    {
        var signals = new List<Signal>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Signals", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    signals.Add(new Signal
                    {
                        SignalId = (int)reader["SignalId"],
                        Location = reader["Location"].ToString(),
                        SignalType = reader["SignalType"].ToString(),
                        Status = reader["Status"].ToString(),
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return signals;
    }
    public void UpdateSignal(Signal signal)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Signals SET Location = @Location, SignalType = @SignalType, Status = @Status WHERE SignalId = @SignalId", connection);
            command.Parameters.AddWithValue("@Location", signal.Location);
            command.Parameters.AddWithValue("@SignalType", signal.SignalType);
            command.Parameters.AddWithValue("@Status ", signal.Status);
            command.Parameters.AddWithValue("@SignalId", signal.SignalId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteSignal(int signalId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Signals WHERE SignalId = @SignalId", connection);
            command.Parameters.AddWithValue("@SignalId", signalId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/PerformanceMetricRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class PerformanceMetricRepository
{
    private readonly string _connectionString;
    public PerformanceMetricRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddPerformanceMetric(PerformanceMetric metric)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO PerformanceMetrics (MetricName, Value, Timestamp) VALUES (@MetricName, @Value, @Timestamp)", connection);
            command.Parameters.AddWithValue("@MetricName", metric.MetricName);
            command.Parameters.AddWithValue("@Value", metric.Value);
            command.Parameters.AddWithValue("@Timestamp", metric.Timestamp);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public PerformanceMetric GetPerformanceMetricById(int metricId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM PerformanceMetrics WHERE MetricId = @MetricId", connection);
            command.Parameters.AddWithValue("@MetricId", metricId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new PerformanceMetric
                    {
                        MetricId = (int)reader["MetricId"],
                        MetricName = reader["MetricName"].ToString(),
                        Value = (decimal)reader["Value"],
                        Timestamp = (DateTime)reader["Timestamp"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<PerformanceMetric> GetAllPerformanceMetrics()
    {
        var metrics = new List<PerformanceMetric>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM PerformanceMetrics", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    metrics.Add(new PerformanceMetric
                    {
                        MetricId = (int)reader["MetricId"],
                        MetricName = reader["MetricName"].ToString(),
                        Value = (decimal)reader["Value"],
                        Timestamp = (DateTime)reader["Timestamp"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return metrics;
    }
    public void UpdatePerformanceMetric(PerformanceMetric metric)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE PerformanceMetrics SET MetricName = @MetricName, Value = @Value, Timestamp = @Timestamp WHERE MetricId = @MetricId", connection);
            command.Parameters.AddWithValue("@MetricName", metric.MetricName);
            command.Parameters.AddWithValue("@Value", metric.Value);
            command.Parameters.AddWithValue("@Timestamp", metric.Timestamp);
            command.Parameters.AddWithValue("@MetricId", metric.MetricId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeletePerformanceMetric(int metricId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM PerformanceMetrics WHERE MetricId = @MetricId", connection);
            command.Parameters.AddWithValue("@MetricId", metricId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/RouteRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class RouteRepository
{
    private readonly string _connectionString;
    public RouteRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddRoute(Route route)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Routes (StartLocation, EndLocation, Distance, EstimatedTime) VALUES (@StartLocation, @EndLocation, @Distance, @EstimatedTime)", connection);
            command.Parameters.AddWithValue("@StartLocation", route.StartLocation);
            command.Parameters.AddWithValue("@EndLocation", route.EndLocation);
            command.Parameters.AddWithValue("@Distance", route.Distance);
            command.Parameters.AddWithValue("@EstimatedTime", route.EstimatedTime);
            connection.Open();
            command .ExecuteNonQuery();
        }
    }
    public Route GetRouteById(int routeId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Routes WHERE RouteId = @RouteId", connection);
            command.Parameters.AddWithValue("@RouteId", routeId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Route
                    {
                        RouteId = (int)reader["RouteId"],
                        StartLocation = reader["StartLocation"].ToString(),
                        EndLocation = reader["EndLocation"].ToString(),
                        Distance = (decimal)reader["Distance"],
                        EstimatedTime = (decimal)reader["EstimatedTime"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<Route> GetAllRoutes()
    {
        var routes = new List<Route>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Routes", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    routes.Add(new Route
                    {
                        RouteId = (int)reader["RouteId"],
                        StartLocation = reader["StartLocation"].ToString(),
                        EndLocation = reader["EndLocation"].ToString(),
                        Distance = (decimal)reader["Distance"],
                        EstimatedTime = (decimal)reader["EstimatedTime"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return routes;
    }
    public void UpdateRoute(Route route)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Routes SET StartLocation = @StartLocation, EndLocation = @EndLocation, Distance = @Distance, EstimatedTime = @EstimatedTime WHERE RouteId = @RouteId", connection);
            command.Parameters.AddWithValue("@StartLocation", route.StartLocation);
            command.Parameters.AddWithValue("@EndLocation", route.EndLocation);
            command.Parameters.AddWithValue("@Distance", route.Distance);
            command.Parameters.AddWithValue("@EstimatedTime", route.EstimatedTime);
            command.Parameters.AddWithValue("@RouteId", route.RouteId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteRoute(int routeId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Routes WHERE RouteId = @RouteId", connection);
            command.Parameters.AddWithValue("@RouteId", routeId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/AlertRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class AlertRepository
{
    private readonly string _connectionString;
    public AlertRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddAlert(Alert alert)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Alerts (User Id, Message, IsRead) VALUES (@User Id, @Message, @IsRead)", connection);
            command.Parameters.AddWithValue("@User Id", alert.UserId);
            command.Parameters.AddWithValue("@Message", alert.Message);
            command.Parameters.AddWithValue("@IsRead", alert.IsRead);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public Alert GetAlertById(int alertId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Alerts WHERE AlertId = @AlertId", connection);
            command.Parameters.AddWithValue("@AlertId", alertId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Alert
                    {
                        AlertId = (int)reader["AlertId"],
                        UserId = (int)reader["User Id"],
                        Message = reader["Message"].ToString(),
                        IsRead = (bool)reader["IsRead"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<Alert> GetAllAlerts()
    {
        var alerts = new List<Alert>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Alerts", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    alerts.Add(new Alert
                    {
                        AlertId = (int)reader["AlertId"],
                        User Id = (int)reader["User  Id"],
                        Message = reader["Message"].ToString(),
                        IsRead = (bool)reader["IsRead"],
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return alerts;
    }
    public void UpdateAlert(Alert alert)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Alerts SET UserId = @User Id, Message = @Message, IsRead = @IsRead WHERE AlertId = @AlertId", connection);
            command.Parameters.AddWithValue("@User Id", alert.UserId);
            command.Parameters.AddWithValue("@Message", alert.Message);
            command.Parameters.AddWithValue("@IsRead", alert.IsRead);
            command.Parameters.AddWithValue("@AlertId", alert.AlertId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteAlert(int alertId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Alerts WHERE AlertId = @AlertId", connection);
            command.Parameters.AddWithValue("@AlertId", alertId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Repositories/ReportRepository.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class ReportRepository
{
    private readonly string _connectionString;
    public ReportRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void AddReport(Report report)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("INSERT INTO Reports (User Id, ReportDate, Description) VALUES (@User Id, @ReportDate, @Description)", connection);
            command.Parameters.AddWithValue("@User Id", report.UserId);
            command.Parameters.AddWithValue("@ReportDate", report.ReportDate);
            command.Parameters.AddWithValue("@Description", report.Description);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public Report GetReportById(int reportId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Reports WHERE ReportId = @ReportId", connection);
            command.Parameters.AddWithValue("@ReportId", reportId);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Report
                    {
                        ReportId = (int)reader["ReportId"],
                        UserId = (int)reader["User Id"],
                        ReportDate = (DateTime)reader["ReportDate"],
                        Description = reader["Description"].ToString(),
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    };
                }
            }
        }
        return null;
    }
    public List<Report> GetAllReports()
    {
        var reports = new List<Report>();
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT * FROM Reports", connection);
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    reports.Add(new Report
                    {
                        ReportId = (int)reader["ReportId"],
                        UserId = (int)reader["User Id"],
                        ReportDate = (DateTime)reader["ReportDate"],
                        Description = reader["Description"].ToString(),
                        CreatedAt = (DateTime)reader["CreatedAt"]
                    });
                }
            }
        }
        return reports;
    }
    public void UpdateReport(Report report)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("UPDATE Reports SET UserId = @User Id, ReportDate = @ReportDate, Description = @Description WHERE ReportId = @ReportId", connection);
            command.Parameters.AddWithValue("@User Id", report.UserId);
            command.Parameters.AddWithValue("@ReportDate", report.ReportDate);
            command.Parameters.AddWithValue("@Description", report.Description);
            command.Parameters.AddWithValue("@ReportId", report.ReportId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    public void DeleteReport(int reportId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("DELETE FROM Reports WHERE ReportId = @ReportId", connection);
            command.Parameters.AddWithValue("@ReportId", reportId);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

This completes the implementation of models and repositories for your Traffic Management System using ADO.NET. Each repository class provides methods for CRUD operations, allowing you to interact with the corresponding database tables effectively.

Now create controller for each repository

Below are the controller implementations for each repository in your Traffic Management System. Each controller will handle HTTP requests and interact with the corresponding repository to perform CRUD operations.

UserController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class UserController : Controller
{
    private readonly UserRepository _userRepository;
    public UserController(UserRepository userRepository)
    {
        _userRepository = userRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var users = _userRepository.GetAllUsers();
        return View(users);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(User user)
    {
        _userRepository.AddUser (user);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var user = _userRepository.GetUser ById(id);
        return View(user);
    }
    [HttpPost]
    public IActionResult Edit(User user)
    {
        _userRepository.UpdateUser (user);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _userRepository.DeleteUser (id);
        return RedirectToAction("Index");
    }
}

RoleController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class RoleController : Controller
{
    private readonly RoleRepository _roleRepository;
    public RoleController(RoleRepository roleRepository)
    {
        _roleRepository = roleRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var roles = _roleRepository.GetAllRoles();
        return View(roles);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Role role)
    {
        _roleRepository.AddRole(role);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var role = _roleRepository.GetRoleById(id);
        return View(role);
    }
    [HttpPost]
    public IActionResult Edit(Role role)
    {
        _roleRepository.UpdateRole(role);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _roleRepository.DeleteRole(id);
        return RedirectToAction("Index");
    }
}

TrafficDataController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class TrafficDataController : Controller
{
    private readonly TrafficDataRepository _trafficDataRepository;
    public TrafficDataController(TrafficDataRepository trafficDataRepository)
    {
        _trafficDataRepository = trafficDataRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var trafficData = _trafficDataRepository.GetAllTrafficData();
        return View(trafficData);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(TrafficData trafficData)
    {
        _trafficDataRepository.AddTrafficData(trafficData);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var trafficData = _trafficDataRepository.GetTrafficDataById(id);
        return View(trafficData);
    }
    [HttpPost]
    public IActionResult Edit(TrafficData trafficData)
    {
        _trafficDataRepository.UpdateTrafficData(trafficData);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _trafficDataRepository.DeleteTrafficData(id);
        return RedirectToAction("Index");
    }
}

IncidentController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class IncidentController : Controller
{
    private readonly IncidentRepository _incidentRepository;
    public IncidentController(IncidentRepository incidentRepository)
    {
        _incidentRepository = incidentRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var incidents = _incidentRepository.GetAllIncidents();
        return View(incidents);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Incident incident)
    {
        _incidentRepository.AddIncident(incident);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var incident = _incidentRepository.GetIncidentById(id);
        return View(incident);
    }
    [HttpPost]
    public IActionResult Edit(Incident incident)
    {
        _incidentRepository.UpdateIncident(incident);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _incidentRepository.DeleteIncident(id);
        return RedirectToAction("Index");
    }
}

SignalController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class SignalController : Controller
{
    private readonly SignalRepository _signalRepository;
    public SignalController(SignalRepository signalRepository)
    {
        _signalRepository = signalRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var signals = _signalRepository.GetAllSignals();
        return View(signals);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Signal signal)
    {
        _signalRepository.AddSignal(signal);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var signal = _signalRepository.GetSignalById(id);
        return View(signal);
    }
    [HttpPost]
    public IActionResult Edit(Signal signal)
    {
        _signalRepository.UpdateSignal(signal);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _signalRepository.DeleteSignal(id);
        return RedirectToAction("Index");
    }
}

PerformanceMetricController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class PerformanceMetricController : Controller
{
    private readonly PerformanceMetricRepository _metricRepository;
    public PerformanceMetricController(PerformanceMetricRepository metricRepository)
    {
        _metricRepository = metricRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var metrics = _metricRepository.GetAllPerformanceMetrics();
        return View(metrics);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(PerformanceMetric metric)
    {
        _metricRepository.AddPerformanceMetric(metric);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var metric = _metricRepository.GetPerformanceMetricById(id);
        return View(metric);
    }
    [HttpPost]
    public IActionResult Edit(PerformanceMetric metric)
    {
        _metricRepository.UpdatePerformanceMetric(metric);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _metricRepository.DeletePerformanceMetric(id);
        return RedirectToAction("Index");
    }
}

RouteController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class RouteController : Controller
{
    private readonly RouteRepository _routeRepository;
    public RouteController(RouteRepository routeRepository)
    {
        _routeRepository = routeRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var routes = _routeRepository.GetAllRoutes();
        return View(routes);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Route route)
    {
        _routeRepository.AddRoute(route);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var route = _routeRepository.GetRouteById(id);
        return View(route);
    }
    [HttpPost]
    public IActionResult Edit(Route route)
    {
        _routeRepository.UpdateRoute(route);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _routeRepository.DeleteRoute(id);
        return RedirectToAction("Index");
    }
}

AlertController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class AlertController : Controller
{
    private readonly AlertRepository _alertRepository;
    public AlertController(AlertRepository alertRepository {
        _alertRepository = alertRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var alerts = _alertRepository.GetAllAlerts();
        return View(alerts);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Alert alert)
    {
        _alertRepository.AddAlert(alert);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var alert = _alertRepository.GetAlertById(id);
        return View(alert);
    }
    [HttpPost]
    public IActionResult Edit(Alert alert)
    {
        _alertRepository.UpdateAlert(alert);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _alertRepository.DeleteAlert(id);
        return RedirectToAction("Index");
    }
}

ReportController.cs


using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
public class ReportController : Controller
{
    private readonly ReportRepository _reportRepository;
    public ReportController(ReportRepository reportRepository)
    {
        _reportRepository = reportRepository;
    }
    [HttpGet]
    public IActionResult Index()
    {
        var reports = _reportRepository.GetAllReports();
        return View(reports);
    }
    [HttpGet]
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Report report)
    {
        _reportRepository.AddReport(report);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Edit(int id)
    {
        var report = _reportRepository.GetReportById(id);
        return View(report);
    }
    [HttpPost]
    public IActionResult Edit(Report report)
    {
        _reportRepository.UpdateReport(report);
        return RedirectToAction("Index");
    }
    [HttpGet]
    public IActionResult Delete(int id)
    {
        _reportRepository.DeleteReport(id);
        return RedirectToAction("Index");
    }
}

This completes the implementation of controllers for each repository in your Traffic Management System. Each controller provides methods for handling HTTP requests related to the respective entities, allowing for the creation, retrieval, updating, and deletion of records.

Below are the Razor view files for each controller in your Traffic Management System

Each view corresponds to the actions defined in the controllers, allowing you to create, read, update, and delete records for each entity.

User Views

Views/User/Index.cshtml


@model IEnumerable<User>
<h2>Users</h2>
<a href='@Url.Action("Create")'>Create New User</a>
<table>
    <thead>
        <tr>
            <th>Username</th>
            <th>Email</th>
            <th>Role ID</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var user in Model)
        {
            <tr>
                <td>@user.Username</td>
                <td>@user.Email</td>
                <td>@user.RoleId</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = user.UserId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = user.UserId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/User/Create.cshtml


@model User
<h2>Create User</h2>
<form asp-action="Create" method="post">
    <label>Username:</label>
    <input asp-for="Username" required />
    <label>Password:</label>
    <input asp-for="PasswordHash" type="password" required />
    <label>Email:</label>
    <input asp-for="Email" required />
    <label>Role ID:</label>
    <input asp-for="RoleId" required />
    <button type="submit">Create</button>
</form>

Views/User/Edit.cshtml


@model User
<h2>Edit User</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="User Id" />
    <label>Username:</label>
    <input asp-for="Username" required />
    <label>Password:</label>
    <input asp-for="PasswordHash" type="password" />
    <label>Email:</label>
    <input asp-for="Email" required />
    <label>Role ID:</label>
    <input asp-for="RoleId" required />
    <button type="submit">Update</button>
</form>

2. Role Views
Views/Role/Index.cshtml

@model IEnumerable<Role>
<h2>Roles</h2>
<a href='@Url.Action("Create")'>Create New Role</a>
<table>
    <thead>
        <tr>
            <th>Role Name</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var role in Model)
        {
            <tr>
                <td>@role.RoleName</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = role.RoleId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = role.RoleId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Role/Create.cshtml


@model Role
<h2>Create Role</h2>
<form asp-action="Create" method="post">
    <label>Role Name:</label>
    <input asp-for="RoleName" required />
    <button type="submit">Create</button>
</form>

Views/Role/Edit.cshtml


@model Role
<h2>Edit Role</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="RoleId" />
    <label>Role Name:</label>
    <input asp-for="RoleName" required />
    <button type="submit">Update</button>
</form>

Traffic Data Views

Views/TrafficData/Index.cshtml


@model IEnumerable<TrafficData>
<h2>Traffic Data</h2>
<a href='@Url.Action("Create")'>Create New Traffic Data</a>
<table>
    <thead>
        <tr>
            <th>Location</th>
            <th>Timestamp</th>
            <th>Vehicle Count</th>
            <th>Average Speed</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var data in Model)
        {
            <tr>
                <td>@data.Location</td>
                <td>@data.Timestamp</td>
                <td>@data.VehicleCount</td>
                <td>@data.AverageSpeed</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = data.TrafficDataId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = data.TrafficDataId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/TrafficData/Create.cshtml


@model TrafficData
<h2>Create Traffic Data</h2>
<form asp-action="Create" method="post">
    <label>Location:</label>
    <input asp-for="Location" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <label>Vehicle Count:</label>
    <input asp-for="VehicleCount" required />
    <label>Average Speed:</label>
    <input asp-for="AverageSpeed" required />
    <button type="submit">Create</button>
</form>

Views/TrafficData/Edit.cshtml


@model TrafficData
<h2>Edit Traffic Data</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="TrafficDataId" />
    <label>Location:</label>
    <input asp-for="Location" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <label>Vehicle Count:</label>
    <input asp-for="VehicleCount" required />
    <label>Average Speed:</label>
    <input asp-for="AverageSpeed" required />
    <button type="submit">Update</button>
</form>

Incident Views

Views/Incident/Index.cshtml


@model IEnumerable<Incident>
<h2>Incidents</h2>
<a href='@Url.Action("Create")'>Create New Incident</a>
<table>
    <thead>
        <tr>
            <th>Description</th>
            <th>Location</th>
            <th>Severity</th>
            <th>Timestamp</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var incident in Model)
        {
            <tr>
                <td>@incident.Description</td>
                <td>@incident.Location</td>
                <td>@incident.Severity</td>
                <td>@incident.Timestamp</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = incident.IncidentId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = incident.IncidentId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Incident/Create.cshtml


@model Incident
<h2>Create Incident</h2>
<form asp-action="Create" method="post">
    <label>Description:</label>
    <textarea asp-for="Description" required></textarea>
    <label>Location:</label>
    <input asp-for="Location" required />
    <label>Severity:</label>
    <input asp-for="Severity" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Create</button>
</form>

Views/Incident/Edit.cshtml


@model Incident
<h2>Edit Incident</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="IncidentId" />
    <label>Description:</label>
    <textarea asp-for="Description" required></textarea>
    <label>Location:</label>
    <input asp-for="Location" required />
    <label>Severity:</label>
    <input asp-for="Severity" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Update</button>
</form>

Signal Views

Views/Signal/Index.cshtml


@model IEnumerable<Signal>
<h2>Signals</h2>
<a href='@Url.Action("Create")'>Create New Signal</a>
<table>
    <thead>
        <tr>
            <th>Signal Type</th>
            <th>Status</th>
            <th>Location</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var signal in Model)
        {
            <tr>
                <td>@signal.SignalType</td>
                <td>@signal.Status</td>
                <td>@signal.Location</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = signal.SignalId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = signal.SignalId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Signal/Create.cshtml


@model Signal
<h2>Create Signal</h2>
<form asp-action="Create" method="post">
    <label>Signal Type:</label>
    <input asp-for="SignalType" required />
    <label>Status:</label>
    <input asp-for="Status" required />
    <label>Location:</label>
    <input asp-for="Location" required />
    <button type="submit">Create</button>
</form>

Views/Signal/Edit.cshtml


@model Signal
<h2>Edit Signal</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="SignalId" />
    <label>Signal Type:</label>
    <input asp-for="SignalType" required />
    <label>Status:</label>
    <input asp-for="Status" required />
    <label>Location:</label>
    <input asp-for="Location" required />
    <button type="submit">Update</button>
</form>

Performance Metric Views

Views/PerformanceMetric/Index.cshtml


@model IEnumerable<PerformanceMetric>
<h2>Performance Metrics</h2>
<a href='@Url.Action("Create")'>Create New Performance Metric</a>
<table>
    <thead>
        <tr>
            <th>Metric Name</th>
            <th>Value</th>
            <th>Timestamp</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var metric in Model)
        {
            <tr>
                <td>@metric.MetricName</td>
                <td>@metric.Value</td>
                <td>@metric.Timestamp</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = metric.MetricId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = metric.MetricId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/PerformanceMetric/Create.cshtml


@model PerformanceMetric
<h2>Create Performance Metric</h2>
<form asp-action="Create" method="post">
    <label>Metric Name:</label>
    <input asp-for="MetricName" required />
    <label>Value:</label>
    <input asp-for="Value" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Create</button>
</form>

Views/PerformanceMetric/Edit.cshtml


@model PerformanceMetric
<h2>Edit Performance Metric</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="MetricId" />
    <label>Metric Name:</label>
    <input asp -for="MetricName" required />
    <label>Value:</label>
    <input asp-for="Value" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Update</button>
</form>

Route Views

Views/Route/Index.cshtml


@model IEnumerable<Route>
<h2>Routes</h2>
<a href='@Url.Action("Create")'>Create New Route</a>
<table>
    <thead>
        <tr>
            <th>Route Name</th>
            <th>Start Location</th>
            <th>End Location</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var route in Model)
        {
            <tr>
                <td>@route.RouteName</td>
                <td>@route.StartLocation</td>
                <td>@route.EndLocation</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = route.RouteId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = route.RouteId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Route/Create.cshtml


@model Route
<h2>Create Route</h2>
<form asp-action="Create" method="post">
    <label>Route Name:</label>
    <input asp-for="RouteName" required />
    <label>Start Location:</label>
    <input asp-for="StartLocation" required />
    <label>End Location:</label>
    <input asp-for="EndLocation" required />
    <button type="submit">Create</button>
</form>

Views/Route/Edit.cshtml


@model Route
<h2>Edit Route</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="RouteId" />
    <label>Route Name:</label>
    <input asp-for="RouteName" required />
    <label>Start Location:</label>
    <input asp-for="StartLocation" required />
    <label>End Location:</label>
    <input asp-for="EndLocation" required />
    <button type="submit">Update</button>
</form>

Alert Views

Views/Alert/Index.cshtml


@model IEnumerable<Alert>
<h2>Alerts</h2>
<a href='@Url.Action("Create")'>Create New Alert</a>
<table>
    <thead>
        <tr>
            <th>Alert Message</th>
            <th>Severity</th>
            <th>Timestamp</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var alert in Model)
        {
            <tr>
                <td>@alert.Message</td>
                <td>@alert.Severity</td>
                <td>@alert.Timestamp</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = alert.AlertId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = alert.AlertId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Alert/Create.cshtml


@model Alert
<h2>Create Alert</h2>
<form asp-action="Create" method="post">
    <label>Message:</label>
    <textarea asp-for="Message" required></textarea>
    <label>Severity:</label>
    <input asp-for="Severity" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Create</button>
</form>

Views/Alert/Edit.cshtml


@model Alert
<h2>Edit Alert</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="AlertId" />
    <label>Message:</label>
    <textarea asp-for="Message" required></textarea>
    <label>Severity:</label>
    <input asp-for="Severity" required />
    <label>Timestamp:</label>
    <input asp-for="Timestamp" type="datetime-local" required />
    <button type="submit">Update</button>
</form>

Report Views

Views/Report/Index.cshtml


@model IEnumerable<Report>
<h2>Reports</h2>
<a href='@Url.Action("Create")'>Create New Report</a>
<table>
    <thead>
        <tr>
 <th>Report Title</th>
            <th>Generated On</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var report in Model)
        {
            <tr>
                <td>@report.Title</td>
                <td>@report.GeneratedOn</td>
                <td>
                    <a href='@Url.Action("Edit", new { id = report.ReportId })'>Edit</a> |
                    <a href='@Url.Action("Delete", new { id = report.ReportId })'>Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Views/Report/Create.cshtml


@model Report
<h2>Create Report</h2>
<form asp-action="Create" method="post">
    <label>Title:</label>
    <input asp-for="Title" required />
    <label>Content:</label>
    <textarea asp-for="Content" required></textarea>
    <label>Generated On:</label>
    <input asp-for="GeneratedOn" type="datetime-local" required />
    <button type="submit">Create</button>
</form>

Views/Report/Edit.cshtml


@model Report
<h2>Edit Report</h2>
<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="ReportId" />
    <label>Title:</label>
    <input asp-for="Title" required />
    <label>Content:</label>
    <textarea asp-for="Content" required></textarea>
    <label>Generated On:</label>
    <input asp-for="GeneratedOn" type="datetime-local" required />
    <button type="submit">Update</button>
</form>

This completes the Razor view files for all controllers in your Traffic Management System. Each view is designed to facilitate the management of the respective entities, providing a user-friendly interface for creating, editing, and deleting records.

To create a dashboard page for your Traffic Management System that displays consolidated data

We will follow these steps:

Create a ViewModel to hold the data for the dashboard.

Create a Dashboard Controller to fetch the data from the repositories.

Create a Razor View to display the dashboard.

Create a Dashboard ViewModel

Create a new class called DashboardViewModel to hold the consolidated data.

Models/DashboardViewModel.cs


public class DashboardViewModel
{
    public int TotalUsers { get; set; }
    public int TotalRoles { get; set; }
    public int TotalTrafficDataEntries { get; set; }
    public int TotalIncidents { get; set; }
    public int TotalSignals { get; set; }
    public int TotalPerformanceMetrics { get; set; }
    public int TotalRoutes { get; set; }
    public int TotalAlerts { get; set; }
    public int TotalReports { get; set; }
}

Create the Dashboard Controller

Create a new controller called DashboardController to fetch the data from the repositories and pass it to the view.

Controllers/DashboardController.cs


using Microsoft.AspNetCore.Mvc;
public class DashboardController : Controller
{
    private readonly UserRepository _userRepository;
    private readonly RoleRepository _roleRepository;
    private readonly TrafficDataRepository _trafficDataRepository;
    private readonly IncidentRepository _incidentRepository;
    private readonly SignalRepository _signalRepository;
    private readonly PerformanceMetricRepository _performanceMetricRepository;
    private readonly RouteRepository _routeRepository;
    private readonly AlertRepository _alertRepository;
    private readonly ReportRepository _reportRepository;
    public DashboardController(
        UserRepository userRepository,
        RoleRepository roleRepository,
        TrafficDataRepository trafficDataRepository,
        IncidentRepository incidentRepository,
        SignalRepository signalRepository,
        PerformanceMetricRepository performanceMetricRepository,
        RouteRepository routeRepository,
        AlertRepository alertRepository,
        ReportRepository reportRepository)
    {
        _userRepository = userRepository;
        _roleRepository = roleRepository;
        _trafficDataRepository = trafficDataRepository;
        _incidentRepository = incidentRepository;
        _signalRepository = signalRepository;
        _performanceMetricRepository = performanceMetricRepository;
        _routeRepository = routeRepository;
        _alertRepository = alertRepository;
        _reportRepository = reportRepository;
    }
    public IActionResult Index()
    {
        var model = new DashboardViewModel
        {
            TotalUsers = _userRepository.GetAllUsers().Count,
            TotalRoles = _roleRepository.GetAllRoles().Count,
            TotalTrafficDataEntries = _trafficDataRepository.GetAllTrafficData().Count,
            TotalIncidents = _incidentRepository.GetAllIncidents().Count,
            TotalSignals = _signalRepository.GetAllSignals().Count,
            TotalPerformanceMetrics = _performanceMetricRepository.GetAllPerformanceMetrics().Count,
            TotalRoutes = _routeRepository.GetAllRoutes().Count,
            TotalAlerts = _alertRepository.GetAllAlerts().Count,
            TotalReports = _reportRepository.GetAllReports().Count
        };
        return View(model);
    }
}

Create the Dashboard View

Create a Razor view for the dashboard that displays the consolidated data.

Views/Dashboard/Index.cshtml


@model DashboardViewModel
<h2>Dashboard</h2>
<div class="dashboard">
    <div class="card">
        <h3>Total Users</h3>
        <p>@Model.TotalUsers</p>
    </div>
    <div class="card">
        <h3>Total Roles</h3>
        <p>@Model.TotalRoles</p>
    </div>
    <div class="card">
        <h3>Total Traffic Data Entries</h3>
        <p>@Model.TotalTrafficDataEntries</p>
    </div>
    <div class="card">
        <h3>Total Incidents</h3>
        <p>@Model.TotalIncidents</p>
    </div>
    <div class="card">
        <h3>Total Signals</h3>
        <p>@Model.TotalSignals</p>
    </div>
    <div class="card">
        <h3>Total Performance Metrics</h3>
        <p>@Model.TotalPerformanceMetrics</p>
    </div>
    <div class="card">
        <h3>Total Routes</h3>
        <p>@Model.TotalRoutes</p>
    </div>
    <div class="card">
        <h3>Total Alerts</h3>
        <p>@Model.TotalAlerts</p>
    </div>
    <div class="card">
        <h3>Total Reports</h3>
        <p>@Model.TotalReports</p>
    </div>
</div>
<style>
    .dashboard {
        display: grid;
        grid-template-columns: repeat(auto-fill, minmax(200px, 1fr));
        gap: 20px;
    }
    .card {
        border: 1px solid #ccc;
        padding: 20px;
        border-radius: 5px;
        text-align: center;
    }
</style>

Dashboard ViewModel

The DashboardViewModel class holds the data needed for the dashboard.

Dashboard Controller

The DashboardController retrieves the counts from the respective repositories and passes the data to the view.

Dashboard View

The Index.cshtml file displays the total counts of various entities in a card format.

Integration

Make sure to add a route to the DashboardController in your Startup.cs or Program.cs file, depending on your ASP.NET version, to ensure that the dashboard can be accessed from your application.

This setup provides a consolidated view of key metrics related to your Traffic Management System, allowing administrators to monitor the system effectively. You can further enhance the dashboard with charts and graphs using libraries like Chart.js or D3.js for better visualization of the data.