iPhone-like Database GUI on Windows Mobile

Abstract

This article shows how you can:

• Download data from the local mobile database within sdf file.
• Create iPhone-like GUI on Windows Mobile to display the data.
• Create functionality to search the table data.
• Polish the app by adding some cool features such as round buttons, vibration, glass effect.
• Find out how to detect the current resolution fast.

At the end the article discusses how to display large amount of data on a mobile device.

What you will need:

• Microsoft Visual Studio 2005/2008
• Windows Mobile SDK (can be downloaded for free from Microsoft web site)
• Bee Mobile iPack – pack of 19 compact framework controls (download the trial version here).

What you will get:

You can download the complete application including the source code at
http://beemobile4.net/products/ipack/controls/touchlist/sample-projects.

Benefits:

• Strong Visual Studio support hence little coding.
• Attractive GUI
• Time Save hence lower cost
• Automatic Portrait/Landscape adaptation

Bee Mobile iPack

After installing Bee Mobile iPack to your computer, you should see the controls in the toolbox of your Visual Studio. In this article we will use these controls:

• TouchList
• SearchBox
• TPanelVista
• TVistaButton

Database

We will use the Products table of Microsoft’s sample Northwind.sdf database. It is part of the download at the top of this page. From the Products table we will display the product name and check box indicating whether the product is discontinued or not. If the user selects an item, we will also display an english name of the product underneath it.

Preparing the GUI for data display

On a new Visual Studio smart device project, we’ll add a TouchList control. Set its Dock property to ‘Fill’. Now we need to design the TouchList control i.e., tell it how the data should look like. This concept is similar to the work of clothes designer. Once the clothes are designed the tailor then creates the clothes (which all look the same, because they are tailored from the same design) and then differentpeople can put the clothes on. So let’s design the “clothes” first.

Creating Row Styles

Row styles to TouchList are what the clothes design is to a clothes designer. You can use Visual Studio designer to create row styles. We will create 3 different row styles – Normal, Alternative and Selected. Normal will be used for the non-selected rows with odd index, alternative for the non-selected rows with even index and Selected for the selected rows. Within each row style there are item styles. Item styles further specify what types of data should be displayed in the row (e.g., text, image, …) and their position. The position is not defined in absolute coordinates but rather as a distance from the row’s border. That way an image can be anchored to an edge of a row and if the orientation of display changes, the layout automatically accomodates itself to the change. There will be two textual item styles (their names are “Name” and “EnglishName” because they refer to the product name) and one image item style (named “Discontinued”) in our rows.

The third row style will be used for selected rows. TouchList allows us to see the graphical cursor during design time so that we can design the way the cursor will like. We will use gradient-fill for it starting in this color RGB: {168, 174, 176} and ending in this color RGB: {183, 191, 197}.

You will notice how Visual Studio automatically displays the design of the rows in the design-time. The other image shows some properties that we set:

If Cursor radius is set above 0, then the selection cursor will have round edges. Overscrolling is a nice feature which causes the list to scroll even if the beginning/end of document is reached and when the user releases his finger, it gradually returns back.We could have put an image into the background or choose a gradient-filled background, but for this particular design (simplistic one) I chose to turn it off.

Adding the SearchBox

For the user to be able to search the database, we are going to add a SearchBox control to the top of the form. SearchBox is like a TextBox, but contains more features such as appealing look, clear button (clear the text when the user clicks it), search button (the magnifying glass image, fires an event when clicked) and can display a hint text which is displayed in the SearchBox, if there is no text in the SearchBox and if it does not have input focus.

TPanelVista and TVistaButtons

Let’s place a TPanelVista at the bottom of the form. TPanelVista is a container for other controls. It can have a gradient-filled background with round edges. Let’s use the same colors for the gradient-fill as we used for the TouchList’s cursor. Finally, let’s add two TVistaButtons to the panel. TVistaButton is a button which can be transparent. It also has round edges and gradient-filled background. We only need to specify the colors of the gradient. Also, we can set the VibratePeriod property to amount of miliseconds the device should vibrate when the button is clicked. TVistaButton is also able to display an icon over its surface or change its look whenever it receives focus.

Fill the list with data

Before we make the connection to the database and load the data, we need to find out the path to our application’s executable (as the sdf file which contains the database will be located in the same folder). We will also use a neat trick to find out the resolution of the display (this is the fastest method we have ever discovered so far):

Imports System.IO ' make sure to import the proper namespaces
Imports System.Reflection

Public Class Form1
	' Define a few data memebers:

    Dim m_sPath As String ' path to the application's executable will be stored here
    Dim m_Connection As SqlCeConnection ' Connection to the database.
    Dim m_Reader As SqlCeDataReader ' Database reader.
    Dim m_OldIndex As Integer ' Index of the last loaded row
    Dim m_Resolution As New SizeF(1.0F, 1.0F) ' Current resolution (as a multpiple of 240x320)
    Dim m_AllRowsCount As Integer

    Sub New()
        Row.DefaultNormalRowStyle = 0 ' default row styles for Touch List
        Row.DefaultSelectedRowStyle = 2
        m_sPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase) ' where is my executable ?
        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        m_Resolution = DetectResolution() ' detect the current resolution
        m_OldIndex = -1 ' index of the last loaded row (-1 means no rows loaded yet)
    End Sub

	' This is how we detect the resolution

    Private Function DetectResolution() As SizeF
        Return New SizeF(Me.CurrentAutoScaleDimensions.Width / 96.0F, _
        Me.CurrentAutoScaleDimensions.Height / 96.0F)
    End Function

