In Our previous article we have seen how to startup with .Net Core. In this article we will take a look on database operation according to previous sample application based previous concept.
If you are new to .Net Core, Please read previous post about .Net Core Startup
In this article we are going to explore,
- Create Database
- Use Entity Framework Core (Db First Approach),
- Overview EF Core
- Install Entity Framework
- Create Models
- Configure EF Service
- Use MVC 6
- Overview MVC6
- Use WebAPI
- Use AngularJS2
- Component,
- Route
- Service
- Configure Server
- Run App inside IIS
- Run App outside IIS
Let’s get started with step by step:
Create Database
Before we get started with IDE lets create a new database using SSMS2014. Name it as PhoneBook.
Create a table Named Contacts, copy & run the below script in SSMS2014
USE [PhoneBook] GO /****** Object: Table [dbo].[Contacts] Script Date: 8/7/2016 11:28:55 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Contacts]( [ContactID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [Phone] [nvarchar](50) NULL, [Email] [nvarchar](50) NULL, CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED ( [ContactID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Let’s get started, following our previous topic I am going to use previous sample application, open it with Visual Studio 2015.
It will automatically started restoring the dependencies. Build & run it. The application is working perfectly.
Install Entity Framework: Before install let’s have an overview on EF Core new Features:
- Modelling: This includes Basic modelling, Data annotations, Relationships and much more.
- Change Tracking: This includes Accessing tracked state, Snapshot, Notification change tracking.
- SaveChanges: This includes Basic save functionality,Async SaveChanges,Transactions.
- Query: This includes Basic LINQ support, Async query, Raw SQL queries
- Database schema management: This includes Database creation/deletion APIs, Relational database migrations, and Reverse engineer from database.
- Database providers: This includes EntityFramework.SqlServer, Sqlite, InMemory
- Platforms: Supports Universal Windows Platform (UWP), .NET Core, Full .NET
Get more details Core.
Let’s add folders for Entity models in our sample app solution.
DbEntities: for model entities.
The installation of EF is pretty much simple. Open project.json file, point tools section modify the section with below line.
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.0", "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final", "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.0"
Save changes after modification.
Packages will automatically restored. Let’s get explanation what are those.
EntityFrameworkCore.SqlServer: Database Provider, that allows Entity Framework Core to be used with Microsoft SQL Server.
EntityFrameworkCore.Tools: Command line tool for EF Core. Includes Commands For Package Manager Console:
- Scaffold-DbContext,
- Add-Migration,
- Udate-Database
For Command Window:
- dotnet ef dbcontext scaffold
We will see how to use both command.
EntityFrameworkCore.SqlServer.Design: Design-time, that allows Entity Framework Core functionality (EF Core Migration) to be used with Microsoft SQL Server.
To access the Command line tools we need to add EntityFrameworkCore.Tools in tools section of our project.json.
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
Save changes after modification.
Command in Package Manager Console: Open package manager console.
Input below command then hit enter,
Scaffold-DbContext “Server=DESKTOP-4T79RA1;Database=PhoneBook;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DbEntities
Command in Command Window: Open Command Window navigate to project directory, type
D:\Article\ASP-CORE\CRUD\CoreMVCAngular2\src\CoreMVCAngular>dotnet ef –help
Here a list of option will be shown in command window, we are going to use dbcontext in Commands.
Next Input below command then hit enter,
D:\Article\ASP-CORE\CRUD\CoreMVCAngular2\src\CoreMVCAngular>dotnet ef dbcontext scaffold “Server=DESKTOP-4T79RA1;Database=PhoneBook;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer –output-dir Models/CwEntities
Here is a screen shot of both process that execute & generate models. We will keep DbEntities folder to work with & will delete the other folder.
Configure EF Service:
In PhoneBookContext Class add constructor
public PhoneBookContext(DbContextOptionsoptions) : base(options) { }
In Startup class we need to enable EF services providing the connectionstring to
public void ConfigureServices(IServiceCollection services) { services.AddMvc(); var connection = @"Server=DESKTOP-4T79RA1;Database=PhoneBook;Trusted_Connection=True;"; services.AddDbContext(options => options.UseSqlServer(connection)); }
We have configure the EF services in our application, next we will work with MVC6 that is included in ASP.NET Core.
MVC 6: We have already discuss about MVC6 in our previous post, let’s have an overview on MVC6 new Features, once again:
- MVC+Web API+Web Pages = MVC6
- No System.Web
- Web pages & HTTP services is Unified
- Dependency injection built in
- Dynamic code compilation (Roslyn compiler)
- Open source &
- Support cross-platform build & run.
- Can be hosted in IIS or self-hosted(Outside IIS)
Ok, now let’s add a WebApi Controller to perform CRUD operation to database table.
In Solution Explorer add a new api folder, right click on it > Add New Item > Web API Controller Class > Add. Modify the initial template.
API Controller
[Route("api/[controller]")] public class ContactController : Controller { private PhoneBookContext _ctx = null; public ContactController(PhoneBookContext context) { _ctx = context; } }
You may notice that there is a new pattern [ ] in MVC6 attribute route, which is [RouteToken]. This mean that the route token is automatically take the controller name.
Like [Route("api/[controller]")] > [Route("api/Contact")]
Another thing is, we know Web API produces XML by default, now in MVC 6 we can set an attribute to change default produces to JSON type by putting attribute in Class label or on method label. In our case we have set it on method label.
[HttpGet("GetContact"), Produces("application/json")]
GET
// GET: api/Contact/GetContact [HttpGet("GetContact"), Produces("application/json")] public async Task
POST
// POST api/Contact/PostContact [HttpPost, Route("PostContact")] public async Task
PUT
// PUT api/Contact/PutContact/5 [HttpPut, Route("PutContact/{id}")] public async Task
DELETE
// DELETE api/Contact/DeleteContactByID/5 [HttpDelete, Route("DeleteContactByID/{id}")] public async Task
So our Web API is ready to dealing with data to database, it’s time to work with client side scripting.
AngularJS2:
Our WebAPI is ready to deal with data from server. Now we are going to work in client-side code with typescript (.ts) files.
First of all we need to create a master page to present our views in it.
Then we need to point this html file while app start, so let’s go to the startup.cs file to add below code snippet.
This is the configuration for the default files Middleware.
Startup.cs
// app-specific root page(Index.html) DefaultFilesOptions options = new DefaultFilesOptions(); options.DefaultFileNames.Clear(); options.DefaultFileNames.Add("/Index.html");
need to add library
using Microsoft.AspNetCore.Builder;
Now add script library reference to the html page & define view point to load our app component views.
Please wait ...
Then we need to reference our bootstrap file in our page, that import & enable the our angular script to the page.
Let’s put together all those in Index.html file.
Index.html
Please wait ...
Bootstrap, Model, Component & Route
Main.ts
/*This is the spa bootstrap File*/ //---------Import Angular2------------ import {bootstrap} from 'angular2/platform/browser'; import {enableProdMode, provide} from 'angular2/core'; //---------Import External Components(Main Component)--------- import {MainComponent} from './app.component'; //---------Bootstrap Component--------- enableProdMode(); bootstrap(MainComponent);
App.component.ts
/*Component Default view For SpaRoute */ //---------Import Angular2------------ import {Component, provide} from 'angular2/core'; import {RouteConfig, ROUTER_DIRECTIVES, ROUTER_PROVIDERS, LocationStrategy, HashLocationStrategy, APP_BASE_HREF} from 'angular2/router'; //---------Import External Components--------- import {Home} from './home/home.component'; import {Contact} from './contact/contact.component'; //---------Declare Components--------- ({ selector: 'spa-app', directives: [ROUTER_DIRECTIVES], //decorate link templateUrl: 'app/main.view.html', providers: [ ROUTER_PROVIDERS, //provide(APP_BASE_HREF, { useValue: '/' }) provide(LocationStrategy, { useClass: HashLocationStrategy }) ] }) //---------Declare Route Config--------- ([ { path: '/', name: 'Home', component: Home, useAsDefault: true }, { path: '/Contact/...', name: 'Contact', component: Contact } ]) //---------Export This Component Class--------- export class MainComponent { title: string; constructor() { this.title = 'Welcome to [.NetCore+MVC6+Angular2] SPA'; } }
Home.ts
import {Component} from 'angular2/core'; ({ selector: 'home', templateUrl: `app/home/home.view.html` }) export class Home { constructor() { } }
Contact.model.ts
export class ContactModel { contactId: number; firstName: string; lastName: string; phone: string; email: string; }
Contact.component.ts
//---------Import Angular2------------ import {Component} from 'angular2/core'; import {ROUTER_DIRECTIVES, RouteConfig} from 'angular2/router'; //---------Import External Components--------- import {ContactMain} from './contact.main'; //---------Declare Components--------- ({ selector: 'contacts', template: ``, directives: [ROUTER_DIRECTIVES] }) ([ { path: '/', name: 'ManageContact', component: ContactMain, useAsDefault: true }, ]) export class Contact { constructor() { } }
Contact.main.ts
//---------Import Angular2------------ import {Component, OnInit} from 'angular2/core'; import {HTTP_PROVIDERS, Http} from 'angular2/http'; import {ROUTER_DIRECTIVES, RouteConfig} from 'angular2/router'; import {FORM_DIRECTIVES, FormBuilder, Control, ControlGroup, Validators} from 'angular2/common'; //---------Import External Components--------- import {ContactModel} from './contact.model'; import {ContactService} from './contact.service'; import {customvalidators} from './customvalidators'; //---------Declare Components--------- ({ selector: 'contact-list', templateUrl: `app/contact/contact.view.html`, directives: [ROUTER_DIRECTIVES, FORM_DIRECTIVES], providers: [ContactService, HTTP_PROVIDERS] }) //---------Export This Component Class--------- export class ContactMain implements OnInit { public resmessage: string; public addmessage: string; public listmessage: string; public contact: ContactModel; public contacts: ContactModel[]; public editContactId: any //Form Control contactForm: ControlGroup; firstName: Control; email: Control; phone: Control; //Constructor constructor(private builder: FormBuilder, private contactService: ContactService) { this.addmessage = 'Add New Contact'; this.listmessage = 'All Contact'; this._formGroup(); } ngOnInit() { this.resmessage = ""; this.editContactId = 0; this.getContacts(); } //Form Group _formGroup() { this.firstName = new Control('', Validators.required); this.email = new Control('', Validators.compose([Validators.required, customvalidators.emailValidator])); this.phone = new Control(''); this.contactForm = this.builder.group({ firstName: this.firstName, email: this.email, phone: this.phone }); } //Get All getContacts() { //debugger this.contactService.getContacts().subscribe( contacts => this.contacts = contacts ); } //Save Form saveContact(contact) { //debugger this.contactService.saveContact(contact) .subscribe(response => { this.resmessage = response; this.getContacts(); this.reset(); }); } //Get by ID editContact(e, m) { //debugger e.preventDefault(); this.editContactId = m.contactId; this.contactService.getContactByID(m.contactId) .subscribe(response => { this.contact = response; this.firstName.updateValue(this.contact.firstName); this.email.updateValue(this.contact.email); this.phone.updateValue(this.contact.phone); }); } //Save Form updateContact(contact: any) { //debugger if (this.editContactId > 0) { this.contactService.updateContact(contact, this.editContactId) .subscribe(response => { this.resmessage = response; this.getContacts(); this.reset(); }); } } //Delete deleteContact(e, m) { //debugger e.preventDefault(); var IsConf = confirm('You are about to delete ' + m.firstName + '. Are you sure?'); if (IsConf) { this.contactService.deleteContact(m.contactId) .subscribe(response => { this.resmessage = response; this.getContacts(); }); } } reset() { this.editContactId = 0; this._formGroup(); } }
Let’s take a closer look at below code snippet, we have the service method call in hare, but the unknown term Subscribe – What is it for? Below we have a simple explanation.
this.contactService.getContacts().subscribe( contacts => this.contacts = contacts );
Subscribe: The subscriber function to be passed to the Observable constructor.
Services
In our service file we have Http service [Get, GetByID, Post, Put, Delete] that connect with WebAPI to perform operation Create, Read, Update & Delete.
GET ALL: Performs a request with `get` http method. For Collection of Object
/Get getContacts(): Observable{ //debugger return this._http.get(this._getUrl) .map(res => res.json()) .catch(this.handleError); }
GET By ID: Performs a request with `get` http method. For Single Object
//GetByID getContactByID(id: string): Observable{ //debugger var getByIdUrl = this._getByIdUrl + '/' + id; return this._http.get(getByIdUrl) .map(res => res.json()) .catch(this.handleError); }
POST: Performs a request with `post` http method.
//Post saveContact(contact: ContactModel): Observable{ //debugger let body = JSON.stringify(contact); let headers = new Headers({ 'Content-Type': 'application/json' }); let options = new RequestOptions({ headers: headers }); //http.post(url: string, body: string, options ?: RequestOptionsArgs): Observable return this._http.post(this._saveUrl, body, options) .map(res => res.json().message) .catch(this.handleError); }
PUT: Performs a request with `put` http method.
//Put updateContact(contact: ContactModel, id: string): Observable{ //debugger var updateUrl = this._updateUrl + '/' + id; var body = JSON.stringify(contact); var headers = new Headers(); headers.append('Content-Type', 'application/json'); //http.post(url: string, body: string, options ?: RequestOptionsArgs): Observable return this._http.put(updateUrl, body, { headers: headers }) .map(response => response.json().message) .catch(this.handleError); }
DELETE: Performs a request with `delete` http method.
//Delete deleteContact(id: string): Observable{ //debugger var deleteByIdUrl = this._deleteByIdUrl + '/' + id //http.post(url: string, options ?: RequestOptionsArgs): Observable return this._http.delete(deleteByIdUrl) .map(response => response.json().message) .catch(this.handleError); }
Observable : [Observable
Let’s put it together in Contact.service file.
Contact.service.ts
import {Injectable, Component} from 'angular2/core'; import {Http, Request, RequestMethod, Response, RequestOptions, Headers} from 'angular2/http'; import 'rxjs/Rx'; import {Observable} from 'rxjs/Observable'; import {ContactModel} from './contact.model'; ({ providers: [Http] }) () export class ContactService { public headers: Headers; constructor(private _http: Http) { } public _saveUrl: string = '/api/Contact/PostContact/'; public _updateUrl: string = '/api/Contact/PutContact/'; public _getUrl: string = '/api/Contact/GetContact/'; public _getByIdUrl: string = '/api/Contact/GetContactByID/'; public _deleteByIdUrl: string = '/api/Contact/DeleteContactByID/'; //Get getContacts(): Observable{ //debugger return this._http.get(this._getUrl) .map(res => res.json()) .catch(this.handleError); } //GetByID getContactByID(id: string): Observable { //debugger var getByIdUrl = this._getByIdUrl + '/' + id; return this._http.get(getByIdUrl) .map(res => res.json()) .catch(this.handleError); } //Post saveContact(contact: ContactModel): Observable { //debugger let body = JSON.stringify(contact); let headers = new Headers({ 'Content-Type': 'application/json' }); let options = new RequestOptions({ headers: headers }); //http.post(url: string, body: string, options ?: RequestOptionsArgs): Observable return this._http.post(this._saveUrl, body, options) .map(res => res.json().message) .catch(this.handleError); } //Put updateContact(contact: ContactModel, id: string): Observable { //debugger var updateUrl = this._updateUrl + '/' + id; var body = JSON.stringify(contact); var headers = new Headers(); headers.append('Content-Type', 'application/json'); //http.post(url: string, body: string, options ?: RequestOptionsArgs): Observable return this._http.put(updateUrl, body, { headers: headers }) .map(response => response.json().message) .catch(this.handleError); } //Delete deleteContact(id: string): Observable { //debugger var deleteByIdUrl = this._deleteByIdUrl + '/' + id //http.post(url: string, options ?: RequestOptionsArgs): Observable return this._http.delete(deleteByIdUrl) .map(response => response.json().message) .catch(this.handleError); } private handleError(error: Response) { return Observable.throw(error.json().error || 'Opps!! Server error'); } }
Let’s discus about form in angular2, there are two strategy of angular2 form
- Template-driven
- Model-driven
Template-driven
In template-driven form directive are added declaratively in the template.
Noticed that the validator is added declaratively with the input element “required”.
Model-driven
In our sample app we have used model-driven form that has ngFormModel & ngFormControl. Here ngFormControl is bind with input element to get the input values through the control.
ngFormModel: binding it to a controller variable “contactForm”
Here is the complete contact view page which we have used in our application.
Contact.view.html
This is all about from our angular section in our sample app, that we have used to perform client-end operation, now it’s time to build & run the application. Next we will get overview on server configuration.
Configure Server:
Outside IIS (Weblistener): We can host & run our application without an IIS environment, we need to add command object that tell the hosting to use the HTTP server weblistener (Windows-only).
"commands": { "OutsideIIS": "Microsoft.AspNet.Hosting --server Microsoft.AspNet.Server.Weblistener --server.urls http://localhost:5001" },
Make sure the dependencies is exist
"Microsoft.AspNetCore.Server.WebListener": "0.1.0"
Now go to solution explorer right click project > properties > Debug , change the profile to OutsideIIS.
Set launch URL, then save & run the application.
Server will start with hosting environment details
The application is running on url with details request info.
Inside IIS (Kestrel): Kestrel is a cross-platform web server which is run behind IIS or Nginx.
Change to IIS Express from navigation bar dropdown list to run the application using IIS. Ok let’s run our application to see the way how it’s work.
Output:
Here we can see the application output with welcome message & the data is listed from the server through our MVC6 –WebAPI. Here we can perform CRUD operation with required validation.
Source Code: I’ve uploaded the full source code to download/clone , Hope this will help 🙂
charan says:
very good explanation
Shashangka Shekhar says:
Thanks (y)