aspnetcore/tutorials/razor-pages/sql/includes/sql5.md
:::moniker range="= aspnetcore-5.0"
View or download sample code (how to download).
The RazorPagesMovieContext 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 Startup.cs:
The ASP.NET Core Configuration system reads the ConnectionString key. For local development, configuration gets the connection string from the appsettings.json file.
The generated connection string is similar to the following JSON:
[!INCLUDE managed-identities]
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.
<a name="ssox"></a>
From the View menu, open SQL Server Object Explorer (SSOX).
Right-click on the Movie table and select View Designer:
Note the key icon next to ID. By default, EF creates a property named ID for the primary key.
Right-click on the Movie table and select View Data:
The SQLite website states:
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world.
There are many third-party tools you can download to manage and view a SQLite database. The image below is from DB Browser for SQLite. If you have a favorite SQLite tool, leave a comment on what you like about it.
[!NOTE] For this tutorial, the Entity Framework Core migrations feature is used where possible. Migrations updates the database schema to match changes in the data model. However, migrations can only do the kinds of changes that the EF Core provider supports, and the SQLite provider's capabilities are limited. For example, adding a column is supported, but removing or changing a column is not supported. If a migration is created to remove or change a column, the
ef migrations addcommand succeeds but theef database updatecommand fails. Due to these limitations, this tutorial doesn't use migrations for SQLite schema changes. Instead, when the schema changes, the database is dropped and re-created.The workaround for the SQLite limitations is to manually write migrations code to perform a table rebuild when something in the table changes. A table rebuild involves:
- Creating a new table.
- Copying data from the old table to the new table.
- Dropping the old table.
- Renaming the new table.
For more information, see the following resources:
Create a new class named SeedData in the Models folder with the following code:
If there are any movies in the database, the seed initializer returns and no movies are added.
if (context.Movie.Any())
{
return;
}
<a name="si"></a>
Replace the contents of the Program.cs with the following code:
In the previous code, the Main method has been modified to do the following:
seedData.Initialize method, passing to it the database context instance.The following exception occurs when Update-Database has not been run:
SqlException: Cannot open database "RazorPagesMovieContext-" requested by the login. The login failed.Login failed for user 'user name'.
Delete all the records in the database. Use the delete links in the browser or from SSOX
Force the app to initialize by calling the methods in the Startup class, so the seed method runs. To force initialization, IIS Express must be stopped and restarted. Stop and restart IIS with any of the following approaches:
Right-click the IIS Express system tray icon in the notification area and select Exit or Stop Site:
If the app is running in non-debug mode, press <kbd>F5</kbd> to run in debug mode.
If the app in debug mode, stop the debugger and press <kbd>F5</kbd>.
Delete all the records in the database, so the seed method will run. Stop and start the app to seed the database.
The app shows the seeded data:
[!div class="step-by-step"] Previous: Scaffolded Razor Pages Next: Update the pages
:::moniker-end