1 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. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p. 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 the base 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类，这样就可以用这个类来控制数据库了。

01    public class MyDataContext : DataContext
02    
03    {
04    
05    public const string ConnectionStr = "Data Source=isostore:/MyDB.sdf";
06    
07    public Table<MyTable> Rows;
08    
09    public MyDataContext()
10    
11    : base(ConnectionStr)
12    
13    {
14    
15    
16    }
17    
18    }

类中同时创建数据库表类。

01
[Table]
002
 
003
    public class MyTable : INotifyPropertyChanged, INotifyPropertyChanging
004
 
005
    {
006
 
007
        private int _index;
008
 
009
        [Column(IsPrimaryKey = true, CanBeNull = false, IsDbGenerated = true, DbType = "INT NOT NULL Identity", AutoSync=AutoSync.OnInsert)]
010
 
011
        public int Index
012
 
013
        {
014
 
015
            get
016
 
017
            {
018
 
019
                return _index;
020
 
021
            }
022
 
023
            set
024
 
025
            {
026
 
027
                if (_index != value)
028
 
029
                {
030
 
031
                    NotifyPropertyChanging("Index");
032
 
033
                    _index = value;
034
 
035
                    NotifyPropertyChanged("Index");
036
 
037
                }
038
 
039
  
040
 
041
            }
042
 
043
        }
044
 
045
  
046
 
047
        private string _name;
048
 
049
        [Column]
050
 
051
        public string Name
052
 
053
        {
054
 
055
            get
056
 
057
            {
058
 
059
                return _name;
060
 
061
            }
062
 
063
            set
064
 
065
            {
066
 
067
                NotifyPropertyChanging("Name");
068
 
069
                _name = value;
070
 
071
                NotifyPropertyChanged("Name");
072
 
073
            }
074
 
075
        }
076
 
077
  
078
 
079
        private String _gen;
080
 
081
        [Column]
082
 
083
        public String Gen
084
 
085
        {
086
 
087
            get
088
 
089
            {
090
 
091
                return _gen;
092
 
093
            }
094
 
095
            set
096
 
097
            {
098
 
099
                NotifyPropertyChanging("Gen");
100
 
101
                _gen = value;
102
 
103
                NotifyPropertyChanged("Gen");
104
 
105
            }
106
 
107
        }
108
 
109
  
110
 
111
        private int _age;
112
 
113
        [Column]
114
 
115
        public int Age
116
 
117
        {
118
 
119
            get
120
 
121
            {
122
 
123
                return _age;
124
 
125
            }
126
 
127
            set
128
 
129
            {
130
 
131
                NotifyPropertyChanging("Age");
132
 
133
                _age = value;
134
 
135
                NotifyPropertyChanged("Age");
136
 
137
            }
138
 
139
        }
140
 
141
  
142
 
143
        #region INotifyPropertyChanged Members
144
 
145
  
146
 
147
        public event PropertyChangedEventHandler PropertyChanged;
148
 
149
  
150
 
151
        private void NotifyPropertyChanged(string propertyName)
152
 
153
        {
154
 
155
            if (PropertyChanged != null)
156
 
157
            {
158
 
159
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
160
 
161
            }
162
 
163
        }
164
 
165
  
166
 
167
        #endregion
168
 
169
  
170
 
171
        #region INotifyPropertyChanging Members
172
 
173
  
174
 
175
        public event PropertyChangingEventHandler PropertyChanging;
176
 
177
  
178
 
179
        private void NotifyPropertyChanging(string propertyName)
180
 
181
        {
182
 
183
            if (PropertyChanging != null)
184
 
185
            {
186
 
187
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
188
 
189
            }
190
 
191
        }
192
 
193
  
194
 
195
        #endregion
196
 
197
    }

　　

6．  操作数据库

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

1
MyDataContext _DB;
02
 
03
public void SelectData()
04
        {
05
            if (IsAddedToDB)
06
            {
07
                if (_DB.DatabaseExists())
08
                {
09
                    IEnumerator<MyTable> enumerator = _DB.Rows.GetEnumerator();
10
                    while (enumerator.MoveNext())
11
                    {
12
                        this.Items.Add(enumerator.Current);
13
                    }
14
                    IsAddedToDB = false;
15
                }
16
            }       
17
        }
18
        public bool SaveData(MyTable table)
19
        {
20
            try
21
            {
22
                this.Items.Add(table);
23
                _DB.Rows.InsertOnSubmit(table);
24
                _DB.SubmitChanges();
25
            }
26
            catch (Exception e)
27
            {
28
                return false;
29
            }
30
            return true;
31
        }
32
 
33
        public bool DeleteData(MyTable table)
34
        {
35
            try
36
            {
37
                if (_DB.DatabaseExists())
38
                {
39
                    _DB.Rows.DeleteOnSubmit(table);
40
                    _DB.SubmitChanges();
41
                    this.Items.Remove(table);
42
                }
43
            }
44
            catch (Exception e)
45
            {
46
                return false;
47
            }
48
            return true;
49
 
50
        }
51
 
52
  
53
 
54
        public bool UpdateData(MyTable source, MyTable dest)
55
        {
56
            try
57
            {
58
                var tables = from item in this.Items where (int)item.Index == source.Index select item;
59
 
60
                foreach (MyTable mt in tables)
61
                {
62
                    mt.Name = dest.Name;
63
                    mt.Age = dest.Age;
64
                    mt.Gen = dest.Gen;
65
 
66
                    break;
67
 
68
                }
69
 
70
                MyTable table = _DB.Rows.GetOriginalEntityState(source);
71
                table.Name = dest.Name;
72
                table.Age = dest.Age;
73
                table.Gen = dest.Gen;
74
                _DB.SubmitChanges();
75
            }
76
            catch (Exception e)
77
            {
78
                return false;
79
            }
80
 
81
            return true;
82
        }

　　

以下是运行效果：