Monday, September 26, 2011

Creating a contact book using Flex, AIR and SQLite database: Part 2

Today we're going to continue making our contact book application in Flex AIR.

Let's continue from where we left off - the selectItems function. Here, we will write a new SQL statement that will select all the items from the contacts database and call a function called onSelected in a Responder object to handle the results.

Also, this is the function that we will call every time we update the database (add, remove or edit an item). When an action like that happens, anything selected in the list will become unselected, and because of that, we also need to disable the Save and Delete buttons if they were enabled. To do that, we first need to give IDs to all 3 buttons - b_new, b_save and b_delete.

<s:HGroup width="500">
<s:List id="contactList" width="290" height="300"/>
<mx:Form>
<mx:FormItem><s:Button id="b_new" label="Create new item"/></mx:FormItem>
<mx:FormItem label="First name"><s:TextInput id="t_fname"/></mx:FormItem>
<mx:FormItem label="Last name"><s:TextInput id="t_lname"/></mx:FormItem>
<mx:FormItem label="Phone"><s:TextInput id="t_phone" restrict="0-9 " /></mx:FormItem>
<mx:FormItem label="Email"><s:TextInput id="t_email"/></mx:FormItem>
<mx:FormItem><s:Button id="b_save" label="Save item" enabled="false" /><s:Button id="b_delete" label="Delete item" enabled="false" /></mx:FormItem>
</mx:Form>
</s:HGroup>

Now we can disable them by setting the enabled property to false. Here's what the selectItems function looks like:

private function selectItems(evt:SQLResult):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

An onSelected function is called, which is supposed to handle the results to display them in the list.

Now, in the AS3 AIR version of this application, this function was where we just applied data to the dataProvider property of the list - by first refreshing, then adding all the items using addItem() method, setting the labels, data, etc.

In Flex, though, we have data binding! Let's take advantage of that. We are going to have a variable called listData, which will be an ArrayCollection that will hold the data about the contact items. It gets updated in the onSelected function, and it is linked to the List object.

[Bindable]
private var listData:ArrayCollection;

private function onSelected(evt:SQLResult):void {
listData = new ArrayCollection(evt.data);
}

<s:List id="contactList" width="290" height="300" dataProvider="{listData}"/>

The code will work if we had items in the contact book, however, it will not display anything other than [object Object] in the label fields. We can fix that by adding a labelFunction getFullName:

<s:List id="contactList" width="290" height="300" dataProvider="{listData}" labelFunction="getFullName" />

private function getFullName(evt:Object):String{
return evt.fname + " " + evt.lname;
}

Now, to test what we have here we need to actually have something in our database. Let's add functionality to the "Create new item" button. Tell it to call createNew() on click:

<mx:FormItem><s:Button id="b_new" label="Create new item" click="createNew();" /></mx:FormItem>

The createNew function will use the INSERT sql command to add an item to the database using the data from the input fields.

private function createNew():void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone, email) VALUES (@fname, @lname, @phone, @email)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute(-1, new Responder(selectItems));
}

And now it works! You can see that you can now add new items to your database and the list gets updated with the new fresh data. Try it!

Here's the full code:

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
                       xmlns:s="library://ns.adobe.com/flex/spark"
                       xmlns:mx="library://ns.adobe.com/flex/mx" 
   width="600" height="350"
   creationComplete="init();"
   title="Flex contact book"
   >
   
<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.data.SQLMode;
import flash.filesystem.File;
import flash.net.Responder;
import flash.data.SQLStatement;
import mx.collections.ArrayCollection;
import mx.controls.Alert;
import flash.data.SQLResult;

private var connection:SQLConnection;
[Bindable]
private var listData:ArrayCollection;

private function init():void{
var dbFile:File = File.applicationStorageDirectory.resolvePath("database.db");
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, onOpen);
connection.openAsync(dbFile, SQLMode.CREATE);
}

private function onOpen(evt:SQLEvent):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, phone TEXT, email TEXT)";
stat.execute(-1, new Responder(selectItems));
}

private function selectItems(evt:SQLResult):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function onSelected(evt:SQLResult):void {
listData = new ArrayCollection(evt.data);
}

private function createNew():void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone, email) VALUES (@fname, @lname, @phone, @email)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute(-1, new Responder(selectItems));
}

private function getFullName(evt:Object):String{
return evt.fname + " " + evt.lname;
}

]]>
</fx:Script>

<s:HGroup width="500">
<s:List id="contactList" width="290" height="300" dataProvider="{listData}" labelFunction="getFullName" />
<mx:Form>
<mx:FormItem><s:Button id="b_new" label="Create new item" click="createNew();" /></mx:FormItem>
<mx:FormItem label="First name"><s:TextInput id="t_fname"/></mx:FormItem>
<mx:FormItem label="Last name"><s:TextInput id="t_lname"/></mx:FormItem>
<mx:FormItem label="Phone"><s:TextInput id="t_phone" restrict="0-9 " /></mx:FormItem>
<mx:FormItem label="Email"><s:TextInput id="t_email"/></mx:FormItem>
<mx:FormItem><s:Button id="b_save" label="Save item" enabled="false" /><s:Button id="b_delete" label="Delete item" enabled="false" /></mx:FormItem>
</mx:Form>
</s:HGroup>

</s:WindowedApplication>

Thanks for reading!

Related:

Creating a contact book using Flex, AIR and SQLite database: Part 1
Creating a contact book using Flex, AIR and SQLite database: Part 3
Creating a contact book using Flex, AIR and SQLite database: Part 4
Creating a contact book using Flex, AIR and SQLite database: Part 5
Creating a contact book using Flex, AIR and SQLite database: Part 6
Creating a contact book using Flex, AIR and SQLite database: Part 7
Creating a contact book using Flex, AIR and SQLite database: Part 8
Creating a contact book using Flex, AIR and SQLite database: Part 9
Creating a contact book using Flex, AIR and SQLite database: Part 10
Creating a contact book using Flex, AIR and SQLite database: Part 11
Creating a contact book using Flex, AIR and SQLite database: Part 12
Creating a contact book using Flex, AIR and SQLite database: Part 13
Creating a contact book using Flex, AIR and SQLite database: Part 14
Creating a contact book using Flex, AIR and SQLite database: Part 15
Creating a contact book using Flex, AIR and SQLite database: Part 16

No comments:

Post a Comment