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:
1 2 3 4 5 6 7 8 9 10 |
create table user( id int, name varchar(20), DOB datetime, gender varchar(20), address varchar(120), pincode int, phone varchar(26), primary key(id) ); |
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?
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:
1 2 3 |
<ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" /> </ItemGroup> |
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
1 2 3 4 5 |
<ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" /> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" /> <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.0.0" /> </ItemGroup> |
To check if there are any errors along way, run
1 2 |
dotnet restore dotnet build |
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.
1 |
dotnet ef dbcontext scaffold "Server=localhost;Database=project1;Uid=root;Pwd=YourUserPassword;SslMode=none" "Pomelo.EntityFrameworkCore.MySql" -d -f -o Model |
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
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <OutputType>Exe</OutputType> <TargetFramework>netcoreapp2.0</TargetFramework> </PropertyGroup> <ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" /> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" /> <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.0.0" /> <Compile Include=".\Models\*" /> </ItemGroup> </Project> |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO `user` (`id`, `name`, `DOB`, `gender`, `address`, `pincode`, `phone`) VALUES (1, "John", "2012-10-09", "Male", "Bangkok", "10110", "123456"); |
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.
1 2 |
using MyApp.Models; using System.Linq; |
Please change “MyApp” to your app name.
Then add the code below in the main method
1 2 3 4 5 |
var dbContext = new project1Context(); var users = dbContext.User.ToList(); foreach (var u in users) { System.Console.WriteLine($"ID:{u.Id} Name:{u.Name} {u.Dob}"); } |
The Program.cs file will look like this
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
using scaffold_app.Models; using System.Linq; using System; namespace scaffold_app { class Program { static void Main(string[] args) { var dbContext = new project1Context(); var users = dbContext.User.ToList(); foreach (var u in users) { System.Console.WriteLine($"ID:{u.Id} Name:{u.Name} {u.Dob}"); } } } } |
run command
dotnet run
The out put are as below
1 2 |
ID:1 Name:John 10/9/12 12:00:00 AM ID:2 Name:test 10/9/12 12:00:00 AM |