Windows Phone 7 使用本地数据库
openkk
12年前
<p>终于在Wp7.1中加入了数据库，使保存、查找、插入数据不再痛苦。在WP7中如果要做到这些，要么使用XML自已实现，要么使用第三方数据库，但是第三方数据库要么性能不好，要么占用空间太大，要么收费，现在总算有原生的本地数据库了。</p> <p>1． 架构</p> <p><img alt="Windows Phone 7 使用本地数据库" src="https://simg.open-open.com/show/55464d93fd6ee915542d6f889121a090.png" width="600" height="200" /></p> <p>这个本地数据库，不能直接支持Transact-SQL，需要通过LINQ to SQL 对象模型作为Proxy来操作数据库，为此引入了一个新的类System.Data.Linq.DataContext。这与windows mobile上的SQL CE有了很大的差别。</p> <p> </p> <p>2． LINQ to SQL</p> <p>在Windows phone中，LINQ to SQL既不能直接支持执行Data Definition Language(DDL)也不支持Data Modeling Language(DML)，另外也不能直接访问ADO.NET。只能支持Microsoft SQL Server Compact Edition (SQL CE)的数据类型。并且需要通过DataContext方式来操来数据库。</p> <p> </p> <p>3． 支持的数据类型</p> <table border="1" cellspacing="0" cellpadding="0"> <tbody> <tr> <td> <p>数据类型</p> </td> <td> <p>描述 </p> </td> </tr> <tr> <td> <p>bigint </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>integer </p> </td> <td> <p>Integer (whole number) data from –2^31 (–2,147,483,648) through 2^31–1 (2,147,483,647).</p> <p>Storage size is 4 bytes.</p> </td> </tr> <tr> <td> <p>smallint </p> </td> <td> <p>Integer data from –32,768 to 32,767. Storage size is 2 bytes.</p> </td> </tr> <tr> <td> <p>tinyint </p> </td> <td> <p>Integer data from 0 to 255. Storage size is 1 byte.</p> </td> </tr> <tr> <td> <p>bit </p> </td> <td> <p>Integer data with a value of either 1 or 0.</p> <p>Storage size is 1 bit.</p> </td> </tr> <tr> <td> <p>numeric (p, s) </p> <p>Synonyms: </p> <p>decimal(p,s) and dec (p,s) </p> </td> <td> <p>Fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. The <em>p </em>variable specifies precision and can vary between 1 and 38. The <em>s</em> variable specifies scale and can vary between 0 and <em>p</em>.</p> <p>Storage size is 19 bytes.</p> </td> </tr> <tr> <td> <p>money </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>float </p> </td> <td> <p>Floating point number data from –1.79E +308 through 1.79E+308</p> <p>Storage size is 8 bytes.</p> </td> </tr> <tr> <td> <p>real </p> </td> <td> <p>Floating precision number data from –3.40E+38 through 3.40E+38.</p> <p>Storage size is 4 bytes.</p> </td> </tr> <tr> <td> <p>datetime </p> </td> <td> <p>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.</p> <p>Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the <em>base date</em>, 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.</p> <p>Format Example </p> <p><em>yyyy/mm/dd hh:mm:ss</em> 1947/08/15 03:33:20 </p> <p><em>mm/dd/yyyy hh:mm:ss</em> 04/15/1947 03:33:20 </p> <p><em>dd mmm yyyy hh:mm:ss</em> 15 Jan 1947 03:33:20 </p> <p><em>dd mmmm yyyy h:mm:ss</em> 15 January 1947 03:33:20 </p> </td> </tr> <tr> <td> <p>national character(n) </p> <p>Synonym:nchar(n) </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>national character varying(n) </p> <p>Synonym:nvarchar(n) </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>ntext⊃1; </p> </td> <td> <p>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.</p> <p><strong>Note</strong> </p> <p>ntext is no longer supported in string functions. </p> </td> </tr> <tr> <td> <p>nchar </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>binary(n) </p> </td> <td> <p>Fixed-length binary data with a maximum length of 8000 bytes. Default length = 1.</p> <p>Storage size is fixed, which is the length in bytes declared in the type.</p> </td> </tr> <tr> <td> <p>varbinary(n) </p> </td> <td> <p>Variable-length binary data with a maximum length of 8000 bytes. Default length = 1.</p> <p>Storage size varies. It is the length of the value in bytes.</p> </td> </tr> <tr> <td> <p>image⊃1; </p> </td> <td> <p>Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes.</p> <p>Storage is the length of the value in bytes.</p> </td> </tr> <tr> <td> <p>uniqueidentifier </p> </td> <td> <p>A globally unique identifier (GUID). Storage size is 16 bytes.</p> </td> </tr> <tr> <td> <p>IDENTITY [(s, i)] </p> </td> <td> <p>This is a property of a data column, not a distinct data type.</p> <p>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.</p> <p>s (seed) = starting value</p> <p>i(increment) = increment value</p> </td> </tr> <tr> <td> <p>ROWGUIDCOL </p> </td> <td> <p>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.</p> </td> </tr> <tr> <td> <p>Timestamp/rowversion </p> </td> <td> <p>This is an automatically generated unique binary number.</p> <p>Storage size is 8 bytes.</p> </td> </tr> </tbody> </table> <p>⊃1;在SQL Server Compact 4.0中，当Ntext 和 image 数据超过256 bytes 时将会保存到一个新的数据页。这会影响到数据库的密度，因为SQL Server Compact 4.0 数据库是按页方式面不是按字节方式来压缩的。</p> <p>4． 创建工程</p> <p>新建一个Windows Phone工程，最好是MVVM工程，也就是选择新建工程中的Windows Phone Databound Application 模板直接生成或者手工创建MVVM工程。创建好后，将System.Data.Linq命名空间引入到工程。这个命名空间所在位置\Program Files\Reference Assemblies\Microsoft\Framework\Silverlight\v4.0\Profile\WindowsPhone71中。</p> <p>5． 创建数据库</p> <p>新建一个类继承System.Data.Linq.DataContext类，这样就可以用这个类来控制数据库了。</p> <pre class="brush:xml; toolbar: true; auto-links: false;">public class MyDataContext : DataContext { public const string ConnectionStr = "Data Source=isostore:/MyDB.sdf"; public Table <mytable> Rows; public MyDataContext() : base(ConnectionStr) { } } </mytable></pre> <p></p> <p>在这个类中同时创建数据库表类。</p> <pre class="brush:xml; toolbar: true; auto-links: false;">[Table] public class MyTable : INotifyPropertyChanged, INotifyPropertyChanging { private int _index; [Column(IsPrimaryKey = true, CanBeNull = false, IsDbGenerated = true, DbType = "INT NOT NULL Identity", AutoSync=AutoSync.OnInsert)] public int Index { get { return _index; } set { if (_index != value) { NotifyPropertyChanging("Index"); _index = value; NotifyPropertyChanged("Index"); } } } private string _name; [Column] public string Name { get { return _name; } set { NotifyPropertyChanging("Name"); _name = value; NotifyPropertyChanged("Name"); } } private String _gen; [Column] public String Gen { get { return _gen; } set { NotifyPropertyChanging("Gen"); _gen = value; NotifyPropertyChanged("Gen"); } } private int _age; [Column] public int Age { get { return _age; } set { NotifyPropertyChanging("Age"); _age = value; NotifyPropertyChanged("Age"); } } #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 }</pre> <p></p> <p>6． 操作数据库</p> <p>新建一个ViewModel类，在这个类中实现数据库的操作。在这个类中实现了对数据的选择、保存、更新和删除。在对数据库的操作因为不能直接使用Transact_SQL,所以操作都是通过LINQ来完成的。</p> <pre class="brush:xml; toolbar: true; auto-links: false;">MyDataContext _DB; public void SelectData() { if (IsAddedToDB) { if (_DB.DatabaseExists()) { IEnumerator <mytable> enumerator = _DB.Rows.GetEnumerator(); while (enumerator.MoveNext()) { this.Items.Add(enumerator.Current); } IsAddedToDB = false; } } } public bool SaveData(MyTable table) { try { this.Items.Add(table); _DB.Rows.InsertOnSubmit(table); _DB.SubmitChanges(); } catch (Exception e) { return false; } return true; } public bool DeleteData(MyTable table) { try { if (_DB.DatabaseExists()) { _DB.Rows.DeleteOnSubmit(table); _DB.SubmitChanges(); this.Items.Remove(table); } } catch (Exception e) { return false; } return true; } public bool UpdateData(MyTable source, MyTable dest) { try { var tables = from item in this.Items where (int)item.Index == source.Index select item; foreach (MyTable mt in tables) { mt.Name = dest.Name; mt.Age = dest.Age; mt.Gen = dest.Gen; break; } MyTable table = _DB.Rows.GetOriginalEntityState(source); table.Name = dest.Name; table.Age = dest.Age; table.Gen = dest.Gen; _DB.SubmitChanges(); } catch (Exception e) { return false; } return true; } </mytable></pre> <p></p> <p>以下是运行效果：</p> <p><img alt="Windows Phone 7 使用本地数据库" src="https://simg.open-open.com/show/2c619014661640367e9439e2fc3b2d58.png" width="366" height="685" /></p> <p><img alt="Windows Phone 7 使用本地数据库" src="https://simg.open-open.com/show/bc1d4f147538a1a1cd91c5f9f4ec4e61.png" width="364" height="690" /></p> <p><img alt="Windows Phone 7 使用本地数据库" src="https://simg.open-open.com/show/65d20252883b43a2843ba3fd903fa38c.png" width="365" height="687" /></p> <p><img alt="Windows Phone 7 使用本地数据库" src="https://simg.open-open.com/show/46c74cc8fb7b8b2cb505c54b32f35410.png" width="368" height="699" /></p>