A Database for the Windows Phone

The purpose of this demonstration is to introduce the embedded Pyrrho engine that is now available for the Windows Phone 7. This uses Web local storage on the client device to hold any databases used.
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to PhoneOSP.dll. This document contains step-by-step instructions to build this complete application. You will need Windows Phone developer tools, which you can get from Microsoft
and the PhoneOSP.dll from the Pyrrho distribution.
The new project
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight for Windows Phone entry under Visual C#, ensure that .NET Framework 4 is selected, and select Windows Phone Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
The Xaml Markup

2. Position the mouse cursor just after “LayoutRoot”, and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment=”Top”.
3. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> . Type an opening pointy bracket < and from the IntelliSense dropdown, double-click StackPanel. Type a closing pointy bracket >.
4. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click ListBox. Change the Height attribute of the ListBox to 350 and the Width to 400.
5. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
6. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
7. Move the cursor to just before the first </StackPanel>, and double-click TextBox from the Toolbox. Set the width property to 240.
8. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
9. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
The Shopping class
10. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add PhoneOSP as an Existing Project…., and browse in to PhoneOSP\PhoneOSP to open PhoneOSP.csproj.
11. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 10, use the Projects tab to add PhoneOSP. If not, Browse to add PhoneOSP.dll.
12. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
13. Change the Shopping.cs file to contain the following:

using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using Pyrrho;

namespace WindowsPhoneShopping
{
    public class Shopping
    {
        public PyrrhoConnect conn = null;
        MainPage main = null;

        public Shopping(MainPage page)
        {
            main = page;
            FillData();
        }
        void FillData()
        {
            try
            {
                conn = new PyrrhoConnect("Files=Shopping");
                conn.Open();
                PyrrhoCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from Items";
                PyrrhoReader rdr = null;
                try
                {
                    rdr = cmd.ExecuteReader();
                }
                catch (Exception)
                {
                    rdr = null;
                }
                if (rdr == null)
                {
                    conn.Act("create table Items(Name char primary key)");
                }
                else
                {
                    while (rdr.Read())
                    {
                        main.listBox1.Items.Add(rdr.GetString(0));
                    }
                    rdr.Close();
                }
            }
            catch (Exception) { }
        }
        public void Refresh()
        {
            main.listBox1.Items.Clear();
            FillData();
        }
    }

}

Check this still runs as before. Close the browser window.
Adding code to add and delete items
14. In the MainPage.xaml markup, place the mouse cursor in the Grid element. In the Properties window, select the Events tab, and double-click the Loaded event. The MainPage.xaml.cs file opens and you should add the following lines to the LayoutRoot_Loaded event handler:

shopping = new Shopping(this);
            textBox1.Text = "";

15. Still in the MainPage.xaml.cs file, position the mouse cursor just inside the first curly bracket, and enter

Shopping shopping = null;

16. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):

try
    {
        shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
        shopping.Refresh();
        textBox1.Text = "";
     }
     catch (Exception) { }

17. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:

try
     {
         var it = listBox1.SelectedItem as string;
         if (it != null)
            shopping.conn.Act("delete from Items where name='" + it + "'");
         shopping.Refresh();
      }
      catch (Exception) { }

18. The application should now run.