ASP.NET 7 Minimal Web API with SQLite

This tutorial is about using ASP.NET 6.0 Minimal WebAPI. We will build a simple application that uses SQLite to save students data. We will then consume the API from an HTML page. 

Source Code: https://github.com/medhatelmasry/StudentsApi Companion Video: https://youtu.be/JG2TeGBs8MU

VS Code extensions needed:

• C#
• C# Extensions

In a suitable working directory, create a Web API web application with:

dotnet new webapi -f net7.0 --no-https --use-minimal-apis -o StudentsMinimalApi 
 
cd StudentsMinimalApi  

 code .

dotnet watch

The app will display in your browser, and you will see this:

Look at API code in Program.cs:

app.MapGet("/weatherforecast", () =>

{

    var forecast =  Enumerable.Range(1, 5).Select(index =>

        new WeatherForecast

        (

            DateTime.Now.AddDays(index),

            Random.Shared.Next(-20, 55),

            summaries[Random.Shared.Next(summaries.Length)]

        ))

        .ToArray();

    return forecast;

})

.WithName("GetWeatherForecast"); 

.WithOpenApi();

Add this tool if you do not already have it: 

dotnet tool install --global dotnet-ef 

 Let us add these packages that provide support for SQLite and reading a CSV file: 

dotnet add package Microsoft.EntityFrameworkCore.Design -v 7.0

dotnet add package Microsoft.EntityFrameworkCore.Tools -v 7.0

dotnet add package Microsoft.EntityFrameworkCore -v 7.0

dotnet add package Microsoft.EntityFrameworkCore.SQLite -v 7.0

dotnet add package Microsoft.EntityFrameworkCore.SQLite.Design -v 7.0

dotnet add package CsvHelper

Create three folders: wwwroot, Data & Models.

Inside the Models folder, add the following Student class: 

public class Student {

        public int StudentId { get; set; }

        public string? LastName { get; set; }

        public string? FirstName { get; set; }

        public string? School { get; set; }

}

Developers prefer having sample data when building data driven applications. Therefore, we will create some sample data to ensure that our application behaves as expected. Copy the following data and save it in a text file wwwroot/students.csv:

StudentId,FirstName,LastName,School

1,Tom,Max,Nursing

2,Ann,Fay,Mining

3,Joe,Sun,Nursing

4,Sue,Fox,Computing

5,Ben,Ray,Mining

6,Zoe,Cox,Business

7,Sam,Ray,Mining

8,Dan,Ash,Medicine

9,Pat,Lee,Computing

10,Kim,Day,Nursing

11,Tim,Rex,Computing

12,Rob,Ram,Business

13,Jan,Fry,Mining

14,Jim,Tex,Nursing

15,Ben,Kid,Business

16,Mia,Chu,Medicine

17,Ted,Tao,Computing

18,Amy,Day,Business

19,Ian,Roy,Nursing

20,Liz,Kit,Nursing

21,Mat,Tan,Medicine

22,Deb,Roy,Medicine

23,Ana,Ray,Mining

24,Lyn,Poe,Computing

25,Amy,Raj,Nursing

26,Kim,Ash,Mining

27,Bec,Kid,Nursing

28,Eva,Fry,Computing

29,Eli,Lap,Business

30,Sam,Yim,Nursing

31,Joe,Hui,Mining

32,Liz,Jin,Nursing

33,Ric,Kuo,Business

34,Pam,Mak,Computing

35,Cat,Yao,Medicine

36,Lou,Zhu,Mining

37,Tom,Dag,Business

38,Stu,Day,Business

39,Tom,Gad,Mining

40,Bob,Bee,Business

41,Jim,Ots,Business

42,Tom,Mag,Business

43,Hal,Doe,Mining

44,Roy,Kim,Mining

45,Vis,Cox,Nursing

46,Kay,Aga,Nursing

47,Reo,Hui,Nursing

48,Bob,Roe,Mining

49,Jay,Eff,Computing

50,Eva,Chu,Business

51,Lex,Rae,Nursing

