Windows Phone 7 本地数据库SQL Server CE(芒果更新)-阿里云开发者社区

开发者社区> 开发与运维> 正文

Windows Phone 7 本地数据库SQL Server CE(芒果更新)

简介:

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

下面用一个实例演示如何使用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 = trueIsDbGenerated = trueDbType = "INT NOT NULL Identity"CanBeNull = falseAutoSyncAutoSync = 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.     }  

 

创建数据库的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.     }  
(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.     }  
(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 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.     }  

运行效果如下:

 

 


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


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章