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


 
 

  
  using System.Data.Linq.Mapping;  

  
  using System.ComponentModel;  

  
   

  
  namespace SQLServerDemo  

  
  {  

  
      [Table]  

  
      public class EmployeeTable : INotifyPropertyChanged, INotifyPropertyChanging  

  
      {  

  
          // 定义员工表独立增长ID，设置为主键  

  
          private int _employeeId;  

  
   

  
          [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSyncAutoSync = AutoSync.OnInsert)]  

  
          public int EmployeeID  

  
          {  

  
              get  

  
              {  

  
                  return _employeeId;  

  
              }  

  
              set  

  
              {  

  
                  if (_employeeId != value)  

  
                  {  

  
                      NotifyPropertyChanging("EmployeeID");  

  
                      _employeeId = value;  

  
                      NotifyPropertyChanged("EmployeeID");  

  
                  }  

  
              }  

  
          }  

  
   

  
          // 定义员工名字字段  

  
          private string _employeeName;  

  
   

  
          [Column]  

  
          public string EmployeeName  

  
          {  

  
              get  

  
              {  

  
                  return _employeeName;  

  
              }  

  
              set  

  
              {  

  
                  if (_employeeName != value)  

  
                  {  

  
                      NotifyPropertyChanging("EmployeeName");  

  
                      _employeeName = value;  

  
                      NotifyPropertyChanged("EmployeeName");  

  
                  }  

  
              }  

  
          }  

  
   

  
          //定义员工简介字段  

  
          private string _employeeDesc;  

  
   

  
          [Column]  

  
          public string EmployeeDesc  

  
          {  

  
              get  

  
              {  

  
                  return _employeeDesc;  

  
              }  

  
              set  

  
              {  

  
                  if (_employeeDesc != value)  

  
                  {  

  
                      NotifyPropertyChanging("EmployeeDesc");  

  
                      _employeeDesc = value;  

  
                      NotifyPropertyChanged("EmployeeDesc");  

  
                  }  

  
              }  

  
          }  

  
   

  
          #region INotifyPropertyChanged Members  

  
   

  
          public event PropertyChangedEventHandler PropertyChanged;  

  
   

  
          //用来通知页面表的字段数据产生了改变  

  
          private void NotifyPropertyChanged(string propertyName)  

  
          {  

  
              if (PropertyChanged != null)  

  
              {  

  
                  PropertyChanged(this, new PropertyChangedEventArgs(propertyName));  

  
              }  

  
          }  

  
   

  
          #endregion  

  
   

  
          #region INotifyPropertyChanging Members  

  
   

  
          public event PropertyChangingEventHandler PropertyChanging;  

  
   

  
          // 用来通知数据上下文表的字段数据将要产生改变  

  
          private void NotifyPropertyChanging(string propertyName)  

  
          {  

  
              if (PropertyChanging != null)  

  
              {  

  
                  PropertyChanging(this, new PropertyChangingEventArgs(propertyName));  

  
              }  

  
          }  

  
   

  
          #endregion  

  
   

  
      }  

  
  }

创建数据库的DataContent，定义一个 EmployeeDataContext 类来继承 DataContext ，在 EmployeeDataContext 中定义数据库连接字符串，以及员工信息表。

EmployeeDataContext.cs


 
 

  
  using System.Data.Linq;  

  
   

  
  namespace SQLServerDemo  

  
  {  

  
      public class EmployeeDataContext : DataContext  

  
      {  

  
          // 数据库链接字符串  

  
          public static string DBConnectionString = "Data Source=isostore:/Employee.sdf";  

  
   

  
          // 传递数据库连接字符串到DataContext基类  

  
          public EmployeeDataContext(string connectionString)  

  
              : base(connectionString)  

  
          { }  

  
   

  
          // 定义一个员工信息表  

  
          public Table<EmployeeTable> Employees;  

  
      }  

  
  }

（2）创建页面数据绑定的集合

EmployeeCollection.cs


 
 

  
  using System.ComponentModel;  

  
  using System.Collections.ObjectModel;  

  
   

  
  namespace SQLServerDemo  

  
  {  

  
      //EmployeeCollection用于跟页面的数据绑定  

  
      public class EmployeeCollection : INotifyPropertyChanged  

  
      {  

  
          //定义ObservableCollection来绑定页面的数据  

  
          private ObservableCollection<EmployeeTable> _employeeTables;  

  
          public ObservableCollection<EmployeeTable> EmployeeTables  

  
          {  

  
              get  

  
              {  

  
                  return _employeeTables;  

  
              }  

  
              set  

  
              {  

  
                  if (_employeeTables != value)  

  
                  {  

  
                      _employeeTables = value;  

  
                      NotifyPropertyChanged("EmployeeTables");  

  
                  }  

  
              }  

  
          }  

  
   

  
          #region INotifyPropertyChanged Members  

  
   

  
          public event PropertyChangedEventHandler PropertyChanged;  

  
   

  
          //用于通知属性的改变  

  
          private void NotifyPropertyChanged(string propertyName)  

  
          {  

  
              if (PropertyChanged != null)  

  
              {  

  
                  PropertyChanged(this, new PropertyChangedEventArgs(propertyName));  

  
              }  

  
          }  

  
          #endregion  

  
      }  

  
  }

