Introduction:

Ionic apps are created and developed primarily through the Ionic command line utility (the “CLI”), and use Cordova to build and deploy as a native app. This means we need to install a few utilities to get developing.

To create Ionic projects, you’ll need to install the latest version of the CLI and Cordova. Before you do that, you’ll need a recent version of Node.js. Download the installer for Node.js 6 or greater and then proceed to install the Ionic CLI and Cordova for native app development:

$ npm install -g ionic cordova

You may need to add “sudo” in front of these commands to install the utilities globally

Getting Started:

create a new project with the following command in terminal/command prompt

$ ionic start partnerList

When running this command you will find a few options to choose to create a project. Select any project structure as you required. In our case, we have selected a blank project. Wait for a while as project downloads from the server. Then step inside the directory via cd and then run the ionic serve command to test your app right in the browser!

$ cd partnerList
$ ionic serve

To run the same project in a connected Android or iOS device, first, you have to add platform using ionic CLI. if we have not added our platform already then we have to add platform.

To add a platform for Android and iOS please use the following command. Please use the following command once in the project development life cycle.

$ ionic cordova platform add android
$ ionic cordova platform add ios

In future, if you want to remove platform please use the following command to remove platform.

$ ionic cordova platform remove android
$ ionic cordova platform remove ios

Then we have to run the below command to run the project in a real Android or iOS device.

$ ionic cordova run android
$ ionic cordova run ios

This is the way you can create and run a simple ionic project. Next, we will see how to add a database and use it inside our created ionic project. To do so, we can use any IDE that goes with HTML, CSS and TypeScript support. In our case, we will be using Visual Studio Code as I found it very flexible for ionic project development.

Open Project in IDE:

Open above created project to Visual Studio Code. Then you will see the project structure as the listed picture below. Our main folder will be the src folder, where you will find a few predefined folders named app, assets, pages, and theme.

Now create a SQL file with the following query. If you want you can use your own SQL file.

CREATE TABLE IF NOT EXISTS tbl_partner (
    p_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    p_name TEXT, 
    p_age TEXT, 
    p_location TEXT
);

Give a name to SQL file. In my case, I have given the name partner.sql file. After that copy that SQL file to assets folder inside src folder.

We will be using the database globally, then we have to define a provider to use database functionality. Now. let’s create the provider using the following command in CLI.

$ ionic g provider database

Above command in CLI will create a folder named providers inside src folder if not already exists, where the database.ts file will be created by default.

Then I have to add SQLite plugin for ionic Cordova with the following command.

$ ionic cordova plugin add cordova-sqlite-storage
$ npm install --save @ionic-native/sqlite

Now open the app.module.ts file inside src/app folder. And add the following line

...
import { SQLite } from '@ionic-native/sqlite';
...
@NgModule({
   declarations: [
      ...
   ],
   imports: [
      ...
   ],
   bootstrap: [IonicApp],
   entryComponents: [
      ...
   ],
   providers: [
      ...,
      SQLite
   ]
})
export class AppModule {}

Then I have to add a sqlite-porter plugin for ionic Cordova with the following command.

$ ionic cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter
$ npm install --save @ionic-native/sqlite-porter

Now open the app.module.ts file inside src/app folder. And add the following line

...
import { SQLitePorter } from '@ionic-native/sqlite-porter';
...
@NgModule({
   declarations: [
      ...
   ],
   imports: [
      ...
   ],
   bootstrap: [IonicApp],
   entryComponents: [
      ...
   ],
   providers: [
      ...,
      SQLitePorter
   ]
})
export class AppModule {}

Then I have to add storage plugin for ionic Cordova with the following command.

$ ionic cordova plugin add cordova-sqlite-storage
$ npm install --save @ionic/storage

Now open the app.module.ts file inside src/app folder. And add the following line

...
import { IonicStorageModule } from '@ionic/storage';
...
@NgModule({
   declarations: [
      ...
   ],
   imports: [
      ...,
      IonicStorageModule.forRoot()
   ],
   bootstrap: [IonicApp],
   entryComponents: [
      ...
   ],
   providers: [
      ...
   ]
})
export class AppModule {}

Then I have to add HTTP plugin for ionic Cordova with the following command.

$ ionic cordova plugin add cordova-plugin-http
$ npm install --save @ionic-native/http

Now open the app.module.ts file inside src/app folder. And add the following line

...
import { HttpModule } from '@angular/http';
...
@NgModule({
   declarations: [
      ...
   ],
   imports: [
      ...,
      HttpModule
   ],
   bootstrap: [IonicApp],
   entryComponents: [
      ...
   ],
   providers: [
      ...
   ]
})
export class AppModule {}

