Edit

Part 5, work with a database in an ASP.NET Core MVC app

Note

This isn't the latest version of this article. For the current release, see the .NET 11 version of this article.

Introduction

This part of the tutorial series focuses on working with a SQL database in your ASP.NET Core MVC application.

You'll learn how to:

  • Register and configure the Entity Framework Core database context for your ASP.NET Core MVC app.
  • Work with database connection strings for local development.
  • Use SQL Server Express LocalDB for development and examine your database and data using SQL Server Object Explorer.
  • Seed your database with initial sample data.

Prerequisite

This tutorial uses a database you set up in the previous step: Part 4, add a model to an ASP.NET Core MVC app.

Working with the database context

The MvcMovieContext object handles the task of connecting to the database and mapping Movie objects to database records. The database context is registered with the Dependency Injection container in the Program.cs file:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<MvcMovieContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MvcMovieContext") ?? throw new InvalidOperationException("Connection string 'MvcMovieContext' not found.")));

The ASP.NET Core Configuration system reads the ConnectionString key. For local development, it gets the connection string from the appsettings.json file:

"ConnectionStrings": {
  "MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-4ebefa10-de29-4dea-b2ad-8a8dc6bcf374;Trusted_Connection=True;MultipleActiveResultSets=true"
}

Warning

This article uses a local database that doesn't require the user to be authenticated. Production apps should use the most secure authentication flow available. For more information on authentication for deployed test and production apps, see Secure authentication flows.

SQL Server Express LocalDB

LocalDB:

  • Is a lightweight version of the SQL Server Express Database Engine, installed by default with Visual Studio.
  • Starts on demand by using a connection string.
  • Is targeted for program development. It runs in user mode, so there's no complex configuration.
  • By default creates .mdf files in the C:/Users/{user} directory.

Examine the database

From the View menu, open SQL Server Object Explorer (SSOX).

Right-click on the Movie table (dbo.Movie) > View Designer

Right-click on the Movie table > View Designer.

Movie table open in Designer.

Note the key icon next to ID. By default, EF makes a property named ID the primary key.

Right-click on the Movie table > View Data

Right-click on the Movie table > View Data.

Movie table open showing no data.

Seed the database

Create a new class named SeedData in the Models folder. Replace the generated code with the following:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using MvcMovie.Data;
using System;
using System.Linq;

namespace MvcMovie.Models;

public static class SeedData
{
    public static void Initialize(IServiceProvider serviceProvider)
    {
        using (var context = new MvcMovieContext(
            serviceProvider.GetRequiredService<
                DbContextOptions<MvcMovieContext>>()))
        {
            // Look for any movies.
            if (context.Movie.Any())
            {
                return;   // DB has been seeded
            }
            context.Movie.AddRange(
                new Movie
                {
                    Title = "When Harry Met Sally",
                    ReleaseDate = DateTime.Parse("1989-2-12"),
                    Genre = "Romantic Comedy",
                    Price = 7.99M
                },
                new Movie
                {
                    Title = "Ghostbusters ",
                    ReleaseDate = DateTime.Parse("1984-3-13"),
                    Genre = "Comedy",
                    Price = 8.99M
                },
                new Movie
                {
                    Title = "Ghostbusters 2",
                    ReleaseDate = DateTime.Parse("1986-2-23"),
                    Genre = "Comedy",
                    Price = 9.99M
                },
                new Movie
                {
                    Title = "Rio Bravo",
                    ReleaseDate = DateTime.Parse("1959-4-15"),
                    Genre = "Western",
                    Price = 3.99M
                }
            );
            context.SaveChanges();
        }
    }
}

If there are any movies in the database, the seed initializer returns and no movies are added.

if (context.Movie.Any())
{
    return;  // DB has been seeded.
}

Add the seed initializer

Replace the contents of Program.cs with the following code. The new code is highlighted.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using MvcMovie.Data;
using MvcMovie.Models;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<MvcMovieContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MvcMovieContext") ?? throw new InvalidOperationException("Connection string 'MvcMovieContext' not found.")));

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var services = scope.ServiceProvider;

    SeedData.Initialize(services);
}

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();

app.UseRouting();

app.UseAuthorization();

app.MapStaticAssets();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}")
    .WithStaticAssets();

app.Run();

Test the app. Force the app to initialize, calling the code in the Program.cs file, so the seed method runs. To force initialization, close the command prompt window that Visual Studio opened, and restart by pressing Ctrl+F5.

The app shows the seeded data.

MVC Movie app open in Microsoft Edge showing movie data.

And a refresh of the Movie table shows the same data.

Movie table has seeded data.

Note

You may not be able to enter decimal commas in decimal fields. To support jQuery validation for non-English locales that use a comma (",") for a decimal point, and non US-English date formats, you must take steps to globalize your app. See this GitHub comment 4076 for instructions on adding decimal comma.