Using a Sterling-database in Windows Phone as alternative to SQL CE 25 April 2012 Michael-Van-Wesemael Windows Phone, databases, Sterling 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: } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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 .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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) }; } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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: } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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: } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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: } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Simple. Retrieving a list is also simple : listBox1.ItemsSource = App.DB.Query<Category, int>().Select(c => c.LazyValue.Value); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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(); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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. Follow @PiekenPuil !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); Tweet