Working with SQL Server LocalDB

By Rick Anderson

The ApplicationDbContext class 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:

1
2
3
4
5
6
7
public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

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

1
2
3
4
5
6
{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=aspnet5-MvcMovie-53e157ca-bf3b-46b7-bb3f-82ac58612f5e;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 .

SQL Server Express LocalDB

LocalDB is a lightweight version of the SQL Server Express Database Engine that is targeted for program development. LocalDB starts on demand and runs in user mode, so there is 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).
../../_images/ssox.png
  • Right click on the Movie table > View Designer
../../_images/design.png ../../_images/dv.png

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
../../_images/ssox2.png ../../_images/vd22.png

Seed the database

We’ll take advantage of Dependency Injection (DI) to seed the database. You add server side dependencies to ASP.NET 5 projects in the project.json file. Open project.json and add the Microsoft DI package. IntelliSense helps us add the package.

../../_images/di_intel.png

The DI package is highlighted below:

{
  "userSecretsId": "aspnet5-MvcMovie-53e157ca-bf3b-46b7-bb3f-82ac58612f5e",
  "version": "1.0.0-*",
  "compilationOptions": {
    "emitEntryPoint": true
  },

    "dependencies": {
        "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final",
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Microsoft.AspNet.Authentication.Cookies": "1.0.0-rc1-final",

Optional: Tap the quick actions light bulb icon and select Sort Properties.

../../_images/sort.png

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

using Microsoft.Extensions.DependencyInjection;
using System;
using System.Linq;

namespace MvcMovie.Models
{
    public static class SeedData
    {
        public static void Initialize(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.GetService<ApplicationDbContext>();

            if (context.Database == null)
            {
                throw new Exception("DB is null");
            }

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

            context.Movie.AddRange(
                 new Movie
                 {
                     Title = "When Harry Met Sally",
                     ReleaseDate = DateTime.Parse("1989-1-11"),
                     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();
        }
    }
}

The GetService method comes from the DI package we just added. Notice if there are any movies in the DB, the seed initializer returns.

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

Add the seed initializer to the end of the Configure method in the Startup.cs file:

1
2
3
4
5
6
7
8
9
    app.UseMvc(routes =>
    {
        routes.MapRoute(
            name: "default",
            template: "{controller=Home}/{action=Index}/{id?}");
    });

    SeedData.Initialize(app.ApplicationServices);
}

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 so the seed method runs. You can do this by setting a break point on the first line of the SeedData Initialize method, and launching the debugger (Tap F5 or tap the IIS Express button).
../../_images/dbg.png

The app shows the seeded data.

../../_images/m55.png