iPhone-like Database GUI on Windows Mobile

Imports System.Data.SqlServerCe
Imports BeeMobile
Imports System.IO ' make sure to import the proper namespaces
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)
        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        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


    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

    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 ' 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

    Private Sub ApplyRowStyles()
        Dim row As Row
        For i As Integer = 0 To i < Me.TouchList1.AllRowsCount
            If i Mod 2 = 0 Then
                row = New Row(0, 2)
            Else
                row = New Row(1, 2)
            End If

            row.RowIndex = i
            Me.TouchList1.Rows.Add(row)
        Next
    End Sub

    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 flasg
            ' 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

    Private Sub SearchBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchBox1.TextChanged
        m_OldIndex = -1
        LoadItems(SearchBox1.Text)
    End Sub

    Private Sub tvbExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tvbExit.Click
        Me.Close()
    End Sub
End Class