Silverlight本地数据库解决方案之使用Sterling数据库
2011-10-18 11:52 zhoujie 阅读(2042) 评论(2) 编辑 收藏 举报Sterling数据库是一个NoSQL数据库，支持linq，是一个Codeplex上的开源项目:http://sterling.codeplex.com 。
可以为.Net, Silverlight 和 Windows Phone提供本地存储方案，由Jeremy Likeness开发。它使用Isolated Storage存储数据。详情可参考这里 here.
我正在编写一个可以同时运行于windows和mac上的OOB的Silverlight应用程序。我选择了Sterling而不是SQL Server，因为只有用户安装了Silverlight插件，Sterling就可以工作，只需要引用Wintellect.Sterling.dll。
本文通过一个简单的示例演示如何从Sterling数据库保存、获取和删除数据. 此示例类似于购物车的逻辑。
首先创建数据库，Sterling中数据库其实只是一个继承于BaseDatabaseInstance的一个Class。
然后，我们定义数据表。数据表也是一些Class，这里我们定义了4个表。对象导向的数据库，表等同于类，表的字段等同于类的属性。
下面是我们使用的数据库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)
};
}
}
}
Sterling数据库由SterlingEngine管理. 我们定义了 "SterlingService"类包装SterlingEngine来在应用程序的生命周期中提供数据库服务。
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);
}
}
}
Starting() 方法激活Sterling引擎，然后注册数据库DemoDB。可以创建并注册多个数据库。
将SterlingService添加到<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>
下面我们创建View和ViewModel：
第一页包含两个组件,一个ComboBox显示customers列表，一个ListBox显示products列表。选择products和customers，点击place order按钮，可以添加一个订单Order
我们先在数据库中创建一些测试数据。调用DataBase的Save方法可以向数据表中添加数据。
SterlingService.Current.Database.Save(new Customer()
{
CustomerID = 1,
CustomerName = "A",
ShippingAddress = "No 9, A road, A Area, A-59"
});
由于Sterling没有自动+1的字段，我们只能先通过Query方法查出OrderID的最大值，然后+1。
if((from o inSterlingService.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>() 范型参数同Save方法。
我们创建一个 OrderDetailsPage页显示订单详情，下面是ListBox的模板定义：
<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>
这个列表框的数据上下文是DisplayOrderDetails的ViewModel：
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; }
}
通过join连接Orders表和Customer表，返回DisplayOrderDetails对象。
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();
其中LstProducts属性通过连接OrderDetails表和Product表，Linq查询得到。
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));
});
当点击Place Order按钮或者Previous Order按钮时导航到OrderDetailsPage页。
OrderDetailsPage显示用户订单详情，点击Order Done按钮执行Delete操作，从数据表中删除。
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();
}
同Save方法一样简单，将要删除的Order对象传给Delete方法即可。
结论: Sterling是一个快速，轻量级并且跨平台的Silverlight数据库。
英文原文：http://www.c-sharpcorner.com/UploadFile/kavithakgowd/7496/
