I you remember my blogpost from a very long time ago, I wrote about using SQL CE with Windows Phone (you can find it here). SQL CE can be queried with LINQ to SQL, making it interesting for storing relational data. Recently I came in contact with the Sterling-database (check it here), an Object-database which can be used in .NET, Silverlight and Windows Phone ! You can add “SterlingPhone” to your project by using NuGet. Just like SQL CE I can use Sterling for storing data in Isolated Storage.
I’ll show you how to use it with a “fake” Northwind-database, but it can also be used for persisting full Viewmodels when using MVVM. In my fake Northwind-database I will use Products and Categories, so I simply start by creating classes for these two entities :
1: public class Product
2: {
3: public int ProductId { get; set; }
4:
5: public string ProductName { get; set; }
6:
7: public double? UnitPrice { get; set; }
8:
9: public Category Category { get; set; }
10: }
11:
12: public class Category
13: {
14: public int CategoryId { get; set; }
15:
16: public string CategoryName { get; set; }
17:
18: public List<Product> Products { get; set; }
19: }
These classes are pure and simple POCO-classes, no specific classes to inherit from or a bunch of attributes to use.
Second step is to create our “database-class”, which inherits from BaseDatabaseInstance :
public class FakeNorthWindDatabase : BaseDatabaseInstance
In here I will register my tables by overriding RegisterTables :
protected override System.Collections.Generic.List<ITableDefinition> RegisterTables()
{
return new List<ITableDefinition>()
{
this.CreateTableDefinition<Product,int>(p=>p.ProductId)
.WithIndex<Product,Category,int>("IX_ProductCategory",p=>p.Category),
this.CreateTableDefinition<Category,int>(c=>c.CategoryId)
};
}
In here I create the table Category and Products, which are respectively using the CategoryId and ProductId-properties as identifiers. The WithIndex-option creates a relationship between these two entities.
This database also needs to be registered. I do that in the Application_Launching:
1: private SterlingEngine engine;
2: private SterlingDefaultLogger logger;
3:
4: public static ISterlingDatabaseInstance DB { get; set; }
5:
6: private void Application_Launching(object sender, LaunchingEventArgs e)
7: {
8: InitDB();
9: }
10:
11: private void InitDB()
12: {
13: engine = new SterlingEngine();
14: engine.SterlingDatabase.RegisterSerializer<DefaultSerializer>();
15: engine.Activate();
16:
17: DB = engine.SterlingDatabase.RegisterDatabase<FakeNorthWindDatabase>();
18: FakeNorthWindDatabase.Init(DB);
19: }
One of the things we need to do is to specify which serializer will be used for writing and reading data. At this moment we only have the DefaultSerializer and AggregateSerializer, but you could create your own classes here, as long as they inherit from BaseSerializer.
At the end I call the init-method of the FakeNorthWindDatabase, which I implemented as follows :
1: internal static void Init(Wintellect.Sterling.ISterlingDatabaseInstance DB)
2: {
3: if (!DB.Query<Category,int>().Any())
4: {
5: Category c1 = new Category() { CategoryId=1, CategoryName = "Beverages"};
6: Category c2 = new Category() { CategoryId = 2, CategoryName = "Cheese" };
7: Category c3 = new Category() { CategoryId = 3, CategoryName = "Seafood" };
8:
9: Product p11 = new Product() { ProductId = 1, ProductName = "WestVleteren 12",
10: UnitPrice = 3, Category = c1 };
11: Product p12 = new Product() { ProductId = 2, ProductName = "Rochefort 10",
12: UnitPrice = 3, Category = c1 };
13: Product p13 = new Product() { ProductId = 3, ProductName = "Maredsous 8",
14: UnitPrice = 2.5, Category = c1 };
15:
16: Product p21 = new Product() { ProductId = 4, ProductName = "Maredsous",
17: UnitPrice = 3, Category = c2 };
18: Product p22 = new Product() { ProductId = 5, ProductName = "Gouda",
19: UnitPrice = 3, Category = c2 };
20: Product p23 = new Product() { ProductId = 6, ProductName = "Cambozola",
21: UnitPrice = 2.5, Category = c2 };
22:
23: Product p31 = new Product() { ProductId = 7, ProductName = "Shrimps",
24: UnitPrice = 3, Category = c3 };
25: Product p32 = new Product() { ProductId = 8, ProductName = "Tuna Fish",
26: UnitPrice = 3, Category = c3 };
27: Product p33 = new Product() { ProductId = 9, ProductName = "Calamares",
28: UnitPrice = 2.5, Category = c3 };
29:
30: DB.Save(c1);
31: DB.Save(c2);
32: DB.Save(c3);
33:
34: DB.Save(p11);
35: DB.Save(p12);
36: DB.Save(p13);
37: DB.Save(p21);
38: DB.Save(p22);
39: DB.Save(p23);
40: DB.Save(p31);
41: DB.Save(p32);
42: DB.Save(p33);
43: }
44: }
This code first checks if there is already categories present, and if not starts filling the database with some sample-data. We can now use our data: let’s simply show the name of a Category. The Sterling-database has a load-function that can be passed the key-value of the entity to find :
1: private void button1_Click(object sender, RoutedEventArgs e)
2: {
3: Category c= App.DB.Load<Category>(1);
4: MessageBox.Show(c.CategoryName);
5: }
Simple. Retrieving a list is also simple :
listBox1.ItemsSource = App.DB.Query<Category, int>().Select(c => c.LazyValue.Value);
The select-method uses a delegate that takes a TableKey<Category,int> and returns an object, in this case the Category-entity. By using LINQ I can get the products for a specific category :
int catId = Convert.ToInt32(NavigationContext.QueryString["cat"]);
listBox1.ItemsSource = (from p in App.DB.Query<Product, int>()
where p.LazyValue.Value.Category.CategoryId == catId
select p.LazyValue.Value).ToList();
Let’s add a new Category and product :
1: Category c = new Category()
2: {
3: CategoryId = 4,
4: CategoryName = "Condiments"
5: };
6:
7: Product p = new Product()
8: {
9: ProductId = 10,
10: ProductName = "Aniseed Syrup",
11: UnitPrice = 12.2,
12: Category = c
13: };
14:
15: App.DB.Save<Product>(p);
Saving the product also saves the category. Save checks the Id-field to know if it should do an update or an insert. Unfortunately there’s no autonumber-functionality, but Sterling also allows me to create triggers, with which I could create autonumber-functionality myself.
This blogpost just scratches the surface of Sterling, there’s lots more fun stuff you can do with it, so check it out if you need to store persistant data.