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);  
            }  
        }  
    }  
} 

运行效果如下：