mono for android mysql_结合使用 SQLite.NET 和 Android

结合使用 SQLite.NET 和 AndroidUsing SQLite.NET with Android

04/18/2018

本文内容

Xamarin 推荐的 SQLite.NET 库是一个非常基本的 ORM，可让你轻松地在 Android 设备上的本地 SQLite 数据库中存储和检索对象。The SQLite.NET library that Xamarin recommends is a very basic ORM that lets you easily store and retrieve objects in the local SQLite database on an Android device. ORM 代表对象关系映射 – 一个 API，该 API 允许在不编写 SQL 语句的情况下保存和检索数据库中的 "对象"。ORM stands for Object Relational Mapping – an API that lets you save and retrieve "objects" from a database without writing SQL statements.

若要在 Xamarin 应用中包括 SQLite.NET 库，请将以下 NuGet 包添加到项目中：To include the SQLite.NET library in a Xamarin app, add the following NuGet package to your project:

包名称： sqlite 网络-pclPackage Name: sqlite-net-pcl

作者： Frank KruegerAuthor: Frank A. Krueger

ID： sqlite net pclId: sqlite-net-pcl

提示

有许多不同的 SQLite 包可用，请确保选择正确的 SQLite 包(它可能不是搜索的最大结果)。There are a number of different SQLite packages available – be sure to choose the correct one (it might not be the top result in search).

获得可用的 SQLite.NET 库后，请执行以下三个步骤以使用它来访问数据库：Once you have the SQLite.NET library available, follow these three steps to use it to access a database:

添加 using 语句– 将以下语句添加到需要数据C#访问的文件中：Add a using statement – Add the following statement to the C# files where data access is required:

using SQLite;

通过将文件路径传递给 SQLiteConnection 类构造函数来创建一个空数据库，– 可以创建数据库引用。Create a Blank Database – A database reference can be created by passing the file path the SQLiteConnection class constructor. 如果文件已存在，则不需要检查该文件是否已存在 – 如果需要，将自动创建该文件，否则将打开现有数据库文件。You do not need to check if the file already exists – it will automatically be created if required, otherwise the existing database file will be opened. 应按照本文档前面讨论的规则确定 dbPath 变量：The dbPath variable should be determined according the rules discussed earlier in this document:

var db = new SQLiteConnection (dbPath);

保存数据– 创建 SQLiteConnection 对象后，将通过调用其方法来执行数据库命令，如 CreateTable 和 Insert，如下所示：Save Data – Once you have created a SQLiteConnection object, database commands are executed by calling its methods, such as CreateTable and Insert like this:

db.CreateTable ();

db.Insert (newStock); // after creating the newStock object

检索数据– 若要检索对象(或对象列表)，请使用以下语法：Retrieve Data – To retrieve an object (or a list of objects) use the following syntax:

var stock = db.Get(5); // primary key id of 5

var stockList = db.Table();

基本数据访问示例Basic Data Access Sample

在 Android 上运行时，此文档的DataAccess_Basic示例代码将如下所示。The DataAccess_Basic sample code for this document looks like this when running on Android. 此代码演示如何执行简单的 SQLite.NET 操作并在应用程序的主窗口中以文本形式显示结果。The code illustrates how to perform simple SQLite.NET operations and shows the results in as text in the application's main window.

Outlook Web Access (OWA)Android

下面的代码示例演示使用 SQLite.NET 库来封装基础数据库访问的整个数据库交互。The following code sample shows an entire database interaction using the SQLite.NET library to encapsulate the underlying database access.

它显示：It shows:

创建数据库文件Creating the database file

通过创建对象并保存来插入一些数据Inserting some data by creating objects and then saving them

查询数据Querying the data

需要包含以下命名空间：You'll need to include these namespaces:

using SQLite; // from the github SQLite.cs class

最后一个要求您已将 SQLite 添加到您的项目中。The last one requires that you have added SQLite to your project. 请注意，通过将属性添加到类(Stock 类)而不是 CREATE TABLE 命令来定义 SQLite 数据库表。Note that the SQLite database table is defined by adding attributes to a class (the Stock class) rather than a CREATE TABLE command.

[Table("Items")]

public class Stock {

[PrimaryKey, AutoIncrement, Column("_id")]

public int Id { get; set; }

[MaxLength(8)]

public string Symbol { get; set; }

}

