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才行。

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, AutoSyncAutoSync = 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. } 

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

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

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 newnewEmployee = 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. } 

运行效果如下：

本文转自linzheng 51CTO博客，原文链接:http://blog.51cto.com/linzheng/1078591