加载中

The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8.

The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and  also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute)

本地数据库(通过 LINQ to SQL访问的 SQL Server Compact database)是一个运行在Windows Phone7.5 和8 上的数据访问API。

在微软的文档(列表 这是我的博客, 还有很多有用的Windows Phone 本地数据库链接)中,总是在描述一个代码优先的工作流,不厌其烦地为Windows Mobile和桌面创建SQL Server Compact 数据库架构,而且它很难和你的App一块发布。我的工具,可作为 SQL Server 工具箱 Visual Studio 插件,也可用于有SQL Server Compact 3.5工具箱的简单的单机版程序 (目前还是alpha版).两种情形都适用。当你不想在PC上安装Visual Studio时,单机版对 VS Express版用户很有用(这是个单一的exe文件,很容易发布)。 


In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps:

- Create the SQL Server Compact database from the server database and add it to the Windows Phone project
- Generate the LINQ to SQL DataContext and releated classes.
- Use the database from code

I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed.

接下来,,我将使用Visual Studio 2012来演示,怎样使用SQL Server Compact Toolbox 获取一个已存在的SQL Server数据库,并把它作为内置Windows Phone数据库嵌入到一个新Windows Phone 8 App中。此操作分3步:

- 在服务器数据库中创建一个SQL Server Compact 数据库,并把它加入Windows Phone 工程。
- 生成 LINQ to SQL DataContext 和关联类。
- 在代码中使用数据库。

我假设你有 Visual Studio 2012 Pro或更高版本,并已经安装Windows Phone 8 SDK。


Create the SQL Server Compact database

I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.

image

I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).

image

I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.

image

Click OK, and a new, empty database will be added to the database list in the Toolbox:

image

Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.

image

Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:

image

Use the Open button in the editor to load the script, and the press the Execute button to run the script.

image

Now the database contains a PostCode table (the script is available here), which has all Danish postcodes.

创建一个 SQL Server Compact数据库

为这个示例,我已经创建了一个新的 Windows Phone Databound App,并选择 Windows Phone OS 8.0 作为目标系统.

image

然后,利用工具箱创建一个新的SQL Server Compact 3.5 数据库,保存到Phone工程所在的文件夹 (你可以用“Open Folder in File Explorer”上下文菜单)。

image

单击 Create,导航到工程文件夹,然后输入 PostCodes.sdf, 点击OK.

image

点击OK,一个新的空数据库会加入到工具箱的数据库列表中:

image

现在,我们需要连接到SQL Server 数据库并生成脚本,然后在那个新的空数据库上执行此脚本。

image

用上面的工具箱菜单项创建并保存数据库脚本,然后挨着PostCodes.sdf数据库文件,打开SQL编辑器:

image

在编辑器中点击 Open 按钮,加载脚本,然后按下Execute按钮,运行脚本。

image

现在,一个包含PostCode表的数据库就创建了(脚本在这),这个表保存着丹麦所有的邮政编码。


The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.

image

Generate the LINQ to SQL DataContext

In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”

image

image

Let’s walk through the various options on this dialog:

Context name: The name of the generated DataContext class

Namespace: Allows you to specify another namespace for the generated code

Language: You can generate C# or VB code.

Pluralize: If checked, will rename tables (Person => People) etc.

Create a file per table: Normally, just a single file is created


最后一步是把数据库文件加入到 Phone工程里。在解决方案管理器中,选择“显示所有文件”,然后把 PostCodes.sdf加入工程。 在本示例中,我们希望在Phone中,这个数据库是可写入的,因此,把它作为“嵌入资源” 加入工程。--如果是 只读数据库,可以作为内容加入工程。

image

生成 LINQ to SQL DataContext

为基于数据库生成DataContext,在工具箱中点右键,选择“Add Windows Phone DataContext to current project ”

image

image

让我们看看这个对话框的选项 dialog:

Context name:生成DataContext的类名

Namespace: 可以为生成的代码指定不同的命名空间

Language: 可生成 C# 或 VB code.

Pluralize: 如果选中,会重命名表 (Person => People) 诸如此类.

