Getting Started with Sterling Database in Silverlight

</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.



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"
});



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));

});



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.