public static void DoSomeDataAccess () {

Console.WriteLine ("Creating database, if it doesn't already exist");

string dbPath = Path.Combine (

Environment.GetFolderPath (Environment.SpecialFolder.Personal),

"ormdemo.db3");

var db = new SQLiteConnection (dbPath);

db.CreateTable ();

if (db.Table ().Count() == 0) {

// only insert the data if it doesn't already exist

var newStock = new Stock ();

newStock.Symbol = "AAPL";

db.Insert (newStock);

newStock = new Stock ();

newStock.Symbol = "GOOG";

db.Insert (newStock);

newStock = new Stock ();

newStock.Symbol = "MSFT";

db.Insert (newStock);

}

Console.WriteLine("Reading data");

var table = db.Table ();

foreach (var s in table) {

Console.WriteLine (s.Id + " " + s.Symbol);

}

}

如果在未指定表名称参数的情况下使用 [Table] 属性，则会导致基础数据库表具有与类相同的名称(在本例中为 "Stock")。Using the [Table] attribute without specifying a table name parameter will cause the underlying database table to have the same name as the class (in this case, "Stock"). 如果直接针对数据库编写 SQL 查询，而不是使用 ORM 数据访问方法，则实际的表名非常重要。The actual table name is important if you write SQL queries directly against the database rather than use the ORM data access methods. 同样，[Column("_id")] 属性是可选的，如果不存在，列将添加到与类中的属性同名的表中。Similarly the [Column("_id")] attribute is optional, and if absent a column will be added to the table with the same name as the property in the class.

SQLite 特性SQLite Attributes

可应用于类以控制它们在基础数据库中的存储方式的常用特性包括：Common attributes that you can apply to your classes to control how they are stored in the underlying database include:

[PrimaryKey] – 此特性可应用于整数属性，以强制其成为基础表的主键。[PrimaryKey] – This attribute can be applied to an integer property to force it to be the underlying table's primary key. 不支持组合主键。Composite primary keys are not supported.

[自动增量] – 此属性将导致插入到数据库中的每个新对象的整数属性值为自动增量[AutoIncrement] – This attribute will cause an integer property's value to be auto-increment for each new object inserted into the database

[Column (name)] – name 参数设置基础数据库列的名称。[Column(name)] – The name parameter sets the underlying database column's name.

[表(名称)] – 将类标记为能够存储在指定名称的基础 SQLite 表中。[Table(name)] – Marks the class as being able to be stored in an underlying SQLite table with the name specified.

[MaxLength (值)] 在尝试执行数据库插入时，– 限制文本属性的长度。[MaxLength(value)] – Restrict the length of a text property, when a database insert is attempted. 在插入对象之前，使用代码应进行验证，因为在尝试执行数据库插入或更新操作时，仅 "选中" 此属性。Consuming code should validate this prior to inserting the object as this attribute is only 'checked' when a database insert or update operation is attempted.

[Ignore] – 导致 SQLite.NET 忽略此属性。[Ignore] – Causes SQLite.NET to ignore this property.

这对于类型不能存储在数据库中的属性或无法由 SQLite 自动解析的模型集合的属性特别有用。This is particularly useful for properties that have a type that cannot be stored in the database, or properties that model collections that cannot be resolved automatically by SQLite.

[Unique] – 确保基础数据库列中的值是唯一的。[Unique] – Ensures that the values in the underlying database column are unique.

其中的大多数属性是可选的。Most of these attributes are optional. 应始终指定整数主键，以便可以对数据高效地执行选择和删除查询。You should always specify an integer primary key so that selection and deletion queries can be performed efficiently on your data.

更多复杂查询More Complex Queries

SQLiteConnection 上的以下方法可用于执行其他数据操作：The following methods on SQLiteConnection can be used to perform other data operations:

插入– 将新的对象添加到数据库。Insert – Adds a new object to the database.

获取 – 尝试使用主键检索对象。Get – Attempts to retrieve an object using the primary key.

Table – 返回表中的所有对象。Table – Returns all the objects in the table.

删除– 使用其主键删除对象。Delete – Deletes an object using its primary key.

查询 – 执行返回多行(作为对象)的 SQL 查询。Query – Perform an SQL query that returns a number of rows (as objects).

执行– 使用此方法(而不是在不 Query)从 SQL 返回行(如插入、更新和删除说明)。Execute – Use this method (and not Query) when you don't expect rows back from the SQL (such as INSERT, UPDATE and DELETE instructions).

通过主键获取对象Getting an object by the primary key

SQLite.Net 提供 Get 方法，以根据其主键检索单个对象。SQLite.Net provides the Get method to retrieve a single object based on its primary key.

var existingItem = db.Get(3);

使用 Linq 选择对象Selecting an object using Linq

