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

首页 | 新闻 | 交流 | 问吧 | 文档 | 手册 | 下载 | 博客

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

作者:  时间: 2011-06-08

    在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
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, AutoSync = 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 newEmployee = 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 发表于 2011-06-08 19:31 原文链接

评论: 0 查看评论 发表评论


最新新闻:
· 双飞燕致罗技的一封公开信(2011-06-08 17:54)
· 苹果iOS 5“借鉴”安卓六大功能(2011-06-08 17:52)
· 乔布斯谈苹果新园区:有点像着陆的飞船(2011-06-08 17:50)
· 外滩画报:音乐系男生的互联网公司(2011-06-08 17:48)
· 传苹果寻找iPad 3零件供应商 或降价抗衡对手(2011-06-08 17:46)

编辑推荐:《设计模式》作者之一Erich Gamma加入微软Visual Studio团队

网站导航:博客园首页  我的园子  新闻  闪存  小组  博问  知识库