Now open the database.ts file inside database folder. And write the following code

import { Injectable } from '@angular/core';
import { Http } from '@angular/http';
import 'rxjs/add/operator/map';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import { BehaviorSubject } from 'rxjs/Rx';
import { SQLitePorter } from '@ionic-native/sqlite-porter';
import { Platform } from 'ionic-angular';

/*
  Generated class for the DatabaseProvider provider.

  See https://angular.io/docs/ts/latest/guide/dependency-injection.html
  for more info on providers and Angular DI.
*/
@Injectable()
export class DatabaseProvider {

  database: SQLiteObject;
  private databaseReady: BehaviorSubject<boolean>;

  constructor(public sqlitePorter: SQLitePorter, private storage: Storage, private sqlite: SQLite, private platform: Platform, private http: Http) {
    this.databaseReady = new BehaviorSubject(false);
    this.platform.ready().then(() => {
      this.sqlite.create({
        name: 'partner.db',
        location: 'default'
      })
        .then((db: SQLiteObject) => {
          this.database = db;
          this.storage.get('database_filled').then(val => {
            if (val) {
              this.databaseReady.next(true);
            } else {
              this.fillDatabase();
            }
          });
        });
    });
  }

  fillDatabase() {
    this.http.get('assets/partner.sql')
      .map(res => res.text())
      .subscribe(sql => {
        this.sqlitePorter.importSqlToDb(this.database, sql)
          .then(data => {
            this.databaseReady.next(true);
            this.storage.set('database_filled', true);
          })
          .catch(e => console.error(e));
      });
  }

  getDatabaseState() {
    return this.databaseReady.asObservable();
  }

  addPartner(name, age, location) {
    let data = [name, age, location]
    return this.database.executeSql("INSERT INTO tbl_partner (p_name, p_age, p_location) VALUES (?,?,?)", data).then(data => {
      console.log('Data: ', data);
      return data;
    }, err => {
      console.log('Error: ', err);
      return err;
    });
  }

  updatePartner(name, age, location, id) {
    let input = [name, age, location, id];
    console.log("Input Data", input);
    return this.database.executeSql("UPDATE tbl_partner SET p_name = ?, p_age = ?, p_location = ? WHERE p_id = ?", input).then((output) => {
      console.log("Output Data", output.rows);
      if (output.rowsAffected > 0) {
        return true;
      }
      else {
        return false;
      }
    }, err => {
      console.log('Error: ', err);
      return false;
    });
  }

  getPartner(id) {
    let input = [id];
    let partner = {};
    return this.database.executeSql("SELECT * FROM tbl_partner WHERE p_id = ?", input).then((output) => {
      if (output.rows.length > 0) {
        let raw = output.rows.item(0);
        partner['id'] = raw.p_id;
        partner['name'] = raw.p_name;
        partner['age'] = raw.p_age;
        partner['location'] = raw.p_location;
      }
      console.log("Database ", partner);
      return partner;
    }, err => {
      console.log('Error: ', err);
      return [];
    });
  }

  getAllPartner() {
    return this.database.executeSql("SELECT * FROM tbl_partner", []).then((data) => {
      let partners = [];
      if (data.rows.length > 0) {
        for (var i = 0; i < data.rows.length; i++) { partners.push({ id: data.rows.item(i).p_id, name: data.rows.item(i).p_name, age: data.rows.item(i).p_age, location: data.rows.item(i).p_location }); } } return partners; }, err => {
      console.log('Error: ', err);
      return [];
    });
  }

   deletePartner(id) {
    let input = [id];
    console.log("Input Data", input);
    return this.database.executeSql("DELETE FROM tbl_partner WHERE p_id = ?", input).then((output) => {
      console.log("Output Data", output.rows);
      if (output.rowsAffected > 0) {
        return true;
      }
      else {
        return false;
      }
    }, err => {
      console.log('Error: ', err);
      return false;
    });
  }

}

now open the src/pages/home/home.css file and add the following code

page-home {
    ion-navbar button ion-icon{
        color: #007ba7;
    }

    .noDataCSS{
        text-align: center;
        padding: 30px;
    }

    .fabStyle{
        margin-right: 10px;
        margin-bottom: 10px;
    }

    .titleCSS{
        color: #007ba7;
        font-weight: bold;
    }

    .fabIconCSS{
        color: #FFFFFF;
    }
}

Now open the src/pages/home/home.html file and add the following code

<ion-header>
  <ion-navbar>
    <ion-item no-lines>
      <ion-label>Partner List</ion-label>
    </ion-item>
  </ion-navbar>
</ion-header>

