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:

create table user(
id int,
name varchar(20),
DOB datetime,
gender varchar(20),
address varchar(120),
pincode int,
phone varchar(26),
primary key(id)
);

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:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" />
</ItemGroup>

</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

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

The whole item group should look like this

<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>

</ItemGroup>

To check if there are any errors along way, run

dotnet restore
dotnet build

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.

dotnet ef dbcontext scaffold "Server=localhost;Database=project1;Uid=root;Pwd=YourUserPassword;SslMode=none" "Pomelo.EntityFrameworkCore.MySql" -d -f -o Model

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

<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>

<TargetFramework>netcoreapp2.0</TargetFramework>

</PropertyGroup>

</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

INSERT INTO `user`
(`id`,
`name`,
`DOB`,
`gender`,
`address`,
`pincode`,
`phone`)
VALUES
(1,
"John",
"2012-10-09",
"Male",
"Bangkok",
"10110",
"123456");

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.

using MyApp.Models;
using System.Linq;

1 2	using MyApp.Models; using System.Linq;

Please change “MyApp” to your app name.

Then add the code below in the main method

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}");
}

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

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}");
            }
        }
    }
}

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

ID:1 Name:John 10/9/12 12:00:00 AM
ID:2 Name:test 10/9/12 12:00:00 AM

1 2	ID:1 Name:John 10/9/12 12:00:00 AM ID:2 Name:test 10/9/12 12:00:00 AM

dvarun.

ASP.NET Core 2.0 Scaffold Models from MySQL Database

admin

Rails Active Storage Tutorial

Dailymotion JS SDK Boilerplate

Leave a reply: Cancel Reply

ASP.NET Core 2.0 Scaffold Models from MySQL Database

admin

Post Navigation

Rails Active Storage Tutorial

Dailymotion JS SDK Boilerplate

Leave a reply: Cancel Reply