SQLITE IONIC 3 EXAMPLE | IONIC CRUD

Share the post

Hello All, On the previous topic we have covered the most important topic that is WebSql using Ionic 3 & Angular 5. In this topic, we will see the SQLite Ionic 3 example & see how the CRUD works in ionic. To implement the functionality please follow the below steps.

Steps to implement SQLite in Ionic 3 :

  1. Install the Plugin & Dependency :
npm install --save @ionic-native/sqlite@4

2. Update app.module.ts as follows:

import { BrowserModule } from '@angular/platform-browser';

import { ErrorHandler, NgModule } from '@angular/core';

import { IonicApp, IonicErrorHandler, IonicModule } from 'ionic-angular';

import { SplashScreen } from '@ionic-native/splash-screen';

import { StatusBar } from '@ionic-native/status-bar';

import { MyApp } from './app.component';

import { HomePage } from '../pages/home/home';

import { SQLite } from '@ionic-native/sqlite';

@NgModule({

declarations: [

MyApp,

HomePage

],

imports: [

BrowserModule,

IonicModule.forRoot(MyApp),

],

bootstrap: [IonicApp],

entryComponents: [

MyApp,

HomePage

],

providers: [

StatusBar,

SplashScreen,

SQLite,

{ provide: ErrorHandler, useClass: IonicErrorHandler }

]

})

export class AppModule { }

3. Write an HTML :

We will create HTML just like the previous topic.1 input box for username & 4 buttons to operate different operations of SQLite. In this topic, we change some button functionality instead of deleting one entry we have changed the button to DeleteAll. Also, We are displaying the data using *ngFor and giving the option for users to delete the individual records.

Please see the below HTML for more details:

<div>

<ion-item>

<ion-label>Username</ion-label>

<ion-input type="text" id="username" [(ngModel)]="username"></ion-input>

</ion-item>

<button ion-button full (click)="saveDataSQL()">Save</button>

<button ion-button full (click)="getDataSQL()">Get Data</button>

<button ion-button full (click)="updateDataSQL()">Edit</button>

<button ion-button full (click)="deleteAllDataSQL()">Delete All</button>

<div>

<ion-list *ngFor="let item of DATA;let z=index;">

<ion-row>

<ion-col>

<ion-item>

{{item.sqNo}}

</ion-item>

</ion-col>

<ion-col>

<ion-item>

{{item.userName}}

</ion-item>

</ion-col>

<ion-col>

<ion-item>

{{item.date}}

</ion-item>

</ion-col>

<ion-col>

<ion-item (click)="deleteDataSQL(z)">

<ion-icon name="trash"></ion-icon>

</ion-item>

</ion-col>

</ion-row>

</ion-list>

</div>

</div>

We have created here 4 buttons :

  • Save : to create a table & save the input value into table.
  • Get Data : to get all saved records.
  • Edit : Write the value in input to change a record for username = test1
  • Delete All : to delete all records present in table.
<button ion-button full (click)="saveDataSQL()">Save</button>

<button ion-button full (click)="getDataSQL()">Get Data</button>

<button ion-button full (click)="updateDataSQL()">Edit</button>

<button ion-button full (click)="deleteAllDataSQL()">Delete All</button>

 4. Write ts as follows :

import { Component } from '@angular/core';

import { NavController } from 'ionic-angular';

import { SQLite, SQLiteObject } from '@ionic-native/sqlite';

@Component({

  selector: 'page-home',

  templateUrl: 'home.html'

})

export class HomePage {

  //SQLite

  COUNT: number = 0;

  DATA: any = [];

  constructor(public navCtrl: NavController,

    private sqlite: SQLite) {

  }




  //SQLite

  createTableSQL() {

    return new Promise((resolve, reject) => {

      this.sqlite.create({

        name: 'USERDATADEVICE.db',

        location: 'default'

      })

        .then((db: SQLiteObject) => {

          db.executeSql('CREATE TABLE IF NOT EXISTS USERTABLE (SR_NO,USER_NAME,LAST_CHANGE_DATE)', [])

            .then((res) => {

              alert('USERTABLE TABLE CREATED');

              resolve(res);

            })

            .catch(e => {

              alert('USERTABLE TABLE NOT CREATED');

              reject(e)

            });

        })

        .catch(e => console.log(e));

    });

  }

  //SQLite

