Summary: This tutorial will teach you how to set up a minimal dotnet6 Rest API using SQL Server as the data store.
According to Microsoft Docs, minimal APIs (Introduced with the Net6 framework) are architected to create HTTP APIs with minimal dependencies. They are ideal for microservices and apps that want to include only the minimum files, features, and dependencies in ASP.NET Core.
I find minimal APIs particularly exciting because you could set up an API with a few endpoints in about 10 minutes. We are going to build a Todo API with CRUD abilities.
The following illustration shows the completed swagger documentation for the API with all CRUD operations implemented.
Prerequisites
Visual Studio 2022 with the ASP.NET and web development workload.
orC# for Visual Studio Code (latest version)
.NET 6.0 SDK
or
For this article. I will be working with Visual Studio 2022. You could follow through and create yours as you read the article or you could just get the source code and look through the code
Step 1:
Create a minimal dotnet API by opening up Visual Studio and selecting “Create a new project”.
Select the ASP.NET Core Web API Project with C# language as there is a different Project for F#. Click Next
Configure your new project by giving it a name. In our case, we are simply naming it MininalAPI. Click Next when done configuring your project
Uncheck the “Use controllers” checkbox to make sure that controllers will not be added to the created project. We’d be working mostly with the program class.
Click Create. A simple Webapi project would be created for you without a controller folder but with a Program class that looks exactly like this:
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
// 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();
var summaries = new[]
{
"Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
};
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");
app.Run();
internal record WeatherForecast(DateTime Date, int TemperatureC, string? Summary)
{
public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
}
Clean up the project by deleting the internal Record WeatherForecast class, the Get Route, and the summaries array sections on your Program.cs class to make it look exactly like this:
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
// 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.Run();
Step 2
Now let’s install the nuget packages that we require to use SQL Server as our data store. The packages are:
Microsoft.EntityFrameworkCore.Design (latest stable dotnet 6 version)
Microsoft.EntityFrameworkCore.SqlServer (latest stable dotnet 6 version)
Microsoft.EntityFrameworkCore (latest stable dotnet 6 version)
Tools (can be gotten with the command “dotnet tool install — global dotnet-ef”
Next, at the root of your project, add a new class called TodoItem. Add the following to the class.
namespace MininalAPI
{
public class TodoItem
{
public int Id { get; set; }
public bool IsDone { get; set; }
public string Title { get; set; } = default!;
public DateTime? DueAt { get; set; }
}
}
Also, create a DataContext class at the root of your project with the following code added to it.
global using Microsoft.EntityFrameworkCore;
namespace MininalAPI
{
public class DataContext : DbContext
{
public DataContext(DbContextOptions<DataContext> options) : base(options)
{
}
public DbSet<TodoItem> TodoItems => Set<TodoItem>();
}
}
The DataContext class would inherit from DbContext, then a constructor is created to get DbContextOptions of our DataContext class which would be passed to the base class. A DbSet of our TodoItem is also added which represents our TodoItem Entity and can be used for creating, reading, updating, and deleting operations.
Next, Update your appsetting.Development.json to contain the connectionString for our data store.
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost\\sqlexpress;Database=TodoAPIDB;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
}
}
Next, Update our Program class by registering a new Service. The AddDbContext service which would use SQL Server and would get the connectionstrings from builder.Configuration.
using MininalAPI;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
//Add this here
builder.Services.AddDbContext<DataContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.Run();
We are using the Code First Approach in creating our Database so we would need to create a migrations folder. EntityFramework is going to come to our rescue here. We would use the following commands to create our migrations folder and update our database.
Click on Tools →NuGet Package Manager →Package Manager Console to open the Package Manager Console. In the package manager console, Change the directory into the project by typing “cd .\MinimalAPI”.
Next, create migration by typing “dotnet ef migrations add InitialMigration” which would create a migration file.
Updating our database with the command “dotnet ef database update” would create the database on SQL Server and also create the TodoItems Table as we see in the image below:
Now it’s time to add our CRUD operations to the API.
Step 3
Add the following codes to the Program class, just after the app.UseHttpsRedirection() middleware.
//Get to Try out the routing
app.MapGet("/", () => "Welcome to minimal APIs");
//Get all Todo Items
app.MapGet("/api/TodoItems", async (DataContext context) => await context.TodoItems.ToListAsync());
//Get Todo Items by id
app.MapGet("/api/TodoItems/{id}", async (DataContext context, int id) =>
await context.TodoItems.FindAsync(id) is TodoItem todoItem ? Results.Ok(todoItem) : Results.NotFound("Todo item not found ./"));
//Create Todo Items
app.MapPost("/api/TodoItems", async (DataContext context, TodoItem todoItem) =>
{
context.TodoItems.Add(todoItem);
await context.SaveChangesAsync();
return Results.Created($"/api/TodoItems/{todoItem.Id}",todoItem);
});
//Updating Todo Items
app.MapPut("/api/TodoItems/{id}", async (DataContext context, TodoItem todoItem, int id) =>
{
var todoItemFromDb = await context.TodoItems.FindAsync(id);
if (todoItemFromDb != null)
{
todoItemFromDb.Title = todoItem.Title;
todoItemFromDb.DueAt = todoItem.DueAt;
todoItemFromDb.IsDone = todoItem.IsDone;
await context.SaveChangesAsync();
return Results.Ok(todoItem);
}
return Results.NotFound("TodoItem not found");
});
//Deleting Todo Items
app.MapDelete("/api/TodoItems/{id}", async (DataContext context, int id) =>
{
var todoItemFromDb = await context.TodoItems.FindAsync(id);
if (todoItemFromDb != null)
{
context.Remove(todoItemFromDb);
await context.SaveChangesAsync();
return Results.Ok();
}
return Results.NotFound("TodoItem not found");
});
We’ve just added 4 different types of routes (Get, Post, Put, Delete) using patterns we were already familiar with. We also passed in DataContext as a dependency to the RequestDelegate to access the Database.
In this tutorial, you have learned how to set up a .Net6 minimal Rest API with SQL Server as a datastore. In our next article, we created an Azure SQL Database single database on Azure and used it on our project. Read HERE