Windows Phone 7 使用本地数据库

终于在Wp7.1中加入了数据库，使保存、查找、插入数据不再痛苦。在WP7中如果要做到这些，要么使用XML自已实现，要么使用第三方数据库，但是第三方数据库要么性能不好，要么占用空间太大，要么收费，现在总算有原生的本地数据库了。

1 这个本地数据库，不能直接支持Transact-SQL，需要通过LINQ to SQL 对象模型作为Proxy来操作数据库，为此引入了一个新的类System.Data.Linq.DataContext。这与windows mobile上的SQL CE有了很大的差别。

2． LINQ to SQL

在Windows phone中，LINQ to SQL既不能直接支持执行Data Definition Language(DDL)也不支持Data Modeling Language(DML)，另外也不能直接访问ADO.NET。只能支持Microsoft SQL Server Compact Edition (SQL CE)的数据类型。并且需要通过DataContext方式来操来数据库。

3． 支持的数据类型

数据类型

描述

bigint

Integer (whole number) data from –2^63 (–9,223,372,036,854,775,808) through 2^63–1 (9,223,372,036,854,775,807). Storage size is 8 bytes.

integer

Integer (whole number) data from –2^31 (–2,147,483,648) through 2^31–1 (2,147,483,647).

Storage size is 4 bytes.

smallint

Integer data from –32,768 to 32,767. Storage size is 2 bytes.

tinyint

Integer data from 0 to 255. Storage size is 1 byte.

bit

Integer data with a value of either 1 or 0.

Storage size is 1 bit.

numeric (p, s)

Synonyms:

decimal(p,s) and dec (p,s)

Fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. Thep variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 andp.

Storage size is 19 bytes.

money

