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:
Click on Ok and in 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 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 above code, it will create the Context class and Employee class into the Models folder as shown below:
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):
If we do not add above constructor then after running we will get below exception:
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:
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 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 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.
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!
LikeLiked by 1 person
Thank you so much for your kind words. Yes it was part I but I gave this as part I because the main target of these part I and Part II was to cover CRUD operations in Angular. Part II is already here: https://neelbhatt.com/2018/01/12/crud-operation-in-angular-4-angular-4-core-2-0-crud-operation-part-ii/. But I will surely try to write the post you suggested.
LikeLike
excellent article, very well detailed, I want to do this tutorial on Linux Ubuntu 17.10.
What do you think
LikeLike
Thanks Charle. I have not used .Net Core on Linux yet. But it is surely possible. See this : https://www.junian.net/2017/01/running-your-first-aspnet-core-web-app-on-ubuntu-16-04.html or with other examples on internet
LikeLike
can i get database first approach with existing database with mysql..
LikeLike
Hey, this post may help you : http://www.c-sharpcorner.com/article/how-to-connect-mysql-with-asp-net-core/
LikeLike
Or this as well if you wish to use Linux: https://www.junian.net/2017/01/running-your-first-aspnet-core-web-app-on-ubuntu-16-04.html
LikeLike
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
LikeLike
Hey, I dont think you are required to run that command every time. update-database along with other changes are enough. see this – https://stackoverflow.com/questions/23662755/how-to-add-new-table-to-existing-database-code-first
LikeLike
okay. thanks
LikeLike
Dear Neel,
Do you have something that belongs to DB first using onion Architecture .
Thanks,
Shakti
LikeLike
Hello, I have not written anything on that yet. This should help – http://l-knowtech.com/2017/10/15/design-asp-net-core-mvc-application-using-onion-architecture/, https://www.c-sharpcorner.com/article/onion-architecture-in-asp-net-core-mvc/, https://code.msdn.microsoft.com/Onion-Architecture-In-9c58c06d
LikeLike
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)
LikeLike
Hello Mayura, at which step you are getting this error? All your libraries have same version? I hope there are not any missing assemblies. Look here: https://stackoverflow.com/questions/50645265/method-not-found-void-entityframeworkcore-design-internal-dbcontextoperations
LikeLike
Hi Neel, this is very useful article, thank you so much. I am curious to know if there is a way to manually create models in EF Core in DB first?
LikeLike