8.7 TallinnaRakenduslikKolledž - JUHAN HEINLAID PORTFOOLIO

1. Projekti Eesmärk ja Selgitus

TallinnaRakenduslikKolledž on kooliandmete haldamise veebirakendus, mis võimaldab hallata õpilasi, õpetajaid, kursuseid, osakondi ja distsiplinaarseid rikkumisi.

Tehnoloogiad:

ASP.NET Core 8.0 MVC
Entity Framework Core 8.0.19
SQL Server LocalDB (Database: MySchoolDatabase5)
C#, Razor, LINQ

Konfiguratsioon (appsettings.json):

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=MySchoolDatabase5;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
}

Rakenduse käivitamine (Program.cs):

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllersWithViews();
builder.Services.AddDbContext&lt;SchoolContext>(options => 
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();
CreateDbIfNotExists(app); // Loob andmebaasi automaatselt

app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.MapControllerRoute(name: "default", pattern: "{controller=Home}/{action=Index}/{id?}");
app.Run();

2. MVC Struktuuri Rakendamine

2.1 Model – Andmebaasi Tabelid

Student (Õpilane)

public class Student
{
    [Key]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime EnrollmentDate { get; set; }
    public ICollection&lt;Enrollment>? Enrollments { get; set; }
    
    // Unikaalsed omadused
    public int? Iq { get; set; }
    public string HomeAddress { get; set; }
    public int Age { get; set; }
}

Tabel: Students
Seosed: Üks-mitmele seos Enrollment tabeliga

Instructor (Õpetaja)

public class Instructor
{
    [Key]
    public int ID { get; set; }
    
    [Required, StringLength(50)]
    public string LastName { get; set; }
    
    [Required, StringLength(50)]
    public string FirstName { get; set; }
    
    public string FullName => LastName + "," + FirstName;
    
    [DataType(DataType.Date)]
    public DateTime HireDate { get; set; }
    
    public ICollection&lt;CourseAssignment>? CourseAssignments { get; set; }
    public OfficeAssignment? OfficeAssignment { get; set; }
    
    // Unikaalsed omadused
    [Required]
    public double Salary { get; set; }
    public string? Bonuses { get; set; }
    
    [Required, StringLength(6)]
    public string Gender { get; set; }
}

Tabel: Instructors
Seosed: Üks-mitmele CourseAssignment, üks-ühele OfficeAssignment

Course (Kursus)

public class Course
{
    [Key]
    public int ID { get; set; }
    public string Title { get; set; }
    public int Credits { get; set; }
    public int? DepartmentId { get; set; }
    
    public Department? Department { get; set; }
    public ICollection&lt;Enrollment>? Enrollments { get; set; }
    public ICollection&lt;CourseAssignment>? CourseAssignments { get; set; }
}

Tabel: Courses
Seosed: Mitu-ühele Department, mitu-mitmele Student ja Instructor

Department (Osakond)

public class Department
{
    [Key]
    public int DepartmentID { get; set; }
    public string Name { get; set; }
    public decimal Budget { get; set; }
    public DateTime StartDate { get; set; }
    public int? InstructorID { get; set; }
    
    public Instructor? Administrator { get; set; }
    public ICollection&lt;Course>? Courses { get; set; }
    
    // Unikaalsed omadused
    public string Aadress { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
}

Tabel: Departments
Enum: DepartmentStatus (Suletud, Avatud, Pausil, Deprecated, Ootel)

Enrollment (Registreerimine) – Liituv Tabel

public class Enrollment
{
    public int EnrollmentID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public Grade? CurrentGrade { get; set; }
    
    public Course Course { get; set; }
    public Student Student { get; set; }
}

public enum Grade { A, B, C, D, F, X, MA }

Tabel: Enrollments
Roll: Seob Student ja Course (mitu-mitmele)

CourseAssignment (Kursuse Määramine) – Liituv Tabel

public class CourseAssignment
{
    [Key]
    public int ID { get; set; }
    public int InstructorID { get; set; }
    public int CourseID { get; set; }
    
    public Instructor Instructor { get; set; }
    public Course Course { get; set; }
}

Tabel: CourseAssignments
Roll: Seob Instructor ja Course (mitu-mitmele)

OfficeAssignment (Kabinet)

public class OfficeAssignment
{
    [Key]
    public int InstructorID { get; set; }
    
    [StringLength(50)]
    public string Location { get; set; }
    
    public Instructor Instructor { get; set; }
}

Tabel: OfficeAssignments
Roll: Üks-ühele seos Instructor’iga

Delinquent (Distsiplinaarne Rikkumine)

public class Delinquent
{
    [Key]
    public int DelinquentId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Violations Violation { get; set; }
    public string TeacherOrStudent { get; set; }
    public string Situation { get; set; }
}

public enum Violations { Mõrv, Rööv, Vandaliseerimine, Pangarööv, Kõndimine }

Tabel: Delinquents

2.2 Controller – Loogika ja LINQ Meetodid

StudentsController

Index() – Kõigi õpilaste nimekiri

public async Task&lt;IActionResult> Index()
{
    return View(await _context.Students.ToListAsync());
}

LINQ: ToListAsync() – Laadib kõik õpilased listina

Create() POST – Õpilase lisamine

[HttpPost]
public async Task&lt;IActionResult> Create([Bind("ID,LastName,FirstName,EnrollmentDate,HomeAddress,Age,Iq")] Student student)
{
    if (ModelState.IsValid) 
    {
        _context.Students.Add(student);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(student);
}

LINQ: Add(), SaveChangesAsync() – Lisab õpilase andmebaasi

Delete() GET – Kustutamise kinnitus

[HttpGet]
public async Task&lt;IActionResult> Delete(int? id)
{
    var student = await _context.Students.FirstOrDefaultAsync(m => m.Id == id);
    if (student == null) return NotFound();
    return View(student);
}

LINQ: FirstOrDefaultAsync(m => m.Id == id) – Leiab õpilase ID järgi või tagastab null

DeleteConfirmed() POST – Õpilase kustutamine

[HttpPost, ActionName("Delete")]
public async Task&lt;IActionResult> DeleteConfirmed(int id)
{
    var student = await _context.Students.FindAsync(id);
    _context.Students.Remove(student);
    await _context.SaveChangesAsync();
    return RedirectToAction("Index");
}

LINQ: FindAsync(id) – Leiab primaarvõtme järgi, Remove() – Kustutab

Edit() POST – Õpilase muutmine

[HttpPost, ActionName("Edit")]
public async Task&lt;IActionResult> Edit([Bind("Id,LastName,FirstName,EnrollmentDate,HomeAddress,Age,Iq")] Student student)
{
    if (ModelState.IsValid)
    {
        _context.Students.Update(student);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(student);
}

LINQ: Update() – Uuendab õpilase andmeid

InstructorsController

Index() – Õpetajate nimekiri koos seotud andmetega

public async Task&lt;IActionResult> Index(int? id, int? courseId)
{
    var vm = new InstructorIndexData();
    vm.Instructors = await _context.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.CourseAssignments)
        .ToListAsync();
    return View(vm);
}

LINQ:

Include(i => i.OfficeAssignment) – Eager Loading – laadib kabineti andmed
Include(i => i.CourseAssignments) – Laadib kursuste määramised

Create() POST – Õpetaja lisamine koos kursustega

[HttpPost]
public async Task&lt;IActionResult> Create(Instructor instructor, string selectedCourses)
{
    if (selectedCourses != null)
    {
        instructor.CourseAssignments = new List&lt;CourseAssignment>();
        foreach (var course in selectedCourses)
        {
            instructor.CourseAssignments.Add(new CourseAssignment
            {
                InstructorID = instructor.ID,
                CourseID = course
            });
        }
    }
    
    if (ModelState.IsValid)
    {
        _context.Add(instructor);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(instructor);
}

LINQ: Add() lisab õpetaja koos seotud kursustega

PopulateAssignedCourseData() – Kursuste määramise andmed

private void PopulateAssignedCourseData(Instructor instructor)
{
    var allCourses = _context.Courses;
    var instructorCourses = new HashSet&lt;int>(instructor.CourseAssignments.Select(c => c.CourseID));
    
    var vm = new List&lt;AssignCourseData>();
    foreach (var course in allCourses) 
    {
        vm.Add(new AssignCourseData
        {
            CourseID = course.ID,
            Title = course.Title,
            Assigned = instructorCourses.Contains(course.ID)
        });
    }
    ViewData["Courses"] = vm;
}

LINQ: Select(c => c.CourseID) – Projekteerib ainult CourseID väljad

CoursesController

Index() – Kursuste nimekiri

public IActionResult Index()
{
    var courses = _context.Courses
        .Include(c => c.Department)
        .AsNoTracking()
        .ToArray();
    return View(courses);
}

LINQ:

Include(c => c.Department) – Laadib osakonna andmed
AsNoTracking() – Performance boost – read-only päring (ei jälgi muudatusi)

PopulateDepartmentDropDownList() – Dropdown andmed

private void PopulateDepartmentDropDownList(object selectedDepartment = null)
{
    var departmentsQuery = from d in _context.Departments
                           orderby d.Name
                           select d;
    ViewBag.DepartmentID = new SelectList(departmentsQuery.AsNoTracking(), "DepartmentID", "Name", selectedDepartment);
}

LINQ:

from d in ... orderby d.Name select d – Query Syntax
orderby d.Name – Sorteerib nime järgi

Edit() POST – Kursuse muutmine

[HttpPost, ActionName("EditConfirmed")]
public async Task&lt;IActionResult> Edit([Bind("ID,Title,Credits,DepartmentId")] Course course)
{
    if (ModelState.IsValid)
    {
        var existingCourse = await _context.Courses.FindAsync(course.ID);
        if (existingCourse == null) return NotFound();

        existingCourse.Title = course.Title;
        existingCourse.Credits = course.Credits;
        existingCourse.DepartmentId = course.DepartmentId;

        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(Index));
    }
    return View("Create", course);
}

LINQ: FindAsync() leiab olemasoleva entity ja uuendab manuaalselt

DepartmentsController

Index() – Osakondade nimekiri

public async Task&lt;IActionResult> Index()
{
    var schoolContext = _context.Departments.Include(d => d.Administrator);
    return View(await schoolContext.ToListAsync());
}

LINQ: Include(d => d.Administrator) – Laadib administraatori andmed

Delete() POST – Osakonna kustutamine

[HttpPost]
public async Task&lt;IActionResult> Delete(Department department)
{
    if (await _context.Departments.AnyAsync(m => m.DepartmentID == department.DepartmentID))
    {
        _context.Departments.Remove(department);
        await _context.SaveChangesAsync();
    }
    return RedirectToAction("Index");
}

LINQ: AnyAsync(m => m.DepartmentID == id) – Kontrollib, kas osakond eksisteerib

DelinquentsController

Index() – Rikkumiste nimekiri

public async Task&lt;IActionResult> Index()
{
    return View(await _context.Delinquents.ToListAsync());
}

Edit() POST – Rikkumise muutmine

[HttpPost]
public async Task&lt;IActionResult> Edit([Bind("DelinquentId,FirstName,LastName,Situation,Violation,TeacherOrStudent")] Delinquent delinquent)
{
    if (ModelState.IsValid)
    {
        _context.Delinquents.Update(delinquent);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(delinquent);
}

DeleteConfirmed() – Rikkumise kustutamine

[HttpPost, ActionName("Delete")]
public async Task&lt;IActionResult> DeleteConfirmed(int id)
{
    var delinquent = await _context.Delinquents.FirstOrDefaultAsync(x => x.DelinquentId == id);
    _context.Delinquents.Remove(delinquent);
    await _context.SaveChangesAsync();
    return RedirectToAction("Index");
}

HomeController

Index() – Avaleht

public IActionResult Index()
{
    return View();
}

Privacy() – Privaatsuspoliitika

public IActionResult Privacy()
{
    ViewData["Värv"] = "Punane";
    return View();
}

Error() – Vealeht

[ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
public IActionResult Error()
{
    return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
}

2.3 View – Vaated

Views/Students/

Index.cshtml – Tabel kõigi õpilastega (FirstName, LastName, EnrollmentDate, Age, IQ, HomeAddress), nupud Muuda/Detailid/Kustuta
Create.cshtml – Vorm õpilase lisamiseks (tekstiväljad, kuupäev, number)
Edit.cshtml – Vorm õpilase muutmiseks
Details.cshtml – Õpilase detailid (read-only)
Delete.cshtml – Kustutamise kinnitus

Views/Instructors/

Index.cshtml – Tabel õpetajatega (LastName, FirstName, HireDate, Kabinet, Palk, Sugu), kursuste nimekiri
Create.cshtml – Vorm õpetaja lisamiseks, checkboxid kursuste valimiseks
Edit.cshtml – Õpetaja muutmine
Details.cshtml – Õpetaja detailid
Delete.cshtml – Kustutamise kinnitus

Views/Courses/

Index.cshtml – Tabel kursustega (ID, Title, Credits, Department)
Create.cshtml – Vorm kursuse lisamiseks, dropdown osakonna valimiseks
Details.cshtml – Kursuse detailid (kasutab sama vaadet kui Delete, eristab ViewData kaudu)

Views/Departments/

Index.cshtml – Tabel osakondadega (Name, Budget, StartDate, Administrator, Aadress, PhoneNumber, Email)
Create.cshtml – Vorm osakonna lisamiseks, dropdown juhataja valimiseks
Delete.cshtml – Osakonna detailid ja kustutamine (sama vaade Details jaoks)

Views/Delinquents/

Index.cshtml – Tabel rikkumistega (FirstName, LastName, Violation, TeacherOrStudent, Situation)
Create.cshtml – Vorm rikkumise lisamiseks, dropdown Violation enum jaoks
Edit.cshtml – Rikkumise muutmine
Delete.cshtml – Kustutamise kinnitus

3. LINQ Meetodite Kokkuvõte

LINQ Meetod	Kirjeldus
`ToListAsync()`	Teisendab päringu listiks (asünkroonselt)
`FindAsync(id)`	Leiab entity primaarvõtme järgi
`FirstOrDefaultAsync(x => x.Id == id)`	Leiab esimese või tagastab null
`SingleAsync(x => x.Id == id)`	Leiab täpselt ühe (exception kui 0 või >1)
`Include(x => x.Property)`	Eager Loading – laadib seotud entitied
`AsNoTracking()`	Read-only päring (kiirem, ei jälgi muudatusi)
`Select(x => x.Property)`	Projekteerib ainult valitud väljad
`AnyAsync(x => x.Id == id)`	Kontrollib, kas vähemalt üks vastab tingimusele
`Add(entity)`	Lisab uue entity
`Update(entity)`	Uuendab entity
`Remove(entity)`	Kustutab entity
`SaveChangesAsync()`	Salvestab muudatused andmebaasi
`orderby x.Name`	Sorteerib tõusvas järjekorras