Database first in .Net Core 2.0 step by step: Angular 4 + Core 2.0 CRUD operation Part I

Untitled design (1)

Note Part II(CRUD operation with Angular 4) of this post is here.

In my previous post, I have explained how to create an Angular 5 application in Visual Studio 2017 using .Net Core templates which you can find here.

In this series of posts, I will explain the Create, Read, Update, Delete(CRUD) using Angular 5 with .Net Core 2 API.

In this part I post, we will see how to do Database first set up in Angular(.Net Core 2.0 project) and will set up the Employee API.

prerequisite:

  • Visual studio 2017 community edition, download here
  • .Net Core 2.0 SDK from here (I have written a post to install SDK here)

Create the Angular application using .Net Core 2.0 template in VS 2017

Once you have all these installed, open your Visual Studio 2017 -> Create New Project -> Select Core Web application:

az1

Click on Ok and in next window, select Angular as shown below:

az2

Visual Studio will create a well-structured application for you(Note that I have manually added Models folder as we will require this in future):

crud3

I will not go deep into the Angular structure in this post but if you require more details then I have written a detailed post on Angular and .Net Core 2.0 which you find here.

Once you run the application on IISExpress, it will have the landing page as below:

crud1

Creation of Employee Database

Let us Create Employee database in SQL, you can use below queries to create the database and table:

CREATE DATABASE AngularCRUDTest;

CREATE TABLE Employees
(
 StudentId [bigint] IDENTITY(1,1) NOT NULL,
 EmpName varchar(50),
 EmpAge int,
 EmpCity varchar(50),
 EmpCountry varchar(50),
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
 [StudentId] ASC
)
);

INSERT INTO Employees
(EmpName, EmpAge, EmpCity, EmpCountry)
VALUES
('Neel', 27, 'Pune', 'India');

INSERT INTO Employees
(EmpName, EmpAge, EmpCity, EmpCountry)
VALUES
('Neel2', 27, 'Pune', 'India');

INSERT INTO Employees
(EmpName, EmpAge, EmpCity, EmpCountry)
VALUES
('Neel3', 27, 'Pune', 'India');

Once the database is created successfully. it will look as below:

crud2

Add EntityFramework references

Once the database is created, let us add EntityFrameworkCore.SqlServer(Microsoft SQL Server database provider for Entity Framework Core), this will help us to go further with EntityFramework operations.

Search with “Microsoft.EntityFrameworkCore.SqlServer” in Nuget Package Manager and click on Install:

crud4

As we are going to use Database First development approach, we need to install the additional packages below as well:

  • Microsoft.EntityFrameworkCore.Tools(Includes Scaffold-DbContext, Add-Migration, and Update-Database)

crud5

  • Microsoft.EntityFrameworkCore.SqlServer.Design(Design-time Entity Framework Core Functionality for Microsoft SQL Server)

crud6

Entity Model creation

Once we have installed all required references, let us add required context and model classes from existing databases.

.Net Team has made this step easier and now we just need to run below line in Package Manager Console:

Scaffold-DbContext “Server=.\SQL2012;Database=AngularCRUDTest;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Here use the connection string as per your database and the –OutputDir attribute allows you to specify the location of the files generated. In this case, we’ve set it to Models(folder which we have added previously).

crud7

Once you run above code, it will create the Context class and Employee class into the Models folder as shown below:

crud8.png

AngularCRUDTestContext.cs class(https://github.com/NeelBhatt/Angular4_2_5CRUD/blob/master/NeelAngular4CRUD/Models/AngularCRUDTestContext.cs):


using Microsoft.EntityFrameworkCore;

namespace NeelAngular4CRUD.Models
{
public partial class AngularCRUDTestContext : DbContext
{
public virtual DbSet<Employees> Employees { get; set; }

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
#warning 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.
optionsBuilder.UseSqlServer(@"Server=.\SQL2012;Database=AngularCRUDTest;Trusted_Connection=True;");
}
} 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employees>(entity =>
{
entity.HasKey(e => e.StudentId);

entity.Property(e => e.EmpCity)
.HasMaxLength(50)
.IsUnicode(false);

entity.Property(e => e.EmpCountry)
.HasMaxLength(50)
.IsUnicode(false);

entity.Property(e => e.EmpName)
.HasMaxLength(50)
.IsUnicode(false);
});
}
}
}

