在 Xamarin 中使用本地数据库 [英] Use a local database in Xamarin

问题描述

我已开始使用适用于 Visual Studio 的 Xamarin 插件来创建 Android 应用.

我有一个本地 SQL 数据库，我想调用它来显示数据.我不明白我怎么能做到这一点.可能吗?

解决方案

在认为这是一件微不足道的事情之后，当我尝试设置一个快速测试项目时，我被证明是错误的.这篇博文将包含有关在 Xamarin 中为 Android 应用设置数据库的完整教程，该教程将作为未来 Xamarin 用户的参考.

一目了然:

1. 将 Sqlite.cs 添加到您的项目中.
2. 将您的数据库文件添加为资产.
3. 将您的数据库文件设置为 AndroidAsset.
4. 手动将数据库文件从您的 apk 复制到另一个目录.
5. 使用 Sqlite.SqliteConnection 打开数据库连接.
6. 使用 Sqlite 对数据库进行操作.

为 Xamarin Android 项目设置本地数据库

1.将 Sqlite.cs 添加到您的项目中.

首先去 中重命名为 db.sqlite 的 Chinook_Sqlite.sqlite 数据库示例.

3.将 DB 设置为 AndroidAsset.

右键单击 DB 文件并将其设置为构建操作 AndroidAsset.这将确保它包含在 APK 的资产目录中.

4.从您的 APK 中手动复制 DB.

由于数据库是作为资产包含的(打包在 APK 中)，因此您需要将其提取出来.

您可以使用以下代码执行此操作:

string dbName = "db.sqlite";string dbPath = Path.Combine (Android.OS.Environment.ExternalStorageDirectory.ToString(), dbName);//检查您的数据库是否已被提取.如果 (!File.Exists(dbPath)){使用 (BinaryReader br = new BinaryReader(Android.App.Application.Context.Assets.Open(dbName))){使用 (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create))){字节[]缓冲区=新字节[2048]；int len = 0;while ((len = br.Read(buffer, 0, buffer.Length)) > 0){bw.Write (buffer, 0, len);}}}}

这会将 DB 作为二进制文件从 APK 中提取出来，并将其放入系统外部存储路径中.实际上 DB 可以去任何你想去的地方，我只是选择把它贴在这里.

我还读到 Android 有一个 databases 文件夹，可以直接存储数据库；我无法让它工作，所以我只是使用这种使用现有数据库的方法运行.

5.打开数据库连接.

现在通过 Sqlite.SqliteConnection 类打开一个到数据库的连接:

using (var conn = new SQLite.SQLiteConnection(dbPath)){//在这里做些事情...}

6.在 DB 上操作.

最后，由于 Sqlite.net 是一个 ORM，您可以使用自己的数据类型对数据库进行操作:

公开课相册{[主键，自动递增]public int AlbumId { 获取；放;}公共字符串标题{获取；放;}公共 int ArtistId { 获取；放;}}//其他代码...使用 (var conn = new SQLite.SQLiteConnection(dbPath)){var cmd = new SQLite.SQLiteCommand (conn);cmd.CommandText = "从相册中选择 *";var r = cmd.ExecuteQuery<专辑>();Console.Write (r);}

总结

这就是将现有的 Sqlite 数据库添加到适用于 Android 的 Xamarin 解决方案的方法！有关更多信息，请查看 Sqlite.net 库中包含的 examples，它的 单元测试 和 examples 在 Xamarin 文档中.

I have started using the Xamarin plugin for Visual Studio to create an Android app.

I have a local SQL database, and I want to call it to display data. I don't see how I can do this. Is it possible?

解决方案

After thinking this was a trivial thing to do, I was proven wrong when I tried setup a quick test project. This post will contain a full tutorial on setting up a DB for an Android App in Xamarin that will come in handy as a reference for future Xamarin users.

At a glance:

1. Add Sqlite.cs to your project.
2. Add your database file as an Asset.
3. Set your database file to build as an AndroidAsset.
4. Manually copy the database file out of your apk to another directory.
5. Open a database connetion using Sqlite.SqliteConnection.
6. Operate on the database using Sqlite.

Setting up a local database for a Xamarin Android project

1. Add Sqlite.cs to your project.

Start by going to this repository and downloading Sqlite.cs; this provides the Sqlite API that you can use to run queries against your db. Add the file to your project as a source file.

2. Add DB as asset.

Next, get your DB and copy it into the Assets directory of your Android project and then import it into your project so that it appears beneath the Assets folder within your solution:

I'm using the Chinook_Sqlite.sqlite database sample renamed to db.sqlite from this site throughout this example.

3. Set DB to build as AndroidAsset.

Right click on the DB file and set it to build action AndroidAsset. This will ensure that it is included into the assets directory of the APK.

4. Manually copy DB out of your APK.

As the DB is included as an Asset (packaged within the APK) you will need to extract it out.

You can do this with the following code:

string dbName = "db.sqlite";
string dbPath = Path.Combine (Android.OS.Environment.ExternalStorageDirectory.ToString (), dbName);
// Check if your DB has already been extracted.
if (!File.Exists(dbPath))
{
    using (BinaryReader br = new BinaryReader(Android.App.Application.Context.Assets.Open(dbName)))
    {
        using (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
        {
            byte[] buffer = new byte[2048];
            int len = 0;
            while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
            {
                bw.Write (buffer, 0, len);
            }
        }
    }
}

This extracts the DB as a binary file from the APK and places it into the system external storage path. Realistically the DB can go wherever you want, I've just chosen to stick it here.

I also read that Android has a databases folder that will store databases directly; I couldn't get it to work so I've just ran with this method of using an existing DB.

5. Open DB Connection.

Now open a connection to the DB through the Sqlite.SqliteConnection class:

using (var conn = new SQLite.SQLiteConnection(dbPath))
{
        // Do stuff here...
}

6. Operate on DB.

Lastly, as Sqlite.net is an ORM, you can operate on the database using your own data types:

public class Album
{
    [PrimaryKey, AutoIncrement]
    public int AlbumId { get; set; }
    public string Title { get; set; }
    public int ArtistId { get; set; }
}

// Other code...

using (var conn = new SQLite.SQLiteConnection(dbPath))
{
    var cmd = new SQLite.SQLiteCommand (conn);
    cmd.CommandText = "select * from Album";
    var r = cmd.ExecuteQuery<Album> ();

    Console.Write (r);
}

Summary

And that's how to add an existing Sqlite database to your Xamarin solution for Android! For more information check out the examples included with the Sqlite.net library, its unit tests and the examples in the Xamarin documentation.

这篇关于在 Xamarin 中使用本地数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！