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.