The Sterling Database is an Object-Oriented NoSQL database for .Net, Silverlight and Windows Phone developed by Jeremy Likeness. It uses Isolated Storage to store the data. For more details visit the official Sterling database website here.
The project I was working on required a Silverlight Out-Of-Browser application that is able to run on both Windows and Mac. We used a Sterling database instead of SQL Server. No other software such as Mono was required for the application to run on the Mac except Silverlight. The Sterling database project is a Silverlight project which can run on any OS with Silverlight installed.
Objective: A simple demonstration Silverlight project to save, retrieve and delete data from a Sterling database. This application just stores, retrieves and deletes the information of a customer who has placed an order for a list of products.
Open VS 2010 and create a new Silverlight Application and name it "SterlingDemoProject".
We have to add a project called Wintellect.Sterling to our solution. Wintellect.Sterling is a project by Jeremy for Silverlight applications which manage database functions. The Wintellect.Sterling project is available with this sample project. To add the project File->Add->Existing Project and select the location of Wintellect.Sterling.csproj. Then make the SterlingDemoProject refer to the Wintellect.Sterling project.
Now we have to create the database. For using a Sterling database we have to create a class which defines the tables and implements the interface BaseDatabaseInstance.
Let's create the table definitions. Tables are nothing but class definitions for Sterling databases. Add a folder called Tables to the SterlingDemoProject. The class definitions are as shown. There are 4 classes Customer, Product, Order and OrderDetails.
![Sterling1.gif]()
Add a folder named Database to the SterlingDemoProject. Add a class called "DemoDB" to the Database folder. Inherit and implement the abstract class "BaseDatabaseInstance" from Wintellect.Sterling.Database. The _RegisterTables method returns a list of Tables (class definitions) for the Database DemoDB.
using System;
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;
using System.Collections.Generic;
using SterlingDemoProject.Tables;
namespace SterlingDemoProject.Database
{
public class DemoDB : BaseDatabaseInstance
{
public override string Name
{
get { return "Purchase Database"; }
}
protected override System.Collections.Generic.List<ITableDefinition> _RegisterTables()
{
return new List<ITableDefinition>
{
CreateTableDefinition<Customer,int>(c=>c.CustomerID),
CreateTableDefinition<Order,int>(c=>c.OrderID),
CreateTableDefinition<OrderDetails,int>(c=>c.OrderDetailsID),
CreateTableDefinition<Product,int>(c=>c.ProductID)
};
}
}
}
In Sterling a database class can be equated to a table of a normal database and properties in the class can be equated to columns in a table of a normal database.
A Sterling database is managed by the SterlingEngine. It is an engine which exists throughout the application lifecycle. Add the class named "SterlingService". It's functionality is to activate the Sterling engine, register serializers, register databases and dispose the engine when the application is closed.
using System;
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;
using System.ComponentModel;
using System.Diagnostics;
using SterlingDemoProject.Database;
namespace SterlingDemoProject
{
public sealed class SterlingService : IApplicationService, IApplicationLifetimeAware, IDisposable
{
private SterlingEngine _engine;
public static SterlingService Current { get; private set; }
public ISterlingDatabaseInstance Database { get; private set; }
private SterlingDefaultLogger _logger;
public void StartService(ApplicationServiceContext context)
{
if (DesignerProperties.IsInDesignTool) return;
_engine = new SterlingEngine();
Current = this;
}
public void StopService()
{ return; }
public void Starting()
{
if (DesignerProperties.IsInDesignTool) return;
_engine.Activate();
Database = _engine.SterlingDatabase.RegisterDatabase<DemoDB>();
}
public void Started()
{ return; }
public void Exiting()
{
if (DesignerProperties.IsInDesignTool) return;
if (Debugger.IsAttached && _logger != null)
{
_logger.Detach();
}
}
public void Exited()
{
Dispose();
_engine = null;
return;
}
public void Dispose()
{
if (_engine != null)
{
_engine.Dispose();
}
GC.SuppressFinalize(this);
}
}
}
In the Starting() method of the SterlingService class the Sterling engine is activated and the database DemoDB is registered. We can define more than one database and register it in this method.
Now we have to add this class in the app.xaml's application lifetime objects section. Create a namespace Sterling which refers to the project and add the SterlingService class in <Application.ApplicationLifetimeObjects>.
<Application xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
x:Class="SterlingDemoProject.App"
xmlns:Sterling="clr-namespace:SterlingDemoProject"
>
<Application.Resources>
</Application.Resources>
<Application.ApplicationLifetimeObjects>
<Sterling:SterlingService/>
</Application.ApplicationLifetimeObjects>
</Application>
Database creation and Sterling Engine activation is now done; now we can create the views and viewmodel.
The first page contains mainly two components, a ComboBox with a list of customers and a ListBox with a list of products. Users can select products and customers and place an order as shown.
![Sterling2.gif]()
Let's insert some dummy customers into the database. In the constructor of the Viewmodel I am saving a few customers. The syntax for saving is:
SterlingService.Current.Database.Save(new Customer()
{
CustomerID = 1,
CustomerName = "A",
ShippingAddress = "No 9, A road, A Area, A-59"
});
![Sterling3.gif]()
To save an object we just have to pass the object; the type of object is determined by the Sterling database using reflection. The type of object passed should be in the list of tables defined for DemoDB.
When the customer is selected and the order is placed an entry in the Order table is made for the customer and new entries for each selected product is made in the OrderDetails table. Since the Sterling DB doesn't support auto-key generation we have to determine the recent id and increment it by 1 for a new entry.
if((from o in SterlingService.Current.Database.Query<Order, int>() select o.LazyValue.Value).Count() >= 1)
{
newOrder.OrderID = (from o in SterlingService.Current.Database.Query<Order, int>() select o.LazyValue.Value.OrderID).Max() +
1;
}
else
{
newOrder.OrderID = 1;
}
SterlingService.Current.Database.Query<Order, int>() queries the table Order(in <Order,int> where int is the type of key for order table).
Now let's create one more page called OrderDetailsPage which has a listbox of template:
<ListBox ItemsSource="{Binding CustomerOrderDetails,Mode=TwoWay}" x:Name="lstOrderList" HorizontalAlignment="Center" VerticalAlignment="Top" Grid.Row="1" Width="400" Height="500" Grid.Column="1">
<ListBox.ItemTemplate>
<DataTemplate>
<Grid>
<Grid.RowDefinitions>
<RowDefinition />
<RowDefinition />
<RowDefinition />
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="0.9*"/>
<ColumnDefinition />
</Grid.ColumnDefinitions>
<TextBlock Grid.Row="0" Text="{Binding Description}"/>
<ListBox Grid.Row="1" ItemsSource="{Binding LstProducts}">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding ProductName}" Margin="5"/>
<TextBlock Text="{Binding Price}" Margin="5"/>
<TextBlock Text="{Binding Quantity}" Margin="5"/>
<TextBlock Text="{Binding Amount}" Margin="5"/>
<!--<TextBlock Text="{Binding}" Margin="5"/>-->
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
<StackPanel Grid.Row="2" Orientation="Horizontal">
<TextBlock Text="Total Amount:"/>
<TextBlock Text="{Binding TotalAmount}"/>
</StackPanel>
<Button x:Name="btnDelete" Margin="5" Width="100" Tag="{Binding OrderId}" Content="Order Done" Click="btnDelete_Click" Grid.Column="1" Grid.Row="0" />
</Grid>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
The listbox is populated with items of type DisplayOrderDetails which is a class in OrderVM namespace to display data from querying. The DisplayOrderDetails class has the following fields:
public class DisplayOrderDetails : INotifyPropertyChanged
{
public int OrderId { get; set; }
public double TotalAmount { get; set; }
public string Description { get; set; }
List<Product> _lstProducts;
public List<Product> LstProducts
{
get
{
return _lstProducts;
}
set
{
_lstProducts = value;
OnPropertyChanged("LstProducts");
}
}
public DateTime OrderDate { get; set; }
public int CustomerID { get; set; }
public string CustomerName { get; set; }
}
When the Place Order button or the Previous Order button is clicked from the first page it navigates to the OrderDetailsPage. A join operation on the Orders and Customer table is made and the required fields for the DisplayOrderDetails class is selected as in:
CustomerOrderDetails = (from o in SterlingService.Current.Database.Query<Order, int>()
join c in SterlingService.Current.Database.Query<Customer, int>()
on o.LazyValue.Value.CustomerID equals c.LazyValue.Value.CustomerID
select new DisplayOrderDetails()
{
Description = o.LazyValue.Value.Description,
OrderDate = o.LazyValue.Value.OrderDate,
OrderId = o.LazyValue.Value.OrderID,
CustomerID = c.LazyValue.Value.CustomerID,
CustomerName = c.LazyValue.Value.CustomerName
}).ToList();
The list of products for each Order is queried by a join operation on OrderDetails table and Product table as in:
CustomerOrderDetails.ForEach(c =>
{
c.LstProducts = new List<Product>();
c.LstProducts = (from od in SterlingService.Current.Database.Query<OrderDetails, int>()
join p in SterlingService.Current.Database.Query<Product, int>()
on od.LazyValue.Value.ProductID equals p.LazyValue.Value.ProductID
where od.LazyValue.Value.OrderID == c.OrderId
select new Product() {
Price = p.LazyValue.Value.Price,
Quantity = od.LazyValue.Value.Quantity,
ProductID = od.LazyValue.Value.ProductID,
ProductName = p.LazyValue.Value.ProductName
}).ToList();
c.LstProducts.ForEach(p =>
c.TotalAmount = c.TotalAmount + (p.Price * p.Quantity));
});
![Sterling4.gif]()
The OrderDetailsPage page shows a list of orders along with products. When the Order Done button is clicked the selected order is deleted from the table.
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
Button btnBox = sender as Button;
(this.DataContext as OrderVM).DeleteSelectedOrder(int.Parse(btnBox.Tag.ToString()));
}
public void DeleteSelectedOrder(int orderid)
{
Order deleteOrder = (from o in SterlingService.Current.Database.Query<Order,int>()
where o.LazyValue.Value.OrderID == orderid select o.LazyValue.Value).FirstOrDefault();
if (deleteOrder != null)
{
SterlingService.Current.Database.Delete(deleteOrder);
SterlingService.Current.Database.Flush();
}
List<OrderDetails> lstOrderDetails = (from o in SterlingService.Current.Database.Query<OrderDetails, int>() where o.LazyValue.Value.OrderID == orderid select o.LazyValue.Value).ToList();
lstOrderDetails.ForEach(o =>
{
SterlingService.Current.Database.Delete(o);
});
GetAllOrderDetails();
}
Deleting a row is as simple as saving; we just have to pass the object. Every row is uniquely identified by the key of the table.
Conclusion: A Sterling database is a fast light-weight database which can work on any platform with the Silverlight plugin installed.