ASP.NET Core 2.0 Scaffold Models from MySQL Database

There will be several cases, while developing a software where we have to work with existing database.

This is where the scaffolding becomes handy for developers to generate Models right away from the tables.

In this tutorial we will be showing on how to use ASP.NET Core 2.0 to generate Models from MySql Database.

Requirements

– ASP.NET Core 2.0

– MySQL Database (Preferably with MySQL Workbench)

– Visual Studio Code (Download)

let’s get started!

Create Database 

In the MySQL Terminal or Editor, run query below:

create database project1;

and then run command below to use this database before we proceed to next step.

use project1;

Create Table

In this tutorial we will creating table call “users”, run the query below to create table:

We have taken example of few data types such as varchar, datetime and integer, for more details on what are data types are please find this url

Now we have prepared our database, we will go ahead to next step on how to scaffold this database using ASP.NET Core 2.0

Create Console Application

To create console application in .Net Core, we first suggest to create suitable directory where your source code will be kept.

If you’re on cmd or terminal, navigate to the folder you wish to generate the console application.

Once you have navigated to that folder, run the command below

dotnet new console

Then open Visual Studio Code and select the folder of application you have generated.

When you first open, you may be prompt with “Required assets to build and debug are missing from ‘yourefoldername’. Add them?

VS Code Prompt Example

Press ‘Yes’ to let the IDE fetch required assets

Add Entity Framework Core and Related Packages

Like many other framework such node.js uses npm to mange packages, ASP.Net Core uses NuGet to manage package dependencies.

Now we will add required packages to our console application, which will help us generate scaffold.

In the terminal, please run the command below (make sure terminal or cmd is in the same path as the project)

dotnet add package Microsoft.EntityFrameworkCore.Design

You maybe prompt with “There are unresolved dependencies…” as per image below.

Press “Restore”

Once the package has successfully been added , you should see the code below appearing in the yourappname.csproj:

Next run the command below

dotnet add package Pomelo.EntityFrameworkCore.MySql

This package is an implementation of the Entity Framework Core’s MySQL driver, please be noted there are many other drivers that can be used but in this example we will be using this.

Next run the command below

dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet

This package is tool for Entity Framework, once the command is executed please go to your “projectname.csproj” file and find

<PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />

Then change PackageReference to DotNetCliToolReference, then it should look like this:

<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />

The whole item group should look like this

To check if there are any errors along way, run

If there are no issues the build will be succeeded

Scaffolding The Application

The most anticipated topic of the content is here.

To scaffold we will need to run the command below.

Please note the Uid is the user id of the MySQL you have set and Pwd is the passowrd. please change to match your system.

Once the command has been run successfully, you will see folder Models has been created with the Class Model User.cs

User Class

Using Domain Model Class

Before we can use the domain model classes and User class, we have to include them in the source code list. Otherwise, they won’t be built.

To include them, add <Compile Include=”.\Models\*” /> in the <ItemGroup> element.

Your yourappname.csproj file should now look similar to this

Access The Database

Before we access the database let’s add few rows into the table, so we can see the data being fetched from the scaffolded Model.

To insert data in table, please open MySQL terminal or editor and run query below

Please feel free to add more data as you wish.

Next add the following code into Program.cs file.

These two line of code will be on top of the file.

Please change “MyApp” to your app name.

Then add the code below in the main method

The Program.cs file will look like this

run command

dotnet run

The out put are as below

Leave a reply:

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Site Footer