xamarin和mysql_Xamarin 中的数据库

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 =