Create a new Angular service inside the src folder and name it app.service.ts. This service will do all operations and will serve the data to the UI component directly from the database. Here, webpages can directly access the database using this service. Add the following content into the service:
import { Injectable } from '@angular/core';
import * as mysql from 'mysql';
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password', // Your database password
database: 'customer_manager'
});
@Injectable()
export class AppService {
}
Use your database username and password to create the MySQL connection object. Update the preceding Angular service template with real database access scripts inside. Add the connection object into the service inside the constructor as follows:
import { Injectable } from '@angular/core';
import * as mysql from 'mysql';
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password', // Your database password
database: 'customer_manager'
});
@Injectable()
export class CustomerService {
connection: any;
constructor() {
this.connection = connection
}
}
This creates a connection to the database using MySQL module. Now let's add the functionality to this service. Add a new method called getCustomers to get a list of all customers:
getCustomers(): Promise<any> {
const QUERY = `SELECT * FROM customers`;
return new Promise((resolve, reject) => {
connection.query(QUERY, (error:any, data:any) => {
if (error) {
reject(error);
return;
}
resolve(data);
});
});
}
This is just a plain select query and there is nothing complicated in this code. This queries the data from the database and returns into the promise as a resolver callback. Next, add customer filter queries as follows to the AppService.ts file inside the class:
getCustomerById(id: any): Promise<any> {
const QUERY = `SELECT * FROM customers WHERE id=?`;
return new Promise((resolve, reject) => {
connection.query(QUERY, [id], (error: any, data: any) => {
if (error) {
reject(error);
return;
}
resolve(rows);
});
});
}
public getCustomerByName(name: string): Promise<any> {
const QUERY = `SELECT * FROM customers WHERE name LIKE '${name}%'`;
return new Promise((resolve, reject) => {
connection.query(QUERY, [name], (error: any, data: any) => {
if (error) {
reject(error);
return;
}
resolve(data);
});
});
}
This query gives customers by name and customer id. If you have previous experience in database programming, then you know this is a very basic thing. Add the createCustomer method into the service so that user can add the customer into the database:
createCustomer(customer: any): Promise<any> {
const QUERY = "INSERT INTO customers SET ?";
return new Promise((resolve, reject) => {
connection.query(QUERY, customer, (error: any, data: any) => {
if (error) {
reject(error);
return;
}
resolve(data.insertId);
});
});
}
Finally, add the code for the update and delete the customer method to the AppService class:
deleteCustomer(id: number): Promise<any> {
const QUERY = "DELETE FROM customers WHERE id = ?";
return new Promise((resolve, reject) => {
connection.query(QUERY, [id], (error: any, data: any) => {
if (error) {
reject(error);
return;
}
resolve(data.affectedRows);
});
});
}
updateCustomer(customer: any): Promise<any> {
const QUERY = "UPDATE customers SET name = ? WHERE id = ?";
return new Promise((resolve, reject) => {
connection.query(QUERY, [customer.name, customer.customer_id], (error: any, res: any) => {
if (error) {
reject(error);
return;
}
resolve(res);
});
});
}
The service provides simple CRUD operation functionality with customer data. Each method returns a JavaScript promise, with MySQL connection query results resolved in its body. That's all for accessing the database from a Node.js/Electron application. Now, let's create a simple user interface to display this data.
Let's add a popular user interface library, and bootstrap into the project to build our user interface. You can use npm or the direct download method to get the library. Download the library and copy the content into the src folder. Last, add the style sheet reference into the index.html file. There are couple of Angular 2 implementations of Twitter Bootstrap available. You can also use those for your real-world applications. In our case, just grab the CSS file in the source using a link tag.
Update the app.component.html template file with the following content. Basically, it's a two-column layout with a customer list on the left side and an editor view in the right-side panel:
<div class="app-container">
<header class="app-header">
<section class="quicklaunch-bar">
<div class="app-logo">
<span class="app-logo__title">Customer Manager</span>
</div>
</section>
</header>
<div class="app-body">
<div class="app-list">
<ul>
<li [ngClass]="{ 'active': customer.id === _customer.id }" *ngFor="let _customer of customers;let idx = index">
<a (click)="setCustomer(_customer, idx)">
{{_customer.customer_name}}
</a>
</li>
</ul>
</div>
<div>
<div class="app-form">
<div class="form-group">
<label>Customer Name</label>
<input type="text" style="width: 300px" class="input"[(ngModel)]="customer.customer_name" />
</div>
<div class="form-group">
<label>Email</label>
<input type="text" class="input" style="width: 300px" [(ngModel)]="customer.customer_email" />
</div>
<div class="form-group">
<label>Address</label>
<input type="text" class="input" style="width: 300px" [(ngModel)]="customer.customer_address" />
</div>
<div class="form-group">
<label>Phone</label>
<input type="text" class="input" style="width: 300px" [(ngModel)]="customer.customer_mobile" />
</div>
<div class="cta-wrapper">
<button class="btn btn-default"
(click)="addNewCustomer()">
Add
</button>
<button class="btn btn-primary" (click)="saveCustomer()">
Save
</button>
<button class="btn btn-primary" (click)="cancel()">
Cancel
</button>
<button class="btn btn-primary" (click)="deleteCustomer()">
Delete
</button>
</div>
</div>
</div>
</div>
</div>
Open the Angular component definition file, and then inject the customer service into the component. We are already familiar with dependency injection in Angular 2. Update the app.component.ts file as the following:
import { Component } from '@angular/core';
import { AppService } from './app.service';
import './style.css'
@Component({
selector: 'electron-app',
templateUrl: './app.component.html'
})
class AppComponent implements OnInit {
constructor(private service: AppService) {
}
}
We have just injected the service into the component. Now retrieve the data into the component from the database using the customer service. Update the component with the following code:
class AppComponent {
public customers: Array<any> = [];
constructor(private _customerService: CustomerService) {
}
ngOnInit() {
// Load data from mysql database
this.service.getCustomers().then(data => this.customers = data; });
}
}
That's all for listing customer data using MySQL and Electron. We don't have any server implementation, you can directly connect to the database from the Node.js and Electron application. Let's add more functionalities to the application. When users select customer data, it should be copied to the form displayed on the right side of the application. Add click listener to each row of customers, and when selecting the row data, set the value of the current customer to the selected data so that the form will be affected by the corresponding values:
class AppComponent {
public customers: Array<any> = [];
public customer: any = {};
public currentSelectedIndex: number = -1;
constructor(private service: AppService) {
}
ngOnInit() {
//...
}
setCustomer(customer: any, idx: number) {
this.customer = customer;
this.currentSelectedIndex = idx;
}
}
Now, whenever you select a customer, the form will be updated with the selected customer information. Add the update functionality to the component on a click of the save button. The following code checks the operation mode based on the customer id. If the customer id is null or empty, then it will execute the insert function and if there is a value present, then the operation will be an update on the database. Update the AppComponent as follows to add the update and insert the functionality:
import { Component } from '@angular/core';
import { CustomerService } from './customer.service';
@Component({
//...
})
class AppComponent {
public customers: Array<any> = [];
public customer: any = {};
//....
setCustomer(customer: any, idx: number) {
}
saveCustomer() {
if (!this.customer.id) {
this.service.createCustomer(this.customer).then(data => {
this.customer.id = data;
this.customers.push(this.customer);
this.customer = {};
});
} else {
this.service.updateCustomer(this.customer);
}
}
addNewCustomer() {
this.customer = {};
this.currentSelectedIndex = -1;
}
}
I am leaving the validation and other functionalities for you. We just need to demonstrate the database operation with Electron. The rest of the things are the same as your normal web applications. Add the function to delete the record into the component:
class AppComponent {
//.....
deleteComponent() {
if (this.currentSelectedIndex !== -1) {
this.service.deleteCustomer(this.customer.id)
.then(affectedRows => {
if (affectedRows > 0) {
this.customers.splice(this.currentSelectedIndex, 1);
}
});
}
}
}
The application is ready for execution now. Run the application with the yarn start and you should get the following screen as output:
