xamarin和mysql_将 SQLite.NET 与 Xamarin 配合使用

将 SQLite.NET 与 Xamarin 配合使用Using SQLite.NET with Xamarin.iOS

04/18/2018

本文内容

Xamarin 推荐的 SQLite.NET 库是一个基本的 ORM，可用于在 iOS 设备上的本地 SQLite 数据库中存储和检索对象。The SQLite.NET library that Xamarin recommends is a basic ORM that lets you store and retrieve objects in the local SQLite database on an iOS device.

ORM 代表对象关系映射–一种 API，可让你从数据库保存和检索 "对象"，而无需编写 SQL 语句。ORM stands for Object Relational Mapping – an API that lets you save and retrieve “objects” from a database without writing SQL statements.

使用情况Usage

若要在 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 A. 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.

var db = new SQLiteConnection (dbPath);

应按照本文档前面讨论的规则来确定 dbPath 变量。The dbPath variable should be determined according the rules discussed earlier in this document.

保存数据-创建 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

在 iOS 上运行时，此文档的DataAccess_Basic示例代码将如下所示。The DataAccess_Basic sample code for this document looks like this when running on iOS. 此代码演示如何执行简单的 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.

iOSiOS

下面的代码示例演示使用 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 添加到项目中，如下所示。This requires that you have added SQLite to your project, as highlighted here. 请注意，SQLite 数据库表是通过将属性添加到类( Stock 类)而不是 CREATE TABLE 命令来定义的。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

[列(名称)] –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. 这对于类型不能存储在数据库中的属性或无法自动解析的模型集合的属性特别有用。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 be 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 –将新的对象添加到数据库。Insert – Adds a new object to the database.

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

**表 **–返回表中的所有对象。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 在 Mono.Data.Sqlite 命名空间中调用。To change the threading mode, call SqliteConnection.SetConfig which is in the Mono.Data.Sqlite namespace. 例如，下面这行代码将为序列化模式配置 SQLite：For example, this line of code configures SQLite for Serialized mode:

using Mono.Data.Sqlite;

...

SqliteConnection.SetConfig(SQLiteConfig.Serialized);

相关链接Related Links