/dotnet-db1

Primary LanguageC#

Dotnet DB1 Project

  • Create a Minimal API
  • Use EF Core to connect and query a database
  • Use docker to host SQL Server Database (or some other DB)

Objective

Successfully create a minimal API that uses EF Core to manage CRUD on the database.

Swagger

This project is configured with Swagger. While the application is running Swagger docs can be viewed at

http://localhost:5189/swagger/index.html

Docker setup

To setup the Database ensure that you have Docker installed and run the following command

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SaPassword1234!" -e "MSSQL_PID=Express" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Once the container has started, use a tool such as Azure Data Studio or some other datbase connection tool to log into the SQL Server instance.

User: sa Password: SaPassword1234!

After logged in create a database called AccountsDB

Instructions

Build a RESTful API for an Accounts resource

Create a Minimal API that will be used to Create, Read, Update and Delete accounts. The project should use Microsoft.EntityFrameworkCore.SqlServer to connect and query the Database.

NOTE: if you are using a DB other than SQL Server, be sure to use the correct EF Core integration eg. Npgsql.EntityFrameworkCore.PostgreSQL if using a Postgres Database

The Accounts schema should conform to the following

Accounts Schema

field data type metadata
id unsigned integer primary key, auto-increments, generated by database
name string required, unique
budget numeric required

1. Create Accounts Model & DB Context

  • Create an Accounts model that will represent the Accounts table

  • Create an AccountsContext class that inherits from DbContext that can be used to query the Database

2. Write Accounts API

  • Write CRUD for the accounts resource

    • [GET] /api/accounts returns an array of accounts (or an empty array if there aren't any).
    • [GET] /api/accounts/:id returns an account by the given id.
    • [POST] /api/accounts returns the created account. Leading or trailing whitespace on budget name should be trimmed before saving to db.
    • [PUT] /api/accounts/:id returns the updated account. Leading or trailing whitespace on budget name should be trimmed before saving to db.
    • [DELETE] /api/accounts/:id returns the deleted account.

Stretch

  • Implement repository pattern.
    • Create a IAccountRepository that defines the following
      • GetAllAsync
      • GetByIdAsync
      • CreateAsync
      • UpdateByIdAsync
      • DeleteByIdAsync
    • Create a AccountRepository class that implements IAccountRepository and uses the AccountsContext
    • Register the AccountRepository as a Transient service
    • Use the AccountRepository within the API hanlders