Creating a Filtered List of Customers on a Mobile Device

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Resco.Controls.AdvancedList;
using System.Data.SqlServerCe;
using System.Reflection;
using System.IO;

namespace CustomerList
{
    public partial class Form1 : Form
    {
        string m_sPath;
        string m_sConnectionString;
        SqlCeConnection m_Connection;
        SqlCeCommand m_Command;

        private void DatabaseInit()
        {
            if (m_Command != null)
            {
                m_Command.Dispose();
            }

            if (m_Connection != null)
            {
                m_Connection.Dispose();
                m_Connection.Close();
            }
            
            m_Connection = new SqlCeConnection(m_sConnectionString);
            m_Command = new SqlCeCommand("SELECT ContactName, Address, City, Country FROM customers", m_Connection);
        }

        public Form1()
        {
            InitializeComponent();

            // find out path to this application on the device
            m_sPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
            // create a connection string
            m_sConnectionString = "Data source = " + m_sPath + "\\Northwind.sdf;";
            DatabaseInit();
        }

        private void TextBox1_TextChanged(object sender, EventArgs e)
        {
            // remove all rows from AdvancedList
            this.advancedList1.DataRows.Clear();

            // Create a new SQL CE command
            m_Command.CommandText = "SELECT ContactName, Address, City, Country FROM customers WHERE ContactName LIKE '%" + textBox1.Text + "%'";
            // Reload the data.
            this.advancedList1.DbConnector.Command = m_Command;
            this.advancedList1.LoadData();
            // Highlight the input text in AdvancedList's TextCells.
            (this.advancedList1.Templates[0][0] as TextCell).SelectedText = this.textBox1.Text;
            (this.advancedList1.Templates[1][0] as TextCell).SelectedText = this.textBox1.Text;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // set focus to TextBox1 on Form1 load.
            this.textBox1.Focus();
            try
            {
                this.advancedList1.DbConnector.Command = m_Command;
                this.advancedList1.LoadData();
            }
            catch (SqlCeException ex)
            {
                // If there is an exception, display the error message and exit
                MessageBox.Show(ex.Message);
                this.Close();
            }
        }

        private void Form1_Closed(object sender, EventArgs e)
        {
            // If the form is closed, close the SqlCe connection.
            m_Connection.Close();
        }

        private void TextBox1_KeyDown(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.Down:
                    if (this.advancedList1.DataRows.Count - 1 > this.advancedList1.ActiveRowIndex)
                        this.advancedList1.ActiveRowIndex += 1;
                    e.Handled = true;
                    break;
                case Keys.Up:
                    if (this.advancedList1.ActiveRowIndex > 0)
                        this.advancedList1.ActiveRowIndex -= 1;
                    e.Handled = true;
                    break;
            }
        }

        private void mnuSimple_Click(object sender, EventArgs e)
        {
            this.advancedList1.LoadXml(m_sPath + "\\SimpleRows.xml");
            (this.advancedList1.Templates[0][0] as TextCell).SelectedText = this.textBox1.Text;
            (this.advancedList1.Templates[1][0] as TextCell).SelectedText = this.textBox1.Text;
            DatabaseInit();
        }

        private void mnuDetailed_Click(object sender, EventArgs e)
        {
            this.advancedList1.LoadXml(m_sPath + "\\DetailedRows.xml");
            (this.advancedList1.Templates[0][0] as TextCell).SelectedText = this.textBox1.Text;
            (this.advancedList1.Templates[1][0] as TextCell).SelectedText = this.textBox1.Text;
            DatabaseInit();
        }
    }
}