返回集合支持 IEnumerable 的方法，以便您可以使用 Linq 查询或排序表的内容。Methods that return collections support IEnumerable so you can use Linq to query or sort the contents of a table. 下面的代码演示了一个示例，该示例使用 Linq 筛选出以字母 "A" 开头的所有条目：The following code shows an example using Linq to filter out all entries that begin with the letter "A":

var apple = from s in db.Table()

where s.Symbol.StartsWith ("A")

select s;

Console.WriteLine ("-> " + apple.FirstOrDefault ().Symbol);

使用 SQL 选择对象Selecting an object using SQL

尽管 SQLite.Net 可以提供对你的数据的基于对象的访问权限，但有时你可能需要执行比 Linq 允许更复杂的查询(或者可能需要更快的性能)。Even though SQLite.Net can provide object-based access to your data, sometimes you might need to do a more complex query than Linq allows (or you may need faster performance). 可以将 SQL 命令与 Query 方法一起使用，如下所示：You can use SQL commands with the Query method, as shown here:

var stocksStartingWithA = db.Query("SELECT * FROM Items WHERE Symbol = ?", "A");

foreach (var s in stocksStartingWithA) {

Console.WriteLine ("a " + s.Symbol);

}

备注

直接编写 SQL 语句时，会创建数据库中表和列的名称的依赖关系，这些表和列是从你的类及其属性生成的。When writing SQL statements directly you create a dependency on the names of tables and columns in your database, which have been generated from your classes and their attributes. 如果在代码中更改这些名称，则必须记住更新所有手动写入的 SQL 语句。If you change those names in your code you must remember to update any manually written SQL statements.

删除对象Deleting an object

主密钥用于删除行，如下所示：The primary key is used to delete the row, as shown here:

var rowcount = db.Delete(someStock.Id); // Id is the primary key

您可以检查 rowcount 以确认受影响的行数(在此示例中已删除)。You can check the rowcount to confirm how many rows were affected (deleted in this case).

将 SQLite.NET 用于多个线程Using SQLite.NET with Multiple Threads

SQLite 支持三个不同的线程模式：单线程、多线程和序列化。SQLite supports three different threading modes: Single-thread, Multi-thread, and Serialized. 如果要从多个线程访问数据库，而不受任何限制，则可以将 SQLite 配置为使用序列化的线程模式。If you want to access the database from multiple threads without any restrictions, you can configure SQLite to use the Serialized threading mode. 在应用程序的早期设置此模式很重要(例如，在 OnCreate 方法的开头)。It's important to set this mode early in your application (for example, at the beginning of the OnCreate method).

若要更改线程模式，请调用 SqliteConnection.SetConfig。To change the threading mode, call SqliteConnection.SetConfig. 例如，下面这行代码将为序列化模式配置 SQLite：For example, this line of code configures SQLite for Serialized mode:

using using Mono.Data.Sqlite;

...

SqliteConnection.SetConfig(SQLiteConfig.Serialized);

Android 版本 SQLite 有一个限制，需要执行几个步骤。The Android version of SQLite has a limitation that requires a few more steps. 如果对 SqliteConnection.SetConfig 的调用产生了一个 SQLite 异常，如 library used incorrectly，则必须使用以下解决方法：If the call to SqliteConnection.SetConfig produces a SQLite exception such as library used incorrectly, then you must use the following workaround:

链接到本机libsqlite.so库，以便将 sqlite3_shutdown 和 sqlite3_initialize api 提供给应用程序：Link to the native libsqlite.so library so that the sqlite3_shutdown and sqlite3_initialize APIs are made available to the app:

[DllImport("libsqlite.so")]

internal static extern int sqlite3_shutdown();

[DllImport("libsqlite.so")]

internal static extern int sqlite3_initialize();

在 OnCreate 方法的最开始处，添加以下代码以关闭 SQLite，将其配置为序列化模式，然后重新初始化 SQLite：At the very beginning of the OnCreate method, add this code to shutdown SQLite, configure it for Serialized mode, and reinitialize SQLite:

using using Mono.Data.Sqlite;

...

sqlite3_shutdown();

SqliteConnection.SetConfig(SQLiteConfig.Serialized);

sqlite3_initialize();

此解决方法还适用于 Mono.Data.Sqlite 库。This workaround also works for the Mono.Data.Sqlite library. 有关 SQLite 和多线程处理的详细信息，请参阅sqlite 和多线程。For more information about SQLite and multi-threading, see SQLite and Multiple Threads.

相关链接Related Links