Windows Phone 8 SQL Server CE 数据库

员工信息表 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 }

员工数据库EmployeeDataContext继承DataContext

 1 using System.Data.Linq;
 2 
 3 namespace SQLServerDemo
 4 {
 5     //定义一个EmployeeDataContext类来继承DataContext，在EmployeeDataContext中定义数据库连接字符串，以及员工信息表
 6     public class EmployeeDataContext : DataContext
 7     {
 8         // 数据库链接字符串
 9         public static string DBConnectionString = "Data Source=isostore:/Employee.sdf";
10 
11         // 传递数据库连接字符串到DataContext基类
12         public EmployeeDataContext(string connectionString)
13             : base(connectionString)
14         { }
15 
16         // 定义一个员工信息表
17         public Table<EmployeeTable> Employees;
18     }
19 }

创建页面数据绑定集合

 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 }

在App.cs 的Launching添加数据库检查

 1 // Code to execute when the application is launching (eg, from Start)
 2         // This code will not execute when the application is reactivated
 3         private void Application_Launching(object sender, LaunchingEventArgs e)
 4         {
 5             //如果数据库不存在则创建一个数据库
 6             using (EmployeeDataContext db = new EmployeeDataContext(EmployeeDataContext.DBConnectionString))
 7             {
 8                 if (db.DatabaseExists() == false)
 9                 {
10                     //创建一个数据库
11                     db.CreateDatabase();
12                 }
13             }
14         }

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 on 2014-12-21 01:41 -　物是人非# 阅读(131) 评论(0) 收藏举报