<ion-content>
  <ion-fab class="fabStyle" right bottom>
    <button ion-fab color="primary" (click)="addPartner()">
    <ion-icon item-left class="fabIconCSS" name="add">
        </ion-icon>
  </button>
  </ion-fab>
  <ion-list *ngIf="dataFound">
    <ion-item-sliding *ngFor="let partner of partners" #slidingItem>
      <ion-item (click)="viewPartner(partner, slidingItem)">
        <h2 class="titleCSS">{{partner.name}}</h2>
        <h1 item-right>{{partner.age}}y</h1>
        <p>{{partner.location}}</p>
      </ion-item>
      <ion-item-options side="right">
        <button ion-button color="secondary" (click)="viewPartner(partner, slidingItem)">
        <ion-icon name="open"></ion-icon>
              View
      </button>
        <button ion-button color="primary" (click)="editPartner(partner, slidingItem)">
        <ion-icon name="create"></ion-icon>
              Edit
      </button>
        <button ion-button color="danger" (click)="deletePartner(partner, slidingItem)">
        <ion-icon name="trash"></ion-icon>
              Delete
      </button>
      </ion-item-options>
    </ion-item-sliding>
  </ion-list>
  <div *ngIf="!dataFound">
    <ion-item class="noDataCSS" no-lines item-right>
      No Data
    </ion-item>
  </div>
</ion-content>

Now create another page with the following command

$ ionic g page addPartner

Then open the app.moduel.ts file from app folder and the following code snippet

...
import { AddPartnerPage } from '../pages/add-partner/add-partner';
...
@NgModule({
   declarations: [
      ...,
      AddPartnerPage
   ],
   imports: [
      ...
   ],
   bootstrap: [IonicApp],
   entryComponents: [
      ...,
      AddPartnerPage
   ],
   providers: [
      ...,
      SQLite
   ]
})
export class AppModule {}

Now open the src/pages/add-partner/add-partner.css file and add the following code

page-add-partner {
    ion-navbar button ion-icon{
        color: green;
    }

    .titleCSS{
        color: green;
        font-weight: bold;
    }
}

Now open the src/pages/add-partner/add-partner.html file and add the following code

<!--
  Generated template for the AddPartnerPage page.

  See http://ionicframework.com/docs/components/#navigation for more info on
  Ionic pages and navigation.
-->
<ion-header>
  <ion-navbar>
    <ion-item no-lines>
      <ion-label>Add Partner</ion-label>
    </ion-item>
  </ion-navbar>
</ion-header>


<ion-content padding>
  <ion-list inset>
    <ion-item>
      <ion-input [(ngModel)]="partner.name" type="text" placeholder="e.g. Davis Paul"></ion-input>
    </ion-item>
    <ion-item>
      <ion-input [(ngModel)]="partner.age" type="number" placeholder="e.g. 26"></ion-input>
    </ion-item>
    <ion-item>
      <ion-input [(ngModel)]="partner.location" type="text" placeholder="e.g. London"></ion-input>
    </ion-item>
  </ion-list>
  <ion-row>
    <button ion-button block class="btn" (click)="addPartner()">Add Partner</button>
  </ion-row>
</ion-content>

Now open the src/pages/add-partner/add-partner.ts file and add the following code

import { Component } from '@angular/core';
import { IonicPage, NavController, NavParams, AlertController, Events } from 'ionic-angular';
import { DatabaseProvider } from './../../providers/database/database';

/**
 * Generated class for the AddPartnerPage page.
 *
 * See http://ionicframework.com/docs/components/#navigation for more info
 * on Ionic pages and navigation.
 */

@IonicPage()
@Component({
  selector: 'page-add-partner',
  templateUrl: 'add-partner.html',
})
export class AddPartnerPage {

  partner = {};
  tag = "add";
  title = "Add Partner";

  constructor(public navCtrl: NavController, public navParams: NavParams, private alertCtrl: AlertController, private database: DatabaseProvider, private events: Events) {
    this.tag = this.navParams.get('tag');
    if (this.tag == "add") {
      this.title = "Add Partner";
    }
    else {
      this.title = "Edit Partner";
      this.partner = this.navParams.get('value');
    }
  }

  ionViewDidLoad() {
    console.log('ionViewDidLoad AddPartnerPage');
  }

