Understanding basic CRUD operations is crucial for developers (especially a newbie) working in any platform, since it covers some very important features, such connecting a database to the application, performing basic data manipulation operations, consuming data using API’s and more.
So, let’s get on with it.
Before you start, make sure you have Angular 6 installed on your computer. If have a previous version, upgrade to Angular 6 or higher.
If you are new to Angular 6 and have not upgraded yet, then follow these steps.
Create a Table in SQL Server
CRUD applications require data. So let’s create a table in SQL Server.
CREATE TABLE [dbo].[Books](
[BookID] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[Price] [numeric](18, 2) NULL,
[Price_Range] [varchar](20) NULL,
PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]
GO
It’s a books table, which has four columns. Keep this table blank. We’ll fill the table with data later when we have create our application.
Web API (using MVC 4 in Asp.Net)
Note: If you have not created a Web API yet, then I would recommend reading this post.
Now, let’s create our Web API methods in Asp.Net. Remember, you should have MVC 4 or higher installed on your computer.
Model Books.cs (C#)
using System;
namespace BooksApp.Models
{
public class Books
{
public int BookID { get; set; }
public string BookName { get; set; }
public string Category { get; set; }
public decimal Price { get; set; }
public string Operation { get; set; }
}
}
Model Books.vb (Visual Basic)
Imports System.Web
Namespace BooksApp.Models
Public Class Books
Public Property BookID() As Integer
Get
Return m_BookID
End Get
Set(value As Integer)
m_BookID = value
End Set
End Property
Private m_BookID As Integer
Public Property BookName() As String
Get
Return m_BookName
End Get
Set(value As String)
m_BookName = value
End Set
End Property
Private m_BookName As String
Public Property Category() As String
Get
Return m_Category
End Get
Set(value As String)
m_Category = value
End Set
End Property
Private m_Category As String
Public Property Price() As Decimal
Get
Return m_Price
End Get
Set(value As Decimal)
m_Price = value
End Set
End Property
Private m_Price As Decimal
Public Property Operation() As String
Get
Return m_Ops
End Get
Set(value As String)
m_Ops = value
End Set
End Property
Private m_Ops As String
End Class
End Namespace
The Web API Controller
The controller has an HTTP <post> method, which we’ll use to perform the CRUD operation. It will receive request from our application, connect with an SQL Server database table (that we have created above) and finally send the process data to the calling app.
BooksController.cs (C#)
using System;
using System.Collections.Generic;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using BooksApp.Models;
using System.Data.SqlClient;
namespace BooksApp.Controllers
{
public class BooksController : ApiController
{
const string sConnString = "Data Source=DNA;Persist Security Info=False;" +
"Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";
// LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
List<Books> MyBooks = new List<Books>();
[HttpPost()]
public IEnumerable<Books> Perform_CRUD(Books list)
{
bool bDone = false;
using (SqlConnection con = new SqlConnection(sConnString))
{
using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
{
cmd.Connection = con;
con.Open();
switch (list.Operation)
{
case "READ":
bDone = true;
break;
case "SAVE":
if (list.BookName != "" & list.Category != "" & list.Price > 0)
{
cmd.CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " +
"VALUES (@BookName, @Category, @Price)";
cmd.Parameters.AddWithValue("@BookName", list.BookName.Trim());
cmd.Parameters.AddWithValue("@Category", list.Category.Trim());
cmd.Parameters.AddWithValue("@Price", list.Price);
bDone = true;
}
break;
case "UPDATE":
if (list.BookName != "" & list.Category != "" & list.Price > 0)
{
cmd.CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " + "Price = @Price WHERE BookID = @BookID";
cmd.Parameters.AddWithValue("@BookName", list.BookName.Trim());
cmd.Parameters.AddWithValue("@Category", list.Category.Trim());
cmd.Parameters.AddWithValue("@Price", list.Price);
cmd.Parameters.AddWithValue("@BookID", list.BookID);
bDone = true;
}
break;
case "DELETE":
cmd.CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID";
cmd.Parameters.AddWithValue("@BookID", list.BookID);
bDone = true;
break;
}
if (bDone)
{
cmd.ExecuteNonQuery();
}
con.Close();
}
}
if (bDone)
{
GetData();
}
return MyBooks; // RETURN THE LIST TO THE CLIENT APP.
}
private void GetData()
{
using (SqlConnection con = new SqlConnection(sConnString))
{
SqlCommand objComm = new SqlCommand("SELECT *FROM dbo.Books", con);
con.Open();
SqlDataReader reader = objComm.ExecuteReader();
// POPULATE THE LIST WITH DATA.
while (reader.Read())
{
MyBooks.Add(new Books
{
BookID = Convert.ToInt32(reader["BookID"]),
BookName = reader["BookName"].ToString(),
Category = reader["Category"].ToString(),
Price = Convert.ToDecimal(reader["Price"])
});
}
con.Close();
}
}
}
}
BooksController.vb (Visual Basic)
Option Explicit On
Imports System.Net.Http
Imports System.Web.Http
Imports System.Data.SqlClient
Imports BooksApp.BooksApp.Models
Namespace BooksApp
Public Class BooksController
Inherits ApiController
Const sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
"Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"
' LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
Dim MyBooks As New List(Of Books)()
<HttpPost()> _
Public Function Perform_CRUD(list As Books) As IEnumerable(Of Books)
Dim bDone As Boolean = False
Using con As SqlConnection = New SqlConnection(sConnString)
Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")
With cmd
.Connection = con
con.Open()
Select Case list.Operation
Case "READ"
bDone = True
Case "SAVE"
If Trim(list.BookName) <> "" And Trim(list.Category) <> "" And
Val(list.Price) > 0 Then
.CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " & _
"VALUES (@BookName, @Category, @Price)"
.Parameters.AddWithValue("@BookName", Trim(list.BookName))
.Parameters.AddWithValue("@Category", Trim(list.Category))
.Parameters.AddWithValue("@Price", list.Price)
bDone = True
End If
Case "UPDATE"
If Trim(list.BookName) <> "" And Trim(list.Category) <> "" And
Val(list.Price) > 0 Then
.CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " & _
"Price = @Price WHERE BookID = @BookID"
.Parameters.AddWithValue("@BookName", Trim(list.BookName))
.Parameters.AddWithValue("@Category", Trim(list.Category))
.Parameters.AddWithValue("@Price", Val(list.Price))
.Parameters.AddWithValue("@BookID", Val(list.BookID))
bDone = True
End If
Case "DELETE"
.CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID"
.Parameters.AddWithValue("@BookID", Val(list.BookID))
bDone = True
End Select
If bDone Then
.ExecuteNonQuery()
End If
con.Close()
End With
End Using
End Using
If bDone Then
GetData()
Return MyBooks
End If
End Function
Private Sub GetData()
Using con As SqlConnection = New SqlConnection(sConnString)
Dim objComm As New SqlCommand("SELECT *FROM dbo.Books", con)
con.Open()
Dim reader As SqlDataReader = objComm.ExecuteReader()
' POPULATE THE LIST WITH DATA.
While reader.Read()
MyBooks.Add(New Books() With { _
.BookID = CInt(reader("BookID")), _
.BookName = reader("BookName").ToString(), _
.Category = reader("Category").ToString(), _
.Price = CDbl(reader("Price")) _
})
End While
con.Close()
End Using
End Sub
End Class
End Namespace
When you are done with the API, run the project and keep it running. We need the URL along with the port in our Angular application.
Create Angular 6 Application
Create the application using Angular Command Line interface or the CLI. Open the command prompt and go to the folder where you want to create the project.
ng new crudApp
Go to the folder …
cd crudApp
and launch the server …
ng serve --o
Now let’s create our Angular components.
Import HttpClientModule in the app.module.ts file
I am using HttpClient service in my application to consume Web API services. Therefore, I will import HttpClientModule in the app.module.ts file.
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppComponent } from './app.component';
import { HttpClientModule } from '@angular/common/http';
@NgModule({
declarations: [
AppComponent
],
imports: [
BrowserModule,
HttpClientModule
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }
Create a Books Service
This is important. To make the API calls, I have created a service named booksService in my Angular project. So, add a folder named services inside src/app/ folder and create books.service.ts file.
Open the books.service.ts file and write the below code in it.
import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
@Injectable()
export class booksService {
constructor (private httpService: HttpClient) { }
public post_request(frmData) {
let myBooksList = this.httpService.post('http://localhost:43487/api/books/', frmData)
.pipe();
return myBooksList;
}
}
The service has a method named post_request(), which takes a parameter named frmData. I have also injected HttpClient to the constructor class. I am using the post() method of the HttpClient object, here in my service to make the request along with some data (inside frmData).
Go to the browser now and see if everything is working fine.
Note: Check your browser console for any errors. A common error that occurs while working with Web APIs running on a different port is Error: No ‘Access-Control-Allow-Origin’ header is present on the requested resource. Please check this post to resolve the above-mentioned error. You’ll have to configure the Global.asax file in your Asp.Net Web API and run API application again.
After a successful call, the post() method will populate a variable named myBooksListwith the data it will receive. It will then return the data to the component that called this service.
Create the Application Component
At this stage, I need to first check the service and see if it’s connecting with the Web API methods, I mean see if its making the requests properly and getting an appropriate response. To do this I need to call the service from my component.
Open the app.component.ts file and write the below code in it.
import { Component } from '@angular/core';
import { booksService } from './services/books.service';
import { HttpParams } from '@angular/common/http';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css'],
providers: [booksService]
})
export class AppComponent {
title = 'CRUD Application in Angular';
constructor (private service: booksService) { }
arrBooks = <any>[];
// Creat an instance of HttpParams() class, to add URL parameters (or Query String).
public myFrmData = new HttpParams();
ngOnInit () {
this.read();
}
// ***** Read data from the database.
read() {
// Assign parameters to the HttpParams() class.
this.myFrmData = this.myFrmData
.set('BookID', '')
.set('BookName', '')
.set('Category', '')
.set('Price', '')
.set('Operation', 'READ');
// Call our service to POST our request for the data.
this.service.post_request(this.myFrmData).subscribe(
data => {
this.arrBooks = data as any[];
console.log(this.arrBooks);
}
);
}
}
See I have highlighted the console.log() method. If the call is a success, your browser console should show the data in the array this.arrBooks and it should be something like this…
CRUD stands for create, read, update and delete and we have just implement the read()method. Now let’s write the remaining methods and design our template.
Here’s the complete code with all the CRUD methods inside the component class.
import { Component } from '@angular/core';
import { booksService } from './services/books.service'
import { HttpParams } from '@angular/common/http';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css'],
providers: [booksService]
})
export class AppComponent {
title = 'CRUD Application in Angular';
constructor (private service: booksService) { }
arrBooks = <any>[];
arrColumns = []; // For table column names.
arrCategories = <any>[]; // Data for the SELECT dropdown list.
// Creat an instance of HttpParams() class, to add URL parameters (or Query String).
public myFrmData = new HttpParams();
ngOnInit () {
this.read();
this.arrCategories = ['Business', 'Computers', 'Programming', 'Science'];
}
// CRUD FUNCTONS (STARTS HERE).
// ***** Read data from the database.
read() {
// Assign parameters to the HttpParams() class.
this.myFrmData = this.myFrmData
.set('BookID', '')
.set('BookName', '')
.set('Category', '')
.set('Price', '')
.set('Operation', 'READ');
// Call our service to POST our request for the data.
this.service.post_request(this.myFrmData).subscribe(
data => {
this.arrBooks = data as any[];
if (this.arrBooks.length > 0) {
// Extract columns (or table headers).
for (let i = 0; i < this.arrBooks.length; i++) {
for (let key in this.arrBooks[i]) {
if (this.arrColumns.indexOf(key) === -1) {
this.arrColumns.push(key); // Fill the array with the column names.
}
}
}
}
else {
// In case the table is empty, get the columns from HttpParams().
this.arrColumns = this.myFrmData.keys();
}
}
);
}
// ***** Update or edit data.
update(e) {
let activeRow = e.target.parentNode.parentNode.rowIndex; // Get the active table row.
let tab = e.target.parentNode.parentNode.parentNode.parentNode.rows[activeRow]; // THIS IS OUR TABLE.
for (let i = 1; i < 4; i++) {
if (i == 2) {
const td = tab.getElementsByTagName("td")[i];
const ele = document.createElement('select'); // ADD A SELECT DROPDOWN LIST TO THE ROW.
ele.innerHTML = '<option value="' + td.innerText + '">' + td.innerText + '</option>';
for (let k = 0; k < this.arrCategories.length; k++) {
ele.innerHTML = ele.innerHTML +
'<option value="' + this.arrCategories[k] + '">' + this.arrCategories[k] + '</option>';
}
td.innerText = '';
ele.setAttribute('style', 'font: 17px Calibri; text-align: center;border: 1px solid #ccc;border-radius: 4px;' );
td.appendChild(ele);
}
else {
const td = tab.getElementsByTagName("td")[i];
const ele = document.createElement('input'); // ADD A TEXTBOX TO THE ROW.
ele.setAttribute('type', 'text');
ele.setAttribute('value', td.innerText);
ele.setAttribute('style', 'font: 17px Calibri; text-align: center;border: 1px solid #ccc;border-radius: 4px;' );
td.innerText = '';
td.appendChild(ele);
}
let iBook_id = tab.getElementsByTagName("td")[0].innerHTML;
// Show a Cancel or "✖" button.
const lblCancel = document.getElementById('lbl' + (iBook_id));
lblCancel.setAttribute('style', 'cursor:pointer; display:block; width:20px; float:left; position:absolute;');
// Show the Save button.
const btSave = document.getElementById('save' + (iBook_id));
btSave.setAttribute('style', 'display:block; margin-left:30px; float:left;');
// Hide the Update button.
e.target.setAttribute('style', 'display:none;');
}
}
// ***** Cancel the action.
cancelAction(e) {
e.target.setAttribute('style', 'display:none; float:none;'); // Hide the Cancel (✖) button.
let activeRow = e.target.parentNode.parentNode.rowIndex;
const tab = e.target.parentNode.parentNode.parentNode.parentNode.rows[activeRow];
let iBook_id = tab.getElementsByTagName("td")[0].innerHTML;
// Hide the Save button.
let btSave = document.getElementById('save' + (iBook_id));
btSave.setAttribute('style', 'display:none;');
// Show the Update button again.
let btUpdate = document.getElementById('update' + (iBook_id));
btUpdate.setAttribute('style', 'display:block; margin:0 auto; background-color: #5DB75F; color: #FFF;');
for (let i = 0; i < this.arrColumns.length - 1; i++) {
const td = tab.getElementsByTagName("td")[i];
td.innerHTML = this.arrBooks[(activeRow -1)][this.arrColumns[i]]; // Write the values in the active row cells.
}
}
// Save the Data (in Update mode).
save(e) {
this.myFrmData = this.myFrmData.delete(''); // Delete all previous "HttpParams" parameters.
const activeRow = e.target.parentNode.parentNode.rowIndex;
const tab = e.target.parentNode.parentNode.parentNode.parentNode.rows[activeRow];
for (let i = 1; i < this.arrColumns.length; i++) {
const td = tab.getElementsByTagName("td")[i];
// Check if its a textbox or a SELECT element.
if (td.childNodes[0].getAttribute('type') == 'text' || td.childNodes[0].tagName == 'SELECT') {
this.myFrmData = this.myFrmData.set(this.arrColumns[i], td.childNodes[0].value)
}
else {
// Get the ID of the Book.
this.myFrmData = this.myFrmData.set('BookID', tab.getElementsByTagName("td")[0].innerHTML)
}
}
this.myFrmData = this.myFrmData.set('Operation', 'UPDATE'); // Set Operation type.
this.perform_CRUD();
}
// Create new data.
createNew(e) {
const activeRow = e.target.parentNode.parentNode.rowIndex;
const tab = e.target.parentNode.parentNode.parentNode.parentNode.rows[activeRow];
let bOk : boolean = false;
for (let i = 1; i < this.arrColumns.length; i++) {
const td = tab.getElementsByTagName("td")[i];
// Check if its a textbox or a SELECT element.
if (td.childNodes[0].getAttribute('type') == 'text' || td.childNodes[0].tagName == 'SELECT') {
let val = td.childNodes[0].value;
if (val != '') {
this.myFrmData = this.myFrmData.set(this.arrColumns[i], td.childNodes[0].value)
bOk = true;
// Clear all input and dropdown values.
td.childNodes[0].tagName == 'SELECT' ? td.childNodes[0].value = '-- select --' : td.childNodes[0].value = '';
}
else {
alert('All fields are compulsory!');
bOk = false;
break;
}
}
}
if (bOk) {
this.myFrmData = this.myFrmData.set('Operation', 'SAVE'); // Set Operation type.
this.perform_CRUD();
}
}
// Delete the selected row data.
delete(e) {
const activeRow = e.target.parentNode.parentNode.rowIndex;
const tab = e.target.parentNode.parentNode.parentNode.parentNode.rows[activeRow];
// ALL WE NEED IT THE ID.
this.myFrmData = this.myFrmData.set('BookID', tab.getElementsByTagName("td")[0].innerHTML)
this.myFrmData = this.myFrmData.set('Operation', 'DELETE'); // Set Operation type.
// Confirm before deleting.
if(confirm("Are you sure? ")) {
this.perform_CRUD();
}
}
// CRUD FUNCTONS (ENDS HERE).
// Finally, post your request.
perform_CRUD(){
// Call "books" service and save all the data.
this.service.post_request(this.myFrmData).subscribe(
data => {
this.arrBooks = data as any[]; // Fill the array with new values.
}
);
}
}
At first sight, it might look too much code. However, remember CRUD operations usually have many functions. It’s a complete project in itself. I have tried to keep it simple. Once you get this app running, you will get many more ideas.
HttpParams()
One of the most important features in the above code, is the HttpParams() class.
import { HttpParams } from '@angular/common/http';
public myFrmData = new HttpParams();
I am using the HttpParams() class to add query strings (or parameters) to the URL, which is then passed to the books service (books.service.ts), where it is used by the HttpClient.post() method to send a request to the Web API.
public myFrmData = new HttpParams();
I have initialized the HttpParams object inside the read() method, where I am passing fiveparameters to the object using the .set() method.
this.myFrmData = this.myFrmData
.set('BookID', '')
.set('BookName', '')
.set('Category', '')
.set('Price', '')
.set('Operation', 'READ');
Every method in my component uses the HttpParams() object to store and pass data to my books service. Once the data is extracted, it will call a function called perform_CRUD().
perform_CRUD(){
this.service.post_request(this.myFrmData).subscribe(
data => {
this.arrBooks = data as any[];
});
}
Create Application Template
I am using an HTML <table> element to do the CRUD operations. It is very convenient to use. Here's another example where I have used an HTML <table> in Angular to show data extracted from an extrenal JSON file.
<div style="text-align: left; margin-bottom: 20px;">
<h1>
{{ title }}
</h1>
</div>
<div>
<table *ngIf="arrBooks" id="booksTable">
<!-- Add the headers! -->
<tr>
<th>ID</th>
<th>Book Name</th>
<th>Category</th>
<th>Price ($)</th>
</tr>
<!-- Bind the array "arrBooks" to the table. -->
<tr *ngFor="let book of arrBooks">
<td>{{book.BookID}}</td>
<td>{{book.BookName}}</td>
<td>{{book.Category}}</td>
<td>{{book.Price}}</td>
<!-- Operations -->
<td>
<label id="lbl{{book.BookID}}" (click)="cancelAction($event)" style="display: none;">✖</label>
<button id="update{{book.BookID}}" (click)="update($event)" style="background-color: #5DB75F; color: #FFF;">Update</button>
<button id="save{{book.BookID}}" (click)="save($event)" style="display: none;">Save</button>
</td>
<td>
<button id="delete" (click)="delete($event)" style="background-color: #D45449; color: #FFF;">Delete</button>
</td>
</tr>
<!-- Last row will have the "Create" option. -->
<tr>
<td></td>
<td><input type="text" id="txtBookName" value=""></td>
<td *ngIf="arrCategories">
<!-- Add a SELECT dropdown list. -->
<select id="sel">
<option>-- select --</option>
<option *ngFor="let cat of arrCategories" value="{{cat}}">
{{cat}}
</option>
</select>
</td>
<td><input type="text" id="price" value=""></td>
<td>
<button id="btCreate" (click)="createNew($event)">Create</button>
</td>
</tr>
</table>
</div>
Save the file and see the browser for the output.
Add Style to the <table> and other Elements in the Template
Web apps are incomplete without the styles. Add some basic style to the application to give it a nice look and feel and more importantly, its how you make the app user friendly.
So, open the app.compontents.css file and copy the below code in it. You can add more style to it, if you want.
div {
width: 700px;
margin: 10px;
}
h1 {
font-family: abeatbyKai;
font-size: 30px;
font-weight: normal;
}
table
{
width: 100%;
font: 17px Calibri;
background-color: #5A5F6F;
color: #FFFFFF;
border-radius: 3px;
}
table, th, td
{
border-bottom: solid 1px #DDD;
border-collapse: collapse;
padding: 8px 5px;
text-align: center;
}
input[type='button'], button
{
font: 17px Calibri;
cursor: pointer;
border: none;
border-radius: 4px;
color: #FFF;
background-color: #44CCEB;
width: 100px;
height: 30px;
}
input[type='text'], select
{
font: 17px Calibri;
text-align: center;
border: 1px solid #CCCCCC;
border-radius: 4px;
width: auto;
padding: 2px 3px;
}
Conclusion
Run the app and perform some basic CRUD operations. There are few things that I want to know is, you can first create the template, like adding the <table> etc., before creating the component. In-addition, you can use dynamic values for your SELECT dropdown list.
this.arrCategories = ['Business', 'Computers', 'Programming', 'Science'];
Well that’s it. You can contact me if you have any queries. Or, you can simply leave a message on my facebook page (see the footer of this blog).
No comments:
Post a Comment