Create a file per table: 通常,只创建一个文件Normally

Advanced options:


Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option .

Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here)

Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion.

For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.

image

高级选项:

Add schema version table: 如果数据库文件可写,而且在以后的App版本中会使用到DatabaseSchemaUpdater 类,请选中此项。

Add rowversion column to all tables: 如果选中此项,将会确保在所有表中包含rowversion列(以前的时间戳),它能在执行UPDATE and DELETE时提高性能(参考我的博客 这里 和 这里)。

Include ConnectionStringBuilder: 会在工程中新建一个 LocalDatabaseConnectionStringBuilder类,能用流行的方式创建强类型的连接字符串。

对于这个示例,仅需单击OK,然后工程中会加入PostCodesContext.cs ,到此,我们就做完了。

image

Use the database from code

Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:

<DataTemplate>
  <StackPanel Margin="0,0,0,17">
      <TextBlock Text="{Binding Zip}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}"/>
      <TextBlock Text="{Binding City}" TextWrapping="Wrap" Margin="12,-6,12,0" Style="{StaticResource PhoneTextSubtleStyle}"/>
  </StackPanel>
</DataTemplate>


Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code:

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
    {
        ctx.CreateIfNotExists();
        ctx.LogDebug = true;
        MainLongListSelector.ItemsSource = ctx.PostCode.ToList();                
    }
}


This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging:

SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]

FROM [PostCode] AS [t0]


And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector.

Result:

pc

在代码中使用数据库

最后,为了演示这个包含数据的App,修改MainPage.xaml的DataTemplate,就像下面:

<DataTemplate>
  <StackPanel Margin="0,0,0,17">
      <TextBlock Text="{Binding Zip}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}"/>
      <TextBlock Text="{Binding City}" TextWrapping="Wrap" Margin="12,-6,12,0" Style="{StaticResource PhoneTextSubtleStyle}"/>
  </StackPanel>
</DataTemplate>

用以下代码替换 MainPage.xaml.cs中的OnNavigatedTo事件处理:

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
    {
        ctx.CreateIfNotExists();
        ctx.LogDebug = true;
        MainLongListSelector.ItemsSource = ctx.PostCode.ToList();                
    }
}

这个代码初始化一个新的PostCodesContext 实例,(包含在“using”中,它是Disposable). CreateIfNotExists 方法,从工程中提取PostCodes.sdf,并复制到隔离存储区。把LogDebug 设置为true,会在调试时在调试窗口中显示所有SQL定义文本:

SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]

FROM [PostCode] AS [t0]

最后,调用ToList()执行SELECT并返回PostCode对象列表,它被绑定到LongListSelector的ItemsSource 属性。

结果: 

pc

Summary

Let us finish with a summary of advantages of this approach:

- Use desktop database tools for data population and schema design
- Saves time doing 1:1 mapping between database tables and DataContext classes
- DataContext class and entity classes are partial and can be extended
- The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension)
- The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage
- The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window
- Optionally split the generated Data Context classes into multiple files
- Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality.
- Optionally add rowversion columns to improve UPDATE and DELETE performance
- Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way,  using advanced connection string options (see some of my Phone blog posts for candidates)

总结

让我们总结下这些方法的优点:

- 使用桌面数据库工具用来操作数据和进行模型设计 
- 在数据库表和 DataContext 类做 1:1 映射可以节省时间 
- DataContext 类和实体类是可扩展的
- 生成的 DataContext 包含索引定义(SqlMetal 不支持,这是 Windows Phone 的扩展) 
- 生成的 DataContext 包含 CreateIfNotExists 方法
- 生成的 DataContext 包含 LogDebug 属性,允许你在调试窗口查看生成的 SQL 语句
- 可将生成的 DataContext 类分成多个文件
- 可增加版本表,来支持模型的改动
- 可增加 rowversion 列里提升 UPDATE 和 DELETE 性能
- 可包含 ConnectionStringBuilder 类来构建一个强类型的连接字符串,使用高级的连接字符串选项

返回顶部
顶部