无法从 WP8 上 SD 卡上的 SQLite DB 检索数据 [英] Unable to retrieve data from SQLite DB on SD Card on WP8

问题描述

我在控制台应用程序中使用 System.Data.SQLite 创建了一个 SQLite 数据库.然后我把它移到了 Windows Phone 的 SD 卡上.

I've created a SQLite DB using System.Data.SQLite in a console app. I've then moved this to the Windows Phone's SD card.

我按照以下说明为我的 WP8 应用程序添加了 SQLite 支持:https://github.com/peterhuene/sqlite-net-wp8

I followed these instructions to add SQLite support to my WP8 app: https://github.com/peterhuene/sqlite-net-wp8

我找到 DB 文件并像这样打开它:

I locate the DB file and open it like so:

ExternalStorageFile file = null;    
IEnumerable<ExternalStorageDevice> storageDevices = await ExternalStorage.GetExternalStorageDevicesAsync();
    foreach (ExternalStorageDevice storageDevice in storageDevices)
    {
        try
        {
            file = await storageDevice.GetFileAsync("northisland.nztopomap");
        }
        catch
        {
            file = null;
        }
        if (file != null) break;
    }

    SQLiteConnection conn = new SQLiteConnection("Data Source=" + file.Path + ";Version=3;Read Only=True;FailIfMissing=True;");

    SQLiteCommand command = new SQLiteCommand(_dbNorthIsland);
    command.CommandText = "SELECT COUNT(*) FROM tiles";
    int count = (int)command.ExecuteScalar<int>();

这会导致以下错误:

{SQLite.SQLiteException: no such table: tiles
   at SQLite.SQLite3.Prepare2(Database db, String query)
   at SQLite.SQLiteCommand.Prepare()
   at SQLite.SQLiteCommand.ExecuteScalar[T]()}

有趣的是，我还尝试了以下 SQL 语句:

Interestingly, I've also tried the following SQL statement:

"SELECT COUNT(*) FROM sqlite_master WHERE type='table'"

哪个给出 0 的结果表明我的tiles"表找不到?

Which gives a result of 0 suggesting my "tiles" table cannot be found?

我怀疑 ExternalStorageFile.Path 返回的路径是 SQLite 无法解析为现有文件的路径，导致它创建一个新数据库，因此在我尝试访问它时抱怨缺少表.

I suspect that ExternalStorageFile.Path is returning a path that SQLite is unable to resolve as an existing file, leading it to create a new database and so complain about the missing table when I try to access it.

这篇微软文章似乎暗示我应该能够从我的应用程序访问 SD 卡中的文件:http://msdn.microsoft.com/library/windowsphone/develop/jj720573%28v=vs.105%29.aspx

This Microsoft article seems to suggest that I should be able to access files from the SD card from my app: http://msdn.microsoft.com/library/windowsphone/develop/jj720573%28v=vs.105%29.aspx

微软员工提供的反馈:

您的应用无法直接访问 SD 卡上的文件.它不能直接用文件系统 API 打开它们，但需要使用 Windows.Storage 的 ExternalStorageFile 和 ExternalStorageFolder 接口.引用从 Windows Phone 8 上的 SD 卡读取:

Your app doesn't have direct access to the files on the SD card. It can't open them directly with file system API, but needs to use the ExternalStorageFile and ExternalStorageFolder interfaces from Windows.Storage. To quote from Reading from the SD card on Windows Phone 8:

Windows Phone 应用程序可以使用以下命令从 SD 卡读取特定文件类型Microsoft.Phone.Storage API.

Windows Phone apps can read specific file types from the SD card using the Microsoft.Phone.Storage APIs.

我希望手机的 SQLite 实现尝试使用标准 C 文件 API 而不是使用 Storage 对象打开数据库，因此要求数据库位于 Xap 或隔离存储中，并且无法访问 SD 卡上的数据库(对于 Windows Store 应用程序的 SQLite 来说绝对是这种情况).

I expect that the SQLite implementation for the phone tries to open the database using standard C file API rather than using the Storage objects and so requires that the database be in the Xap or isolated storage and cannot access a database on the SD card (this is definitely the case for SQLite for Windows Store apps).

理论上可以更新 SQLite 以使用 Storage 对象，但我怀疑这将是一个重要的项目.

In theory it would be possible to update SQLite to use Storage objects, but I suspect it would be a significant project to do so.

示例准系统项目:

我创建了一个简单的示例项目来突出我的问题，以防万一有人想快速查看并尝试任何想法:

I've created a bare-bones example project that highlights my issue, just in case anyone wants to look and potentially try out any ideas quickly:

https:/skydrive.live.com/?cid=de82af8533ac6d28&id=DE82AF8533AC6D28!242&ithint=file,.zip&authkey=!AF4IwcI0G7bsDFE

将 bx24.nztopomap 文件复制到 SD 卡的根目录进行测试.

Copy the bx24.nztopomap file to the root of your SD card to test.

来自 SQLite SDK 社区的反馈:

显然，为具有一些 C++ 技能的人添加对 SQLite SDK 的支持应该是相当直接的(我的有点生疏！):

Apparently it should be fairly straight forward to add support to the SQLite SDK for someone with some C++ skills (mine are a bit rusty!):

回复:http://www.mail-archive.com/[e-mail]/msg81059.htmlhttp://www.mail-archive.com/[e-mail]/msg81060.html

对于我原来的问题:http://www.mail-archive.com/[e-mail]/msg81055.html

有人知道可以从 SD 卡读取数据的 Windows Phone 的 SQLite 库吗?

推荐答案

您不需要用于只读访问的库，您只需要在清单中注册文件扩展名.这可能看起来像这样:

You don't need a library for read-only access, you just need to register the files extension in a manifest. That might look something like this:

<Extensions>
   <FileTypeAssociation Name="SQLite"
                        TaskID="_default"
                        NavUriFragment="fileToken=%s">
       <Logos>
           <Logo Size="small" IsRelative="true">...</Logo>
           <Logo Size="medium" IsRelative="true">...</Logo>
           <Logo Size="large" IsRelative="true">...</Logo>
       </Logos>
       <SupportedFileTypes>
         <FileType ContentType="application/sqlite">.sqlite</FileType>
       </SupportedFileTypes>
   </FileTypeAssociation>
</Extensions>

这篇关于无法从 WP8 上 SD 卡上的 SQLite DB 检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！