Working with SQL Server LocalDB
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 (xref:)Dependency Injection container in the ConfigureServices method in the Startup.cs file:
[!code-csharpMain]
1: #if V1x
2: using System;
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Builder;
7: using Microsoft.AspNetCore.Hosting;
8: using Microsoft.Extensions.Configuration;
9: using Microsoft.Extensions.DependencyInjection;
10: using Microsoft.Extensions.Logging;
11: using Microsoft.EntityFrameworkCore;
12: using MvcMovie.Models;
13:
14: namespace MvcMovie
15: {
16: public class Startup
17: {
18: public Startup(IHostingEnvironment env)
19: {
20: var builder = new ConfigurationBuilder()
21: .SetBasePath(env.ContentRootPath)
22: .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
23: .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
24: .AddEnvironmentVariables();
25: Configuration = builder.Build();
26: }
27:
28: public IConfigurationRoot Configuration { get; }
29:
30: // This method gets called by the runtime. Use this method to add services to the container.
31: #region ConfigureServices
32: public void ConfigureServices(IServiceCollection services)
33: {
34: // Add framework services.
35: services.AddMvc();
36:
37: services.AddDbContext<MvcMovieContext>(options =>
38: options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContext")));
39: }
40: #endregion
41:
42:
43: // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
44: public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
45: {
46: loggerFactory.AddConsole(Configuration.GetSection("Logging"));
47: loggerFactory.AddDebug();
48:
49: if (env.IsDevelopment())
50: {
51: app.UseDeveloperExceptionPage();
52:
53: // Browser Link is not compatible with Kestrel 1.1.0
54: // For details on enabling Browser Link, see https://go.microsoft.com/fwlink/?linkid=840936
55: // app.UseBrowserLink();
56: }
57: else
58: {
59: app.UseExceptionHandler("/Home/Error");
60: }
61:
62: #region snippet_seed
63: app.UseStaticFiles();
64: #region snippet_1
65: app.UseMvc(routes =>
66: {
67: routes.MapRoute(
68: name: "default",
69: template: "{controller=Home}/{action=Index}/{id?}");
70: });
71: #endregion
72:
73: SeedData.Initialize(app.ApplicationServices);
74: }
75: }
76: }
77: #endregion
78: #endif
The ASP.NET Core (xref:)Configuration system reads the ConnectionString. For local development, it gets the connection string from the appsettings.json file:
[!code-jsonMain]
1: {
2: "Logging": {
3: "IncludeScopes": false,
4: "LogLevel": {
5: "Default": "Warning"
6: }
7: },
8: "ConnectionStrings": {
9: "MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-2;Trusted_Connection=True;MultipleActiveResultSets=true"
10: }
11: }
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 (xref:)Configuration for more information.
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).
View menu Right click on the
Movietable > View Designer
Contextual menu open on Movie table 
Movie table open in Designer
Note the key icon next to ID. By default, EF will make a property named ID the primary key.
Right click on the
Movietable > View Data
Contextual menu open on Movie table 
Movie table open showing table data
Seed the database
Create a new class named SeedData in the Models folder. Replace the generated code with the following:
[!code-csharpMain]
1: //#define First
2: #if First
3: // Seed without Rating
4: #region snippet_1
5: using Microsoft.EntityFrameworkCore;
6: using Microsoft.Extensions.DependencyInjection;
7: using System;
8: using System.Linq;
9:
10: namespace MvcMovie.Models
11: {
12: public static class SeedData
13: {
14: public static void Initialize(IServiceProvider serviceProvider)
15: {
16: using (var context = new MvcMovieContext(
17: serviceProvider.GetRequiredService<DbContextOptions<MvcMovieContext>>()))
18: {
19: // Look for any movies.
20: if (context.Movie.Any())
21: {
22: return; // DB has been seeded
23: }
24:
25: context.Movie.AddRange(
26: new Movie
27: {
28: Title = "When Harry Met Sally",
29: ReleaseDate = DateTime.Parse("1989-1-11"),
30: Genre = "Romantic Comedy",
31: Price = 7.99M
32: },
33:
34: new Movie
35: {
36: Title = "Ghostbusters ",
37: ReleaseDate = DateTime.Parse("1984-3-13"),
38: Genre = "Comedy",
39: Price = 8.99M
40: },
41:
42: new Movie
43: {
44: Title = "Ghostbusters 2",
45: ReleaseDate = DateTime.Parse("1986-2-23"),
46: Genre = "Comedy",
47: Price = 9.99M
48: },
49:
50: new Movie
51: {
52: Title = "Rio Bravo",
53: ReleaseDate = DateTime.Parse("1959-4-15"),
54: Genre = "Western",
55: Price = 3.99M
56: }
57: );
58: context.SaveChanges();
59: }
60: }
61: }
62: }
63: #endregion
64: #endif
If there are any movies in the DB, the seed initializer returns and no movies are added.
ASP.NET Core 2.x
Add the seed initializer to the Main method in the Program.cs file:
[!code-csharpMain]
1: using Microsoft.AspNetCore;
2: using Microsoft.AspNetCore.Hosting;
3: using Microsoft.Extensions.DependencyInjection;
4: using Microsoft.Extensions.Logging;
5: using MvcMovie.Models;
6: using System;
7:
8: namespace MvcMovie
9: {
10: public class Program
11: {
12: public static void Main(string[] args)
13: {
14: var host = BuildWebHost(args);
15:
16: using (var scope = host.Services.CreateScope())
17: {
18: var services = scope.ServiceProvider;
19:
20: try
21: {
22: // Requires using MvcMovie.Models;
23: SeedData.Initialize(services);
24: }
25: catch (Exception ex)
26: {
27: var logger = services.GetRequiredService<ILogger<Program>>();
28: logger.LogError(ex, "An error occurred seeding the DB.");
29: }
30: }
31:
32: host.Run();
33: }
34:
35: public static IWebHost BuildWebHost(string[] args) =>
36: WebHost.CreateDefaultBuilder(args)
37: .UseStartup<Startup>()
38: .Build();
39: }
40: }
ASP.NET Core 1.x
Add the seed initializer to the end of the Configure method in the Startup.cs file.
[!code-csharpMain]
1: #if V1x
2: using System;
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Builder;
7: using Microsoft.AspNetCore.Hosting;
8: using Microsoft.Extensions.Configuration;
9: using Microsoft.Extensions.DependencyInjection;
10: using Microsoft.Extensions.Logging;
11: using Microsoft.EntityFrameworkCore;
12: using MvcMovie.Models;
13:
14: namespace MvcMovie
15: {
16: public class Startup
17: {
18: public Startup(IHostingEnvironment env)
19: {
20: var builder = new ConfigurationBuilder()
21: .SetBasePath(env.ContentRootPath)
22: .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
23: .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
24: .AddEnvironmentVariables();
25: Configuration = builder.Build();
26: }
27:
28: public IConfigurationRoot Configuration { get; }
29:
30: // This method gets called by the runtime. Use this method to add services to the container.
31: #region ConfigureServices
32: public void ConfigureServices(IServiceCollection services)
33: {
34: // Add framework services.
35: services.AddMvc();
36:
37: services.AddDbContext<MvcMovieContext>(options =>
38: options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContext")));
39: }
40: #endregion
41:
42:
43: // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
44: public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
45: {
46: loggerFactory.AddConsole(Configuration.GetSection("Logging"));
47: loggerFactory.AddDebug();
48:
49: if (env.IsDevelopment())
50: {
51: app.UseDeveloperExceptionPage();
52:
53: // Browser Link is not compatible with Kestrel 1.1.0
54: // For details on enabling Browser Link, see https://go.microsoft.com/fwlink/?linkid=840936
55: // app.UseBrowserLink();
56: }
57: else
58: {
59: app.UseExceptionHandler("/Home/Error");
60: }
61:
62: #region snippet_seed
63: app.UseStaticFiles();
64: #region snippet_1
65: app.UseMvc(routes =>
66: {
67: routes.MapRoute(
68: name: "default",
69: template: "{controller=Home}/{action=Index}/{id?}");
70: });
71: #endregion
72:
73: SeedData.Initialize(app.ApplicationServices);
74: }
75: }
76: }
77: #endregion
78: #endif
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
Startupclass) 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
IIS Express system tray icon 
Contextual menu - 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.
Comments (
)
)
<00>
<01>
<02>
<03>
<04>
<05>
<06>
<07>
<08>
<09>
<10>
<11>
<12>
<13>
<14>
<15>
<16>
<17>
<18>
<19>
<20>
<21>
<22>
<23>
Link to this page:
//www.vb-net.com/AspNet-DocAndSamples-2017/aspnetcore/tutorials/first-mvc-app/working-with-sql.htm
| <SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |