Work with SQL in ASP.NET Core
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 ConfigureServices
method in the Startup.cs file:
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
services.AddDbContext<MvcMovieContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContext")));
}
The ASP.NET Core Configuration system reads the ConnectionString
. For local development, it gets the connection string from the appsettings.json file:
"ConnectionStrings": {
"MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-2;Trusted_Connection=True;MultipleActiveResultSets=true"
}
When the app is deployed to a test or production server, an environment variable can be used to set the connection string to a production SQL Server. See Configuration for more information.
SQL Server Express LocalDB
LocalDB is a lightweight version of the SQL Server Express Database Engine that's targeted for program development. LocalDB starts on demand and runs in user mode, so there's no complex configuration. By default, LocalDB database creates .mdf files in the C:/Users/{user} directory.
From the View menu, open SQL Server Object Explorer (SSOX).
Right click on the
Movie
table > View Designer
Note the key icon next to ID
. By default, EF will make a property named ID
the primary key.
Right click on the
Movie
table > View 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 DB, 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:
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using MvcMovie.Data;
using MvcMovie.Models;
using System;
namespace MvcMovie
{
public class Program
{
public static void Main(string[] args)
{
var host = CreateHostBuilder(args).Build();
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
try
{
SeedData.Initialize(services);
}
catch (Exception ex)
{
var logger = services.GetRequiredService<ILogger<Program>>();
logger.LogError(ex, "An error occurred seeding the DB.");
}
}
host.Run();
}
public static IHostBuilder CreateHostBuilder(string[] args) =>
Host.CreateDefaultBuilder(args)
.ConfigureWebHostDefaults(webBuilder =>
{
webBuilder.UseStartup<Startup>();
});
}
}
Test the app
Delete all the records in the DB. You can do this with the delete links in the browser or from SSOX.
Force the app to initialize (call the methods in the
Startup
class) so the seed method runs. To force initialization, IIS Express must be stopped and restarted. You can do this with any of the following approaches:Right click the IIS Express system tray icon in the notification area and tap Exit or Stop Site
- If you were running VS in non-debug mode, press F5 to run in debug mode
- If you were running VS in debug mode, stop the debugger and press F5
The app shows the seeded data.
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 ConfigureServices
method in the Startup.cs file:
public void ConfigureServices(IServiceCollection services)
{
services.Configure<CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies
// is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
services.AddDbContext<MvcMovieContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContext")));
}
The ASP.NET Core Configuration system reads the ConnectionString
. For local development, it gets the connection string from the appsettings.json file:
"ConnectionStrings": {
"MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-2;Trusted_Connection=True;MultipleActiveResultSets=true"
}
When you deploy the app to a test or production server, you can use an environment variable or another approach to set the connection string to a real SQL Server. See Configuration for more information.
SQL Server Express LocalDB
LocalDB is a lightweight version of the SQL Server Express Database Engine that's targeted for program development. LocalDB starts on demand and runs in user mode, so there's no complex configuration. By default, LocalDB database creates .mdf files in the C:/Users/{user} directory.
From the View menu, open SQL Server Object Explorer (SSOX).
Right click on the
Movie
table > View Designer
Note the key icon next to ID
. By default, EF will make a property named ID
the primary key.
Right click on the
Movie
table > View 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 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 DB, 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:
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System;
using Microsoft.EntityFrameworkCore;
using MvcMovie.Models;
using MvcMovie;
namespace MvcMovie
{
public class Program
{
public static void Main(string[] args)
{
var host = CreateWebHostBuilder(args).Build();
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
try
{
var context = services.GetRequiredService<MvcMovieContext>();
context.Database.Migrate();
SeedData.Initialize(services);
}
catch (Exception ex)
{
var logger = services.GetRequiredService<ILogger<Program>>();
logger.LogError(ex, "An error occurred seeding the DB.");
}
}
host.Run();
}
public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
WebHost.CreateDefaultBuilder(args)
.UseStartup<Startup>();
}
}
Test the app
Delete all the records in the DB. You can do this with the delete links in the browser or from SSOX.
Force the app to initialize (call the methods in the
Startup
class) so the seed method runs. To force initialization, IIS Express must be stopped and restarted. You can do this with any of the following approaches:Right click the IIS Express system tray icon in the notification area and tap Exit or Stop Site
- If you were running VS in non-debug mode, press F5 to run in debug mode
- If you were running VS in debug mode, stop the debugger and press F5
The app shows the seeded data.
Feedback
Loading feedback...