Windows Phone 7 本地数据库SQL Server CE（芒果更新）

在Windows Phone的第一个版本7.0版本里面是没有本地数据库支持的，要使用数据库只能够使用第三方的数据库组件。Windows Phone的本地数据库SQL Server CE是7.1版本即芒果更新的新特性，所以你要在应用程序中使用SQL Server CE数据库必须使用Windows Phone 7.1的API才行。

下面用一个实例演示如何使用SQL Server CE数据库。
（1）创建数据表以及数据库的数据上下文DateContent
先创建一个员工信息表，用于保存员工的名字和简介，员工表有一个自增的ID。
EmployeeTable.cs 
  1 using System.Data.Linq.Mapping;
  2 using System.ComponentModel;
  3 
  4 namespace SQLServerDemo
  5 {
  6     [Table]
  7     public class EmployeeTable : INotifyPropertyChanged, INotifyPropertyChanging
  8     {
  9         // 定义员工表独立增长ID，设置为主键
 10         private int _employeeId;
 11 
 12         [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
 13         public int EmployeeID
 14         {
 15             get
 16             {
 17                 return _employeeId;
 18             }
 19             set
 20             {
 21                 if (_employeeId != value)
 22                 {
 23                     NotifyPropertyChanging("EmployeeID");
 24                     _employeeId = value;
 25                     NotifyPropertyChanged("EmployeeID");
 26                 }
 27             }
 28         }
 29 
 30         // 定义员工名字字段
 31         private string _employeeName;
 32 
 33         [Column]
 34         public string EmployeeName
 35         {
 36             get
 37             {
 38                 return _employeeName;
 39             }
 40             set
 41             {
 42                 if (_employeeName != value)
 43                 {
 44                     NotifyPropertyChanging("EmployeeName");
 45                     _employeeName = value;
 46                     NotifyPropertyChanged("EmployeeName");
 47                 }
 48             }
 49         }
 50 
 51         //定义员工简介字段
 52         private string _employeeDesc;
 53 
 54         [Column]
 55         public string EmployeeDesc
 56         {
 57             get
 58             {
 59                 return _employeeDesc;
 60             }
 61             set
 62             {
 63                 if (_employeeDesc != value)
 64                 {
 65                     NotifyPropertyChanging("EmployeeDesc");
 66                     _employeeDesc = value;
 67                     NotifyPropertyChanged("EmployeeDesc");
 68                 }
 69             }
 70         }
 71 
 72         #region INotifyPropertyChanged Members
 73 
 74         public event PropertyChangedEventHandler PropertyChanged;
 75 
 76         //用来通知页面表的字段数据产生了改变
 77         private void NotifyPropertyChanged(string propertyName)
 78         {
 79             if (PropertyChanged != null)
 80             {
 81                 PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
 82             }
 83         }
 84 
 85         #endregion
 86 
 87         #region INotifyPropertyChanging Members
 88 
 89         public event PropertyChangingEventHandler PropertyChanging;
 90 
 91         // 用来通知数据上下文表的字段数据将要产生改变
 92         private void NotifyPropertyChanging(string propertyName)
 93         {
 94             if (PropertyChanging != null)
 95             {
 96                 PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
 97             }
 98         }
 99 
100         #endregion
101 
102     }
103 }
创建数据库的DataContent，定义一个EmployeeDataContext类来继承DataContext，在EmployeeDataContext中定义数据库连接字符串，以及员工信息表。
EmployeeDataContext.cs 
 1 using System.Data.Linq;
 2 
 3 namespace SQLServerDemo
 4 {
 5     public class EmployeeDataContext : DataContext
 6     {
 7         // 数据库链接字符串
 8         public static string DBConnectionString = "Data Source=isostore:/Employee.sdf";
 9 
10         // 传递数据库连接字符串到DataContext基类
11         public EmployeeDataContext(string connectionString)
12             : base(connectionString)
13         { }
14 
15         // 定义一个员工信息表
16         public Table<EmployeeTable> Employees;
17     }
18 }
（2）创建页面数据绑定的集合
EmployeeCollection.cs 
 1 using System.ComponentModel;
 2 using System.Collections.ObjectModel;
 3 
 4 namespace SQLServerDemo
 5 {
 6     //EmployeeCollection用于跟页面的数据绑定
 7     public class EmployeeCollection : INotifyPropertyChanged
 8     {
 9         //定义ObservableCollection来绑定页面的数据
10         private ObservableCollection<EmployeeTable> _employeeTables;
11         public ObservableCollection<EmployeeTable> EmployeeTables
12         {
13             get
14             {
15                 return _employeeTables;
16             }
17             set
18             {
19                 if (_employeeTables != value)
20                 {
21                     _employeeTables = value;
22                     NotifyPropertyChanged("EmployeeTables");
23                 }
24             }
25         }
26 
27         #region INotifyPropertyChanged Members
28 
29         public event PropertyChangedEventHandler PropertyChanged;
30 
31         //用于通知属性的改变
32         private void NotifyPropertyChanged(string propertyName)
33         {
34             if (PropertyChanged != null)
35             {
36                 PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
37             }
38         }
39         #endregion
40     }
41 }

 

（3）创建数据库，绑定数据，实现员工信息表的增删改查操作。
在App.xaml.cs中的程序加载事件中进行创建数据库 
 1 private void Application_Launching(object sender, LaunchingEventArgs e)
 2         {
 3             //如果数据库不存在则创建一个数据库
 4             using (EmployeeDataContext db = new EmployeeDataContext(EmployeeDataContext.DBConnectionString))
 5             {
 6                 if (db.DatabaseExists() == false)
 7                 {
 8                     //创建一个数据库
 9                     db.CreateDatabase();
10                 }
11             }
12         }
MainPage.xaml文件代码 
 1 <phone:PhoneApplicationPage 
 2     x:Class="SQLServerDemo.MainPage"
 3     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
 4     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
 5     xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
 6     xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
 7     xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
 8     xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
 9     mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
10     FontFamily="{StaticResource PhoneFontFamilyNormal}"
11     FontSize="{StaticResource PhoneFontSizeNormal}"
12     Foreground="{StaticResource PhoneForegroundBrush}"
13     SupportedOrientations="Portrait" Orientation="Portrait"
14     shell:SystemTray.IsVisible="True">
15 
16     <Grid x:Name="LayoutRoot" Background="Transparent">
17         <Grid.RowDefinitions>
18             <RowDefinition Height="Auto"/>
19             <RowDefinition Height="*"/>
20         </Grid.RowDefinitions>
21 
22         <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
23             <TextBlock x:Name="ApplicationTitle" Text="MY APPLICATION" Style="{StaticResource PhoneTextNormalStyle}"/>
24             <TextBlock x:Name="PageTitle" Text="SQL Server" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
25         </StackPanel>
26 
27         <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
28             <Grid Margin="0,0,0,385">
29                 <Grid.ColumnDefinitions>
30                     <ColumnDefinition Width="*" />
31                     <ColumnDefinition Width="Auto" />
32                 </Grid.ColumnDefinitions>
33                 <TextBlock FontSize="30" Height="37" HorizontalAlignment="Left" Margin="12,18,0,0" Name="textBlock1" Text="员工名字:" VerticalAlignment="Top" />
34                 <TextBox Name="name" Text=""  Margin="145,0,6,144" />
35                 
36                 <TextBlock FontSize="30" Height="52" HorizontalAlignment="Left" Margin="18,74,0,0" Name="textBlock2" Text="简介：" VerticalAlignment="Top" />
37                 <TextBox Height="79" HorizontalAlignment="Left" Margin="93,65,0,0" Name="desc" Text="" VerticalAlignment="Top" Width="357" />
38 
39                 <Button 
40                     Content="保存" x:Name="addButton"
41                     Click="addButton_Click" Margin="219,132,6,6" />
42 
43             </Grid>
44 
45             <ListBox x:Name="toDoItemsListBox" ItemsSource="{Binding EmployeeTables}" Margin="12,241,12,0" Width="440">
46                <ListBox.ItemTemplate>
47                     <DataTemplate>
48                         <Grid HorizontalAlignment="Stretch" Width="440">
49                             <Grid.ColumnDefinitions>
50                                 <ColumnDefinition Width="50" />
51                                 <ColumnDefinition Width="*" />
52                                 <ColumnDefinition Width="100" />
53                             </Grid.ColumnDefinitions>
54                         
55                             <TextBlock
56                                 Text="{Binding EmployeeName}"
57                                 FontSize="{StaticResource PhoneFontSizeLarge}"
58                                 Grid.Column="1"
59                                 VerticalAlignment="Center"/>
60                             <Button
61                                 Grid.Column="2"
62                                 x:Name="deleteButton"
63                                 BorderThickness="0"                                
64                                 Margin="0"
65                                 Click="deleteButton_Click"
66                                 Content="删除">
67                             </Button>
68                             <Button
69                                 Grid.Column="1"
70                                 x:Name="editButton"
71                                 BorderThickness="0"                                
72                                 Margin="209,0,81,0"
73                                 Click="editButton_Click"
74                                 Content="编辑" Grid.ColumnSpan="2">
75                             </Button>
76                         </Grid>
77                    </DataTemplate>
78                 </ListBox.ItemTemplate> 
79             </ListBox>   
80         </Grid>
81     </Grid>
82 
83 </phone:PhoneApplicationPage>
MainPage.xaml.cs文件代码 
  1 using System.Linq;
  2 using System.Windows;
  3 using System.Windows.Controls;
  4 using Microsoft.Phone.Controls;
  5 using System.Collections.ObjectModel;
  6 
  7 namespace SQLServerDemo
  8 {
  9     public partial class MainPage : PhoneApplicationPage
 10     {
 11         // 创建DataContext实例用于用于操作本地的数据库
 12         private EmployeeDataContext employeeDB;
 13         private EmployeeCollection employeeCol = new EmployeeCollection();
 14 
 15         public MainPage()
 16         {
 17             InitializeComponent();
 18 
 19             //连接数据库并初始化DataContext实例
 20             employeeDB = new EmployeeDataContext(EmployeeDataContext.DBConnectionString);
 21 
 22             // 使用Linq查询语句查询EmployeeTable表的所有数据
 23             var employeesInDB = from EmployeeTable employee in employeeDB.Employees
 24                                 select employee;
 25 
 26             // 将查询的结果返回到页面数据绑定的集合里面
 27             employeeCol.EmployeeTables = new ObservableCollection<EmployeeTable>(employeesInDB);
 28 
 29             //赋值给当前页面的DataContext用于数据绑定
 30             this.DataContext = employeeCol;
 31         }
 32 
 33         /// <summary>
 34         /// 删除操作
 35         /// </summary>
 36         /// <param name="sender"></param>
 37         /// <param name="e"></param>
 38         private void deleteButton_Click(object sender, RoutedEventArgs e)
 39         {
 40             // 获取单击的按钮实例
 41             var button = sender as Button;
 42 
 43             if (button != null)
 44             {
 45                 //获取当前按钮绑定的DataContext，即当前的删除的EmployeeTable实例
 46                 EmployeeTable employeeForDelete = button.DataContext as EmployeeTable;
 47                 //移除绑定集合里面要删除的EmployeeTable记录
 48                 employeeCol.EmployeeTables.Remove(employeeForDelete);
 49                 // 移除数据库里面要删除的EmployeeTable记录
 50                 employeeDB.Employees.DeleteOnSubmit(employeeForDelete);
 51                 //保存数据库的改变
 52                 employeeDB.SubmitChanges();
 53             }
 54         }
 55 
 56         /// <summary>
 57         /// 保存操作，处理新增和编辑员工信息
 58         /// </summary>
 59         /// <param name="sender"></param>
 60         /// <param name="e"></param>
 61         private void addButton_Click(object sender, RoutedEventArgs e)
 62         {
 63             //控制员工名字和简介不能为空
 64             if (name.Text != "" && desc.Text != "")
 65             {
 66                 if (State.Count>0 && State["employee"] != null )//编辑状态
 67                 {
 68                     //获取编辑的EmployeeTable对象
 69                     EmployeeTable employee = (EmployeeTable)State["employee"];
 70                     employee.EmployeeName = name.Text;
 71                     employee.EmployeeDesc = desc.Text;
 72                     //保存数据库的改变
 73                     employeeDB.SubmitChanges();
 74                     //添加绑定集合的数据,因为在单击编辑的时候移除了
 75                     employeeCol.EmployeeTables.Add(employee);
 76                     State["employee"] = null;
 77                 }
 78                 else//新增状态
 79                 {
 80                     //创建一条表的数据
 81                     EmployeeTable newEmployee = new EmployeeTable { EmployeeName = name.Text, EmployeeDesc = desc.Text };
 82                     //添加绑定集合的数据
 83                     employeeCol.EmployeeTables.Add(newEmployee);
 84                     //插入数据库
 85                     employeeDB.Employees.InsertOnSubmit(newEmployee);
 86                     //保存数据库的改变
 87                     employeeDB.SubmitChanges();
 88                 }
 89                 
 90                 name.Text = "";
 91                 desc.Text = "";
 92             }
 93             else
 94             {
 95                 MessageBox.Show("姓名和简介不能为空！");
 96             }
 97         }
 98 
 99         /// <summary>
100         /// 编辑操作
101         /// </summary>
102         /// <param name="sender"></param>
103         /// <param name="e"></param>
104         private void editButton_Click(object sender, RoutedEventArgs e)
105         {
106             // 获取单击的按钮实例
107             var button = sender as Button;
108 
109             if (button != null)
110             {
111                 //获取当前按钮绑定的DataContext，即当前的编辑的EmployeeTable实例
112                 EmployeeTable employeeForEdit = button.DataContext as EmployeeTable;
113                 name.Text = employeeForEdit.EmployeeName;
114                 desc.Text = employeeForEdit.EmployeeDesc;
115                 //将需要编辑的表实例存储在State里面
116                 State["employee"] = employeeForEdit;
117                 employeeCol.EmployeeTables.Remove(employeeForEdit);
118             }
119         }
120     }
121 }
posted @ 2012-06-19 11:41  ╰→劉じ尛鶴  阅读(168)  评论(0)  编辑  收藏  举报