Employees.cs class represents the Employees table(https://github.com/NeelBhatt/Angular4_2_5CRUD/blob/master/NeelAngular4CRUD/Models/Employees.cs):


namespace NeelAngular4CRUD.Models
{
public partial class Employees
{
public long StudentId { get; set; }
public string EmpName { get; set; }
public int? EmpAge { get; set; }
public string EmpCity { get; set; }
public string EmpCountry { get; set; }
}
}

Once this is done, we will add the connection string into appsettings.json file, in .Net core we have json file instead of web.config files(https://github.com/NeelBhatt/Angular4_2_5CRUD/blob/master/NeelAngular4CRUD/appsettings.json):


{
"ConnectionStrings": {
"i.": null,
"AngularCRUDTestDatabase": "Server=.\\SQL2012;Database=AngularCRUDTest;Trusted_Connection=True;"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
}
}

Next step is to add DB Context to the Startup.cs class.

Add below references into the Startup.cs class:

using NeelAngular5CRUD.Models;
using Microsoft.EntityFrameworkCore;

Add below lines into the ConfigureService method:

services.AddDbContext(options => options.UseSqlServer(Configuration.GetConnectionString("AngularCRUDTestDatabase")));

Statup.cs class looks like below(https://github.com/NeelBhatt/Angular4_2_5CRUD/blob/master/NeelAngular4CRUD/Startup.cs):


using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.SpaServices.Webpack;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using NeelAngular4CRUD.Models;
using Microsoft.EntityFrameworkCore;

namespace NeelAngular4CRUD
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}

public IConfiguration Configuration { get; }

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
// Add ASPNETCoreDemoDBContext services.
services.AddDbContext<AngularCRUDTestContext>(options => options.UseSqlServer(Configuration.GetConnectionString("AngularCRUDTestDatabase")));
}

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseWebpackDevMiddleware(new WebpackDevMiddlewareOptions
{
HotModuleReplacement = true
});
}
else
{
app.UseExceptionHandler("/Home/Error");
}

app.UseStaticFiles();

app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");

routes.MapSpaFallbackRoute(
name: "spa-fallback",
defaults: new { controller = "Home", action = "Index" });
});
}
}
}

As shown in above code, we are passing configuration to AddDbContext so we are required to add a constructor in AngularCRUDTestContext that accepts DbContextOptions.

For that go to AngularCRUDTestContext class and remove OnConfiguring method as we do not need it and add the constructor to allow the configuration to be passed into the context by dependency injection:

public AngularCRUDTestContext(DbContextOptions options)
 : base(options)
 { }

After making the changes, AngularCRUDTestContext  class looks as below(https://github.com/NeelBhatt/Angular4_2_5CRUD/blob/master/NeelAngular4CRUD/Models/AngularCRUDTestContext.cs):

crud9

If we do not add above constructor then after running we will get below exception:

crud17.png

That is it. We have completed all required code.

Add API controller with actions using Entity Framework

Right click on Controllers folder -> Add new Controller -> select API controller with actions using Entity Framework:

crud14

In next window, select Employees in Model drop-down and AngularCRUDTestContext in Data context drop-down:

crud15

Once you click on Add, an EmployeeController API class will be created with all the CRUD operations as shown below:

crud16

Let us test our API, run the application:

http://localhost:53008/api/Employees

crud18

As you can see all the employees which we added to the database are returned which shows our code is working fine.

In my next post, we will add different Angular component class to do the CRUD operation using the API we created in this post and will show the details on UI.

Note Part II(CRUD operation with Angular 4) of this post is here.

Hope it helps.

 

Advertisements

18 thoughts on “Database first in .Net Core 2.0 step by step: Angular 4 + Core 2.0 CRUD operation Part I

  1. Awesome article! I watched a pluralsight video but it only briefly touched on DB first. This totally cemented all that in. I noticed you called this part 1. Could I be so bold as to suggest for part 2 an “ongoing workflow” for Db first? So, showing the workflow of adding other tables and then scaffolding those in as well. Best practicies for continuing to expand the project, etc… Thanks again!

    Liked by 1 person

  2. every time I add a new table, I will need to forcefully use “Scaffold-DbContext “Server=.\SQL2012;Database=AngularCRUDTest;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models”. or is there any way to add it automatically in my C# code

    Like

  3. thanks for sharing ..I am getting following error .. Can you please help me on this
    Method not found: ‘Void Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations..ctor(Microsoft.EntityFrameworkCore.Design.Internal.IOperationReporter, System.Reflection.Assembly, System.Reflection.Assembly)’.
    at Microsoft.VisualStudio.Web.CodeGeneration.ActionInvoker.b__6_0()
    at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
    at Microsoft.VisualStudio.Web.CodeGeneration.ActionInvoker.Execute(String[] args)
    at Microsoft.VisualStudio.Web.CodeGeneration.CodeGenCommand.Execute(String[] args)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s