End Class

When the form loads up, we will create the connection to the database (which we will keep open until the form is closed) and load the data:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            m_Connection = New SqlCeConnection("Data source=" + m_sPath + "\\Northwind.sdf")
            m_Connection.Open()
            LoadItems(String.Empty)
        Catch ex As SqlCeException
            MessageBox.Show(ex.Message)
            Me.Close()
        End Try
    End Sub

The LoadItems method constructs the query string using SQL and creates the data reader. It also informs the TouchList that new set of data is going to be loaded into it:

Private Sub LoadItems(ByRef sName As String)
        Dim sQuery, sCountQuery As String

        If SearchBox1.Text = String.Empty Then

            sQuery = "SELECT [Product ID], [Product Name], Discontinued FROM Products ORDER BY [Product Name] ASC"
            sCountQuery = "SELECT COUNT([Product Name]) FROM Products"
        Else
            sQuery = "SELECT [Product ID], [Product Name], Discontinued FROM Products WHERE [Product Name] LIKE '%" + sName + "%' ORDER BY [Product Name] ASC"
            sCountQuery = "SELECT COUNT([Product Name]) FROM Products WHERE [Product Name] LIKE '%" + sName + "%'"
        End If

        Dim cmd As SqlCeCommand = New SqlCeCommand(sQuery, m_Connection)
        Dim cmdCount As SqlCeCommand = New SqlCeCommand(sCountQuery, m_Connection)

        TouchList1.Rows.Clear()
        m_Reader = cmd.ExecuteReader()
        m_AllRowsCount = CType(cmdCount.ExecuteScalar(), Integer)
        ApplyRowStyles()
        TouchList1.SelectedIndex = 0 ' let the first row be selected
        TouchList1.JumpToTheTop() ' scrolls to the very beginning of the document
        TouchList1.ForceRefresh() ' refresh the list with new data
    End Sub

As you can see no code has read any data from the database so far. This is because the actual data loading happens in TouchList’s GatheringItem event handler. This event is fired by TouchList each time the TouchList needs another piece of data to be loaded. TouchList only loads data which it really needs so if there are e.g., 15 rows visible, only 15 rows of data will be loaded from the database. Let’s say your database table which you want to show to the user contains many rows (thousands). It’s not only unnecessary to download all thousand rows to the memory of the mobile device (because of its memory and speed constraints) but it’s also unlikely the user will ever want to scroll all of them and read all of them. The user is likely searching for a particular row or a couple of rows. This way you only need to provide him with a sort of a search tool (which in this case is the job of SearchBox) and a mechanism which would allow him to see how many records match the search query. The user is able to see that thanks to the scroll bar thumb’s size. That is why the amount of rows in the search result is first counted.

And this is how the GatheringItem event handler looks like:

Private Sub TouchList1_GatheringItemEvent(ByVal sender As System.Object, ByVal args As BeeMobile.GatheringItemEventArgs) Handles TouchList1.GatheringItemEvent
If m_OldIndex < args.RowIndex Then ' if TouchList asks for a row which has not been loaded yet m_OldIndex = args.RowIndex m_Reader.Read() ElseIf args.RowItemStyle.Name = "EnglishName" Then ' if the user clicked a row then the english name has to be fetched Dim productID As String = TouchList1.Rows(args.RowIndex).Items("Name").Tag.ToString() Dim sQuery As String = "SELECT [English Name] FROM Products WHERE [Product ID]=" + productID Using cmd As SqlCeCommand = New SqlCeCommand(sQuery, m_Connection) Using scdr As SqlCeDataReader = cmd.ExecuteReader() scdr.Read() Dim item As RowItemText = New RowItemText() item.Text = CType(scdr("English Name"), String) args.Item = item End Using End Using End If If args.RowItemStyle.Name = "Name" Then ' if TouchList asks for the product name ' then get it from the database reader Dim item As RowItemText = New RowItemText() item.Text = CType(m_Reader("Product Name"), String) item.Tag = CType(m_Reader("Product ID"), Integer) args.Item = item ElseIf args.RowItemStyle.Name = "Discontinued" Then ' if TouchList asks for the discontinued flag ' then based on the current resolution we choose a proper image size Dim item As RowItemImage = New RowItemImage() If m_Resolution.Width < 2.0F Then item.TImageList = Me.tImageListQVGA Else item.TImageList = Me.tImageList End If Dim bDiscontinued As Boolean = CType(m_Reader("Discontinued"), Boolean) item.ImageIndex = IIf(bDiscontinued, 1, 0) args.Item = item ElseIf args.RowItemStyle.GetType() Is GetType(ItemStyleRectangle) Then Dim item As RowItemRectangle = New RowItemRectangle() args.Item = item End If End Sub[/vb]