  addPartner() {
    if (this.partner['name']) {
      if (this.partner['age']) {
        if (this.partner['location']) {
          if (this.tag == 'add') {
            this.database.addPartner(this.partner['name'], this.partner['age'], this.partner['location']).then(data => {
              console.log("Partner Inserted", data);
              if (data.rowsAffected > 0) {
                //this.showInfo("Success", "Partner Added Successfully");
                let alert = this.alertCtrl.create({
                  title: 'Success',
                  subTitle: 'Partner added successfully',
                  buttons: [
                    {
                      text: 'Dismiss',
                      handler: () => {
                        console.log('Dismiss clicked');
                        this.navCtrl.pop();
                        this.events.publish('update_partner_list');
                      }
                    }
                  ]
                });
                alert.present();
              }
            });
          }
          else {
            this.database.updatePartner(this.partner['name'], this.partner['age'], this.partner['location'], this.partner['id']).then(data => {
              if (data) {
                let alert = this.alertCtrl.create({
                  title: 'Success',
                  subTitle: 'Partner updated successfully',
                  buttons: [
                    {
                      text: 'Dismiss',
                      handler: () => {
                        console.log('Dismiss clicked');
                        this.navCtrl.pop();
                        this.events.publish('update_partner_list');
                      }
                    }
                  ]
                });
                alert.present();
              }
              else {
                let alert = this.alertCtrl.create({
                  title: 'Success',
                  subTitle: 'Partner not updated successfully',
                  buttons: [
                    {
                      text: 'Dismiss',
                      handler: () => {
                        console.log('Dismiss clicked');
                        this.navCtrl.pop();
                        this.events.publish('update_partner_list');
                      }
                    }
                  ]
                });
                alert.present();
              }
            })
          }
        }
        else {
          this.showInfo("Warning", "Location Required");
        }
      }
      else {
        this.showInfo("Warning", "Age Required");
      }
    }
    else {
      this.showInfo("Warning", "Name Required");
    }
  }
  showInfo(title, message) {
    let alert = this.alertCtrl.create({
      title: title,
      subTitle: message,
      buttons: ['Dismiss']
    });
    alert.present();
  }
}

Now open the src/pages/home/home.ts file and add the following code

import { Component } from '@angular/core';
import { IonicPage, NavController, NavParams, Events, ToastController, AlertController, ItemSliding, ModalController } from 'ionic-angular';

import { DatabaseProvider } from './../../providers/database/database';

import { AddPartnerPage } from '../add-partner/add-partner'

@Component({
  selector: 'page-home',
  templateUrl: 'home.html'
})
export class HomePage {

  partners = [];
  dataFound: boolean = false;

  constructor(public navCtrl: NavController, public navParams: NavParams, private modalCtrl: ModalController, private database: DatabaseProvider, private events: Events, private toastCtrl: ToastController, private alertCtrl: AlertController) {
    this.database.getDatabaseState().subscribe(rdy => {
      if (rdy) {
        this.loadProductList();
      }
    })
    this.events.subscribe('update_partner_list', () => {
      this.loadProductList();
    });
  }

  loadProductList() {
    this.database.getAllPartner().then(data => {
      this.partners = data;
      console.log("Partner Data", this.partners);
      console.log("Partner Data count", this.partners.length);
      if (this.partners.length > 0) {
        this.dataFound = true;
      }
      else {
        this.dataFound = false;
      }
    })
  }

  addPartner() {
    this.navCtrl.push(AddPartnerPage, {
      tag: 'add'
    });
  }

  viewPartner(partner, slidingItem: ItemSliding) {
    slidingItem.close();
    console.log("Partner", partner);
    this.showInfo(partner.name, partner.age + " year(s) old from " + partner.location);
  }

  editPartner(partner, slidingItem: ItemSliding) {
    slidingItem.close();
    this.navCtrl.push(AddPartnerPage, {
      tag: 'edit',
      value: partner
    });
  }

  deletePartner(partner, slidingItem: ItemSliding) {
    slidingItem.close();
    let alert = this.alertCtrl.create({
      title: "Warning",
      subTitle: "Are you sure want to delete?",
      buttons: [{
        text: 'Cancel',
        role: 'cancel',
        handler: () => {
          console.log('Cancel clicked');
        }
      },
      {
        text: 'Yes',
        role: 'yes',
        handler: () => {
          console.log('yes clicked');
          this.database.deletePartner(partner.id).then(data => {
            if (data) {
              let toast = this.toastCtrl.create({
                message: 'Partner Deleted Succesfully',
                duration: 2000,
                position: 'top'
              });
              toast.present();
              this.loadProductList();
            }
            else{
              let toast = this.toastCtrl.create({
                message: 'Partner Delete Error',
                duration: 2000,
                position: 'top'
              });
              toast.present();
            }
          })
        }
      }]
    });
    alert.present();
  }

  showInfo(title, message) {
    let alert = this.alertCtrl.create({
      title: title,
      subTitle: message,
      buttons: ['Dismiss']
    });
    alert.present();
  }

}

Now run your app and you will find it working. If you have any query please feel free to leave a comment and we will get back to you.