Monetary data values from (–2^63/10000) (–922,337,203,685,477.5808) through 2^63–1 (922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

float

Floating point number data from –1.79E +308 through 1.79E+308

Storage size is 8 bytes.

real

Floating precision number data from –3.40E+38 through 3.40E+38.

Storage size is 4 bytes.

datetime

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after thebase date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

Format Example

yyyy/mm/dd hh:mm:ss 1947/08/15 03:33:20

mm/dd/yyyy hh:mm:ss 04/15/1947 03:33:20

dd mmm yyyy hh:mm:ss 15 Jan 1947 03:33:20

dd mmmm yyyy h:mm:ss 15 January 1947 03:33:20

national character(n)

Synonym:nchar(n)

Fixed-length Unicode data with a maximum length of 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.

national character varying(n)

Synonym:nvarchar(n)

Variable-length Unicode data with a length of 1 to 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.

ntext⊃1;

Variable-length Unicode data with a maximum length of (2^30–2)/2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered.

Note

ntext is no longer supported in string functions.

nchar

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.

binary(n)

Fixed-length binary data with a maximum length of 8000 bytes. Default length = 1.

Storage size is fixed, which is the length in bytes declared in the type.

varbinary(n)

Variable-length binary data with a maximum length of 8000 bytes. Default length = 1.

Storage size varies. It is the length of the value in bytes.

image⊃1;

Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes.

Storage is the length of the value in bytes.

uniqueidentifier

A globally unique identifier (GUID). Storage size is 16 bytes.

IDENTITY [(s, i)]

This is a property of a data column, not a distinct data type.

Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified and the column cannot be updated.

s (seed) = starting value

i(increment) = increment value

ROWGUIDCOL

This is a property of a data column, not a distinct data type. It is a column in a table that is defined by using the uniqueidentifier data type. A table can have only one ROWGUIDCOL column.

Timestamp/rowversion

This is an automatically generated unique binary number.

Storage size is 8 bytes.

⊃1;在SQL Server Compact 4.0中，当Ntext 和 image 数据超过256 bytes 时将会保存到一个新的数据页。这会影响到数据库的密度，因为SQL Server Compact 4.0 数据库是按页方式面不是按字节方式来压缩的。

4． 创建工程

新建一个Windows Phone工程，最好是MVVM工程，也就是选择新建工程中的Windows Phone Databound Application 模板直接生成或者手工创建MVVM工程。创建好后，将System.Data.Linq命名空间引入到工程。这个命名空间所在位置\Program Files\Reference Assemblies\Microsoft\Framework\Silverlight\v4.0\Profile\WindowsPhone71中。

5． 创建数据库

新建一个类继承System.Data.Linq.DataContext类，这样就可以用这个类来控制数据库了。

查看源码

打印?

01public class MyDataContext : DataContext

02

03{

04

05public const string ConnectionStr = "Data Source=isostore:/MyDB.sdf";

06

07public Table<MYTABLE> Rows;

08

09public MyDataContext()

10

11: base(ConnectionStr)

12

13{

14

15

16}

17

18}</MYTABLE>

在这个类中同时创建数据库表类。

查看源码

打印?

001[Table]

002

003public class MyTable : INotifyPropertyChanged, INotifyPropertyChanging

004

005{

006

007private int _index;

008

009[Column(IsPrimaryKey = true, CanBeNull = false, IsDbGenerated = true, DbType = "INT NOT NULL Identity", AutoSync=AutoSync.OnInsert)]

010

011public int Index

012

013{

014

015get

016

017{

018

019return _index;

020

021}

022

023set

024

025{

026

027if (_index != value)

028

029{

030

031NotifyPropertyChanging("Index");

032

033_index = value;

034

035NotifyPropertyChanged("Index");

036

037}

038

039

040

041}

042

043}

044

045

046

047private string _name;

048

049[Column]

050

051public string Name

052

053{

054

055get

056

057{

058

059return _name;

060

061}

062

063set

064

065{

066

067NotifyPropertyChanging("Name");

068

069_name = value;

070

071NotifyPropertyChanged("Name");

072

073}

074

075}

076

077

078

079private String _gen;

080

081[Column]

082

083public String Gen

084

085{

086

087get

088

089{

090

091return _gen;

092

093}

094

095set

096

097{

098

099NotifyPropertyChanging("Gen");

100

101_gen = value;

102

103NotifyPropertyChanged("Gen");

104

105}

106

107}

108

109

110

111private int _age;

112

113[Column]

114

115public int Age

116

117{

118

119get

120

121{

122

123return _age;

124

125}

126

127set

128

129{

130

131NotifyPropertyChanging("Age");

132

133_age = value;

134

135NotifyPropertyChanged("Age");

136

137}

138

139}

140

141

142

143#region INotifyPropertyChanged Members

144

145

146

147public event PropertyChangedEventHandler PropertyChanged;

148

149

150

151private void NotifyPropertyChanged(string propertyName)

152

153{

154

155if (PropertyChanged != null)

156

157{

158

159PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

160

161}

162

163}

164

165

166

167#endregion

168

169

170

171#region INotifyPropertyChanging Members

172

173

174

175public event PropertyChangingEventHandler PropertyChanging;

176

177

178

179private void NotifyPropertyChanging(string propertyName)

180

181{

182

183if (PropertyChanging != null)

184

185{

186

187PropertyChanging(this, new PropertyChangingEventArgs(propertyName));

188

189}

190

191}

192

193

194

195#endregion

196

197}

6． 操作数据库

新建一个ViewModel类，在这个类中实现数据库的操作。在这个类中实现了对数据的选择、保存、更新和删除。在对数据库的操作因为不能直接使用Transact_SQL,所以操作都是通过LINQ来完成的。

查看源码

打印?

01MyDataContext _DB;

02

03public void SelectData()

04{

05if (IsAddedToDB)

06{

07if (_DB.DatabaseExists())

08{

09IEnumerator<MYTABLE> enumerator = _DB.Rows.GetEnumerator();

10while (enumerator.MoveNext())

11{

12this.Items.Add(enumerator.Current);

13}

14IsAddedToDB = false;

15}

16}

17}

18public bool SaveData(MyTable table)

19{

20try

21{

22this.Items.Add(table);

23_DB.Rows.InsertOnSubmit(table);

24_DB.SubmitChanges();

25}

26catch (Exception e)

27{

28return false;

29}

30return true;

31}

32

33public bool DeleteData(MyTable table)

34{

35try

36{

37if (_DB.DatabaseExists())

38{

39_DB.Rows.DeleteOnSubmit(table);

40_DB.SubmitChanges();

41this.Items.Remove(table);

42}

43}

44catch (Exception e)

45{

46return false;

47}

48return true;

49

50}

51

52

53

54public bool UpdateData(MyTable source, MyTable dest)

55{

56try

57{

58var tables = from item in this.Items where (int)item.Index == source.Index select item;

59

60foreach (MyTable mt in tables)

61{

62mt.Name = dest.Name;

63mt.Age = dest.Age;

64mt.Gen = dest.Gen;

65

66break;

67

68}

69

70MyTable table = _DB.Rows.GetOriginalEntityState(source);

71table.Name = dest.Name;

72table.Age = dest.Age;

73table.Gen = dest.Gen;

74_DB.SubmitChanges();

75}

76catch (Exception e)

77{

78return false;

79}

80

81return true;

82}</MYTABLE>

以下是运行效果：