52,Lin,Dex,Mining

53,Tom,Dag,Business

54,Ben,Shy,Computing

55,Rob,Bos,Nursing

56,Ali,Mac,Business

57,Ken,Sim,Medicine

Add the following connection string to appsettings.json:

"ConnectionStrings": {

  "DefaultConnection": "DataSource=school.db;cache=shared"

},

Next, we need to add an Entity Framework context class. Inside the Data folder, add a class file named SchoolContext with the following content: 

public class SchoolDbContext : DbContext {

    public DbSet<Student> Students => Set<Student>();

    public SchoolDbContext(DbContextOptions<SchoolDbContext> options)

            : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {

        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Student>().HasData(GetStudents());

    }

    private static IEnumerable<Student> GetStudents() {

        string[] p = { Directory.GetCurrentDirectory(), "wwwroot", "students.csv" };

        var csvFilePath = Path.Combine(p);

        var config = new CsvConfiguration(CultureInfo.InvariantCulture) {

            PrepareHeaderForMatch = args => args.Header.ToLower(),

        };

        var data = new List<Student>().AsEnumerable();

        using (var reader = new StreamReader(csvFilePath)) {

            using (var csvReader = new CsvReader(reader, config)) {

                data = csvReader.GetRecords<Student>().ToList();

            }

        }

        return data;

    }

}

In the above code, student data is being seeded in the OnModelCreating() method by reading contents of students.csv file.

We need to register the context class (SchoolDbContext) with dependency injection in Program.cs. Add the following code right before “var app = builder.Build();” in Program.cs: 

var connStr = builder.Configuration.GetConnectionString("DefaultConnection") 

    ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");

builder.Services.AddDbContext<SchoolDbContext>(option => option.UseSqlite(connStr));

Let us add a migration and subsequently update the database. Execute the following CLI commands in a terminal window.

dotnet ef migrations add M1 -o Data/Migrations

dotnet ef database update

At this point the database and tables are created.

Students API

Let us add API endpoints that:

• Read all the students
• Read students that belong to a particular school
• Read student data by id
• Add student data
• Update student data
• Delete student data

Therefore, add the following code to Program.cs just before the final statement “app.Run();”:

app.MapGet("/api/students", async (SchoolDbContext db) =>

    await db.Students.ToListAsync());

app.MapGet("/api/students/school/{school}", async (string school, SchoolDbContext db) =>

    await db.Students.Where(t => t.School!.ToLower() == school.ToLower()).ToListAsync());

app.MapGet("/api/students/{id}", async (int id, SchoolDbContext db) =>

    await db.Students.FindAsync(id)

        is Student student ? Results.Ok(student) : Results.NotFound());

app.MapPost("/api/students", async (Student student, SchoolDbContext db) =>

{

    db.Students.Add(student);

    await db.SaveChangesAsync();

    return Results.Created($"/students/{student.StudentId}", student);

});

app.MapPut("/api/students/{id}", async (int id, Student inputStudent, SchoolDbContext db) =>

{

    var student = await db.Students.FindAsync(id);

    if (student is null) return Results.NotFound();

    student.FirstName = inputStudent.FirstName;

    student.LastName = inputStudent.LastName;

    student.School = inputStudent.School;

    await db.SaveChangesAsync();

    return Results.NoContent();

});

app.MapDelete("/api/students/{id}", async (int id, SchoolDbContext db) =>

{

    if (await db.Students.FindAsync(id) is Student student)

    {

        db.Students.Remove(student);

        await db.SaveChangesAsync();

        return Results.Ok(student);

    }

    return Results.NotFound();

});

Run the app and point your browser to /api/students. You should see the following results:

Try endpoint /api/students/22: 

Try endpoint /api/students/school/medicine:

OPTIONAL: If you want migrations to be applied automatically, add the following code to Program.cs right before the last “app.Run()” statement

using (var scope = app.Services.CreateScope()) {

    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<SchoolDbContext>();    

    context.Database.Migrate();

}

CORS (Cross-Origin Resource Sharing)