（3）创建数据库，绑定数据，实现员工信息表的增删改查操作。

在App.xaml.cs中的程序加载事件中进行创建数据库


 
 

  
  private void Application_Launching(object sender, LaunchingEventArgs e)  

  
          {  

  
              //如果数据库不存在则创建一个数据库  

  
              using (EmployeeDataContext db = new EmployeeDataContext(EmployeeDataContext.DBConnectionString))  

  
              {  

  
                  if (db.DatabaseExists() == false)  

  
                  {  

  
                      //创建一个数据库  

  
                      db.CreateDatabase();  

  
                  }  

  
              }  

  
          }

MainPage.xaml文件代码


 
 

  
  <phone:PhoneApplicationPage   

  
      x:Class="SQLServerDemo.MainPage" 

  
      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" 

  
      mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768" 

  
      FontFamily="{StaticResource PhoneFontFamilyNormal}" 

  
      FontSize="{StaticResource PhoneFontSizeNormal}" 

  
      Foreground="{StaticResource PhoneForegroundBrush}" 

  
      SupportedOrientations="Portrait" Orientation="Portrait" 

  
      shell:SystemTray.IsVisible="True"> 

  
   

  
      <Grid x:Name="LayoutRoot" Background="Transparent"> 

  
          <Grid.RowDefinitions> 

  
              <RowDefinition Height="Auto"/> 

  
              <RowDefinition Height="*"/> 

  
          </Grid.RowDefinitions> 

  
   

  
          <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28"> 

  
              <TextBlock x:Name="ApplicationTitle" Text="MY APPLICATION" Style="{StaticResource PhoneTextNormalStyle}"/> 

  
              <TextBlock x:Name="PageTitle" Text="SQL Server" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/> 

  
          </StackPanel> 

  
   

  
          <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0"> 

  
              <Grid Margin="0,0,0,385"> 

  
                  <Grid.ColumnDefinitions> 

  
                      <ColumnDefinition Width="*" /> 

  
                      <ColumnDefinition Width="Auto" /> 

  
                  </Grid.ColumnDefinitions> 

  
                  <TextBlock FontSize="30" Height="37" HorizontalAlignment="Left" Margin="12,18,0,0" Name="textBlock1" Text="员工名字:" VerticalAlignment="Top" /> 

  
                  <TextBox Name="name" Text=""  Margin="145,0,6,144" /> 

  
                    

  
                  <TextBlock FontSize="30" Height="52" HorizontalAlignment="Left" Margin="18,74,0,0" Name="textBlock2" Text="简介：" VerticalAlignment="Top" /> 

  
                  <TextBox Height="79" HorizontalAlignment="Left" Margin="93,65,0,0" Name="desc" Text="" VerticalAlignment="Top" Width="357" /> 

  
   

  
                  <Button   

  
                      Content="保存" x:Name="addButton" 

  
                      Click="addButton_Click" Margin="219,132,6,6" /> 

  
   

  
              </Grid> 

  
   

  
              <ListBox x:Name="toDoItemsListBox" ItemsSource="{Binding EmployeeTables}" Margin="12,241,12,0" Width="440"> 

  
                 <ListBox.ItemTemplate> 

  
                      <DataTemplate> 

  
                          <Grid HorizontalAlignment="Stretch" Width="440"> 

  
                              <Grid.ColumnDefinitions> 

  
                                  <ColumnDefinition Width="50" /> 

  
                                  <ColumnDefinition Width="*" /> 

  
                                  <ColumnDefinition Width="100" /> 

  
                              </Grid.ColumnDefinitions> 

  
                            

  
                              <TextBlock 

  
                                  Text="{Binding EmployeeName}" 

  
                                  FontSize="{StaticResource PhoneFontSizeLarge}" 

  
                                  Grid.Column="1" 

  
                                  VerticalAlignment="Center"/> 

  
                              <Button 

  
                                  Grid.Column="2" 

  
                                  x:Name="deleteButton" 

  
                                  BorderThickness="0"                                  

  
                                  Margin="0" 

  
                                  Click="deleteButton_Click" 

  
                                  Content="删除"> 

  
                              </Button> 

  
                              <Button 

  
                                  Grid.Column="1" 

  
                                  x:Name="editButton" 

  
                                  BorderThickness="0"                                  

  
                                  Margin="209,0,81,0" 

  
                                  Click="editButton_Click" 

  
                                  Content="编辑" Grid.ColumnSpan="2"> 

  
                              </Button> 

  
                          </Grid> 

  
                     </DataTemplate> 

  
                  </ListBox.ItemTemplate>   

  
              </ListBox>     

  
          </Grid> 

  
      </Grid> 

  
   

  
  </phone:PhoneApplicationPage>

