WP之Sql Server CE数据库

如何在WP8中进行数据存储,你首先想到应该是独立存储,但是独立存储似乎存储文件更方便,如果我们希望像处理对象的形式,该怎么办呢,答案就是Sql Server CE。

Sql Server CE并不是新鲜东西,它是专门用在移动端的一个工具,它和SQLServer有很大的不同,SQLServer中我们使用Sql语句,而Sql Server CE则要使用Linq处理。

一、创建表

 /// <summary>
/// 员工表
/// </summary>
[Table]
class EmployeeTable : INotifyPropertyChanged, INotifyPropertyChanging
{ private int _employeeId;
/// <summary>
/// 员工Id
/// </summary>
[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)
{
OnPropertyChanging("EmployeeId");
_employeeId = value;
OnPropertyChanged("EmployeeId");
}
}
} private string _employeeName;
/// <summary>
/// 员工名称
/// </summary>
[Column]
public string EmployeeName
{
get { return _employeeName; }
set
{
if (_employeeName != value)
{
OnPropertyChanging("EmployeeName");
_employeeName = value;
OnPropertyChanged("EmployeeName");
}
}
} private string _employeeDesc;
/// <summary>
/// 员工简介
/// </summary>
[Column]
public string EmployeeDesc
{
get { return _employeeDesc; }
set
{
if (_employeeDesc != value)
{
OnPropertyChanging("EmployeeDesc");
_employeeDesc = value;
OnPropertyChanged("EmployeeDesc");
}
}
} public event PropertyChangedEventHandler PropertyChanged; private void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
public event PropertyChangingEventHandler PropertyChanging;
private void OnPropertyChanging(string propertyName)
{
if (PropertyChanging != null)
{
PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
}
}
}

一张员工表,有三个属性。类需要加上Table特性,如果你使用过Linq to sql,那么整个特性应该是见过的;另外就是对每个属性需要使用Column特性进行标注;最最重要的一点,类要实现自INotifyPropertyChanged, INotifyPropertyChanging,这两个接口主要是为了通知,同时在类中实现着两个接口的时间即可,并且在属性的Set中进行调用就ok了。
二、上下文对象类(用于操作数据,和数据库交互)

   /// <summary>
/// 上下文对象类
/// </summary>
class EmployeeDataContext:DataContext
{
//数据库连接字符串
public static string DBConnectionString = "Data Source=isostore:/Employee.sdf";
public EmployeeDataContext(string connectionString)
: base(connectionString) { }
//员工信息表
public Table<EmployeeTable> Employees;
}

就是一般的类,只不过继承自DataContext,这个和Linq to sql中的上下文对象同一个基类。类中有一个连接字符串属性,同时构造函数支持传入一个连接字符串,另外就是有我们上文中创建的表的一个Table类型的集合,表示一个集合类型。

三、数据列表类(此类大家也可以没有,因为对于集合的处理,大家可以根据自己的需要进行修改)

 /// <summary>
/// 列表集合
/// </summary>
class EmployeeCollection : INotifyPropertyChanged
{
private ObservableCollection<EmployeeTable> _employeeTables;
/// <summary>
/// 员工列表集合
/// </summary>
public ObservableCollection<EmployeeTable> EmployeeTables
{
get { return _employeeTables; }
set
{
if (_employeeTables != value)
{
_employeeTables = value;
OnPropertyChanged("EmployeeTables");
}
}
} public void UpdateEmployee(EmployeeTable employee)
{
var oldEmployee = EmployeeTables.Where(e => e.EmployeeId == employee.EmployeeId).FirstOrDefault();
oldEmployee.EmployeeName = employee.EmployeeName;
oldEmployee.EmployeeDesc = employee.EmployeeDesc;
} public event PropertyChangedEventHandler PropertyChanged;
private void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}

类中只有一个Employee的集合和一个用于更新对象的方法,以及实现自INotifyPropertyChanged的内容。

四、使用

     <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="saveButton"
Click="saveButton_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>

后台代码:

 public partial class MainPage : PhoneApplicationPage
{
//上下文对象
private EmployeeDataContext employeeDB;
//列表集合对象
private EmployeeCollection employeeCollection = new EmployeeCollection();
// Constructor
public MainPage()
{
InitializeComponent();
//连接数据库并初始化DataContext实例
employeeDB = new EmployeeDataContext(EmployeeDataContext.DBConnectionString);
//查询所有的记录
var employeeQry = from e in employeeDB.Employees select e; employeeCollection.EmployeeTables=new ObservableCollection<EmployeeTable>(employeeQry);
//设置当前页面的上下文对象为列表集合
this.DataContext = employeeCollection;
} /// <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)
{
//得到当前按钮所绑定的数据
EmployeeTable employee = button.DataContext as EmployeeTable;
//从集合中移除
employeeCollection.EmployeeTables.Remove(employee);
//从数据库中移除
employeeDB.Employees.DeleteOnSubmit(employee);
//更新到数据库
employeeDB.SubmitChanges();
}
} /// <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)
{
//得到当前按钮绑定的数据
EmployeeTable employ = button.DataContext as EmployeeTable;
//将名称和简介设置为文本框的值
name.Text = employ.EmployeeName;
desc.Text = employ.EmployeeDesc;
//将当前编辑的对象放置在State中,用于保存时候使用
State["employee"] = employ;
//将当前对象从集合中移除
employeeCollection.UpdateEmployee(employ);
// employeeCollection.EmployeeTables.Remove(employ);
}
} /// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void saveButton_Click(object sender, RoutedEventArgs e)
{
if (name.Text != "" && desc.Text != "")
{
//如果当前的State不为空,并且存在Employee,则为编辑
if (State.Count > && State["employee"] != null)
{
//得到State中的对象
EmployeeTable employ = (EmployeeTable)State["employee"];
//将文本框的值设置给对象
employ.EmployeeName = name.Text;
employ.EmployeeDesc = desc.Text;
//将数据保存到数据库
employeeDB.SubmitChanges();
// employeeCollection.EmployeeTables.Add(employ);
State["employee"] = null; }
//新增
else
{
//创建Employee对象
EmployeeTable employee = new EmployeeTable { EmployeeName = name.Text, EmployeeDesc = desc.Text };
//将Employee放入集合中
employeeCollection.EmployeeTables.Add(employee);
//将Employee添加到数据库
employeeDB.Employees.InsertOnSubmit(employee);
//将数据更新到数据库
employeeDB.SubmitChanges();
} name.Text = "";
desc.Text = "";
}
else
{
MessageBox.Show("姓名和简介不能为空");
}
}
}

五、迟到的创建数据库
细心的童鞋会发现,为什么木有创建数据库的过程,如下:

 private void Application_Launching(object sender, LaunchingEventArgs e)
{
using (EmployeeDataContext db=new EmployeeDataContext (EmployeeDataContext.DBConnectionString))
{
if (db.DatabaseExists()==false)
{
db.CreateDatabase();
}
}
}

在App.xaml.cs中放入上述代码即可,也就是创建了一个数据库。

Tips:数据库文件同样是在独立存储空间中。

上一篇:OpenGL顶点缓冲区对象(VBO)


下一篇:[转]vs2010 快捷键大全