Sample Site Repo
This document references a Repo found on GitHub at:
Fork or Clone the Repo to experiment locally with the Project in Visual Studio.
Supporting Video: Cloning Projects in Visual Studio
Supporting Video: Cloning Projects plus Database Migration, Population and First Database Driven Pages
Database Set Up
Entity Framework Package Requirements
This tutorial will use Entity Framework to set up a database table and then use that data in a MVC web application.
Entity Framework is an Object-Relational Mapper (O/RM) that is used to map objects in code to a backend database. Objects are used to save and retrieve data from a Relational Database rather than hand-coded SQL queries.
To use Entity Framework requires adding additional packages to Visual Studio. The following packages are required:
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.tools
In Visual Studio packages are managed through:
Note: The tutorial will make use of the local SQL Server ((localdb)\MSSQLLocalDB) provided as part of Visual Studio.
Create a Model
To use Entity Framework we need to set up a Model. This model will map to a database table.
We will create a 'Films' table with the following structure:
In SQL terms the 'Films' table could be created with:
[FilmID] [int] NOT NULL,
[FilmCertificate] [text] NOT NULL,
[FilmTitle] [text] NOT NULL,
[FilmDescription] [text] NULL,
[FilmImage] [text] NULL,
[FilmPrice] [decimal](18, 0) NOT NULL,
[Stars] [int] NOT NULL,
[ReleaseDate] [date] NOT NULL)
Create a Model class file at Models/Film.cs. This can be thought of representing one 'row' within the database, so save this in the singular as 'Film'.
The model uses Data Annotations attributes such as [Key] and [Column(TypeName = "varchar(100)")] to set appropriate data types. For example the attribute [Key] tells the Model the Id is a primary key. The [Required] attribute tells the Model that the FilmTitle is a required field. Validation on this can then be done server and/or client side.
Storing the Connection String in appsettings.json
We need to apply settings in the MVC project to tell the application which database we intend to use. This is done through a 'Connection String'. The settings for the Connection String are placed in appsettings.json
The above shows the connection string for a Local MSSQL database of name 'MyFilmsDb'. This will be stored on a local server (localdb)\\mssqllocaldb.
Note:
You can connect to a range of different databases, for example to connect to a SQLite database the connection string may appear:
Creating a Context and Declare a DbSet Property
As we're using Entity Framework to manage our database we need to establish a context which is achieved via the DbContext class.
Part of the Entity Framework Core, the DbContext class represents a session with a database and provides an API for working with the database.
To establish a Context, create a class file at Models/ApplicationDbContext.cs
This was named ApplicationDbContext as it will be the main DbContext for our application.
A DbSet property of Film is also created.
The DbSet<TEntity> class represents a collection for a given entity or model. In the above the collection will contain data mapped to the Film model created early. The convention is that this collection be given a plural name to match the singular name of the entity/model it contains. In terms of the database the DbSet property will be mapped to a table, in this example a table named 'Films'.
Tip:
If needed you can establish more than one DbContext to allow any given application to connect to multiple datasources.
You can also create more than one DbSet to allow transations with multiple database tables.
Configuring the Startup.cs
Once created, we need to make the Context known to the application and this is done in the Startup.cs files ConfigureServices() method by adding the Context in as a service.
This will facilitate the distribution of the DbContext to where it is needed in our application through a technique known as 'Dependency Injection'.
Migrate and Build
With the files set up we can now use the console in Visual Studio create a Migration file and then build the database.
Use the console to run the migration command and then update the database.
Add-Migration Initial
If successful the above should create a Migration file that will have the necessary code to create the table. Next update the database with the second console command:
Update-Database
It is important to run both console commands. The Add-Migration command can be given a name by the developer. In this example, it was sensible to call it Initial.
Tip: if using a MAC the terminal commands differ slightly use:
####### MAC VERSION ########
dotnet ef migrations add Initial --context ApplicationDbContext
dotnet ef database update --context ApplicationDbContext
View the Database Table
The Table and the Data (or lack of) can now be viewed through the SQL Server Object Explorer.
In Visual Studio select View > SQL Server Object Explorer, locate the (localdb)\\mssqllocaldb and drill in to locate the table created by the migration. Right click on the table and choose View Data.
We don't have any data in the table yet. Next we'll populate the table with data.