  saveDataSQL() {

    this.createTableSQL().then(res => {

      this.sqlite.create({

        name: 'USERDATADEVICE.db',

        location: 'default'

      })

        .then((db: SQLiteObject) => {

          db.executeSql('INSERT INTO USERTABLE (SR_NO,USER_NAME,LAST_CHANGE_DATE) VALUES (?,?,?)', [this.COUNT, this.username, new Date().getDate() + "/" + (new Date().getMonth() + 1) + "/" + new Date().getFullYear()])

            .then(() => {

              alert('DATA INSERTED SUCCESSFULLY');

              this.COUNT++;

            })

            .catch(e => {

              console.log(e);

              alert('DATA NOT INSERTED');

            });

        })

        .catch(e => console.log(e));

    }, err => {

      console.log("FAIL");

    });

  }

  //SQLite

  updateDataSQL() {




    this.sqlite.create({

      name: 'USERDATADEVICE.db',

      location: 'default'

    })

      .then((db: SQLiteObject) => {

        db.executeSql("UPDATE USERTABLE SET USER_NAME='" + this.username + "' WHERE USER_NAME = 'test1' ", [])

          .then((res) => {

            alert('DATA UPDATED SUCCESSFULLY');

            this.getDataSQL();

          })

          .catch(e => {

            alert('DATA NOT UPDATED');

          });

      })

      .catch(e => console.log(e));

  }

  //SQLite

  deleteDataSQL(id) {

    let q = 'DELETE FROM USERTABLE WHERE SR_NO =' + id + '';

    console.log(q);

    this.sqlite.create({

      name: 'USERDATADEVICE.db',

      location: 'default'

    })

      .then((db: SQLiteObject) => {

        db.executeSql('DELETE FROM USERTABLE WHERE SR_NO =' + id + '', [])

          .then((res) => {

            alert('DATA DELETED SUCCESSFULLY');

            this.COUNT--;

            this.getDataSQL();

          })

          .catch(e => {

            alert('DATA NOT DELETED');

          });

      })

      .catch(e => console.log(e));




  }




  //SQLite

  deleteAllDataSQL() {

    this.sqlite.create({

      name: 'USERDATADEVICE.db',

      location: 'default'

    })

      .then((db: SQLiteObject) => {

        db.executeSql('DELETE FROM USERTABLE', [])

          .then((res) => {

            alert('ALL DATA DELETED SUCCESSFULLY');

            this.COUNT = 0;

            this.getDataSQL();

          })

          .catch(e => {

            alert('ALL DATA NOT DELETED');

          });

      })

      .catch(e => console.log(e));

  }




  //SQLite

  getDataSQL() {




    this.DATA = [];

    this.sqlite.create({

      name: 'USERDATADEVICE.db',

      location: 'default'

    })

      .then((db: SQLiteObject) => {

        db.executeSql("SELECT * FROM USERTABLE", [])

          .then((res) => {

            console.log(res);

            for (let z = 0; z < res.rows.length; z++) {

              this.DATA.push({

                sqNo: res.rows.item(z).SR_NO,

                userName: res.rows.item(z).USER_NAME,

                date: res.rows.item(z).LAST_CHANGE_DATE

              })

            }

            alert('DATA LOADED SUCCESSFULLY');

          })

          .catch(error => {

            console.log(error);

            console.log('DATA NOT LOADED');

          });

      })

      .catch(e => console.log(e));

  }

}

Output of above example is as shown below :

After writing a above code we will insert the data as shown in below images :

SQLite_Ionic3_Angular5
SQLite_Ionic3_Angular5

After saving three records for test,test1,test2,We will click on Get Data. The output for get data is as follows :

SQLite_Ionic3_Angular5

After that we will delete one individual record to test our delete query. So, we click on delete icon showing at extreme right of table of 1st record (row for test record). The output after deleted entry is as follows :

SQLite_Ionic3_Angular5
SQLite_Ionic3_Angular5

So, now we have only two records saved in our table. Now we write New test1 in input and click on edit then the username in the table for test1 is updated by New test1. Please see the below output:

SQLite_Ionic3_Angular5
SQLite_Ionic3_Angular5

Now, finally, we will test delete all data by clicking on the delete all button. Please see the output as follows:

SQLite_Ionic3_Angular5

1 thought on “SQLITE IONIC 3 EXAMPLE | IONIC CRUD”

Leave a Comment