Sterling Database for Silverlight and Windows Phone 7

Jeremy Likness http://www.wintellect.com/cs/blogs/jlikness/default.aspx has put together a sweet little database engine for Silverlight and Windows Phone 7 called "Sterling". You can find this up on Codeplex.com here: http://sterling.codeplex.com/

I know that Jeremy has been working on this for some time now, and it looks like it has matured sufficiently to be quite usable.  I put together a little "test" Silverlight app to use it, and I'm sharing the sample code here.

Going back to the paradigm of a database of famous quotations that I have used in other articles, I decided to use a delimited text file of 1001 quotations that can be loaded by the user from a FileDialog.

The app then parses each line into a Quotation class instance, and saves them all to a Sterling database in Isolated Storage. An index is created on LastName + FirstName in the table definition, and I issue a LINQ query by LastName and display the result.

First, the Quotation Class:

using System;
using System.Globalization;
using System.Net;
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;

namespace SterlingSL
{  
    public class Quotation
    {
         public int Id { get; set; }
        public string AuthorFirstName { get; set; }
         public string AuthorLastName { get; set; }
         public string AuthorInfo { get; set; }
         public string QuotationText { get; set; }
         public string Subject { get; set; }

         public Quotation()
        {
         }

         public Quotation(int id, string authorFirstName, string authorLastName, string authorInfo, string quotation, string subject)
         {
             this.Id = id;
             this.AuthorFirstName = authorFirstName;
             this.AuthorLastName = authorLastName;
             this.AuthorInfo = authorInfo;
             this.QuotationText = quotation;
             this.Subject = subject;
        }
        
         public override string ToString()
        {
            return string.Format(CultureInfo.InvariantCulture,
                 "{0}|{1}|{2}|{3}|{4}|{5}",
                Id,
                AuthorFirstName ?? string.Empty,
                AuthorLastName ?? string.Empty,
                AuthorInfo ?? String.Empty, QuotationText, Subject ?? String.Empty);
        }
    }
}

Next, we have my required Database class:

using System;
using System.Collections.Generic;
using System.Net;
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 Wintellect.Sterling.Database;

namespace SterlingSL
{
    public class QuotesDatabase : BaseDatabaseInstance
    {
         public const string LAST_NAME_FIRSTNAME = "LastNameFirstName";
        public override string Name { get { return "Quotations"; } }
        
         protected override List<ITableDefinition> _RegisterTables()
         {
             return new List<ITableDefinition>
                       {
                           CreateTableDefinition<Quotation, int>(c => c.Id).WithIndex<Quotation, string, string, int>
                               (LAST_NAME_FIRSTNAME , c => Tuple.Create(c.AuthorLastName, c.AuthorFirstName))
                       };
        }
    }
}


All the rest of the code is done "inline" in my Silverlight button click handler that lets you load the "quotes.txt" from the root of the web app. I realize this isn't very object - oriented, but it is useful for a demo because you can see exactly what needs to be done in the exact order necessary:

public partial class MainPage : UserControl
    {
         private string dbText = String.Empty;
        private List<Quotation> Quotes = new List<Quotation>();
        private int MaxId = 0;
        public MainPage()
        {
             InitializeComponent();
        }

         private void button1_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Multiselect = false;
            dlg.Filter = "Text Files (*.txt)|*.txt";
            bool? retval = dlg.ShowDialog();

             //Load the quotes.txt file
            FileInfo info = dlg.File;
            Stream s = info.OpenRead();
            byte[] b = new byte[s.Length];
             s.Read(b, 0, b.Length);
            dbText = System.Text.UTF8Encoding.UTF8.GetString(b,0,b.Length );
             s.Close();

             // Parse the quotes.txt file into Quotation objects
            string[] lines = dbText.Split('\r');
            foreach (string line in lines)
             {
                 if (line == "\n") continue;
                string tmpLine = line.TrimEnd('\n');
                string[] sp = tmpLine.Split('|');
                var q = new Quotation
                            {
                                Id = int.Parse(sp[0]),
                                AuthorFirstName = sp[1],
                                AuthorLastName = sp[2],
                                AuthorInfo = sp[3],
                                QuotationText = sp[4],
                                Subject = sp[5]
                              };
                 Quotes.Add(q);
            }
            MaxId = lines.Length;
             MessageBox.Show("Loaded " + MaxId.ToString() +". Please wait while records are saved.");

            SterlingEngine engine = new SterlingEngine();
            engine.Activate();
            ISterlingDatabaseInstance db = engine.SterlingDatabase.RegisterDatabase<QuotesDatabase>();
            // clean out any previous "Stuff":
          db.Truncate(typeof(Quotation));
            int ctr=0;

             //Save all the quotations to the database:
            foreach (Quotation q in Quotes)
             {
                 try
                 {
                     db.Save(q);
                     ctr++;
                 }
                 catch
                 {  // Just in case...
                     MessageBox.Show("stored " + ctr.ToString() + " objects.");
                 }
             }
             MessageBox.Show("stored " + ctr.ToString() + " objects.");
          
             // Query from Database by LastName in index:
            var q1 =
                db.Query<Quotation, string, string, int>(QuotesDatabase.LAST_NAME_FIRSTNAME).Where(
                    x => x.Index.Item1 == "Field").FirstOrDefault();
             // Assemble the display message text:
            string stuff ="ID: "+q1.LazyValue.Value.Id.ToString( ) + "\r\n" + q1.LazyValue.Value.AuthorFirstName + " " + q1.LazyValue.Value.AuthorLastName + "\r\n";
            stuff += q1.LazyValue.Value.AuthorInfo + "\r\n";
            stuff += q1.LazyValue.Value.Subject + "\r\n";
            stuff += q1.LazyValue.Value.QuotationText;
            MessageBox.Show(stuff);
             engine.Dispose();
        }
    }

    We allow the user to load the quotes.txt file, then we read the file and parse each line into a Quotation class instance, which is added to a List<Quotation> colllection.

    Then we create a new Sterling engine and Activate it.

    Then we register the database with the table definition and index.

    I Truncate any previous work, and then loop over the collection calling db.Save on each Quotation.

    Finally, I issue a LINQ query using the Index for the first quotation with LastName = "Field", and I display the result.

If you have any Isolated Storage problems with this, you probably need to request a quota increase. To "clean everything out", just right-click on the Silverlight app, choose the Application Storage tab, and delete the store for the app.

I wouldn't exactly categorize Sterling as a "speed champion", but if you want a decent IsolatedStorage database engine that is lightweight and LINQ - enabled for your Silverlight or Windows Phone 7 apps, I'd say Sterling is a pretty good choice. Queries, especially with an index, seem to be pretty fast.

You can download the Visual Studio 2010 Silverlight 4 Solution, which includes all the source for the Sterling engine here.

By Peter Bromberg   Popularity  (5085 Views)