{{announcement.body}}
{{announcement.title}}

Database First Approach, Part 1: Angular 4 and .NET Core 2.0 CRUD Operations

DZone 's Guide to

Database First Approach, Part 1: Angular 4 and .NET Core 2.0 CRUD Operations

In this post, we'll take a look at how to perform a database first set up in and Angular and .NET Core 2.0-based project, and set up an Employee API.

· Web Dev Zone ·
Free Resource

In another post, I 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) structure, using Angular 5 with the .NET Core 2 API.

In this post, we will see how to perform a database first set up in Angular (.NET Core 2.0 project) and will set up the Employee API.

Prerequisites:

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

Angular App 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:

Click on Ok and in the next window select Angular, as shown below:Visual Studio will create a well-structured application for you (note that I have manually added a Models folder, as we will require this in future):

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 display the below landing page:

Create an Employee Database

Let us create an employee database in SQL. You can use the 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 the screenshot below:

Add EntityFramework References

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

Search for "Microsoft.EntityFrameworkCore.SqlServer" in Nuget Package Manager and click on Install:

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

Entity Model Creation

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

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

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

Here, we're using the connection string as per our database requirements and the  -OutputDir attribute allows you to specify the location of the files generated. In this case, we've set it to Models (a folder which we have previously added).

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

AngularCRUDTestContext.cs class (GitHub repo):

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 (GitHub repo):

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 a JSON file instead of the web.config files (GitHub repo):

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

The next step is to add a DB Context to the Startup.cs class.

Add the below references into the Startup.cs class:

using NeelAngular5CRUD.Models;
using Microsoft.EntityFrameworkCore;

Add the below lines into the  ConfigureService method:

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

Statup.cs class looks like below (GitHub repo):

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 the above code, we are passing the configuration to  AddDbContext so we are required to add a constructor in  AngularCRUDTestContext that accepts DbContextOptions.

For that, go to the AngularCRUDTestContext class and remove the  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, the  AngularCRUDTestContext class looks as below (GitHub repo):

If we do not add the above constructor, we will get the below exception after running our code:

That is it. We have completed all the required code.

Add an API Controller With Actions Using Entity Framework

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

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

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

Let us test our API, run the application:

http://localhost:53008/api/Employees

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 classes to do the CRUD operation using the API we created in this post and will show the details on the UI.

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

Hope this helps.

Topics:
web dev ,tutorial ,angular 4 ,.net core ,crud operations

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}