MainPage.xaml.cs文件代码


 
 

  
  using System.Linq;  

  
  using System.Windows;  

  
  using System.Windows.Controls;  

  
  using Microsoft.Phone.Controls;  

  
  using System.Collections.ObjectModel;  

  
   

  
  namespace SQLServerDemo  

  
  {  

  
      public partial class MainPage : PhoneApplicationPage  

  
      {  

  
          // 创建DataContext实例用于用于操作本地的数据库  

  
          private EmployeeDataContext employeeDB;  

  
          private EmployeeCollection employeeCol = new EmployeeCollection();  

  
   

  
          public MainPage()  

  
          {  

  
              InitializeComponent();  

  
   

  
              //连接数据库并初始化DataContext实例  

  
              employeeDB = new EmployeeDataContext(EmployeeDataContext.DBConnectionString);  

  
   

  
              // 使用Linq查询语句查询EmployeeTable表的所有数据  

  
              var employeesInDB = from EmployeeTable employee in employeeDB.Employees  

  
                                  select employee;  

  
   

  
              // 将查询的结果返回到页面数据绑定的集合里面  

  
              employeeCol.EmployeeTables = new ObservableCollection<EmployeeTable>(employeesInDB);  

  
   

  
              //赋值给当前页面的DataContext用于数据绑定  

  
              this.DataContext = employeeCol;  

  
          }  

  
   

  
          /// <summary> 

  
          /// 删除操作  

  
          /// </summary> 

  
          /// <param name="sender"></param> 

  
          /// <param name="e"></param> 

  
          private void deleteButton_Click(object sender, RoutedEventArgs e)  

  
          {  

  
              // 获取单击的按钮实例  

  
              var button = sender as Button;  

  
   

  
              if (button != null)  

  
              {  

  
                  //获取当前按钮绑定的DataContext，即当前的删除的EmployeeTable实例  

  
                  EmployeeTable employeeForDelete = button.DataContext as EmployeeTable;  

  
                  //移除绑定集合里面要删除的EmployeeTable记录  

  
                  employeeCol.EmployeeTables.Remove(employeeForDelete);  

  
                  // 移除数据库里面要删除的EmployeeTable记录  

  
                  employeeDB.Employees.DeleteOnSubmit(employeeForDelete);  

  
                  //保存数据库的改变  

  
                  employeeDB.SubmitChanges();  

  
              }  

  
          }  

  
   

  
          /// <summary> 

  
          /// 保存操作，处理新增和编辑员工信息  

  
          /// </summary> 

  
          /// <param name="sender"></param> 

  
          /// <param name="e"></param> 

  
          private void addButton_Click(object sender, RoutedEventArgs e)  

  
          {  

  
              //控制员工名字和简介不能为空  

  
              if (name.Text != "" && desc.Text != "")  

  
              {  

  
                  if (State.Count>0 && State["employee"] != null )//编辑状态  

  
                  {  

  
                      //获取编辑的EmployeeTable对象  

  
                      EmployeeTable employee = (EmployeeTable)State["employee"];  

  
                      employee.EmployeeName = name.Text;  

  
                      employee.EmployeeDesc = desc.Text;  

  
                      //保存数据库的改变  

  
                      employeeDB.SubmitChanges();  

  
                      //添加绑定集合的数据,因为在单击编辑的时候移除了  

  
                      employeeCol.EmployeeTables.Add(employee);  

  
                      State["employee"] = null;  

  
                  }  

  
                  else//新增状态  

  
                  {  

  
                      //创建一条表的数据  

  
                      EmployeeTable newnewEmployee = new EmployeeTable { EmployeeName = name.Text, EmployeeDesc = desc.Text };  

  
                      //添加绑定集合的数据  

  
                      employeeCol.EmployeeTables.Add(newEmployee);  

  
                      //插入数据库  

  
                      employeeDB.Employees.InsertOnSubmit(newEmployee);  

  
                      //保存数据库的改变  

  
                      employeeDB.SubmitChanges();  

  
                  }  

  
                    

  
                  name.Text = "";  

  
                  desc.Text = "";  

  
              }  

  
              else  

  
              {  

  
                  MessageBox.Show("姓名和简介不能为空！");  

  
              }  

  
          }  

  
   

  
          /// <summary> 

  
          /// 编辑操作  

  
          /// </summary> 

  
          /// <param name="sender"></param> 

  
          /// <param name="e"></param> 

  
          private void editButton_Click(object sender, RoutedEventArgs e)  

  
          {  

  
              // 获取单击的按钮实例  

  
              var button = sender as Button;  

  
   

  
              if (button != null)  

  
              {  

  
                  //获取当前按钮绑定的DataContext，即当前的编辑的EmployeeTable实例  

  
                  EmployeeTable employeeForEdit = button.DataContext as EmployeeTable;  

  
                  name.Text = employeeForEdit.EmployeeName;  

  
                  desc.Text = employeeForEdit.EmployeeDesc;  

  
                  //将需要编辑的表实例存储在State里面  

  
                  State["employee"] = employeeForEdit;  

  
                  employeeCol.EmployeeTables.Remove(employeeForEdit);  

  
              }  

  
          }  

  
      }  

  
  }

运行效果如下：

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

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件