Database First In .Net Core 2.0 CRUD Operation - Part One

In my previous post, I explained how to create an Angular5 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 Angular5 with .NET Core 2 API.

In this 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.



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 Models folder as we will require this in the 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 have the landing page as below.



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.


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,



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)
  
  
  

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

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



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



AngularCRUDTestContext.cs class here,

using Microsoft.EntityFrameworkCore;
namespace NeelAngular4CRUD.Models {
        public partial class AngularCRUDTestContext: DbContext {
                public virtual DbSet 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(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 here,

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

{
    "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 here,

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(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 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)  { }

If we do not add the above constructor, then after running, we will get the below exception.



That is it. We have completed all the 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.



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



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

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