Part 4: Deep Dive into the Data Access Layer (DAL) and Configurations

Introduction

In this part, we’ll set up the Data Access Layer (DAL) in our multi-layered architecture. The DAL is a crucial component, as it interfaces directly with the database and handles all data-related tasks. Here, we’ll integrate Entity Framework Core (EF Core), define our database context, establish repository classes, and set up both CRUD operations and stored procedure calls in a well-organized, maintainable way.

Step 1: Installing Required Packages

For our DAL setup, we’ll need specific packages:

1. Microsoft.EntityFrameworkCore: Core package for using EF Core.
2. Microsoft.EntityFrameworkCore.SqlServer: Provides SQL Server database provider for EF Core.
3. Microsoft.EntityFrameworkCore.Tools: Enables tools like Migrations in EF Core, allowing us to create or update our database schema.

To install these packages, use the following commands in the Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

Step 2: Setting Up AppDbContext

AppDbContext is the class that connects EF Core with our database. It manages the database connection and maps our entities to tables.

AppDbContext.cs

using Microsoft.EntityFrameworkCore;
using MovieSeries.CoreLayer.Entities;

namespace MovieSeries.DataAccessLayer
{
    public class AppDbContext : DbContext
    {
        public DbSet<Movie> Movies { get; set; }
        public DbSet<Review> Reviews { get; set; }
        public DbSet<Tag> Tags { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<MovieSeriesTag> MovieSeriesTags { get; set; }
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MovieSeriesTag>()
                .HasKey(mst => new { mst.MovieSeriesId, mst.TagId });
            modelBuilder.Entity<MovieSeriesTag>()
                .HasOne(mst => mst.Movie)
                .WithMany(m => m.MovieSeriesTags)
                .HasForeignKey(mst => mst.MovieSeriesId);
            modelBuilder.Entity<MovieSeriesTag>()
                .HasOne(mst => mst.Tag)
                .WithMany(t => t.MovieSeriesTags)
                .HasForeignKey(mst => mst.TagId);
        }
    }
}

Step 3: Setting Up Connection Strings and Database Configuration

We use a Connection String to store our database connection information. In appsettings.json, we define the connection string:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;"
  }
}

In Program.cs, we configure EF Core to use this connection string with AppDbContext.

Program.cs

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Step 4: Repository Pattern and CRUD Operations

The Repository Pattern is essential in the DAL as it abstracts database operations for each entity. Each repository class is dedicated to a specific entity, implementing CRUD methods and, in some cases, stored procedure calls.

Example Repository: MovieRepository.cs

public async Task<IEnumerable<Movie>> GetAllMoviesAsync()
{
    return await _context.Movies.ToListAsync();
}

public async Task AddMovieAsync(Movie movie)
{
    await _context.Movies.AddAsync(movie);
    await _context.SaveChangesAsync();
}

Summary

In Part 4, we configured the DAL using EF Core, established the AppDbContext, set up a connection string, and implemented CRUD operations using the Repository Pattern. This setup enables reliable and maintainable data handling for our application.

Next Part

Part 5: Deepening the Service Layer’s Role in Our Architecture