.NET / C# / Web API, Blazor
Connecting a database to Web API
Step 1: NuGet packages
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
Step 2: The connection string in configuration
// appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=OrderDb;Trusted_Connection=True;TrustServerCertificate=True"
}
}
Step 3: DbContext
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<Order> Orders { get; set; }
public DbSet<Customer> Customers { get; set; }
}
Step 4: Registration in DI (Program.cs)
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
// Scoped by default - exactly as we discussed with Singleton/Scoped/Transient
builder.Services.AddScoped<IOrderService, OrderService>();
builder.Services.AddControllers();
var app = builder.Build();
app.MapControllers();
app.Run();
Step 5: Migrations - creating the database schema
dotnet ef migrations add InitialCreate
dotnet ef database update
Step 6: Use in a controller/service (through DI, constructor injection)
public class OrderService : IOrderService
{
private readonly AppDbContext _context;
public OrderService(AppDbContext context) => _context = context; // injected automatically
public async Task<OrderDto> GetOrderAsync(int id)
{
var order = await _context.Orders.AsNoTracking().FirstOrDefaultAsync(o => o.Id == id);
return order == null ? null : new OrderDto(order.Id, order.CustomerName, order.TotalAmount);
}
}
An alternative: different connection strings per environment
// appsettings.Development.json
{ "ConnectionStrings": { "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=OrderDb_Dev;..." } }
// appsettings.Production.json (or environment variables/Key Vault in practice)
{ "ConnectionStrings": { "DefaultConnection": "..." } }
ASP.NET Core automatically loads the right file based on ASPNETCORE_ENVIRONMENT - a connection string should never be hardcoded, and in production it usually goes to Azure Key Vault/environment variables, not to a file in the repository.
Summary in 4 points
- The EF Core package for the specific database (
SqlServer/Npgsqlfor PostgreSQL/Sqlite) - A
DbContextwith aDbSet<T>for each table AddDbContext<T>()in DI with a connection string from configuration- Migrations (
dotnet ef migrations add+database update) to create/update the schema