Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 2] -- Words (1246)

Ionic
ionic 2+ SQLite and barcode scanner

This is part 2 of a series of tutorials to learn Ionic 2+ by building a real world and simple product inventory manager that also shows how to use Cordova SQLite and Barcode Scanner plugins to interface respectively with SQLite databases and Barcode/UPC scanning with Camera in mobile devices .

Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 1]

How to Mock the SQLite Plugin to Develop Your App Entirely On the Browser

Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 2]

Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 3]

Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 4]

Here is what we have done on the previous part :

We have created a new project based on Ionic 2+ .

We have installed both Cordova SQLite and Barcode Scanner plugins and their Ionic Native 3.x+ wrappers .

We have created and configured our data service provider to work with SQLite .

We have created our SQLite database and different SQL tables (Products , Families , Locations and Transactions ).

Now lets start with the second part of our tutorial where we are going to continue building on our app .

Adding SQLite CRUD Methods to our Data Service


Head over to src/providers/data-service/data-service.ts and lets add 4 methods to work with our SQLite database data .

create(tableName , item) : responsible for creating data rows or inserting items in SQLite database .

list(tableName) : responsible for reading or listing data rows from SQLite database .

update(tableName , item ) : responsible for updating data rows .

remove(tableName , item ) : responsible for remove or delete a specified row or item by its ID on SQLite database .

Creating SQLite data rows / items

lets now see an implementation for create() method .The method takes two parameters :

the tableName which is the name of the table to use and the item which is the data to insert in the database .

public create(tableName,item){
    let sqlText;
    let values ;
    switch(tableName){
        case "families":
            sqlText = "INSERT INTO families (reference , name ,unit) VALUES (?,?,?)";
            values = [item.reference || null , item.name || null, item.unit || null] 
            break;
        case "locations":
            sqlText = "INSERT INTO locations (name) VALUES (?)";
            values = [item.name || null ] 
            break;
        case "transactions":
            sqlText = "INSERT INTO transactions (date , quantity ,unitCost , reason , upc , comment , product_id) VALUES (?,?,?,?,?,?,?)";
            values = [item.date || null , item.quantity || null, item.unitCost || null ,item.reason || null,item.upc || null , item.comment || null ,item.product_id || null]         
            break;
        case "products":
            sqlText = "INSERT INTO products (sku,barcode,title,description,quantity,unit,unitPrice,minQuantity,family_id,location_id) VALUES (?,?,?,?,?,?,?,?,?,?)";
            values = [item.sku || null , item.barcode || null, item.title || null, item.description || null, item.quantity || null,item.unit || null, item.unitPrice || null, item.minQuantity || null, item.family_id || null, item.location_id || null ]                    
            break;
        default :
        return ;

    }
    return this.database.executeSql(sqlText,values);
}

The create() method first adds a switch statement to make a decision on the SQLite query to build ,and the values to insert ,based on the table name .

then calls this.database.executeSql(sqlText,values) , with query and values as parameters ,which effectively executes the SQL query against our SQLite database .

Updating data rows / items

The update() method takes two parameters ,the table name and the new item data .The item object has also the id of the old item to update .

public update(tableName,item){
    let sqlText;
    let values ;
    switch(tableName){
        case "families":
            sqlText = "UPDATE families SET (reference , name , unit ) = ( ? , ? , ? ) where id = ? ;";
            values = [item.reference || null , item.name || null, item.unit || null , item.id] 
            break;
        case "locations":
            sqlText = "UPDATE locations SET name = ? where id = ? ; ";
            values = [item.name || null , item.id] 
            break;
        case "transactions":
            sqlText = "UPDATE transactions SET (date , quantity ,unitCost , reason , upc , comment , product_id)  = (?,?,?,?,?,?,?) where id = ? ; ";
            values = [item.date || null , item.quantity || null, item.unitCost || null ,item.reason || null,item.upc || null , item.comment || null ,item.product_id || null , item.id]         
            break;
        case "products":
            sqlText = "UPDATE products SET (sku,barcode,title,description,quantity,unit,unitPrice,minQuantity,family_id,location_id) = (?,?,?,?,?,?,?,?,?,?) where id = ? ; ";
            values = [item.sku || null , item.barcode || null, item.title || null, item.description || null, item.quantity || null,item.unit || null, item.unitPrice || null, item.minQuantity || null, item.family_id || null, item.location_id || null , item.id ]                    
            break;
        default :
        return ;

    }
    return this.database.executeSql(sqlText,values);

} 

The method builds SQL updates queries based on the table name and values to update then again calls this.database.executeSql(sqlText,values); to execute query against the database .

Deleting SQL data rows / items

The remove() method takes two parameters : the first is the target table name ,the second is the item to remove . We pass the whole item but we need just the id to remove the row from the SQLite database .

public remove(tableName,item){
    let sqlText;
    let values ;
    sqlText = `delete from ${tableName} where id = ? `;
    values = [item.id || null ] 
    return this.database.executeSql(sqlText,values);    
}

We don't need a switch to make a decision since this query is not complex and doesn't involve all table columns but just the id which a common column between all SQLite tables .

We are using TypeScript template strings to insert the table in the query dynamically .Note that we are using back ticks `` for the string and we insert the table name with interpolation operator :${} .

After building the query we just call this.database.executeSql(sqlText,values) to execute the query against the database .

Reading or listing data from SQLite database

The last CRUD operation is data reading from database .This method needs more work since we need to implement pagination so it will be the subject of the next part but for a simple implementation to get your data from the database .You can write something like :

public list(tableName){
    let sqlText;
    let values =[];
    sqlText = `select * from ${tableName} `;
    return this.database.executeSql(sqlText,values);    
}

For now ,we just use a select * SQL statement then we build the query with TypeScript string templates and interpolation operator ${} then we execute the query against the database with this.database.executeSql(sqlText,values) .

Conclusion


In this tutorial part from our tutorial series ,to learn Ionic 2+ by building a simple real world cross platform mobile application for managing products inventory in small businesses ,we have continued building our project by implementing SQLite CRUD (Create , Read , Update and Delete) operations .

On the next part we are going to improve the data rows read/listing operation by adding pagination so we can retrieve data from SQLite database in small chuncks which an inevitable step to create a performant application that doesn't hang if database contains millions of data rows .

Ionic 2+ : Using Cordova SQLite and Barcode Scanner to build a Product Inventory Manager [PART 3 ]



mrnerd is a Web Entrepreneur & Developer, Digital Marketer and a Passionate Blogger. He writes about software and website development,money making tips and SEO etc.. He is a tech addict and a computer geek with a lot of hobbies.