In wwwroot folder, create a file named show.html and add to it this HTML/JavaScript code:

<!DOCTYPE html>

<html>

  <html>

    <head>

      <meta charset="utf-8" />

      <title>Test API</title>

    </head>

    <body>

      <h3>Test API</h3>

      <button id="btnGetData">Get Data</button>

      <pre id="preOutput"></pre>

      <script>

        const url = "PUT-API-URL-HERE";

        var showResponse = function (object) {

          document.querySelector("#preOutput").innerHTML = JSON.stringify(

            object,

            null,

            4

          );

        };

        const button = document.querySelector("#btnGetData");

        button.addEventListener("click", (e) => {

          getData();

        });

        var getData = async function () {

          await fetch(url)

            .then((response) => {

              return response.json();

            })

            .then((data) => {

              showResponse(data);

            });

          return false;

        };

      </script>

    </body>

  </html>

</html>

Replace PUT-API-URL-HERE with the URL that gets all the students (Example: http://localhost:5143/api/students). 

From the file system, double-click on the wwwroot/show.html file. You will see the following page:

When you click on the “Get Data” button, nothing will appear because there is a JavaScript error. To understand where this error is coming from, hit F12 in your browser and check the console. This error will appear:

We need to enable CORS on the API project. This is done by adding the following code in Program.cs just before “var app = builder.Build();”:

 

// Add Cors

builder.Services.AddCors(o => o.AddPolicy("Policy", builder => {

  builder.AllowAnyOrigin()

    .AllowAnyMethod()

    .AllowAnyHeader();

}));

Also, in the same Program.cs file, add this code just after “var app = builder.Build();”: 

app.UseCors("Policy");

Save your code then make a new request for data from show.html. This time you should be successful:

Declutter Program.cs (optional)

Program.cs can get too cluttered. How about we move all the business logic into a separate class?

Create a Services/StudentService.cs class with the following content:

public class StudentService {

    public static async Task<IResult> GetAllStudents(SchoolDbContext db) {

        return TypedResults.Ok(await db.Students.ToListAsync());

    }

    public static async Task<IResult> GetStudentsBySchool(string school, SchoolDbContext db) {

        var students = await db.Students.Where(t => t.School!.ToLower() == school.ToLower()).ToListAsync();

        return TypedResults.Ok(students);

    }

    public static async Task<IResult> GetStudent(int id, SchoolDbContext db) {

        return await db.Students.FindAsync(id)

        is Student student

            ? Results.Ok(student)

            : Results.NotFound();

    }

    public static async Task<IResult> CreateSttudent(Student student, SchoolDbContext db) {

        db.Students.Add(student);

        await db.SaveChangesAsync();

        return Results.Created($"/students/{student.StudentId}", student);

    }

    public static async Task<IResult> UpdateStudent(int id, Student inputStudent, SchoolDbContext db) {

        var student = await db.Students.FindAsync(id);

        if (student is null) return Results.NotFound();

        student.FirstName = inputStudent.FirstName;

        student.LastName = inputStudent.LastName;

        student.School = inputStudent.School;

        await db.SaveChangesAsync();

        return Results.NoContent();

    }

    public static async Task<IResult> DeleteStudent(int id, SchoolDbContext db) {

        if (await db.Students.FindAsync(id) is Student student)

        {

            db.Students.Remove(student);

            await db.SaveChangesAsync();

            return TypedResults.Ok(student);

        }

        return TypedResults.NotFound();

    }

}

Also, change all the Map??? in Program.cs to:

    var route = app.MapGroup("/api/students");

route.MapGet("/", StudentService.GetAllStudents);

route.MapGet("/school/{school}", StudentService.GetStudentsBySchool);

route.MapGet("/{id}", StudentService.GetStudent);

route.MapPost("/", StudentService.CreateSttudent);

route.MapPut("/{id}", StudentService.UpdateStudent);

route.MapDelete("/{id}", StudentService.DeleteStudent);

The application should work as expected.

Congrats for coming this far.