与众不同 windows phone (7) - Local Database(本地数据库)
作者:webabcd
介绍
与众不同 windows phone 7.5 (sdk 7.1) 之本地数据库
- 概述
- 演示如何使用“本地数据库”
示例
1、概述
Summary.xaml
<phone:PhoneApplicationPage x:Class="Demo.LocalDatabase.Summary" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone" xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" FontFamily="{StaticResource PhoneFontFamilyNormal}" FontSize="{StaticResource PhoneFontSizeNormal}" Foreground="{StaticResource PhoneForegroundBrush}" SupportedOrientations="Portrait" Orientation="Portrait" mc:Ignorable="d" d:DesignHeight="768" d:DesignWidth="480" shell:SystemTray.IsVisible="True"> <Grid x:Name="LayoutRoot" Background="Transparent"> <TextBlock TextWrapping="Wrap"> <Run>本地数据库概述</Run> <LineBreak /> <LineBreak /> <Run>1、App 创建数据库时,其文件会被保存到独立存储;程序包内数据库只能被读取,如果需要更新它,则必须把其复制到独立存储后再操作</Run> <LineBreak /> <Run>2、数据库结构发生改变时优先使用 DatabaseSchemaUpdater 来更新数据库结构;数据迁移是下策</Run> <LineBreak /> <Run>3、只读场景下建议将 DataContext 的 ObjectTrackingEnabled 设置为 false(因为只读时不需要对象跟踪),从而关闭对象跟踪以减小内存使用量</Run> <LineBreak /> <Run>4、在多线程操作本地数据库的场景下,建议使用互斥锁,即 System.Threading.Mutex</Run> </TextBlock> </Grid> </phone:PhoneApplicationPage>
2、使用“本地数据库”的 Demo
Model层 - ModelBase.cs
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 System.ComponentModel; using System.Data.Linq.Mapping; using System.Data.Linq; namespace Demo.LocalDatabase.Model { public class ModelBase : INotifyPropertyChanged, INotifyPropertyChanging { // 实现 INotifyPropertyChanged 是为了属性变更后的通知 public event PropertyChangedEventHandler PropertyChanged; protected void NotifyPropertyChanged(string propertyName) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } // 实现 INotifyPropertyChanging 是为了最大限度地减少内存使用量(NotifyPropertyChanging 的用法:在属性赋值之前调用,具体可见 Category 或 Product) /* * 为什么会减少内存使用量呢? * 因为 LINQ to SQL 更改跟踪是通过维护每个对象的两个副本进行工作的,第一个副本保存原始数据,第二个副本有程序更改,这样提交更新时 LINQ to SQL 就知道哪些数据被更改了,从而只提交这些被更改的数据 * INotifyPropertyChanging 接口允许应用程序在将修改后的数据提交到数据库前通知 DataContext,DataContext 可以将该通知用作创建副本的触发器,这样就不用保留第二个副本了,从而减少内存使用 */ public event PropertyChangingEventHandler PropertyChanging; protected void NotifyPropertyChanging(string propertyName) { if (PropertyChanging != null) { PropertyChanging(this, new PropertyChangingEventArgs(propertyName)); } } } }
Model层 - Category.cs
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 System.Data.Linq.Mapping; using System.Data.Linq; namespace Demo.LocalDatabase.Model { /* * Table - 将类标记为数据库中的一个表 * Index - 把类中的指定字段标记为索引字段 */ [Table] public class Category : ModelBase { // 版本列,可以显著改进表的更新性能 [Column(IsVersion = true)] private Binary _version; private int _categoryId; /* * Column - 将属性标记为数据表中的一个字段 * IsPrimaryKey - 是否是主键 * IsDbGenerated - 数据是否由数据库自动生成,如自增列 * DbType = "INT NOT NULL Identity" - int类型,不能为null,标识列 * CanBeNull - 是否可以为 null * AutoSync = AutoSync.OnInsert - 标记此值的作用是:当数据添加完成后,此属性的值会自动同步为数据库自增后的值 */ [Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true, IsPrimaryKey = true)] public int CategoryId { get { return _categoryId; } set { NotifyPropertyChanging("CategoryId"); _categoryId = value; NotifyPropertyChanged("CategoryId"); } } private string _name; [Column] public string Name { get { return _name; } set { NotifyPropertyChanging("Name"); _name = value; NotifyPropertyChanged("Name"); } } private EntitySet<Product> _products; /* * Association - 用于标记表之间的关联关系 * Storage - 指定用于保存关联数据的私有字段。本例中类型为 EntitySet<T> 的私有字段 _products 用于保存关联数据,本例中所谓的关联数据就是 Category 下的 Products * ThisKey - 关联数据在本表中所对应的 key 字段 * OtherKey - 关联数据在他表中所对应的 key 字段 * IsForeignKey - 是否是外键 */ [Association(Storage = "_products", ThisKey = "CategoryId", OtherKey = "_categoryId")] public EntitySet<Product> Products { get { return this._products; } set { /* * Assign() - 将一个 EntitySet<T> 赋值给另一个 EntitySet<T> */ // 将 value 赋值给 _products this._products.Assign(value); } } // 指定 _products 做添加和删除操作时的关联操作 public Category() { _products = new EntitySet<Product> ( new Action<Product>(this.attach), new Action<Product>(this.detach) ); } // _products 添加 Product 时的关联操作 private void attach(Product product) { NotifyPropertyChanging("Product"); product.Category = this; } // _products 删除 Product 时的关联操作 private void detach(Product product) { NotifyPropertyChanging("Product"); product.Category = null; } } }
Model层 - Product.cs
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 System.Data.Linq.Mapping; using System.Data.Linq; using Microsoft.Phone.Data.Linq.Mapping; namespace Demo.LocalDatabase.Model { /* * Table - 将类标记为数据库中的一个表 * Index - 把类中的指定字段标记为索引字段 */ [Table] [Index(Columns = "Name ASC, ProductId DESC", Name="MyIndex")] public class Product : ModelBase { // 版本列,可以显著改进表的更新性能 [Column(IsVersion = true)] private Binary _version; private int _productId; /* * Column - 将属性标记为数据表中的一个字段 * IsPrimaryKey - 是否是主键 * IsDbGenerated - 数据是否由数据库自动生成,如自增列 * DbType = "INT NOT NULL Identity" - int类型,不能为null,标识列 * CanBeNull - 是否可以为 null * AutoSync = AutoSync.OnInsert - 标记此值的作用是:当数据添加完成后,此属性的值会自动同步为数据库自增后的值 */ [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)] public int ProductId { get { return _productId; } set { if (_productId != value) { NotifyPropertyChanging("ProductId"); _productId = value; NotifyPropertyChanged("ProductId"); } } } private string _name; [Column] public string Name { get { return _name; } set { if (_name != value) { NotifyPropertyChanging("Name"); _name = value; NotifyPropertyChanged("Name"); } } } private double _price; [Column] public double Price { get { return _price; } set { if (_price != value) { NotifyPropertyChanging("Price"); _price = value; NotifyPropertyChanged("Price"); } } } [Column] internal int _categoryId; private EntityRef<Category> _category; /* * Association - 用于标记表之间的关联关系 * Storage - 指定用于保存关联数据的私有字段。本例中类型为 EntityRef<T> 的私有字段 _category 用于保存关联数据,本例中所谓的关联数据就是 Product 所属的 Category * ThisKey - 关联数据在本表中所对应的 key 字段 * OtherKey - 关联数据在他表中所对应的 key 字段 * IsForeignKey - 是否是外键 */ [Association(Storage = "_category", ThisKey = "_categoryId", OtherKey = "CategoryId", IsForeignKey = true)] public Category Category { get { return _category.Entity; } set { NotifyPropertyChanging("Category"); // 更新 Storage 以及 ThisKey _category.Entity = value; if (value != null) _categoryId = value.CategoryId; NotifyPropertyChanging("Category"); } } } }
Model层 - MyContext.cs
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 System.Data.Linq; namespace Demo.LocalDatabase.Model { public class MyContext : DataContext { public MyContext(string connectionString) : base(connectionString) { } public Table<Product> Products; public Table<Category> Categories; } }
ViewModel层 - MyViewModel.cs
/* * 连接字符串设置 * 1、data source - 本地数据库文件地址 * 示例:data source=isostore:/database.sdf;appdata:/ 代表程序包内,isostore:/ 代表独立存储,默认为独立存储 * 2、pwd - 密码 * 3、max buffer size - 最大内存使用量(保存到磁盘之前会使用内存),默认值为 384,最大值为 5120,单位为 KB * 4、max database size - 数据库文件的最大大小,默认值为 32,最大值为 512,单位为 MB * 5、file mode - 操作数据库文件时的模式 * Read Write - 可读写,默认值 * Read Only - 只读 * Exclusive - 不允许其他进程打开或修改数据库 * Shared Read - 数据库打开后,允许其他进程读取,但不允许其他进程修改 * 6、Culture Identifier - 区域代码,如中国大陆地区是 zh-CN(创建数据库时此属性才有用) * 7、Case Sensitive - 排序时是否区分大小写,默认值为 false(创建数据库时此属性才有用) */ 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 System.ComponentModel; using Demo.LocalDatabase.Model; using Microsoft.Phone.Data.Linq; using System.Collections.ObjectModel; using System.Linq; using System.Data.Linq; namespace Demo.LocalDatabase.ViewModel { public class MyViewModel : INotifyPropertyChanged { private MyContext _context; public MyViewModel() { DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Category>(p => p.Products); dlo.AssociateWith<Category>(p => p.Products.OrderByDescending(x => x.Price)); _context = new MyContext("Data Source=isostore:/database.sdf"); _context.LoadOptions = dlo; Init(); Categories = new ObservableCollection<Category>(_context.Categories.ToList()); } public void AddProduct(Product product) { var category = Categories.Single(p => p.CategoryId == product.Category.CategoryId); category.Products.Add(product); _context.Products.InsertOnSubmit(product); _context.SubmitChanges(); } public void DeleteProduct(Product product) { var category = Categories.Single(p => p.CategoryId == product.Category.CategoryId); category.Products.Remove(product); _context.Products.DeleteOnSubmit(product); _context.SubmitChanges(); } public void Init() { if (!_context.DatabaseExists()) { _context.CreateDatabase(); DatabaseSchemaUpdater dbUpdater = _context.CreateDatabaseSchemaUpdater(); dbUpdater.DatabaseSchemaVersion = 0; dbUpdater.Execute(); Category c1 = new Category { Name = "肉" }; c1.Products.Add(new Product { Name = "牛肉", Price = 35.5 }); c1.Products.Add(new Product { Name = "羊肉", Price = 38 }); Category c2 = new Category { Name = "水果" }; c2.Products.Add(new Product { Name = "苹果", Price = 2.5 }); c2.Products.Add(new Product { Name = "香蕉", Price = 3.2 }); Category c3 = new Category { Name = "蔬菜" }; c3.Products.Add(new Product { Name = "菠菜", Price = 2.2 }); c3.Products.Add(new Product { Name = "白菜", Price = 1.3 }); _context.Categories.InsertOnSubmit(c1); _context.Categories.InsertOnSubmit(c2); _context.Categories.InsertOnSubmit(c3); _context.SubmitChanges(); } else { /* * DatabaseSchemaUpdater - 数据库结构更新器 * DatabaseSchemaVersion - 数据库结构的版本 * Execute() - 更新数据库结构和版本号 * * 数据库结构有改变时,用以下方法更新不同版本的数据库结构 * AddColumn<T>(string columnPropertyName) - 为表 T 添加列 columnPropertyName * AddIndex<T>(string indexName) - 为表 T 添加索引 indexName * AddTable<T>() - 为数据库添加表 T * AddAssociation<T>(string associationPropertyName) - 为表 T 添加数据库关联 associationPropertyName */ DatabaseSchemaUpdater dbUpdater = _context.CreateDatabaseSchemaUpdater(); if (dbUpdater.DatabaseSchemaVersion == 10) { dbUpdater.AddColumn<Product>("UpdateTime"); dbUpdater.DatabaseSchemaVersion = 11; dbUpdater.Execute(); } } } private ObservableCollection<Category> _categories; public ObservableCollection<Category> Categories { get { return _categories; } set { _categories = value; NotifyPropertyChanged("Categories"); } } public event PropertyChangedEventHandler PropertyChanged; private void NotifyPropertyChanged(string propertyName) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } } }
View层 - App.xaml
<Application x:Class="Demo.App" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone" xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone" xmlns:localDatabaseViewModel="clr-namespace:Demo.LocalDatabase.ViewModel"> <Application.Resources> <localDatabaseViewModel:MyViewModel x:Key="LocalDatabaseViewModel" /> </Application.Resources> </Application>
View层 - NewProduct.xaml
<phone:PhoneApplicationPage x:Class="Demo.LocalDatabase.NewProduct" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone" xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" FontFamily="{StaticResource PhoneFontFamilyNormal}" FontSize="{StaticResource PhoneFontSizeNormal}" Foreground="{StaticResource PhoneForegroundBrush}" SupportedOrientations="Portrait" Orientation="Portrait" mc:Ignorable="d" d:DesignHeight="696" d:DesignWidth="480" shell:SystemTray.IsVisible="True" xmlns:toolkit="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Toolkit" DataContext="{Binding Source={StaticResource LocalDatabaseViewModel}}"> <Grid x:Name="LayoutRoot" Background="Transparent"> <StackPanel Orientation="Vertical"> <TextBlock Text="Product Name"/> <TextBox x:Name="txtProductName"/> <TextBlock Text="Product Price"/> <TextBox x:Name="txtProductPrice"/> <TextBlock Text="Product Category"/> <toolkit:ListPicker x:Name="listPickerCategory" ItemsSource="{Binding Categories}" DisplayMemberPath="Name" /> </StackPanel> </Grid> <phone:PhoneApplicationPage.ApplicationBar> <shell:ApplicationBar Mode="Default" IsVisible="True"> <shell:ApplicationBarIconButton x:Name="btnConfirm" IconUri="/ApplicationBarDemo/Assets/appbar.check.rest.png" Text="确认" Click="btnConfirm_Click" /> <shell:ApplicationBarIconButton x:Name="btnCancel" IconUri="/ApplicationBarDemo/Assets/appbar.cancel.rest.png" Text="取消" Click="btnCancel_Click" /> </shell:ApplicationBar> </phone:PhoneApplicationPage.ApplicationBar> </phone:PhoneApplicationPage>
View层 - NewProduct.xaml.cs
using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Windows; using System.Windows.Controls; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Animation; using System.Windows.Shapes; using Microsoft.Phone.Controls; using Demo.LocalDatabase.ViewModel; using Demo.LocalDatabase.Model; namespace Demo.LocalDatabase { public partial class NewProduct : PhoneApplicationPage { public NewProduct() { InitializeComponent(); } private void btnConfirm_Click(object sender, EventArgs e) { double price = 0; if (!double.TryParse(txtProductPrice.Text, out price)) { MessageBox.Show("价格必须是 double 型"); return; } var vm = Application.Current.Resources["LocalDatabaseViewModel"] as MyViewModel; Product product = new Product() { Name = txtProductName.Text, Price = double.Parse(txtProductPrice.Text), Category = (Category)listPickerCategory.SelectedItem }; vm.AddProduct(product); NavigationService.Navigate(new Uri("/LocalDatabase/Demo.xaml", UriKind.Relative)); } private void btnCancel_Click(object sender, EventArgs e) { } } }
View层 - Demo.xaml
<phone:PhoneApplicationPage x:Class="Demo.LocalDatabase.Demo" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone" xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" FontFamily="{StaticResource PhoneFontFamilyNormal}" FontSize="{StaticResource PhoneFontSizeNormal}" Foreground="{StaticResource PhoneForegroundBrush}" SupportedOrientations="Portrait" Orientation="Portrait" mc:Ignorable="d" d:DesignHeight="696" d:DesignWidth="480" shell:SystemTray.IsVisible="True" DataContext="{Binding Source={StaticResource LocalDatabaseViewModel}}" xmlns:controls="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls"> <phone:PhoneApplicationPage.Resources> <DataTemplate x:Key="item"> <ListBox ItemsSource="{Binding Products}"> <ListBox.ItemTemplate> <DataTemplate> <Grid Width="460" HorizontalAlignment="Center"> <Grid.ColumnDefinitions> <ColumnDefinition Width="5*" /> <ColumnDefinition Width="5*" /> </Grid.ColumnDefinitions> <StackPanel Orientation="Horizontal" VerticalAlignment="Center"> <TextBlock Text="{Binding Name}" /> <TextBlock Text="{Binding Price}" Margin="10 0 0 0" /> </StackPanel> <StackPanel Orientation="Horizontal" Grid.Column="1" HorizontalAlignment="Right"> <Button x:Name="btnDelete" Content="删除" Click="btnDelete_Click" /> </StackPanel> </Grid> </DataTemplate> </ListBox.ItemTemplate> </ListBox> </DataTemplate> <DataTemplate x:Key="header"> <TextBlock Text="{Binding Name}" /> </DataTemplate> </phone:PhoneApplicationPage.Resources> <Grid x:Name="LayoutRoot" Background="Transparent"> <controls:Pivot x:Name="pivot" Title="产品列表" ItemTemplate="{StaticResource item}" HeaderTemplate="{StaticResource header}" ItemsSource="{Binding Categories}"> </controls:Pivot> </Grid> <phone:PhoneApplicationPage.ApplicationBar> <shell:ApplicationBar Mode="Default" IsVisible="True"> <shell:ApplicationBarIconButton x:Name="btnAddProduct" IconUri="/ApplicationBarDemo/Assets/appbar.add.rest.png" Text="添加产品" Click="btnAddProduct_Click" /> </shell:ApplicationBar> </phone:PhoneApplicationPage.ApplicationBar> </phone:PhoneApplicationPage>
View层 - Demo.xaml.cs
using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Windows; using System.Windows.Controls; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Animation; using System.Windows.Shapes; using Microsoft.Phone.Controls; using Demo.LocalDatabase.ViewModel; using Demo.LocalDatabase.Model; namespace Demo.LocalDatabase { public partial class Demo : PhoneApplicationPage { public Demo() { InitializeComponent(); } private void btnAddProduct_Click(object sender, EventArgs e) { NavigationService.Navigate(new Uri("/LocalDatabase/NewProduct.xaml", UriKind.Relative)); } private void btnDelete_Click(object sender, RoutedEventArgs e) { var vm = Application.Current.Resources["LocalDatabaseViewModel"] as MyViewModel; vm.DeleteProduct((sender as Button).DataContext as Product); } } }
OK
[源码下载]