Implement a Relational Database With ASP.NET Core

Importing Models From an Existing Database

It’s time to get back into Visual Studio now and generate some data models from your new database.

To avoid any confusion between database contexts, we need to create a new MVC app called MyRecipes2. We’re going to use this project to teach database-first integration, so you don’t need to include any authentication, such as individual user accounts. 

Create a new MVC project, and when it is ready, add a new folder called Data to the project. This will be the output folder for the model scaffolding process. 

Scaffold the Database Model With the Package Manager Console

As of the writing of this course, .NET Core does not have automatic scaffolding from the database built directly into the Visual Studio UI. So we’re going to use the Package Manager Console instead.

If it’s not open already, open the Package Manager Console by clicking the Tools menu, then select NuGet Package Manager, and Package Manager Console. 

At the prompt, run the following command:

Scaffold-DbContext “Server=your-server-name;Database=your-database-name;
Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir your-output-directory

Remember, your server name, which you will supply for the Server attribute,  is the name of the local server you connected to using SSMS when you created this database. If you don’t remember the server name, you can find it by right-clicking your database in SSMS. 

The Database attribute is the name you gave the database in SSMS.

The -OutputDir attribute is the name of the folder in which you want the new model generated. This is the new folder, DBFirst_Models, that we created a few moments ago.

So in my case, my command looks like this:

The command may take a few minutes to complete, depending on the processing power of your system. You’ll know it’s done when the prompt reappears in the Package Manager Console and a series of .cs files show beneath the output folder in the Solution Manager pane. 

Open each of the new files and verify that they are as you expect them to be. You should see code that is virtually identical to the versions from Part 1 of this course.

One difference is that all of the models are partial classes, which allows you to extend the class by creating another partial class for any of the model classes.

A second difference is that there is a new database context class: MyRecipes2Context. This is your DbContext class for your database. Since we didn’t manually build the Entity Framework tables into the database (AspNetUsers, AspNetRoles, AspNetClaims, etc.), you won’t see these as part of your DbContext. You could inject .NET Identity into the app, but it's not necessary for what we're doing. 

The data model is imported, but you need to register the new DbContext before you can use it. 

Register the New DbContext With Dependency Injection

ASP.NET Core’s configuration pattern is flexible, allowing for the connection string to be stored in appsettings.json, as an environment variable, in the user secret store, or some other configuration source. In keeping with Microsoft’s best practices, we’re going to store our database provider configurations Startup.cs and our connection strings in appsettings.json.

To do this:

1. Open Data\MyRecipes2Context.cs

2. Verify that the following constructor is present. If it is not, add it: 

   public MyRecipes2Context(DbContextOptions<MyRecipesDB2Context> options) : base(options)
   {
   }

   This constructor allows configuration to be passed into the context via dependency injection.

3. Find the  OnConfiguring  method. You’ll see a warning in the middle of the method’s code, which reads: 

   To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.

   Your database’s connection string currently sits in your scaffolded code, in the  OnConfiguring  method right below this message. When the connection string references a database on a non-local server, there may be login information in the connection string. To keep that information secure, it is a good practice to remove it from the source code where it currently sits. A better place to put it is in the appsettings.json file, as shown below. You can delete the above message and the connection string code from your  dbcontext class. 

4. Open appsettings.json and add the connection string for your new database: 

   {
       "ConnectionStrings": {
           "DefaultConnection":"Server=your-server-name;
           Database=your-database-name;Trusted_Connection=True;"
       },
       "Logging": {
           "LogLevel": {
               "Default": "Warning"
           }
       },
       "AllowedHosts": "*"
   }

5. Now open Startup.cs.

6. Add the following using statements to the beginning of the file: 

   using MyRecipes2.Data;
   
   using Microsoft.EntityFrameworkCore;

7. Locate the  ConfigureServices  method and add the following code to register your new  DbContext: 

   public void ConfigureServices(IServiceCollection services)
   
   {
       services.Configure<CookiePolicyOptions>(options =>
       {
           options.CheckConsentNeeded = context => true;
           options.MinimumSameSitePolicy = SameSiteMode.None;
       });
   
       // Add MyRecipes2Context
       services.AddDbContext<MyRecipes2Context>(options =>
           options.UseSqlServer(
               Configuration.GetConnectionString("DefaultConnection")));
   
       services.AddMvc().SetCompatibilityVersion(
           CompatibilityVersion.Version_2_2);
   }

That’s it. You have now created a complete data model from an existing database and registered it for use with dependency injection.

If you were building a complete application around your imported data model, you would now begin scaffolding controllers and views for the model classes, just as you did for the code-first models in the other MVC projects. However, our focus is not on the complete application at this point, but rather, on the database and the object-relational modeling we use in applications to interact with it.

So for now, we’re done with the project code.

Let's Recap!

In this chapter, you did the reverse of the code-first migrations process. You used database-first integration to construct a C# data model and database context from an existing database. This wasn’t nearly as difficult a process as you imagined, was it?

• You created a new MVC project for this part of the course in which to generate your new data model.

• Using the package manager console, you scaffolded all of the model classes and the database context class with a single command.

• You set up a connection string that ties your database context object to the database.

• And finally, you registered your new database context with Entity Framework so that it can be used via dependency injection.

In the next chapter, we’re going to go back to SSMS so you can learn how to build stored procedures, embed them in your database, and then execute them from your web application.