# Developing the API Data access

# START FROM PREVIOUS MODULE'S END

# ADD PROJECT REFERENCE FOR API PROJECT

# ADD PROJECT REFERENCE FOR DOMAIN PROJECT

# INSTALL OR UPDATE THR EF CORE CLI

dos
dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef

# ADD NUGET PACKAGES TO DATA PROJECT

dos
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

# SCAFFOLD DBCONTEXT AND ENTITIES FROM CHINOOK DATABASE IN DATA PROJECT

From the embedded Terminal in VS 2022, you must change directories to the Chinook.Data project.

Run the following command in Developer Command Prompt inside the Data project

# Windows Local MMSQL

dotnet ef dbcontext scaffold "Server=.;Database=Chinook;MultipleActiveResultSets=true;TrustServerCertificate=true;Integrated Security=true;Trusted_Connection=true;;Application Name=ChinookWebAPI" Microsoft.EntityFrameworkCore.SqlServer --context-dir Data --output-dir Entities

# DOCKER BASED MSSQL

dotnet ef dbcontext scaffold "Server=localhost,<docker-container-port>;Database=Chinook;MultipleActiveResultSets=true;TrustServerCertificate=true;User=sa;Password=<mssql-db-password>;Trusted_Connection=false;Application Name=ChinookWebAPI" Microsoft.EntityFrameworkCore.SqlServer --context-dir Data --output-dir Entities

# MOVE ENTITIES IN DATA PROJECT TO DOMAIN

Adjust the namespace for the Entities

Add using reference in DBContext to Entities in Domain

# ADD REPOSITORY INTERFACES TO DOMAIN

using Chinook.Domain.Entities;

namespace Chinook.Domain.Repositories
{
    public interface IAlbumRepository : IDisposable
    {
        Task<List<Album>> GetAll();
        Task<Album> GetById(int id);
        Task<List<Album>> GetByArtistId(int id);
        Task<Album> Add(Album newAlbum);
        Task<bool> Update(Album album);
        Task<bool> Delete(int id);
    }
}

# ADD REPOSITORIES TO DATA

using Chinook.Data.Data;
using Chinook.Domain.Entities;
using Chinook.Domain.Repositories;
using Microsoft.EntityFrameworkCore;

namespace Chinook.Data.Repositories
{
    public class AlbumRepository : IAlbumRepository
    {
        private readonly ChinookContext _context;

        public AlbumRepository(ChinookContext context)
        {
            _context = context;
        }

        private async Task<bool> AlbumExists(int id) =>
            await _context.Albums.AnyAsync(a => a.Id == id);

        public void Dispose() => _context.Dispose();

        public async Task<List<Album>> GetAll() => await _context.Albums.AsNoTrackingWithIdentityResolution().ToListAsync();

        public async Task<Album> GetById(int id)
        {
            var dbAlbum = await _context.Albums.FindAsync(id);
            return dbAlbum;
        }

        public async Task<Album> Add(Album newAlbum)
        {
            await _context.Albums.AddAsync(newAlbum);
            await _context.SaveChangesAsync();
            return newAlbum;
        }

        public async Task<bool> Update(Album album)
        {
            if (!await AlbumExists(album.Id))
                return false;
            _context.Albums.Update(album);
            await _context.SaveChangesAsync();
            return true;
        }

        public async Task<bool> Delete(int id)
        {
            if (!await AlbumExists(id))
                return false;
            var toRemove = await _context.Albums.FindAsync(id);
            _context.Albums.Remove(toRemove);
            await _context.SaveChangesAsync();
            return true;
        }

        public async Task<List<Album>> GetByArtistId(int id) =>
            await _context.Albums.Where(a => a.ArtistId == id).AsNoTrackingWithIdentityResolution().ToListAsync();
    }
}

# ADD CONFIGURATIONS FOLDER to API PROJECT

# ADD CONNECTIONSTRING TO APPSETTINGS.JSON

"ConnectionStrings": {
  "ChinookDbWindows": "Server=.;Database=Chinook;MultipleActiveResultSets=true;TrustServerCertificate=true;Integrated Security=true;Trusted_Connection=true;Application Name=Chinook7WebAPI",
  "ChinookDbDocker": "Server=localhost,1433;Database=Chinook;User=sa;Password=P@55w0rd;MultipleActiveResultSets=true;TrustServerCertificate=true;Integrated Security=true;Trusted_Connection=true;Application Name=Chinook7WebAPI"
},

# ADD APPSETTINGS TO API PROJECT IN CONFIGURATIONS FOLDER

namespace Chinook.API.Configurations;

public abstract class AppSettings
{
}

# ADD CONFIGUREAPPSETTINGS TO API PROJECT

# CONFIGUREAPPSETTINGS CLASS IN CONFIGURATIONS FOLDER

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace Chinook.API.Configurations
{
    public static class ConfigureAppSettings
    {
        public static IServiceCollection AddAppSettings(this IServiceCollection services, IConfiguration configuration)
        {
            services.Configure<AppSettings>(_ => configuration.GetSection("AppSettings").Bind(_));

            return services;
        }
    }
}

# ADD AddAppSettings TO PROGRAM.CS IN API PROJECT

using Chinook.API.Configurations;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddAppSettings(builder.Configuration);
builder.Services.AddControllers();

# ADD DBCONTEXT TO DEPENDANCY INJECTION IN API PROJECT

using System.Runtime.InteropServices;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Chinook.Data.Data;

namespace Chinook.API.Configurations;

public static class ConfigureConnections
{
    public static IServiceCollection AddConnectionProvider(this IServiceCollection services,
        IConfiguration configuration)
    {
        var connection = String.Empty;

        if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
            connection = configuration.GetConnectionString("ChinookDbWindows");
        else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux) ||
                 RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
            connection = configuration.GetConnectionString("ChinookDbDocker");

        services.AddDbContextPool<ChinookContext>(options => options.UseSqlServer(connection));
        services.AddSingleton(new SqlConnection(connection));

        return services;
    }
}

# CALL ADDCONNECTIONPROVIDER() IN CONFIGURESERVICES IN API PROJECT'S StartUP

builder.Services.AddConnectionProvider(builder.Configuration);