[[oktatas:programozas:csharp:dotnetcore:web_api|< Web API]]

====== .Net Core Web SQLite ======

  * **Szerző:** Sallai András
  * Copyright (c) 2022, Sallai András
  * Szerkesztve: 2022, 2023, 2024
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC Attribution-Share Alike 4.0 International]]
  * Web: https://szit.hu

===== Előkészítés =====

<code>
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.Extensions.PlatformAbstractions
</code>


  * A Design a migrációs fájl készítéséhez szükséges.
  * A PlatformAbstractions csak azért kell, hogy kiderítsük a projekt útvonalát. 


<code xml app01.csproj>
<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.3">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.3" />
    <PackageReference Include="Microsoft.Extensions.PlatformAbstractions" Version="1.1.0" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>

</Project>
</code>


===== Program.cs =====

<code csharp Program.cs>
global using App01.Data;
global using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.PlatformAbstractions;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();


builder.Services.AddDbContext<DataContext>(options => 
{
    var path = PlatformServices.Default.Application.ApplicationBasePath;
    options.UseSqlite("Filename=" + Path.Combine(path, "database.db"));

});

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

</code>

A Data és Microsoft.EntityFrameworkCore a global kulcsszóval lett megadva, így a 
DataContextben és az EmployeeControllerben nem szükséges megadni:
<code csharp>
global using App01.Data;
global using Microsoft.EntityFrameworkCore;
</code>

===== DataContext =====

<code csharp Data/DataContext.cs>
namespace App01.Data
{
    public class DataContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; } = null!;

        public DataContext(DbContextOptions<DataContext> options) : base(options) {}
    }
}
</code>

A null! a következő figyelmeztetés elkerülésére való:

<code>
Non-nullable property 'Employees' must contain 
a non-null value when exiting constructor.
</code>

===== Migráció =====

Migrációs fájlok létrehozása:
  dotnet ef migrations add InitialCreate

Adatbázis fájl létrehozása a táblákkal együtt.
  dotnet ef database update

A database.db a következő helyen jön létre:
  bin/Debug/net6.0



===== Kontroller =====

<code csharp Controllers/EmployeeController.cs>

using Microsoft.AspNetCore.Mvc;

namespace App01.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {

        private readonly DataContext _context;
        public EmployeeController(DataContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<ActionResult<List<Employee>>> Get()
        {
            return Ok(await _context.Employees.ToListAsync());
        }

        [HttpGet("{id}")]
        public async Task<ActionResult<List<Employee>>> Get(int id)
        {
            var dbEmployee = await _context.Employees.FindAsync(id);
            if (dbEmployee == null) {
                return BadRequest("A dolgozó nem található");
            }
            return Ok(dbEmployee);
        }

        [HttpPost]
        public async Task<ActionResult<List<Employee>>> AddEmployee(Employee employee)
        {
            _context.Employees.Add(employee);
            await _context.SaveChangesAsync();
            return Ok(await _context.Employees.ToListAsync());
        }
        
        [HttpPut]
        public async Task<ActionResult<List<Employee>>> UpdateEmployee(Employee request)
        {
            var dbEmployee = await _context.Employees.FindAsync(request.Id);
            
            if (dbEmployee == null) {
                return BadRequest("A dolgozó nem található");
            }            
            dbEmployee.Name = request.Name;
            dbEmployee.City = request.City;
            dbEmployee.Salary = request.Salary;

            await _context.SaveChangesAsync();
            _context.Employees.Remove(dbEmployee);
            await _context.SaveChangesAsync();
            return Ok(await _context.Employees.ToListAsync());
        }
        
        [HttpDelete("{id}")]
        public async Task<ActionResult<List<Employee>>> Delete(int id)
        {
            var dbEmployee = await _context.Employees.FindAsync(id);
            if (dbEmployee == null) {
                return BadRequest("A dolgozó nem található");
            }

            _context.Employees.Remove(dbEmployee);
            await _context.SaveChangesAsync();
            return Ok(await _context.Employees.ToListAsync());
        }

    }
}
</code>


===== Az adatbázisfájl a projekt alapkönyvtárába =====

<code javascript appsettings.Development.json>
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "DataSource=app.db"
  }  
}
</code>


<code csharp Program.cs>
global using Microsoft.EntityFrameworkCore;
global using App01.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();


builder.Services.AddDbContext<DataContext>(options => 
{
    var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
    options.UseSqlite(connectionString);
});

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

</code>

Így nincs szükség a Microsoft.Extensions.PlatformAbstractions függőségre.

===== Példa migráció nélkül =====

==== Projekt létrehozása ====

  dotnet new webapi -n sqlitepelda
  code -r sqlitepelda


Ebben a példában nem használunk adatbázis migárciót.
==== Telepítés ====

  dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 7.0.10

  dotnet add package Microsoft.EntityFrameworkCore

==== SQLite fájl neve ====

<code javascript appsettings.json>
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "database": "database"
  }
}
</code>

==== SQLite adatbázis beépítése ====

<code csharp Program.cs>
//...
builder.Services.AddDbContext<Database>(options => {
    var config = builder.Configuration;
    var connectionString = config.GetConnectionString("database");
    options.UseSqlite($"Data Source={connectionString}.db");
});
//...
</code>


==== Adatbázis és modell kötése ====

<code csharp Data/Database.cs>
using hello.Models;
using Microsoft.EntityFrameworkCore;


namespace hello.Data;

public class Database: DbContext
{
    public DbSet<Employee> Employees { get; set; } = null!;
    public Database(DbContextOptions<Database> options): base(options) {}
}
</code>

==== Kontroller ====

  dotnet-aspnet-codegenerator controller -name EmployeeController -outDir Controllers -api


<code csharp Controllers/EmployeeController.cs>
using hello.Data;
using hello.Models;

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace hello.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private readonly Database _database;
        public EmployeeController(Database database)
        {
            _database = database;
        }

        [HttpGet]
        public async Task<ActionResult<List<Employee>>> Get()
        {
            return Ok(await _database.Employees.ToListAsync());
        }

    }
}
</code>

==== Model létrehozása ====

<code csharp Models/Employee.cs>
namespace hello.Models;

public class Employee
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public string? City { get; set; }
    public double Salary { get; set; }
}

</code>

==== Hozzuk létre az SQLite adatbázist ====

Például sqlite3 shellben:

<code mysql>
create table employees (
    id integer not null primary key autoincrement,
    name text,
    city text,
    Salary real
);
</code>


A Dotnet Core alkalmazás olyan néven keresi a táblát és a mezőneveket, amit a modellben megadtunk, esetünkben az Employee.cs fájlban. A kis- és nagybetűt azonban nem veszi figyelembe, így az adatbázisban lehetnek akár kisbetűvel is.

==== Tesztelés ====

HTTPie teszt:

  http http://localhost:5214/api/employee