Xamarin 中的数据库Databases in Xamarin.Mac
03/14/2017
本文内容
本文介绍如何使用键/值编码和键-值观察，以允许在 Xcode 的 Interface Builder 中的 SQLite 数据库和 UI 元素之间进行数据绑定。还介绍了如何使用 SQLite.NET ORM 提供对 SQLite 数据的访问。This article covers using key-value coding and key-value observing to allow for data binding between SQLite databases and UI elements in Xcode's Interface Builder. It also covers using the SQLite.NET ORM to provide access to SQLite data.
概述Overview
在 Xamarin 应用程序中使用 c # 和 .NET 时，可以访问 Xamarin 或 Xamarin 应用程序可以访问的相同 SQLite 数据库。When working with C# and .NET in a Xamarin.Mac application, you have access to the same SQLite databases that a Xamarin.iOS or Xamarin.Android application can access.
在本文中，我们将介绍两种访问 SQLite 数据的方法：In this article we will be covering two ways to access SQLite data:
直接访问 -通过直接访问 SQLite 数据库，我们可以将数据库中的数据用于键值编码，并使用在 Xcode 的 Interface Builder 中创建的 UI 元素进行数据绑定。Direct Access - By directly accessing a SQLite Database, we can use data from the database for key-value coding and data binding with UI elements created in Xcode's Interface Builder. 通过在 Xamarin 应用程序中使用键/值编码和数据绑定技术，可以极大地减少需要编写和维护的代码量，以填充和处理 UI 元素。By using key-value coding and data binding techniques in your Xamarin.Mac application, you can greatly decrease the amount of code that you have to write and maintain to populate and work with UI elements. 你还可以从你的前端用户界面 (模型-视图-控制器) 中进一步分离你的支持数据 (数据) 模型，从而更易于维护、更灵活的应用程序设计。You also have the benefit of further decoupling your backing data (Data Model) from your front end User Interface (Model-View-Controller), leading to easier to maintain, more flexible application design.
SQLITE.NET orm -通过使用开源 SQLite.NET 对象关系管理器 (orm) 我们可以极大地减少在 SQLite 数据库中读取和写入数据所需的代码量。SQLite.NET ORM - By using the open source SQLite.NET Object Relationship Manager (ORM) we can greatly reduce the amount of code required to read and write data from a SQLite database. 然后，可以使用此数据填充某个用户界面项，如表视图。This data can then be used to populate a user interface item such as a Table View.
在本文中，我们将介绍有关在 Xamarin Mac 应用程序中使用 SQLite 数据库的键值编码和数据绑定的基本知识。In this article, we'll cover the basics of working with key-value coding and data binding with SQLite Databases in a Xamarin.Mac application. 强烈建议您先完成 Hello，Mac 一文，特别是 Xcode 和 Interface Builder 及 输出口和操作 部分的简介，因为它涵盖了我们将在本文中使用的重要概念和技巧。It is highly suggested that you work through the Hello, Mac article first, specifically the Introduction to Xcode and Interface Builder and Outlets and Actions sections, as it covers key concepts and techniques that we'll be using in this article.
由于我们将使用键/值编码和数据绑定，因此，请先通过 数据绑定和键-值编码 ，因为本文档及其示例应用程序中将介绍核心技术和概念。Since we will be using key-value coding and data binding, please work through the Data binding and key-value coding first, as core techniques and concepts will be covered that will be used in this documentation and its sample application.
您可能想要了解如何向Xamarin 内部对象公开 c # 类/方法， Register 并说明用于将 Export c # 类与目标 c 对象和 UI 元素连接起来的和特性。You may want to take a look at the Exposing C# classes / methods to Objective-C section of the Xamarin.Mac Internals document as well, it explains the Register and Export attributes used to wire up your C# classes to Objective-C objects and UI elements.
直接 SQLite 访问Direct SQLite access
对于将绑定到 Xcode 的 Interface Builder 中的 UI 元素的 SQLite 数据，强烈建议你直接访问 SQLite 数据库 (而不是使用 ORM) 等方法，因为你完全控制从数据库中写入和读取数据的方式。For SQLite data that is going to be bound to UI elements in Xcode's Interface Builder, it is highly suggested that you access the SQLite database directly (as opposed to using a technique such as an ORM), since you have total control over the way the data is written and read from the database.
正如我们在 数据绑定和键/值编码 文档中所述，通过在 Xamarin 应用程序中使用键/值编码和数据绑定技术，可以极大地减少需要编写和维护的代码量，以填充和处理 UI 元素。As we have seen in the Data Binding and Key-Value Coding documentation, by using key-value coding and data binding techniques in your Xamarin.Mac application, you can greatly decrease the amount of code that you have to write and maintain to populate and work with UI elements. 与对 SQLite 数据库的直接访问结合使用时，它还可以极大地减少在数据库中读取和写入数据所需的代码量。When combined with direct access to a SQLite database, it can also greatly reduce the amount of code required to read and write data to that database.
在本文中，我们将从数据绑定和键值编码文档中修改示例应用程序，以使用 SQLite 数据库作为绑定的后备源。In this article, we will be modifying the sample app from the data binding and key-value coding document to use a SQLite Database as the backing source for the binding.
包括 SQLite 数据库支持Including SQLite database support
在继续操作之前，我们需要将对几个的引用添加到应用程序中。Dll 文件。Before we can continue, we need to add SQLite database support to our application by including References to a couple of .DLLs files.
执行以下操作：Do the following:
在 Solution Pad中，右键单击 " 引用 " 文件夹，然后选择 " 编辑引用"。In the Solution Pad, right-click on the References folder and select Edit References.
同时选择 Mono 和 system.object 程序集：Select both the Mono.Data.Sqlite and System.Data assemblies:
单击 "确定" 按钮保存所做的更改并添加引用。Click the OK button to save your changes and add the references.
修改数据模型Modifying the data model
现在，我们已添加了对直接访问应用程序的 SQLite 数据库的支持，接下来需要修改数据模型对象，以便从数据库 (读取和写入数据，并提供键值编码和数据绑定) 。Now that we have added support for directly accessing a SQLite database to our application, we need to modify our Data Model Object to read and write data from the database (as well as provide key-value coding and data binding). 对于我们的示例应用程序，我们将编辑 PersonModel.cs 类，使其类似于以下内容：In the case of our sample application, we'll edit the PersonModel.cs class and make it look like the following:
using System;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
using Foundation;
using AppKit;
namespace MacDatabase
{
[Register("PersonModel")]
public class PersonModel : NSObject
{
#region Private Variables
private string _ID = "";
private string _managerID = "";
private string _name = "";
private string _occupation = "";
private bool _isManager = false;
private NSMutableArray _people = new NSMutableArray();
private SqliteConnection _conn = null;
#endregion
#region Computed Properties
public SqliteConnection Conn {
get { return _conn; }
set { _conn = value; }
}
[Export("ID")]
public string ID {
get { return _ID; }
set {
WillChangeValue ("ID");
_ID = value;
DidChangeValue ("ID");
}
}
[Export("ManagerID")]
public string ManagerID {
get { return _managerID; }
set {
WillChangeValue ("ManagerID");
_managerID = value;
DidChangeValue ("ManagerID");
}
}
[Export("Name")]
public string Name {
get { return _name; }
set {
WillChangeValue ("Name");
_name = value;
DidChangeValue ("Name");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
[Export("Occupation")]
public string Occupation {
get { return _occupation; }
set {
WillChangeValue ("Occupation");
_occupation = value;
DidChangeValue ("Occupation");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
[Export("isManager")]
public bool isManager {
get { return _isManager; }
set {
WillChangeValue ("isManager");
WillChangeValue ("Icon");
_isManager = value;
DidChangeValue ("isManager");
DidChangeValue ("Icon");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
[Export("isEmployee")]
public bool isEmployee {
get { return (NumberOfEmployees == 0); }
}
[Export("Icon")]
public NSImage Icon {
get {
if (isManager) {
return NSImage.ImageNamed ("group.png");
} else {
return NSImage.ImageNamed ("user.png");
}
}
}
[Export("personModelArray")]
public NSArray People {
get { return _people; }
}
[Export("NumberOfEmployees")]
public nint NumberOfEmployees {
get { return (nint)_people.Count; }
}
#endregion
#region Constructors
public PersonModel ()
{
}
public PersonModel (string name, string occupation)
{
// Initialize
this.Name = name;
this.Occupation = occupation;
}
public PersonModel (string name, string occupation, bool manager)
{
// Initialize
this.Name = name;
this.Occupation = occupation;
this.isManager = manager;
}
public PersonModel (string id, string name, string occupation)
{
// Initialize
this.ID = id;
this.Name = name;
this.Occupation = occupation;
}
public PersonModel (SqliteConnection conn, string id)
{
// Load from database
Load (conn, id);
}
#endregion
#region Array Controller Methods
[Export("addObject:")]
public void AddPerson(PersonModel person) {
WillChangeValue ("personModelArray");
isManager = true;
_people.Add (person);
DidChangeValue ("personModelArray");
}
[Export("insertObject:inPersonModelArrayAtIndex:")]
public void InsertPerson(PersonModel person, nint index) {
WillChangeValue ("personModelArray");
_people.Insert (person, index);
DidChangeValue ("personModelArray");
}
[Export("removeObjectFromPersonModelArrayAtIndex:")]
public void RemovePerson(nint index) {
WillChangeValue ("personModelArray");
_people.RemoveObject (index);
DidChangeValue ("personModelArray");
}
[Export("setPersonModelArray:")]
public void SetPeople(NSMutableArray array) {
WillChangeValue ("personModelArray");
_people = array;
DidChangeValue ("personModelArray");
}
#endregion
#region SQLite Routines
public void Create(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Create new record ID?
if (ID == "") {
ID = Guid.NewGuid ().ToString();
}
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "INSERT INTO [People] (ID, Name, Occupation, isManager, ManagerID) VALUES (@COL1, @COL2, @COL3, @COL4, @COL5)";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
command.Parameters.AddWithValue ("@COL2", Name);
command.Parameters.AddWithValue ("@COL3", Occupation);
command.Parameters.AddWithValue ("@COL4", isManager);
command.Parameters.AddWithValue ("@COL5", ManagerID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Save manager ID and create the sub record
Person.ManagerID = ID;
Person.Create (conn);
}
// Save last connection
_conn = conn;
}
public void Update(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "UPDATE [People] SET Name = @COL2, Occupation = @COL3, isManager = @COL4, ManagerID = @COL5 WHERE ID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
command.Parameters.AddWithValue ("@COL2", Name);
command.Parameters.AddWithValue ("@COL3", Occupation);
command.Parameters.AddWithValue ("@COL4", isManager);
command.Parameters.AddWithValue ("@COL5", ManagerID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Update sub record
Person.Update (conn);
}
// Save last connection
_conn = conn;
}
public void Load(SqliteConnection conn, string id) {
bool shouldClose = false;
// Clear last connection to prevent circular call to update
_conn = null;
// Is the database already open?
if (conn.State != ConnectionState.Open) {
shouldClose = true;
conn.Open ();
}
// Execute query
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT * FROM [People] WHERE ID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Pull values back into class
ID = (string)reader [0];
Name = (string)reader [1];
Occupation = (string)reader [2];
isManager = (bool)reader [3];
ManagerID = (string)reader [4];
}
}
}
// Is this a manager?
if (isManager) {
// Yes, load children
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT ID FROM [People] WHERE ManagerID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Load child and add to collection
var childID = (string)reader [0];
var person = new PersonModel (conn, childID);
_people.Add (person);
}
}
}
}
// Should we close the connection to the database
if (shouldClose) {
conn.Close ();
}
// Save last connection
_conn = conn;
}
public void Delete(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "DELETE FROM [People] WHERE (ID = @COL1 OR ManagerID = @COL1)";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Empty class
ID = "";
ManagerID = "";
Name = "";
Occupation = "";
isManager = false;
_people = new NSMutableArray();
// Save last connection
_conn = conn;
}
#endregion
}
}
让我们看一下下面的详细修改。Let's take a look at the modifications in detail below.
首先，我们添加了几个使用 SQLite 所需的 using 语句，并添加了一个变量来保存到 SQLite 数据库的最后一个连接：First, we've added several using statements that are required to use SQLite and we've added a variable to save our last connection to the SQLite database:
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
...
private SqliteConnection _conn = null;
当用户通过数据绑定在 UI 中修改内容时，我们将使用此保存的连接自动将对记录所做的任何更改保存到数据库：We'll use this saved connection to automatically save any changes to the record to the database when the user modifies the contents in the UI via data binding:
[Export("Name")]
public string Name {
get { return _name; }
set {
WillChangeValue ("Name");
_name = value;
DidChangeValue ("Name");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
[Export("Occupation")]
public string Occupation {
get { return _occupation; }
set {
WillChangeValue ("Occupation");
_occupation = value;
DidChangeValue ("Occupation");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
[Export("isManager")]
public bool isManager {
get { return _isManager; }
set {
WillChangeValue ("isManager");
WillChangeValue ("Icon");
_isManager = value;
DidChangeValue ("isManager");
DidChangeValue ("Icon");
// Save changes to database?
if (_conn != null) Update (_conn);
}
}
如果在 (之前保存了数据，则对 名称、 职业 或 ismanager.exe 属性所做的任何更改都将发送到数据库，例如，如果 _conn 未) 变量 null 。Any changes made to the Name, Occupation or isManager properties will be sent to the database if the data has been saved there before (e.g. if the _conn variable is not null). 接下来，让我们看一下为 创建、 更新、 加载 和 删除 数据库中的人员而添加的方法。Next, let's look at the methods that we've added to Create, Update, Load and Delete people from the database.
新建记录Create a new record
添加了以下代码以在 SQLite 数据库中创建新记录：The following code was added to create a new record in the SQLite database:
public void Create(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Create new record ID?
if (ID == "") {
ID = Guid.NewGuid ().ToString();
}
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "INSERT INTO [People] (ID, Name, Occupation, isManager, ManagerID) VALUES (@COL1, @COL2, @COL3, @COL4, @COL5)";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
command.Parameters.AddWithValue ("@COL2", Name);
command.Parameters.AddWithValue ("@COL3", Occupation);
command.Parameters.AddWithValue ("@COL4", isManager);
command.Parameters.AddWithValue ("@COL5", ManagerID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Save manager ID and create the sub record
Person.ManagerID = ID;
Person.Create (conn);
}
// Save last connection
_conn = conn;
}
我们使用在 SQLiteCommand 数据库中创建新记录。We are using a SQLiteCommand to create the new record in the database. 我们将从 (conn) 获取一个新命令，该命令 SQLiteConnection 通过调用传递到方法 CreateCommand 。We get a new command from the SQLiteConnection (conn) that we passed into the method by calling CreateCommand. 接下来，我们将 SQL 指令设置为实际写入新记录，并为实际值提供参数：Next, we set the SQL instruction to actually write the new record, providing parameters for the actual values:
command.CommandText = "INSERT INTO [People] (ID, Name, Occupation, isManager, ManagerID) VALUES (@COL1, @COL2, @COL3, @COL4, @COL5)";
稍后我们使用中的方法设置参数的值 Parameters.AddWithValue SQLiteCommand 。Later we set the values for the parameters using the Parameters.AddWithValue method on the SQLiteCommand. 使用参数，可以确保在将值 (如单引号) 在发送到 SQLite 之前得到正确编码。By using parameters, we ensure that values (such as a single quote) get properly encoded before being sent to SQLite. 示例：Example:
command.Parameters.AddWithValue ("@COL1", ID);
最后，由于一个人可以是经理，并且拥有一个员工的集合，因此我们将以递归方式调用 Create 这些人的方法将其保存到数据库中：Finally, since a person can be a manager and have a collection of employees under them, we are recursively calling the Create method on those people to save them to the database as well:
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Save manager ID and create the sub record
Person.ManagerID = ID;
Person.Create (conn);
}
更新记录Updating a record
添加了以下代码来更新 SQLite 数据库中的现有记录：The following code was added to update an existing record in the SQLite database:
public void Update(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "UPDATE [People] SET Name = @COL2, Occupation = @COL3, isManager = @COL4, ManagerID = @COL5 WHERE ID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
command.Parameters.AddWithValue ("@COL2", Name);
command.Parameters.AddWithValue ("@COL3", Occupation);
command.Parameters.AddWithValue ("@COL4", isManager);
command.Parameters.AddWithValue ("@COL5", ManagerID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Update sub record
Person.Update (conn);
}
// Save last connection
_conn = conn;
}
与上面的 创建 类似，我们 SQLiteCommand 从传入的中获取 SQLiteConnection ，并设置 SQL 来更新记录 (提供) 的参数：Like Create above, we get a SQLiteCommand from the passed in SQLiteConnection, and set our SQL to update our record (providing parameters):
command.CommandText = "UPDATE [People] SET Name = @COL2, Occupation = @COL3, isManager = @COL4, ManagerID = @COL5 WHERE ID = @COL1";
我们会填写参数值 (例如： command.Parameters.AddWithValue ("@COL1", ID);) 再次递归调用任何子记录上的更新：We fill in the parameter values (example: command.Parameters.AddWithValue ("@COL1", ID);) and again, recursively call update on any child records:
// Save children to database as well
for (nuint n = 0; n < People.Count; ++n) {
// Grab person
var Person = People.GetItem(n);
// Update sub record
Person.Update (conn);
}
正在加载记录Loading a record
添加了以下代码以从 SQLite 数据库加载现有记录：The following code was added to load an existing record from the SQLite database:
public void Load(SqliteConnection conn, string id) {
bool shouldClose = false;
// Clear last connection to prevent circular call to update
_conn = null;
// Is the database already open?
if (conn.State != ConnectionState.Open) {
shouldClose = true;
conn.Open ();
}
// Execute query
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT * FROM [People] WHERE ID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Pull values back into class
ID = (string)reader [0];
Name = (string)reader [1];
Occupation = (string)reader [2];
isManager = (bool)reader [3];
ManagerID = (string)reader [4];
}
}
}
// Is this a manager?
if (isManager) {
// Yes, load children
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT ID FROM [People] WHERE ManagerID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Load child and add to collection
var childID = (string)reader [0];
var person = new PersonModel (conn, childID);
_people.Add (person);
}
}
}
}
// Should we close the connection to the database
if (shouldClose) {
conn.Close ();
}
// Save last connection
_conn = conn;
}
由于可以从父对象(例如 (管理对象) 加载其 employees 对象)递归调用例程，因此添加了特殊代码来处理打开和关闭数据库的连接：Because the routine can be called recursively from a parent object (such as a manager object loading their employees object), special code was added to handle opening and closing the connection to the database:
bool shouldClose = false;
...
// Is the database already open?
if (conn.State != ConnectionState.Open) {
shouldClose = true;
conn.Open ();
}
...
// Should we close the connection to the database
if (shouldClose) {
conn.Close ();
}
与往常一样，我们将 SQL 设置为检索记录并使用参数：As always, we set our SQL to retrieve the record and use parameters:
// Create new command
command.CommandText = "SELECT ID FROM [People] WHERE ManagerID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
最后，我们使用数据读取器来执行查询，并返回 (复制到类的实例中的记录字段 PersonModel) ：Finally, we use a Data Reader to execute the query and return the record fields (which we copy into the instance of the PersonModel class):
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Pull values back into class
ID = (string)reader [0];
Name = (string)reader [1];
Occupation = (string)reader [2];
isManager = (bool)reader [3];
ManagerID = (string)reader [4];
}
}
如果此人是经理，还需要通过递归调用其方法) 来加载其所有员工 (Load ：If this person is a manager, we need to also load all of their employees (again, by recursively calling their Load method):
// Is this a manager?
if (isManager) {
// Yes, load children
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT ID FROM [People] WHERE ManagerID = @COL1";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", id);
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Load child and add to collection
var childID = (string)reader [0];
var person = new PersonModel (conn, childID);
_people.Add (person);
}
}
}
}
删除记录Deleting a record
添加了以下代码以从 SQLite 数据库中删除现有记录：The following code was added to delete an existing record from the SQLite database:
public void Delete(SqliteConnection conn) {
// Clear last connection to prevent circular call to update
_conn = null;
// Execute query
conn.Open ();
using (var command = conn.CreateCommand ()) {
// Create new command
command.CommandText = "DELETE FROM [People] WHERE (ID = @COL1 OR ManagerID = @COL1)";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
// Write to database
command.ExecuteNonQuery ();
}
conn.Close ();
// Empty class
ID = "";
ManagerID = "";
Name = "";
Occupation = "";
isManager = false;
_people = new NSMutableArray();
// Save last connection
_conn = conn;
}
在这里，我们将使用参数) ，为 SQL 提供删除管理器记录和该管理器下任何员工的记录 (：Here we provide the SQL to delete both the managers record and the records of any employees under that manager (using parameters):
// Create new command
command.CommandText = "DELETE FROM [People] WHERE (ID = @COL1 OR ManagerID = @COL1)";
// Populate with data from the record
command.Parameters.AddWithValue ("@COL1", ID);
删除记录后，将清除类的当前实例 PersonModel ：After the record has been removed, we clear out the current instance of the PersonModel class:
// Empty class
ID = "";
ManagerID = "";
Name = "";
Occupation = "";
isManager = false;
_people = new NSMutableArray();
正在初始化数据库Initializing the database
对数据模型进行了更改，以便支持读取和写入数据库时，需要打开到数据库的连接，并在首次运行时对其进行初始化。With the changes to our Data Model in place to support reading and writing to the database, we need to open a connection to the database and initialize it on the first run. 让我们将以下代码添加到 MainWindow.cs 文件中：Let's add the following code to our MainWindow.cs file:
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
...
private SqliteConnection DatabaseConnection = null;
...
private SqliteConnection GetDatabaseConnection() {
var documents = Environment.GetFolderPath (Environment.SpecialFolder.Desktop);
string db = Path.Combine (documents, "People.db3");
// Create the database if it doesn't already exist
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
// Create connection to the database
var conn = new SqliteConnection("Data Source=" + db);
// Set the structure of the database
if (!exists) {
var commands = new[] {
"CREATE TABLE People (ID TEXT, Name TEXT, Occupation TEXT, isManager BOOLEAN, ManagerID TEXT)"
};
conn.Open ();
foreach (var cmd in commands) {
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
c.ExecuteNonQuery ();
}
}
conn.Close ();
// Build list of employees
var Craig = new PersonModel ("0","Craig Dunn", "Documentation Manager");
Craig.AddPerson (new PersonModel ("Amy Burns", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Joel Martinez", "Web & Infrastructure"));
Craig.AddPerson (new PersonModel ("Kevin Mullins", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Mark McLemore", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Tom Opgenorth", "Technical Writer"));
Craig.Create (conn);
var Larry = new PersonModel ("1","Larry O'Brien", "API Documentation Manager");
Larry.AddPerson (new PersonModel ("Mike Norman", "API Documentor"));
Larry.Create (conn);
}
// Return new connection
return conn;
}
让我们详细了解上面的代码。Let's take a closer look at the code above. 首先，我们选择新数据库的位置 (在此示例中，用户的桌面) ，查看数据库是否存在，如果不存在，请创建它：First, we pick a location for the new database (in this example, the user's Desktop), look to see if the database exists, and if it doesn't, create it:
var documents = Environment.GetFolderPath (Environment.SpecialFolder.Desktop);
string db = Path.Combine (documents, "People.db3");
// Create the database if it doesn't already exist
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
接下来，使用我们在上面创建的路径建立连接到数据库的连接：Next, we establish the connect to the database using the path we created above:
var conn = new SqliteConnection("Data Source=" + db);
然后，在数据库中创建所需的所有 SQL 表：Then we create all the SQL tables in the database that we require:
var commands = new[] {
"CREATE TABLE People (ID TEXT, Name TEXT, Occupation TEXT, isManager BOOLEAN, ManagerID TEXT)"
};
conn.Open ();
foreach (var cmd in commands) {
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
c.ExecuteNonQuery ();
}
}
conn.Close ();
最后，我们使用数据模型 (PersonModel) 在应用程序第一次运行时或数据库丢失时，为数据库创建一组默认记录：Finally, we use our Data Model (PersonModel) to create a default set of records for the database the first time the application is run or if the database is missing:
// Build list of employees
var Craig = new PersonModel ("0","Craig Dunn", "Documentation Manager");
Craig.AddPerson (new PersonModel ("Amy Burns", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Joel Martinez", "Web & Infrastructure"));
Craig.AddPerson (new PersonModel ("Kevin Mullins", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Mark McLemore", "Technical Writer"));
Craig.AddPerson (new PersonModel ("Tom Opgenorth", "Technical Writer"));
Craig.Create (conn);
var Larry = new PersonModel ("1","Larry O'Brien", "API Documentation Manager");
Larry.AddPerson (new PersonModel ("Mike Norman", "API Documentor"));
Larry.Create (conn);
当应用程序启动并打开主窗口时，我们将使用上面添加的代码建立与数据库的连接：When the application starts and opens the Main Window, we make a connection to the database using the code we added above:
public override void AwakeFromNib ()
{
base.AwakeFromNib ();
// Get access to database
DatabaseConnection = GetDatabaseConnection ();
}
加载绑定数据Loading bound data
使用所有组件直接从 SQLite 数据库访问绑定数据，我们可以将数据加载到应用程序提供的不同视图中，并自动在我们的 UI 中显示。With all the components for directly accessing bound data from a SQLite database in place, we can load the data in the different views that our application provides and it will automatically be displayed in our UI.
加载单个记录Loading a single record
若要加载 ID 已知的单个记录，可以使用以下代码：To load a single record where the ID is know, we can use the following code:
Person = new PersonModel (Conn, "0");
加载所有记录Loading all records
若要加载所有人员，无论他们是否为经理，请使用以下代码：To load all people, regardless if they are a manager or not, use the following code:
// Load all employees
_conn.Open ();
using (var command = _conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT ID FROM [People]";
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Load child and add to collection
var childID = (string)reader [0];
var person = new PersonModel (_conn, childID);
AddPerson (person);
}
}
}
_conn.Close ();
此处，我们使用类的构造函数重载 PersonModel 将人员加载到内存中：Here, we are using an overload of the constructor for the PersonModel class to load the person into memory:
var person = new PersonModel (_conn, childID);
我们还将调用数据绑定类以将人员添加到我们的人员集合 AddPerson (person) ，这可确保用户的 UI 识别并显示更改：We are also calling the Data Bound class to add the person to our collection of people AddPerson (person), this ensures that our UI recognizes the change and displays it:
[Export("addObject:")]
public void AddPerson(PersonModel person) {
WillChangeValue ("personModelArray");
isManager = true;
_people.Add (person);
DidChangeValue ("personModelArray");
}
仅加载顶级记录Loading top level records only
若要仅加载经理 (例如，若要在大纲视图) 中显示数据，请使用以下代码：To load only managers (for example, to display data in an Outline View), we use the following code:
// Load only managers employees
_conn.Open ();
using (var command = _conn.CreateCommand ()) {
// Create new command
command.CommandText = "SELECT ID FROM [People] WHERE isManager = 1";
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Load child and add to collection
var childID = (string)reader [0];
var person = new PersonModel (_conn, childID);
AddPerson (person);
}
}
}
_conn.Close ();
In SQL 语句中唯一的实际差别 (仅加载管理器 command.CommandText = "SELECT ID FROM [People] WHERE isManager = 1") 但与上述部分的工作方式相同。The only real difference in the in SQL statement (which loads only managers command.CommandText = "SELECT ID FROM [People] WHERE isManager = 1") but works the same as the section above otherwise.
数据库和组合框Databases and comboboxes
可对 macOS ((如组合框)使用的菜单控件) 可以设置为从可在) Interface Builder 中预定义的内部列表 (或通过提供自己的自定义外部数据源填充下拉列表。The Menu Controls available to macOS (such as the Combo Box) can be set to populate the dropdown list either from an internal list (that can be pre-defined in Interface Builder or populated via code) or by providing your own custom, external data source. 有关更多详细信息，请参阅 提供菜单控件数据 。
例如，在 Interface Builder 中编辑上述简单绑定示例，添加一个组合框，并使用名为的插座来公开它 EmployeeSelector ：As an example, edit the Simple Binding example above in Interface Builder, add a Combo Box and expose it using an outlet named EmployeeSelector:
在 " 属性检查器" 中，检查 Prompt ，并 使用数据源 属性：In the Attributes Inspector, check the Autocompletes and Uses Data Source properties:
保存更改并返回到要同步的 Visual Studio for Mac。Save your changes and return to Visual Studio for Mac to sync.
提供 combobox 数据Providing combobox data
接下来，将一个名为的新类添加到项目， ComboBoxDataSource 并使其类似于以下内容：Next, add a new class to the project called ComboBoxDataSource and make it look like the following:
using System;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
using Foundation;
using AppKit;
namespace MacDatabase
{
public class ComboBoxDataSource : NSComboBoxDataSource
{
#region Private Variables
private SqliteConnection _conn = null;
private string _tableName = "";
private string _IDField = "ID";
private string _displayField = "";
private nint _recordCount = 0;
#endregion
#region Computed Properties
public SqliteConnection Conn {
get { return _conn; }
set { _conn = value; }
}
public string TableName {
get { return _tableName; }
set {
_tableName = value;
_recordCount = GetRecordCount ();
}
}
public string IDField {
get { return _IDField; }
set {
_IDField = value;
_recordCount = GetRecordCount ();
}
}
public string DisplayField {
get { return _displayField; }
set {
_displayField = value;
_recordCount = GetRecordCount ();
}
}
public nint RecordCount {
get { return _recordCount; }
}
#endregion
#region Constructors
public ComboBoxDataSource (SqliteConnection conn, string tableName, string displayField)
{
// Initialize
this.Conn = conn;
this.TableName = tableName;
this.DisplayField = displayField;
}
public ComboBoxDataSource (SqliteConnection conn, string tableName, string idField, string displayField)
{
// Initialize
this.Conn = conn;
this.TableName = tableName;
this.IDField = idField;
this.DisplayField = displayField;
}
#endregion
#region Private Methods
private nint GetRecordCount ()
{
bool shouldClose = false;
nint count = 0;
// Has a Table, ID and display field been specified?
if (TableName !="" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT count({IDField}) FROM [{TableName}]";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read count from query
var result = (long)reader [0];
count = (nint)result;
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return the number of records
return count;
}
#endregion
#region Public Methods
public string IDForIndex (nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {IDField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
public string ValueForIndex (nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
public string IDForValue (string value)
{
NSString result = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {IDField} FROM [{TableName}] WHERE {DisplayField} = @VAL";
// Populate parameters
command.Parameters.AddWithValue ("@VAL", value);
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
result = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return result;
}
#endregion
#region Override Methods
public override nint ItemCount (NSComboBox comboBox)
{
return RecordCount;
}
public override NSObject ObjectValueForItem (NSComboBox comboBox, nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
public override nint IndexOfItem (NSComboBox comboBox, string value)
{
bool shouldClose = false;
bool found = false;
string field = "";
nint index = NSRange.NotFound;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read () && !found) {
// Read the display field from the query
field = (string)reader [0];
++index;
// Is this the value we are searching for?
if (value == field) {
// Yes, exit loop
found = true;
}
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return index;
}
public override string CompletedString (NSComboBox comboBox, string uncompletedString)
{
bool shouldClose = false;
bool found = false;
string field = "";
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Escape search string
uncompletedString = uncompletedString.Replace ("'", "");
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] WHERE {DisplayField} LIKE @VAL";
// Populate parameters
command.Parameters.AddWithValue ("@VAL", uncompletedString + "%");
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
field = (string)reader [0];
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return field;
}
#endregion
}
}
在此示例中，我们将创建一个新 NSComboBoxDataSource 的，它可以显示来自任何 SQLite 数据源的组合框项。In this example, we are creating a new NSComboBoxDataSource that can present Combo Box Items from any SQLite Data Source. 首先，我们定义以下属性：First, we define the following properties:
Conn -获取或设置与 SQLite 数据库的连接。Conn - Gets or sets a connection to the SQLite database.
TableName -获取或设置表名。TableName - Gets or sets the table name.
IDField -获取或设置为给定表提供唯一 ID 的字段。IDField - Gets or sets the field that provides the unique ID for the given Table. 默认值为 ID。The default value is ID.
DisplayField -获取或设置下拉列表中显示的字段。DisplayField - Gets or sets the field that is displayed in the dropdown list.
RecordCount -获取给定表中的记录数。RecordCount - Gets the number of records in the given Table.
创建对象的新实例时，我们传入连接、表名称，还可以选择 "ID" 字段和显示字段：When we create a new instance of the object, we pass in the connection, table name, optionally the ID field and the display field:
public ComboBoxDataSource (SqliteConnection conn, string tableName, string displayField)
{
// Initialize
this.Conn = conn;
this.TableName = tableName;
this.DisplayField = displayField;
}
GetRecordCount方法返回给定表中的记录数：The GetRecordCount method returns the number of records in the given Table:
private nint GetRecordCount ()
{
bool shouldClose = false;
nint count = 0;
// Has a Table, ID and display field been specified?
if (TableName !="" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT count({IDField}) FROM [{TableName}]";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read count from query
var result = (long)reader [0];
count = (nint)result;
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return the number of records
return count;
}
只要 TableName IDField 更改了或属性值，就会调用此方法 DisplayField 。It is called any time the TableName, IDField or DisplayField properties value is changed.
IDForIndex方法返回 IDField 给定下拉列表项索引处的记录的唯一 ID () ：The IDForIndex method returns the unique ID (IDField) for the record at the given dropdown list item index:
public string IDForIndex (nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {IDField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
ValueForIndex方法返回 DisplayField 给定下拉列表索引处的项 () 值：The ValueForIndex method returns the value (DisplayField) for the item at the given dropdown list index:
public string ValueForIndex (nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
IDForValue方法返回给定值的唯一 ID (IDField) (DisplayField) ：The IDForValue method returns the unique ID (IDField) for the given value (DisplayField):
public string IDForValue (string value)
{
NSString result = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {IDField} FROM [{TableName}] WHERE {DisplayField} = @VAL";
// Populate parameters
command.Parameters.AddWithValue ("@VAL", value);
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
result = new NSString ((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return result;
}
ItemCount TableName 更改或属性时，将返回列表中的预计算项数 IDField DisplayField ：The ItemCount returns the precomputed number of items in the list as calculated when the TableName, IDField or DisplayField properties are changed:
public override nint ItemCount (NSComboBox comboBox)
{
return RecordCount;
}
ObjectValueForItem方法 DisplayField 为给定下拉列表项索引 () 提供值：The ObjectValueForItem method provides the value (DisplayField) for the given dropdown list item index:
public override NSObject ObjectValueForItem (NSComboBox comboBox, nint index)
{
NSString value = new NSString ("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
value = new NSString((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return value;
}
请注意，我们将在 LIMIT OFFSET SQLite 命令中使用和语句，以限制我们所需的一条记录。Notice that we are using the LIMIT and OFFSET statements in our SQLite command to limit to the one record that we are needed.
IndexOfItem方法返回) 给定 (值的下拉项索引 DisplayField ：The IndexOfItem method returns dropdown item index of the value (DisplayField) given:
public override nint IndexOfItem (NSComboBox comboBox, string value)
{
bool shouldClose = false;
bool found = false;
string field = "";
nint index = NSRange.NotFound;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC";
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read () && !found) {
// Read the display field from the query
field = (string)reader [0];
++index;
// Is this the value we are searching for?
if (value == field) {
// Yes, exit loop
found = true;
}
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return index;
}
如果找不到该值，则 NSRange.NotFound 返回值，并在下拉列表中取消选择所有项。If the value cannot be found, the NSRange.NotFound value is return and all items are deselected in the dropdown list.
CompletedString方法返回 DisplayField 部分类型的项 () 的第一个匹配值：The CompletedString method returns the first matching value (DisplayField) for a partially typed entry:
public override string CompletedString (NSComboBox comboBox, string uncompletedString)
{
bool shouldClose = false;
bool found = false;
string field = "";
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "") {
// Is the database already open?
if (Conn.State != ConnectionState.Open) {
shouldClose = true;
Conn.Open ();
}
// Escape search string
uncompletedString = uncompletedString.Replace ("'", "");
// Execute query
using (var command = Conn.CreateCommand ()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] WHERE {DisplayField} LIKE @VAL";
// Populate parameters
command.Parameters.AddWithValue ("@VAL", uncompletedString + "%");
// Get the results from the database
using (var reader = command.ExecuteReader ()) {
while (reader.Read ()) {
// Read the display field from the query
field = (string)reader [0];
}
}
}
// Should we close the connection to the database
if (shouldClose) {
Conn.Close ();
}
}
// Return results
return field;
}
显示数据和响应事件Displaying data and responding to events
若要将所有部分组合在一起，请编辑， SubviewSimpleBindingController 并使其看起来如下所示：To bring all of the pieces together, edit the SubviewSimpleBindingController and make it look like the following:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;
using Foundation;
using AppKit;
namespace MacDatabase
{
public partial class SubviewSimpleBindingController : AppKit.NSViewController
{
#region Private Variables
private PersonModel _person = new PersonModel();
private SqliteConnection Conn;
#endregion
#region Computed Properties
//strongly typed view accessor
public new SubviewSimpleBinding View {
get {
return (SubviewSimpleBinding)base.View;
}
}
[Export("Person")]
public PersonModel Person {
get {return _person; }
set {
WillChangeValue ("Person");
_person = value;
DidChangeValue ("Person");
}
}
public ComboBoxDataSource DataSource {
get { return EmployeeSelector.DataSource as ComboBoxDataSource; }
}
#endregion
#region Constructors
// Called when created from unmanaged code
public SubviewSimpleBindingController (IntPtr handle) : base (handle)
{
Initialize ();
}
// Called when created directly from a XIB file
[Export ("initWithCoder:")]
public SubviewSimpleBindingController (NSCoder coder) : base (coder)
{
Initialize ();
}
// Call to load from the XIB/NIB file
public SubviewSimpleBindingController (SqliteConnection conn) : base ("SubviewSimpleBinding", NSBundle.MainBundle)
{
// Initialize
this.Conn = conn;
Initialize ();
}
// Shared initialization code
void Initialize ()
{
}
#endregion
#region Private Methods
private void LoadSelectedPerson (string id)
{
// Found?
if (id != "") {
// Yes, load requested record
Person = new PersonModel (Conn, id);
}
}
#endregion
#region Override Methods
public override void AwakeFromNib ()
{
base.AwakeFromNib ();
// Configure Employee selector dropdown
EmployeeSelector.DataSource = new ComboBoxDataSource (Conn, "People", "Name");
// Wireup events
EmployeeSelector.Changed += (sender, e) => {
// Get ID
var id = DataSource.IDForValue (EmployeeSelector.StringValue);
LoadSelectedPerson (id);
};
EmployeeSelector.SelectionChanged += (sender, e) => {
// Get ID
var id = DataSource.IDForIndex (EmployeeSelector.SelectedIndex);
LoadSelectedPerson (id);
};
// Auto select the first person
EmployeeSelector.StringValue = DataSource.ValueForIndex (0);
Person = new PersonModel (Conn, DataSource.IDForIndex(0));
}
#endregion
}
}
DataSource属性提供了在 ComboBoxDataSource 附加到组合框) 上面创建的 (的快捷方式。The DataSource property provides a shortcut to the ComboBoxDataSource (created above) attached to the Combo Box.
LoadSelectedPerson方法从数据库中为给定的唯一 ID 加载人员：The LoadSelectedPerson method loads the person from the database for the given Unique ID:
private void LoadSelectedPerson (string id)
{
// Found?
if (id != "") {
// Yes, load requested record
Person = new PersonModel (Conn, id);
}
}
在 AwakeFromNib 方法重写中，首先附加自定义组合框数据源的实例：In the AwakeFromNib method override, first we attach an instance of our custom Combo Box Data Source:
EmployeeSelector.DataSource = new ComboBoxDataSource (Conn, "People", "Name");
接下来，我们将通过查找关联的唯一 ID (来响应该组合框的文本值，如果找到该用户，则为该用户 IDField 提供数据) ：Next, we respond to the user editing the text value of the Combo Box by finding the associated unique ID (IDField) of the data presenting and loading the given person if found:
EmployeeSelector.Changed += (sender, e) => {
// Get ID
var id = DataSource.IDForValue (EmployeeSelector.StringValue);
LoadSelectedPerson (id);
};
如果用户从下拉列表中选择新项，我们还会加载新的人员：We also load a new person if the user selects a new item from the dropdown list:
EmployeeSelector.SelectionChanged += (sender, e) => {
// Get ID
var id = DataSource.IDForIndex (EmployeeSelector.SelectedIndex);
LoadSelectedPerson (id);
};
最后，通过列表中的第一项自动填充组合框和显示的人员：Finally, we auto-populate the Combo Box and displayed person with the first item in the list:
// Auto select the first person
EmployeeSelector.StringValue = DataSource.ValueForIndex (0);
Person = new PersonModel (Conn, DataSource.IDForIndex(0));
SQLite.NET ORMSQLite.NET ORM
如上所述，通过使用开源 SQLite.NET 对象关系管理器 (ORM) ，可以极大地减少读取和写入 SQLite 数据库的数据所需的代码量。As stated above, by using the open source SQLite.NET Object Relationship Manager (ORM) we can greatly reduce the amount of code required to read and write data from a SQLite database. 这可能不是在绑定数据时要执行的最佳路由，因为对象上存在键值编码和数据绑定的几个要求。This may not be the best route to take when binding data because of several of the requirements that key-value coding and data binding place on an object.
根据 SQLite.Net 网站， "SQLite 是实现自包含的无服务器的零配置 SQL 数据库引擎的软件库。SQLite 是世界上最广泛部署的数据库引擎。SQLite 的源代码位于公共域中。 "According to the SQLite.Net website, "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world. The source code for SQLite is in the public domain."
在以下部分中，我们将演示如何使用 SQLite.Net 为表视图提供数据。In the following sections, we'll show how to use SQLite.Net to provide data for a Table View.
包括 SQLite.net NuGetIncluding the SQLite.net NuGet
SQLite.NET 以 NuGet 包的形式显示在应用程序中。SQLite.NET is presented as a NuGet Package that you include in your application. 在可以使用 SQLite.NET 添加数据库支持之前，需要包含此包。Before we can add database support using SQLite.NET, we need to include this package.
执行以下操作来添加包：Do the following to add the package:
在Solution Pad中，右键单击 "包" 文件夹，然后选择 "添加包 ... "In the Solution Pad, right-click the Packages folder and select Add Packages...
SQLite.net在搜索框中输入，并选择 " sqlite-net " 条目：Enter SQLite.net in the Search Box and select the sqlite-net entry:
单击 " 添加包 " 按钮完成操作。Click the Add Package button to finish.
创建数据模型Creating the data model
让我们将一个新类添加到项目中，并在中调用 OccupationModel 。Let's add a new class to the project and call in OccupationModel. 接下来，让我们编辑 OccupationModel.cs 文件并使其类似于以下内容：Next, let's edit the OccupationModel.cs file and make it look like the following:
using System;
using SQLite;
namespace MacDatabase
{
public class OccupationModel
{
#region Computed Properties
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
public string Name { get; set;}
public string Description { get; set;}
#endregion
#region Constructors
public OccupationModel ()
{
}
public OccupationModel (string name, string description)
{
// Initialize
this.Name = name;
this.Description = description;
}
#endregion
}
}
首先，我们包括 SQLite.NET (using Sqlite) ，然后公开多个属性，每个属性都将在保存此记录时写入数据库。First, we include SQLite.NET (using Sqlite), then we expose several Properties, each of which will be written to the database when this record is saved. 作为主键的第一个属性，并将其设置为自动递增，如下所示：The first property we make as the primary key and set it to auto increment as follows:
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
正在初始化数据库Initializing the database
对数据模型进行了更改，以便支持读取和写入数据库时，需要打开到数据库的连接，并在首次运行时对其进行初始化。With the changes to our Data Model in place to support reading and writing to the database, we need to open a connection to the database and initialize it on the first run. 接下来，添加以下代码：Let's add the following code:
using SQLite;
...
public SQLiteConnection Conn { get; set; }
...
private SQLiteConnection GetDatabaseConnection() {
var documents = Environment.GetFolderPath (Environment.SpecialFolder.Desktop);
string db = Path.Combine (documents, "Occupation.db3");
OccupationModel Occupation;
// Create the database if it doesn't already exist
bool exists = File.Exists (db);
// Create connection to database
var conn = new SQLiteConnection (db);
// Initially populate table?
if (!exists) {
// Yes, build table
conn.CreateTable ();
// Add occupations
Occupation = new OccupationModel ("Documentation Manager", "Manages the Documentation Group");
conn.Insert (Occupation);
Occupation = new OccupationModel ("Technical Writer", "Writes technical documentation and sample applications");
conn.Insert (Occupation);
Occupation = new OccupationModel ("Web & Infrastructure", "Creates and maintains the websites that drive documentation");
conn.Insert (Occupation);
Occupation = new OccupationModel ("API Documentation Manager", "Manages the API Documentation Group");
conn.Insert (Occupation);
Occupation = new OccupationModel ("API Documenter", "Creates and maintains API documentation");
conn.Insert (Occupation);
}
return conn;
}
首先，在这种情况下，我们会获取数据库 (数据库的路径，) 并查看数据库是否已经存在：First, we get a path to the database (the User's Desktop in this case) and see if the database already exists:
var documents = Environment.GetFolderPath (Environment.SpecialFolder.Desktop);
string db = Path.Combine (documents, "Occupation.db3");
OccupationModel Occupation;
// Create the database if it doesn't already exist
bool exists = File.Exists (db);
接下来，我们在前面创建的路径中建立与数据库的连接：Next, we establish a connection to the database at the path we created above:
var conn = new SQLiteConnection (db);
最后，我们创建表并添加一些默认记录：Finally, we create the table and add some default records:
// Yes, build table
conn.CreateTable ();
// Add occupations
Occupation = new OccupationModel ("Documentation Manager", "Manages the Documentation Group");
conn.Insert (Occupation);
Occupation = new OccupationModel ("Technical Writer", "Writes technical documentation and sample applications");
conn.Insert (Occupation);
Occupation = new OccupationModel ("Web & Infrastructure", "Creates and maintains the websites that drive documentation");
conn.Insert (Occupation);
Occupation = new OccupationModel ("API Documentation Manager", "Manages the API Documentation Group");
conn.Insert (Occupation);
Occupation = new OccupationModel ("API Documenter", "Creates and maintains API documentation");
conn.Insert (Occupation);
添加表视图Adding a table view
例如，我们将在 Xcode 的 Interface builder 中向用户界面添加表视图。As an example usage, we'll add a Table View to our UI in Xcode's Interface builder. 我们将通过) (的插座公开此表格视图 OccupationTable ，以便我们可以通过 c # 代码进行访问：We'll expose this Table View via an outlet (OccupationTable) so we can access it via C# code:
接下来，我们将添加自定义类，以便用 SQLite.NET 数据库中的数据填充此表。Next, we'll add the custom classes to populate this table with data from the SQLite.NET database.
创建表数据源Creating the table data source
让我们创建一个自定义数据源，以便为我们的表提供数据。Let's create a custom Data Source to provide data for our table. 首先，添加一个名为的新类 TableORMDatasource ，使其看起来如下所示：First, add a new class called TableORMDatasource and make it look like the following:
using System;
using AppKit;
using CoreGraphics;
using Foundation;
using System.Collections;
using System.Collections.Generic;
using SQLite;
namespace MacDatabase
{
public class TableORMDatasource : NSTableViewDataSource
{
#region Computed Properties
public List Occupations { get; set;} = new List();
public SQLiteConnection Conn { get; set; }
#endregion
#region Constructors
public TableORMDatasource (SQLiteConnection conn)
{
// Initialize
this.Conn = conn;
LoadOccupations ();
}
#endregion
#region Public Methods
public void LoadOccupations() {
// Get occupations from database
var query = Conn.Table ();
// Copy into table collection
Occupations.Clear ();
foreach (OccupationModel occupation in query) {
Occupations.Add (occupation);
}
}
#endregion
#region Override Methods
public override nint GetRowCount (NSTableView tableView)
{
return Occupations.Count;
}
#endregion
}
}
以后创建此类的实例时，我们将传入开放式 SQLite.NET 数据库连接。When we create an instance of this class later, we'll pass in our open SQLite.NET database connection. LoadOccupations方法使用我们的 OccupationModel 数据模型) 查询数据库，并将找到的记录复制到内存 (。The LoadOccupations method queries the database and copies the found records into memory (using our OccupationModel data model).
创建表委托Creating the table delegate
我们需要的最终类是一个自定义表委托，用于显示从 SQLite.NET 数据库加载的信息。The final class we need is a custom Table Delegate to display the information that we have loaded from the SQLite.NET database. 让我们在项目中添加一个新的 TableORMDelegate ，并使其看起来如下所示：Let's add a new TableORMDelegate to our project and make it look like the following:
using System;
using AppKit;
using CoreGraphics;
using Foundation;
using System.Collections;
using System.Collections.Generic;
using SQLite;
namespace MacDatabase
{
public class TableORMDelegate : NSTableViewDelegate
{
#region Constants
private const string CellIdentifier = "OccCell";
#endregion
#region Private Variables
private TableORMDatasource DataSource;
#endregion
#region Constructors
public TableORMDelegate (TableORMDatasource dataSource)
{
// Initialize
this.DataSource = dataSource;
}
#endregion
#region Override Methods
public override NSView GetViewForItem (NSTableView tableView, NSTableColumn tableColumn, nint row)
{
// This pattern allows you reuse existing views when they are no-longer in use.
// If the returned view is null, you instance up a new view
// If a non-null view is returned, you modify it enough to reflect the new data
NSTextField view = (NSTextField)tableView.MakeView (CellIdentifier, this);
if (view == null) {
view = new NSTextField ();
view.Identifier = CellIdentifier;
view.BackgroundColor = NSColor.Clear;
view.Bordered = false;
view.Selectable = false;
view.Editable = false;
}
// Setup view based on the column selected
switch (tableColumn.Title) {
case "Occupation":
view.StringValue = DataSource.Occupations [(int)row].Name;
break;
case "Description":
view.StringValue = DataSource.Occupations [(int)row].Description;
break;
}
return view;
}
#endregion
}
}
这里，我们使用数据源的 Occupations 集合 (从 SQLite.NET 数据库加载) ，通过方法重写来填充表的列 GetViewForItem 。Here we use the Data Source's Occupations collection (that we loaded from the SQLite.NET database) to fill in the columns of our table via the GetViewForItem method override.
填充表Populating the table
使用所有片段后，让我们通过重写 AwakeFromNib 方法并使其看起来类似于以下内容来填充表：从 xib 文件中放大。With all of the pieces in place, let's populate our table when it is inflated from the .xib file by overriding the AwakeFromNib method and making it look like the following:
public override void AwakeFromNib ()
{
base.AwakeFromNib ();
// Get database connection
Conn = GetDatabaseConnection ();
// Create the Occupation Table Data Source and populate it
var DataSource = new TableORMDatasource (Conn);
// Populate the Product Table
OccupationTable.DataSource = DataSource;
OccupationTable.Delegate = new TableORMDelegate (DataSource);
}
首先，我们获取对 SQLite.NET 数据库的访问权限，并在不存在的情况下创建并填充它。First, we gain access to our SQLite.NET database, creating and populating it if it doesn't already exist. 接下来，我们将创建自定义表数据源的新实例，传入数据库连接，并将其附加到该表。Next, we create a new instance of our custom Table Data Source, pass in our database connection and we attach it to the Table. 最后，我们创建一个新的自定义表委托实例，传入我们的数据源并将其附加到该表。Finally, we create a new instance of our custom Table Delegate, pass in our Data Source and attach it to the table.
总结Summary
本文详细介绍了如何在 Xamarin 应用程序中使用 SQLite 数据库的数据绑定和键/值编码。This article has taken a detailed look at working with data binding and key-value coding with SQLite databases in a Xamarin.Mac application. 首先，它介绍了如何使用键/值编码向目标-C 公开 c # 类 (KVC) 和键-值观察 (KVO) 。First, it looked at exposing a C# class to Objective-C by using key-value coding (KVC) and key-value observing (KVO). 接下来，该示例演示如何使用 KVO 兼容类并将其绑定到 Xcode 的 Interface Builder 中的 UI 元素。Next, it showed how to use a KVO compliant class and Data Bind it to UI elements in Xcode's Interface Builder. 本文还介绍了如何通过 SQLite.NET ORM 使用 SQLite 数据并在表视图中显示这些数据。The article also covered working with SQLite data via the SQLite.NET ORM and displaying that data in a Table View.
相关链接Related Links
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
