009. Working with SQL Server LocalDB, 009. workinglocaldb

009. Working with SQL Server LocalDB, 009. workinglocaldb

Working with SQL Server LocalDB

Operate data on SQL server localdb

2-minute reading duration

Content

1. SQL Server Express LocalDB

A simplified free version of SQL Server Express LocalDB SQL server

2. Seed the database

Initialize the initial table data of the database.

By Rick Anderson

The MvcMovieContext object handles the task of connecting to the database and mapping Movie objects to database records.

The MvcMovieContext object processes the tasks of linking a database to map a Movie object to a table record.

The database context is registered with the Dependency Injection container in the ConfigureServices method inStartup. csFile:

DB context inStartup. csThe ConfigureServices method of the file is registered to the DI container:

1 public void ConfigureServices (IServiceCollection services) 2 3 {4 5 // Add framework services. 6 7 services. addMvc (); 8 9 10 11 services. addDbContext <MvcMovieContext> (options => 12 13 options. useSqlServer (Configuration. getConnectionString ("MvcMovieContext"); 14 15}C # code

The ASP. NET Core Configuration system reads the ConnectionString.

The Configuration System of Asp.net core reads the ConnectionString configuration value.

For local development, it gets the connection string fromAppsettings. jsonFile:

For local development, it starts fromAppsettings. jsonFile to obtain the value of the link string:

1 "ConnectionStrings": {2 3 "MvcMovieContext": "Server = (localdb) \ mssqllocaldb; Database = MvcMovieContext-20613a4b-deb5-4145-b6cc-a5fd19afda13; Trusted_Connection = True; MultipleActiveResultSets = true" 4 5}JSON Code

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.

When you deploy an application to a test or production server, you can use environment variables or other methods to set the value of a real db link string.

See Configuration for more information.

View Configuration for more information.

SQL Server Express LocalDB

(A simplified free and lightweight SQL server Version)

LocalDB is a lightweight version of the SQL Server Express Database Engine that is targeted for program development.

LocalDB is a lightweight version of SQL Server Express Database Engine for local program development.

LocalDB starts on demand and runs in user mode, so there is no complex configuration.

LocalDB directly starts querying in user mode, so there is no complicated configuration.

By default, LocalDB database creates "*. mdf" files inC:/Users/<user>Directory.

By defaultC:/Users/<user>Create a "*. mdf" database file under the folder.

• FromViewMenu, openSQL Server Object Explorer(SSOX ).

SlaveViewMenu, openSQL Server Object Explorer:

• Right click on the Movie table> View Designer

Right-click the Movie table and click> View DesignerMenu

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

Note that the PK icon is next to the ID field. EF uses ID as a PK by default.

• Right click on the Movie table> View Data

Right-click the Movie table and select> View DataMenu

Seed the database

Initialize value to database

Create a new class named SeedData inModelsFolder. Replace the generated code with the following:

InModelsCreate a new SeedData class under the folder and replace the automatically generated code with the following code:

1 using Microsoft. entityFrameworkCore; 2 3 using Microsoft. extensions. dependencyInjection; 4 5 using System; 6 7 using System. linq; 8 9 10 11 namespace MvcMovie. models 12 13 {14 15 public static class SeedData 16 17 {18 19 public static void Initialize (IServiceProvider serviceProvider) 20 21 {22 23 using (var context = new MvcMovieContext (24 25 serviceProvider. getRequiredService <DbContextOptions <MvcMovieContext> () 26 27 {28 29 // Look for any movies. 30 31 if (context. movie. any () 32 33 {34 35 return; // DB has been seeded 36 37} 38 39 40 41 context. movie. addRange (42 43 new Movie 44 45 {46 47 Title = "When Harry Met Sally", 48 49 ReleaseDate = DateTime. parse ("1989-1-11"), 50 51 Genre = "Romantic Comedy", 52 53 Price = 7.99 M 54 55 }, 56 57 58 59 new Movie 60 61 {62 63 Title = "Ghostbusters", 64 65 ReleaseDate = DateTime. parse ("1984-3-13"), 66 67 Genre = "Comedy", 68 69 Price = 8.99 M 70 71 }, 72 73 74 75 new Movie 76 77 {78 79 Title = "Ghostbusters 2", 80 81 ReleaseDate = DateTime. parse ("1986-2-23"), 82 83 Genre = "Comedy", 84 85 Price = 9.99 M 86 87 }, 88 89 90 91 new Movie 92 93 {94 95 Title = "Rio Bravo", 96 97 ReleaseDate = DateTime. parse ("1959-4-15"), 98 99 Genre = "Western", 100 101 Price = 3.99M102 103} 104 105); 106 107 context. saveChanges (); 108 109} 110 111} 112 113} 114 115}C # Code

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

If there are data records in the database, the initial data will be directly returned. If not, the initial data will be added.

1 if (context. Movie. Any () 2 3 {4 5 return; // DB has been seeded.6 7}C # Code

Add the seed initializer to the end of the Configure method inStartup. csFile:

Add the data initialization classStartup. csThe last line of the file's Configure method:

1 app. useStaticFiles (); 2 3 app. useMvc (routes => 4 5 {6 7 routes. mapRoute (8 9 name: "default", 10 11 template: "{controller = Home}/{action = Index}/{id ?} "); 12 13}); 14 15 16 17 SeedData. Initialize (app. ApplicationServices); 18 19} 20 21} 22 23}C # Code

Test the app

Test Application

• Delete all the records in the DB. You can do this with the delete links in the browser or from SSOX.

To delete all data records in the database, you can click the delete link in SSOX.

• Force the app to initialize (call the methods in the Startup class) so the seed method runs.

Force Application initialization. The seed method is executed in the Startup class.

To force initialization, IIS Express must be stopped and restarted. You can do this with any of the following approaches:

To force initialization, iis express must be restarted. You can use either of the following methods:

• Right click the IIS Express system tray icon in the notification area and tapExitOrStop Site

Right-click the iis icon in the notification area on the system tray and clickExitOrStop Site.

• If you were running VS in non-debug mode, press F5 to run in debug mode

If you run vs in non-debug mode, press F5 to enter the debug mode.

• If you were running VS in debug mode, stop the debugger and press F5

If you run vs in debug mode, stop debugging and press F5

The app shows the seeded data.

The app displays the initialized data:

 

 

 

 

Mon

Monday