Windows phone数据库SQLCE
在wp 7.1中终于可以使用SQLCE了,要使用sqlce需要添加system.data.linq的命名空间的引用
要使用sqlce,首先要建立一个实体类,表示数据库中的一行的实例
using System.Data.Linq.Mapping;
using System.ComponentModel;
namespace WindowsPhoneSQLCE.App_Code
{
[Table]
public class Person : INotifyPropertyChanged, INotifyPropertyChanging
{
private int _id;
//用户编号,该列自动生成,不可为空
[Column(IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int Id
{
get { return _id; }
set
{
if (_id != value)
{
OnPropertyChanging("Id");
_id = value;
OnPropertyChanged("Id");
}
_id = value;
}
}
private string _name;
//用户名称
[Column]
public string Name
{
get { return _name; }
set
{
_name = value;
}
}
private string _job;
//用户工作
[Column]
public string Job
{
get { return _job; }
set { _job = value; }
}
//属性更改完毕事件
public event PropertyChangedEventHandler PropertyChanged;
public void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
//属性更改事件
public event PropertyChangingEventHandler PropertyChanging;
public void OnPropertyChanging(string propertyName)
{
if (PropertyChanging != null)
{
PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
}
}
}
}
建立一个数据上下文
using System.Data.Linq;
namespace WindowsPhoneSQLCE.App_Code
{
//Person数据上下文
public class PersonDataContext : DataContext
{
private const string dbConstring = "Data Source=isostore:/db.sdf";
public PersonDataContext()
: base(dbConstring)
{
}
public Table<Person> person;
}
}
使用时 的前台布局
<phone:PhoneApplicationPage
x:Class="WindowsPhoneSQLCE.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">
<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<!--TitlePanel contains the name of the application and page title-->
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="SQLCE" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="sqlce练习" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<Grid.RowDefinitions>
<RowDefinition Height="*" />
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<ListBox x:Name="ListBoxPerson"
SelectionChanged="ListBoxPerson_SelectionChanged">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal">
<StackPanel.Resources>
<Style x:Key="txtbkStyle"
TargetType="TextBlock">
<Setter Property="Margin"
Value="20,10,0,0"/>
</Style>
</StackPanel.Resources>
<TextBlock Text="{Binding Path=Id}"
Style="{StaticResource txtbkStyle}"/>
<TextBlock Text="{Binding Path=Name}"
Style="{StaticResource txtbkStyle}"/>
<TextBlock Text="{Binding Path=Job}"
Style="{StaticResource txtbkStyle}"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
<StackPanel Grid.Row="1">
<StackPanel Orientation="Horizontal"
VerticalAlignment="Bottom">
<TextBlock Text="姓名:"
Height="40"
FontSize="26"/>
<TextBox x:Name="txtUserName"
Width="160"/>
<TextBlock Text="工作:"
Height="40"
FontSize="26"/>
<TextBox x:Name="txtJob"
Width="160"/>
</StackPanel>
<StackPanel Orientation="Horizontal"
Width="460"
HorizontalAlignment="Center">
<Button Content="加载"
Click="ButtonLoad_Click"/>
<Button Content="增加"
Click="ButtonAdd_Click"/>
<Button Content="修改"
Click="ButtonUpdate_Click"/>
<Button Content="删除"
Click="ButtonDel_Click"/>
<Button Content="查询"
Click="ButtonSelect_Click"/>
</StackPanel>
</StackPanel>
</Grid>
</Grid>
<!--Sample code showing usage of ApplicationBar-->
<!--<phone:PhoneApplicationPage.ApplicationBar>
<shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
<shell:ApplicationBarIconButton IconUri="/Images/appbar_button1.png" Text="Button 1"/>
<shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Button 2"/>
<shell:ApplicationBar.MenuItems>
<shell:ApplicationBarMenuItem Text="MenuItem 1"/>
<shell:ApplicationBarMenuItem Text="MenuItem 2"/>
</shell:ApplicationBar.MenuItems>
</shell:ApplicationBar>
</phone:PhoneApplicationPage.ApplicationBar>-->
</phone:PhoneApplicationPage>
后台操作数据库代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
using WindowsPhoneSQLCE.App_Code;
using System.Collections.ObjectModel;
namespace WindowsPhoneSQLCE
{
public partial class MainPage : PhoneApplicationPage
{
public ObservableCollection<Person> persons { get; set; }
PersonDataContext persondb;
// Constructor
public MainPage()
{
InitializeComponent();
persondb = new PersonDataContext();
if (!persondb.DatabaseExists())//如果数据库不存在则新建一个数据库
{
persondb.CreateDatabase();
}
}
private void ButtonAdd_Click(object sender, RoutedEventArgs e)
{
for (int i = 0; i < 10; i++)
{
persondb.person.InsertOnSubmit(RandomPerson());
}
//保存更改
persondb.SubmitChanges();
UpdateUI();
}
/// <summary>
/// 随机生成一个用户信息
/// </summary>
/// <returns></returns>
private Person RandomPerson()
{
Random rand = new Random();
Person person = new Person() { Id = rand.Next(), Name = "用户" + rand.Next(), Job = "工作" + rand.Next() };
return person; }
private void UpdateUI()
{
persons = new ObservableCollection<Person>();
var query = from per in persondb.person
select per;
foreach (var item in query)
{
persons.Add(item);
}
Dispatcher.BeginInvoke(() =>
{
this.DataContext = persons;
ListBoxPerson.ItemsSource = persons;
});
}
private void ButtonUpdate_Click(object sender, RoutedEventArgs e)
{
string name = txtUserName.Text;
if (string.IsNullOrEmpty(name))
{
MessageBox.Show("先选择要修改的项!");
return;
}
//得到要修改的用户id
int id = Convert.ToInt32(txtUserName.Tag as string);
//查询用户id等于id变量的用户
var query = from per in persondb.person
where per.Id == id
select per;
foreach (var item in query)
{
item.Name = txtUserName.Text;
item.Job = txtJob.Text;
}
persondb.SubmitChanges();//提交操作
UpdateUI();
}
private void ButtonDel_Click(object sender, RoutedEventArgs e)
{
string name = txtUserName.Text;
if (string.IsNullOrEmpty(name))
{
MessageBox.Show("先选择要删除的项!");
return;
}
//得到要修改的用户id
int id = Convert.ToInt32(txtUserName.Tag as string);
//查询用户id等于id变量的用户
var query = from per in persondb.person
where per.Id == id
select per;
foreach (var item in query)
{
persondb.person.DeleteOnSubmit(item);
}
persondb.SubmitChanges();//提交操作
UpdateUI();
}
private void ButtonSelect_Click(object sender, RoutedEventArgs e)
{
persons = new ObservableCollection<Person>();
string name = txtUserName.Text;
if (string.IsNullOrEmpty(name))
{
MessageBox.Show("先选择要删除的项!");
return;
}
//得到要修改的用户id
int id = Convert.ToInt32(txtUserName.Tag as string);
//查询用户id等于id变量的用户
var query = from per in persondb.person
where per.Id == id
select per;
foreach (var item in query)
{
persons.Add(item);
}
Dispatcher.BeginInvoke(() =>
{
this.DataContext = persons;
ListBoxPerson.ItemsSource = persons;
});
}
private void ListBoxPerson_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
//得到ListBox
ListBox lst = sender as ListBox;
if (lst.SelectedIndex>-1)//如果选择的项是StackPanel
{
StackPanel stackPanel = lst.SelectedItem as StackPanel;
//用户编号
//string id = (stackPanel.Children[0] as TextBlock).Text;
string id = (lst.SelectedItem as Person).Id.ToString();
//用户名称
string name = (lst.SelectedItem as Person).Name;
//用户工作
string job = (lst.SelectedItem as Person).Job;
txtUserName.Text = name;
txtUserName.Tag = id;//用户id赋给txtUserName的Tag属性
txtJob.Text = job;
}
}
private void ButtonLoad_Click(object sender, RoutedEventArgs e)
{
UpdateUI();
}
}
}