Using SQLite component with ListPicker

Developing Android app with database functionality has been simplified in Ai2LiveComplete. SQLite component is an alternative to TinyDb with much powerful functionality.

This post shows you how to use SQLite component in Ai2LiveComplete and how to perform CRUD (Create, Read, Update, Delete) operations on database tables.

The SQLite is an alternative to TinyDB with full database functionality.

This example shows you how you can quickly create a  table, insert data, read (query) and then populate the result into a ListPicker.

• On application start, the procedure initDb procedure is invoked, which creates a simple table with 2 columns.
• Next, we insert 3 rows into the database table using SQL command

Next, we use a ListPicker to display result data. In the ListPicker.BeforePicking, the table is queried. The query returns a list which can directly be populated into our ListPicker. Our table has a key and value columns. Let’s say that we use key for specifying a product (e.g. Lemonade) and value is the quantity of product on hand (e.g. 3). To keep data generic, both columns are defined as Varchar (String).

The query we want to perform is to determine how many of an item has been sold for each day.  Also, we want to multiply the quantity by 2 (assuming each item cost $2) and name the resulting column as ‘sold’. Also, we only want to select rows where quantity (value) is < 30.    This logic can be converted into sql statement like below:

select cast(value as decimal) * 2 sold, value from eav_table where value  between 0 and 30 

Above query means, select column value, convert (cast) to decimal and multiple by 2, name the result as ‘sold’.

When the ListPicker opens, it will display data as requested:

You can see the column headings and result data in the ListPicker.

Interested in Ai2LiveComplete? please see HERE for details and more information.