software solutions for a mobile world

Perst - a database for Windows Phone 7 Silverlight - Part 2

Andy Wigley

In this post, I explain some of the key logic in the Perst sample showing how to create a database and create, delete, read and search records. You'll need to look at the full sample code that you can download from the previous post on this topic: Perst - a database for Windows Phone 7 Silverlight

Creating a Perst Database

When the app starts up, the ApplicationStartup event handler gets a reference to the IsolatedStorage for the application and looks to see if the database container file exists (the name of the container file is defined in the StorageName property of the DataGenerator class as "PerstDemoDB.dbs". If the file already exists, then the app has been run before so we just call the InitializePerstStorage method to setup the objects to access the existing database. If the file does not exist, then this is the first running of the app so no further logic executes during startup - the user has to open the menu on the main screen and tap the generate data menu item to create the database.

 private void ApplicationStartup(object sender, StartupEventArgs e)
{
   
using (var stor = IsolatedStorageFile.GetUserStoreForApplication())
    {
       
if (stor.FileExists(DataGenerator.StorageName))
        {
            InitializePerstStorage();
        }
    }
}

The InitializePerstStorage method calls the CreateStorage method of the StorageFactory singleton to create the perst persistant storage. Then it sets a couple of properties that set the initial size of the container file (on first opening) and the amount it will be auto-extended by each time it needs to grow to accomodate more objects, after which it calls the Open method to create the Perst storage container. Finally it creates a Database object that wraps the Perst storage and allows you to program it in much the same way as you would a relational database, working with tables and rows. The Database wrapper is optional, but if you program Perst using its 'vanilla' object database API, the code you write to interact with it is different from that used in this sample. If you are interested in using Perst as an object database, then read the documentation here: Tutorial and Getting Started for .NET.

internal void InitializePerstStorage()
{
   
var storage = StorageFactory.Instance.CreateStorage(); // Creating Instance of Perst Storage
   
storage.SetProperty("perst.file.extension.quantum", 512 * 1024); // Initial Size set 512KB
   
storage.SetProperty("perst.extension.quantum", 256 * 1024); // Step of storage extension 256KB
   
storage.Open(DataGenerator.StorageName, 0); // Open Storage
   
//Create Database wrapper over Perst Storage
   
Database = new Database(storage, false, true, new FullTextSearchHelper(storage));
    Database.EnableAutoIndices =
false; //Turn off auto-index creation (defined manually)
}

Creating Tables

All the code that creates the database and the records of sample data inside it are in the DataGenerator class. When using the Perst database wrapper, you don't have to explicitly create tables - you can just create an instance of a 'persistence-enabled' class and store it in the database; the table that contains it is created implicitly.

How do you create a 'persistence-enabled' class? One simple way is to have your viewmodel class inherit from the Perst.Persistent class which is what is done here. If you look at the Contact class in the ViewModels folder, you'll see that it inherits from a class called Base, which itself inherits from Persistent. You'll also notice that certain fields are marked with the [FullTextIndexable] attribute which causes them to be included in the full text search index that perst maintains. Notice that the fields have to be public which is regrettable, but you'll get runtime errors if you make them private as the Perst Database wrapper uses reflection to get at field values of data records which falls down if you make them private.

public class Contact : Base, INotifyPropertyChanged
{
   
// Fields must be public because Perst database class uses reflection to get field values
   
[FullTextIndexable]
   
public string address;
    [
FullTextIndexable]
   
public string company;
   
public string email;
    [
FullTextIndexable]
   
public string firstName;
    [
FullTextIndexable]
   
public string lastName;

   
public string Address
    {
       
get { return address; }
       
set {
            address =
value;
            InvokePropertyChanged(
new PropertyChangedEventArgs("Address"));
        }
    }

    // Lots more not shown...

    private void InvokePropertyChanged(PropertyChangedEventArgs e)
    {
       
var handler = PropertyChanged;
       
if (handler != null) handler(this, e);
    }
}

 

Saving Records in Tables

To save a record, you call the Store method of the Persistent base class. In this sample, the Base class that Contact (and the other data classes in this sample) inherit from has a method called Save that not only stores the object in the database but also updates the Full Text Search index as follows: 

public void Save()
{
    Store();
   
// Manually updating index for all fields marked with [FullTextIndexable] attribute
   
Database.UpdateFullTextIndex(this);
}
 

Deleting Records from a table

To delete a record, you call the Database.DeleteRecord method. This is found in the Deallocate method of the Base class: 

public override void Deallocate()
{
    Database.DeleteRecord(
this);
}

Getting Records from a Table

If you look at the constructor of the ContactsViewModel class, you can see two examples of how to fetch all the Contacts records from the database. The uncommented version shows how you can use the Database.Select<T> method to use T-SQL - like syntax to fetch a subset - or in this case ordered - collection of records:

public ContactsViewModel()
{
    Contacts =
new ObservableCollection<Contact>();
   
// Get all contacts from database
   
if (Database != null)
    {
       
//Contacts = Database.GetRecords<Contact>().ToObservableCollection(); // Load all contacts
       
Contacts = Database.Select<Contact>("order by LastName").ToObservableCollection();
       
// Load them but sorted
   
}
}

The commented out line is valid too, just not used here. It shows how to retrieve all the records of a certain type from the database using the Database.GetRecords<T> method.

 

Using Full Text Search

One really interesting feature of this sample is the Full Text Index search. As you can see in the code samples earlier in this post, certain fields in the Contact record are marked with the [FullTextIndexable] attribute, and when a record is stored in the database, the Database.UpdateFullTextIndex(object) record is called to keep the index up to date (you should also call this method after updating a data object).

When the user taps the Search icon on the AppBar (and yes, I know: the search should be enabled by the physical search button on the phone, but in this version of the tools there is no event fired by the phone search button), the Search box shows on the screen. The control shown there is a custom control of type AutosuggestTextBox which is a specialisation of the AutoCompleteBox control from the Silverlight Toolkit. This sample app uses the Full Text Search in two interesting ways: firstly, as the user enters characters into the search box, the code in the AutosuggestTextBox.OnPopulating method returns a list of words from the Full Text Search index that contain the characters entered. This list is then set to be the ItemsSource of the AutosuggestTextBox which determines the list of words that display in the dropdown list component of the control. The user can select a word in the list to save them typing in the full word.

SearchHere's the code from AutosuggestTextBox.cs that fetches the matching words from the index. The try catch is just a workaround for a bug in the WP7 tools CTP that was throwing an InvalidCastException at design time only which was stopping the designer working in Visual Studio:

protected override void OnPopulating(PopulatingEventArgs e)
{
   
try
   
{
       
if (((App)Application.Current).Database == null) return;

        
var kwrds = new List<string>();
        foreach (Keyword keyword in ((App)Application.Current).Database.GetKeywords(Text))
            kwrds.Add(keyword.NormalForm);
        ItemsSource = kwrds;
    }
   
catch (InvalidCastException)
    {
       
// workaround for CTP bug
   
}
}

In addition to that logic, the XAML for the AutosuggestTextBox control in MainPage.xaml hooks the TextChanged event with event handler tbSearch_SearchStringChanged which is in the codebehind in MainPage.xaml.cs. That method calls the Search method of MainPage which contais the logic to select records based on a search string. In there, it first calls the Database.SearchPrefix method which locates all objects in the database that contain words that start with the specified search string and returns a FullTextSearchResult object. It creates a new List<FullTextSearchHit> object and calls its AddRange method to 'apply' the FullTextSearchResult and the output of that is a filtered list of any documents (i.e. database objects) that contain an indexed field that starts with the search string.

The database contains Contacts, Leads and Activity records, so the final part of the Search method determines whether the matched record is indeed a Contact, and if it is adds it to the ObservableCollection<Contact> collection which we then set to be the ItemsSource of the ContactsList. The result of all this is the user sees only those Contacts that match the search string. Cool functionality for a phone app which effectively limits the typing the user has to do!

 private void tbSearch_SearchStringChanged(object sender, EventArgs e)
{
   
if (!tbSearch.IsEmpty)
        Search();
   
else
       
RefreshContacts();
}

private void Search()
{
   
if (Database == null) return;

   
// Make full-text search in DB limited to 1000 items and 4 seconds without results sorting
   
var prefixes = Database.SearchPrefix(tbSearch.Text.ToLower(), 1000, 4000, false);
   
var contacts = new ObservableCollection<Contact>();
   
var arrayRes = new List<FullTextSearchHit>();
   
if (prefixes != null) arrayRes.AddRange(prefixes.Hits);
   
foreach (var hit in arrayRes)
    {
       
if (hit.Document is Contact)
        {
           
if (!contacts.Contains((Contact)hit.Document))
                contacts.Add((
Contact)hit.Document);
        }
   
}

   
this.ContactsListBox.ItemsSource = contacts;
}

 

 

 


Posted Jun 08 2010, 12:05 PM by Andy Wigley
Copyright © 2014 APPA Mundi Limited. All Rights Reserved. Terms of Use and Privacy Policy. OrcsWeb's Windows Cloud Server Hosting