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 the top of this page or also from Bee Mobile web at
http://beemobile4.net/?mod=samples&productID=11.
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 different people 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 Imports System.Reflection
Public Class Form1
Dim m_sPath As String Dim m_Connection As SqlCeConnection Dim m_Reader As SqlCeDataReader Dim m_OldIndex As Integer Dim m_Resolution As New SizeF(1.0F, 1.0F)
Sub New()
Row.DefaultNormalRowStyle = 0 Row.DefaultSelectedRowStyle = 2
m_sPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase)
InitializeComponent()
m_Resolution = DetectResolution() m_OldIndex = -1 End Sub
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()
TouchList1.AllRowsCount = CType(cmdCount.ExecuteScalar(), Integer)
ApplyRowStyles()
TouchList1.SelectedIndex = 0 TouchList1.JumpToTheTop() TouchList1.ForceRefresh() 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
m_OldIndex = args.RowIndex
m_Reader.Read()
ElseIf args.RowItemStyle.Name = "EnglishName" Then
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
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
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