内容预告:
- Windows Phone 的数据库支持
- LINQ to SQL
- 性能和最佳实践
LINQ to Everything:
支持复杂的结构:
支持外键:
WebService缓存:
本地存储:
架构:
对象:
定义表:
// Define the tables in the database
[Table]
public class Wine : INotifyPropertyChanged, INotifyPropertyChanging
{
private string wineID;
private string name;
[Column(IsPrimaryKey=true)]
public string WineID
{
get { return wineID; }
set {
InvokePropertyChanging(new PropertyChangingEventArgs("WineID"));
wineID = value;
InvokePropertyChanged(new PropertyChangedEventArgs("WineID"));
}
}
[Column]
public string Name { ... }
...}
定义数据上下文:
// Define the data context. public partial class WineDataContext : DataContext { public Table<Wine> Wines; public Table<Vineyard> Vineyards; public WineDataContext(string connection) : base(connection) { } } ... // Create the database from data context, using a connection string DataContext db = new WineDataContext("isostore:/wineDB.sdf"); if (!db.DatabaseExists()) db.CreateDatabase();
用SQLMetal代码生成工具:
c:\>Sqlmetal /code:northwindEntities.cs
/context:NorthwindDataContext
/pluralize northwind.sdf
查询:
// Create the database form data context, using a connection string
DataContext db = new WineDataContext("isostore:/wineDB.sdf"); // Find all wines currently at home, ordered by date acquired
var q = from w in db.Wines
where w.Varietal.Name == “Shiraz” && w.IsAtHome == true
orderby w.DateAcquired select w;
插入,更新,删除:别忘了submitChanges
插入:
Wine newWine = new Wine { WineID = “1768", Name = “Windows Phone Syrah", Description = “Bold and spicy" }; db.Wines.InsertOnSubmit(newWine); db.SubmitChanges();
更新:
Wine wine = (from w in db.Wines where w.WineID == “1768" select w).First(); wine.Description = “Hints of plum and melon"; db.SubmitChanges();
删除:
var vineyardsToDelete = from Vineyards v in db.Vineyards where v.Country == “Australia” select v; db.Vineyards.DeleteAllOnSubmit (vineyardsToDelete); db.SubmitChanges();
更新数据库结构:
WineDataContext wineDC = new WineDataContext(App.WineDBConnectionString); DatabaseSchemaUpdater dsu = wineDC.CreateDatabaseSchemaUpdater(); if (dsu.DatabaseSchemaVersion == 1) { dsu.AddColumn<Wine>("BottleType"); dsu.DatabaseSchemaVersion = 2; dsu.Execute(); }
性能和最佳实践:
- 保持修改的集合很小,换句话说,尽早提交修改,以避免程序终止时数据丢失。
- 用后台线程。
- 优化只读查询。
- 提前填充大量数据。
- 用对的工具,大量复杂的数据用数据库,小数据用独立存储。